8000 fix: block OPTIMIZE DEDUPLICATE and MODIFY/MATERIALIZE TTL without CLICKHOUSE_ALLOW_DROP by bishalde · Pull Request #260 · ClickHouse/mcp-clickhouse · GitHub
[go: up one dir, main page]

Skip to content
Merged
Show file tree
Hide file tree
Changes from all commits
Commits
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
4 changes: 4 additions & 0 deletions CHANGELOG.md
Original file line number Diff line number Diff line change
Expand Up @@ -14,6 +14,10 @@ All notable changes to this project will be documented in this file.
- Locked development and container environments now use clickhouse-connect 1.11.0, Pydantic 2.14.1, and cffi 2.1.1.
- The missing-chDB warning on Python 3.15 explains that the extra skips installation and points to Python 3.10 through 3.14.

### Fixed

- The destructive-operation guard now blocks `OPTIMIZE ... DEDUPLICATE`, `ALTER TABLE ... MODIFY TTL` / `MATERIALIZE TTL`, and `MODIFY/ALTER COLUMN` with `TTL` when `CLICKHOUSE_ALLOW_DROP` is unset. These could delete rows or reset expired column values with only write access enabled. This also blocks `MODIFY COLUMN c REMOVE TTL`. ([#259](https://github.com/ClickHouse/mcp-clickhouse/issues/259))

## 0.7.0 - 2026-09-18

### Added
Expand Down
4 changes: 2 additions & 2 deletions README.md
Original file line number Diff line number Diff line change
Expand Up @@ -438,7 +438,7 @@ Read-only mode still permits the ClickHouse user to cancel its own queries, incl

### Destructive Operation Protection

Even when write access is enabled (`CLICKHOUSE_ALLOW_WRITE_ACCESS=true`), destructive operations require an additional opt-in flag for safety. The check covers any `DROP` statement (including the `ALTER TABLE ... DROP PARTITION` / `DROP PART` / `DROP COLUMN` clauses), any `TRUNCATE`, `DELETE` and `UPDATE` (both the lightweight statements and the `ALTER TABLE ... DELETE` / `ALTER TABLE ... UPDATE` mutations), `REPLACE TABLE`, `CREATE OR REPLACE`, `ALTER TABLE ... REPLACE PARTITION`, `ALTER TABLE ... CLEAR COLUMN` / `CLEAR INDEX` / `CLEAR PROJECTION`, and `DETACH ... PERMANENTLY`. Keywords inside string literals, quoted identifiers, SQL comments, and `{name:Type}` parameter names are ignored, so they neither trigger the check nor hide a statement from it.
Even when write access is enabled (`CLICKHOUSE_ALLOW_WRITE_ACCESS=true`), destructive operations require an additional opt-in flag for safety. The check covers any `DROP` statement (including the `ALTER TABLE ... DROP PARTITION` / `DROP PART` / `DROP COLUMN` clauses), any `TRUNCATE`, `DELETE` and `UPDATE` (both the lightweight statements and the `ALTER TABLE ... DELETE` / `ALTER TABLE ... UPDATE` mutations), `REPLACE TABLE`, `CREATE OR REPLACE`, `ALTER TABLE ... REPLACE PARTITION`, `ALTER TABLE ... CLEAR COLUMN` / `CLEAR INDEX` / `CLEAR PROJECTION`, `OPTIMIZE ... DEDUPLICATE`, `ALTER TABLE ... MODIFY TTL` / `MATERIALIZE TTL`, `MODIFY/ALTER COLUMN` with `TTL`, and `DETACH ... PERMANENTLY`. This also blocks `MODIFY COLUMN c REMOVE TTL`. Keywords inside string literals, quoted identifiers, SQL comments, and `{name:Type}` parameter names are ignored, so they neither trigger the check nor hide a statement from it.

This check runs in the MCP server and is a best-effort guard against accidents. It is not a security boundary. The security boundary is the ClickHouse user's grants. Read-only mode (the default) is enforced server-side via `readonly=1`. The destructive-operation gate is not server-enforced.

Expand Down Expand Up @@ -827,7 +827,7 @@ mcp-clickhouse requires clickhouse-connect 1.x, starting with 1.0.0.
* Default: `"false"`
* Set to `"true"` to allow non-destructive DDL and DML (CREATE, INSERT, ALTER ADD COLUMN). Destructive statements additionally need `CLICKHOUSE_ALLOW_DROP=true`
* When disabled (default), queries run with `readonly=1` setting to prevent data modifications
* `CLICKHOUSE_ALLOW_DROP`: Allow destructive operations (any `DROP` or `TRUNCATE`, `DELETE` and `UPDATE` including the `ALTER TABLE` variants, `REPLACE TABLE` / `REPLACE PARTITION` / `CREATE OR REPLACE`, `CLEAR COLUMN` / `CLEAR INDEX` / `CLEAR PROJECTION`, and `DETACH ... PERMANENTLY`)
* `CLICKHOUSE_ALLOW_DROP`: Allow destructive operations (any `DROP` or `TRUNCATE`, `DELETE` and `UPDATE` including the `ALTER TABLE` variants, `REPLACE TABLE` / `REPLACE PARTITION` / `CREATE OR REPLACE`, `CLEAR COLUMN` / `CLEAR INDEX` / `CLEAR PROJECTION`, `OPTIMIZE ... DEDUPLICATE`, `MODIFY TTL` / `MATERIALIZE TTL`, `MODIFY/ALTER COLUMN` with `TTL` including `MODIFY COLUMN c REMOVE TTL`, and `DETACH ... PERMANENTLY`)
* Default: `"false"`
* Only takes effect when `CLICKHOUSE_ALLOW_WRITE_ACCESS=true` is also set
* This gate is a best-effort accident guard in the MCP server, not a security boundary. Restrict the ClickHouse user's grants for real enforcement (see [Destructive Operation Protection](#destructive-operation-protection))
Expand Down
3 changes: 2 additions & 1 deletion mcp_clickhouse/mcp_server.py
Original file line number Diff line number Diff line change
Expand Up @@ -152,7 +152,8 @@ def _register_chdb_tools():
"Set CLICKHOUSE_ALLOW_WRITE_ACCESS=true to allow DDL and DML operations. "
"Set CLICKHOUSE_ALLOW_DROP=true to additionally allow destructive operations "
"(DROP, TRUNCATE, DELETE, UPDATE, REPLACE TABLE/PARTITION, CREATE OR REPLACE, "
"CLEAR COLUMN/INDEX/PROJECTION, DETACH PERMANENTLY). That gate is a best-effort "
"CLEAR COLUMN/INDEX/PROJECTION, OPTIMIZE DEDUPLICATE, MODIFY/MATERIALIZE TTL, "
"MODIFY/ALTER COLUMN with TTL, DETACH PERMANENTLY). That gate is a best-effort "
"accident guard, not a security boundary. Integers outside "
"[-9007199254740991, 9007199254740991] are returned as decimal strings. "
"Two optional checks also run through this tool. Use DESCRIBE (<query>) when "
Expand Down
17 changes: 14 additions & 3 deletions mcp_clickhouse/queries.py
Original file line number Diff line number Diff line change
Expand Up @@ -48,7 +48,10 @@ class _ActiveQueryState:
# parenthesis is the rounding function, as is replace() after OR. Bare DELETE
# and UPDATE cover both the lightweight and ALTER mutation forms. REPLACE
# TABLE/PARTITION and OR REPLACE overwrite existing data. Bare CLEAR is
# reversible, so only CLEAR COLUMN/INDEX/PROJECTION is flagged.
# reversible, so only CLEAR COLUMN/INDEX/PROJECTION is flagged. OPTIMIZE ...
# DEDUPLICATE removes duplicate rows. MODIFY TTL or MATERIALIZE TTL can delete
# expired rows, and MODIFY/ALTER COLUMN with TTL can reset expired column values.
# A TTL in CREATE TABLE or ADD COLUMN stays allowed.
_DESTRUCTIVE_KEYWORDS = re.compile(
r"""
\bDROP\b
Expand All @@ -58,6 +61,8 @@ class _ActiveQueryState:
| \bREPLACE\s+(?:TABLE|PARTITION)\b
| \bOR\s+REPLACE\b(?!\s*\()
| \bCLEAR\s+(?:COLUMN|INDEX|PROJECTION)\b
| \bDEDUPLICATE\b
| \b(?:MODIFY|MATERIALIZE)\s+TTL\b
""",
re.IGNORECASE | re.VERBOSE,
)
Expand All @@ -68,6 +73,9 @@ class _ActiveQueryState:
# Plain DETACH is reversible via ATTACH and stays allowed.
_DETACH_KEYWORD = re.compile(r"\bDETACH\b", re.IGNORECASE)
_PERMANENTLY_KEYWORD = re.compile(r"\bPERMANENTLY\b", re.IGNORECASE)
# MODIFY/ALTER COLUMN and TTL are also matched independently.
_ALTER_COLUMN_KEYWORD = re.compile(r"\b(?:MODIFY|ALTER)\s+COLUMN\b", re.IGNORECASE)
_TTL_KEYWORD = re.compile(r"\bTTL\b", re.IGNORECASE)
# Only mask the parameter name. Types and surrounding SQL remain visible.
_SQL_PARAMETER_NAME = re.compile(r"\{\s*[A-Za-z_$][A-Za-z0-9_$]*\s*:")
_SQL_PARAMETER_PREFIX = re.compile(r"\{[\w$]+:")
Expand Down Expand Up @@ -124,12 +132,15 @@ def _validate_query_for_destructive_ops(query: str) -> None:

statement = _strip_comments_and_quoted_text(query)
statement = _SQL_PARAMETER_NAME.sub(" ", statement)
if _DESTRUCTIVE_KEYWORDS.search(statement) or (
_DETACH_KEYWORD.search(statement) and _PERMANENTLY_KEYWORD.search(statement)
if (
_DESTRUCTIVE_KEYWORDS.search(statement)
or (_DETACH_KEYWORD.search(statement) and _PERMANENTLY_KEYWORD.search(statement))
or (_ALTER_COLUMN_KEYWORD.search(statement) and _TTL_KEYWORD.search(statement))
):
raise ToolError(
"Destructive operations are not allowed (DROP, TRUNCATE, DELETE, UPDATE, "
"REPLACE TABLE/PARTITION, CREATE OR REPLACE, CLEAR COLUMN/INDEX/PROJECTION, "
"OPTIMIZE DEDUPLICATE, MODIFY/MATERIALIZE TTL, MODIFY/ALTER COLUMN with TTL, "
"DETACH PERMANENTLY). Set CLICKHOUSE_ALLOW_DROP=true to enable them. "
"This gate is a best-effort accident guard, not a security boundary. "
"Restrict the ClickHouse user's grants for real enforcement."
Expand Down
4 changes: 2 additions & 2 deletions server.json
Original file line number Diff line number Diff line change
Expand Up @@ -97,7 +97,7 @@
},
{
"name": "CLICKHOUSE_ALLOW_DROP",
"description": "Allow destructive operations (DROP, TRUNCATE, DELETE, UPDATE, REPLACE, CLEAR, and DETACH PERMANENTLY) when write access is also enabled",
"description": "Allow destructive operations (DROP, TRUNCATE, DELETE, UPDATE, REPLACE, CLEAR, OPTIMIZE DEDUPLICATE, MODIFY/MATERIALIZE TTL, MODIFY/ALTER COLUMN with TTL, and DETACH PERMANENTLY) when write access is also enabled",
"format": "boolean",
"default": "false",
"isRequired": false
Expand Down Expand Up @@ -203,7 +203,7 @@
},
{
"name": "CLICKHOUSE_ALLOW_DROP",
"description": "Allow destructive operations (DROP, TRUNCATE, DELETE, UPDATE, REPLACE, CLEAR, and DETACH PERMANENTLY) when write access is also enabled",
"description": "Allow destructive operations (DROP, TRUNCATE, DELETE, UPDATE, REPLACE, CLEAR, OPTIMIZE DEDUPLICATE, MODIFY/MATERIALIZE TTL, MODIFY/ALTER COLUMN with TTL, and DETACH PERMANENTLY) when write access is also enabled",
"format": "boolean",
"default": "false",
"isRequired": false
Expand Down
37 changes: 37 additions & 0 deletions tests/test_destructive_ops.py
Original file line number Diff line number Diff line change
Expand Up @@ -66,6 +66,21 @@ def write_mode_without_drop():
"REPLACE TABLE d.t (a UInt8) ENGINE = MergeTree ORDER BY a",
"ALTER TABLE d.t REPLACE PARTITION 'p' FROM d.s",
"CREATE OR REPLACE VIEW d.v AS SELECT 1",
# DEDUPLICATE removes duplicate rows.
"OPTIMIZE TABLE d.t FINAL DEDUPLICATE",
"OPTIMIZE TABLE d.t DEDUPLICATE BY a",
# A new or materialized TTL deletes the rows it has already expired.
"ALTER TABLE d.t MODIFY TTL ts + INTERVAL 1 DAY",
"ALTER TABLE d.t MATERIALIZE TTL",
"ALTER TABLE d.t MATERIALIZE TTL IN PARTITION 'p'",
# Column TTL resets expired values.
"ALTER TABLE d.t MODIFY COLUMN c String TTL ts + INTERVAL 1 DAY",
"ALTER TABLE d.t MODIFY COLUMN c TTL ts + INTERVAL 1 DAY",
"ALTER TABLE d.t MODIFY\n/* comment */ COLUMN c String\nTTL ts + INTERVAL 1 DAY",
"ALTER TABLE d.t ALTER COLUMN c TTL ts + INTERVAL 1 DAY",
"ALTER TABLE d.t ALTER COLUMN c TYPE String TTL ts + INTERVAL 1 DAY",
# The conservative column TTL check also gates removal.
"ALTER TABLE d.t MODIFY COLUMN c REMOVE TTL",
# DETACH is only destructive with PERMANENTLY.
"DETACH TABLE d.t PERMANENTLY",
"DETACH TABLE d.t SYNC PERMANENTLY",
Expand Down Expand Up @@ -94,6 +109,12 @@ def write_mode_without_drop():
"CREATE TABLE d.t (a UInt8) ENGINE = MergeTree ORDER BY a",
"ALTER TABLE d.t ADD COLUMN b UInt8",
"INSERT INTO d.t (a) VALUES (1)",
"INSERT INTO d.t SETTINGS insert_deduplicate = 0 VALUES (1)",
"ALTER TABLE d.t MODIFY SETTING merge_with_ttl_timeout = 3600",
"ALTER TABLE d.t ADD COLUMN c String TTL ts + INTERVAL 1 DAY",
"ALTER TABLE d.t MODIFY COLUMN c UInt64",
"ALTER TABLE d.t MODIFY COLUMN ttl_seconds UInt64",
"ALTER TABLE d.t MODIFY COLUMN `ttl` UInt64",
# `truncate` is also a rounding function, `replace` a string function.
"SELECT truncate(3.7)",
"SELECT truncate(1.234, 2)",
Expand All @@ -119,6 +140,11 @@ def write_mode_without_drop():
"SELECT dropped, undropped FROM d.t",
"SELECT truncated FROM d.t",
"SELECT updated_at, deleted_at FROM d.t",
# Plain OPTIMIZE can collapse superseded Replacing engine versions but deletes no live data.
"OPTIMIZE TABLE d.t FINAL",
# A TTL on a new table has no old rows.
"CREATE TABLE d.t (ts DateTime) ENGINE = MergeTree ORDER BY ts TTL ts + INTERVAL 1 DAY",
"ALTER TABLE d.t REMOVE TTL",
# Plain DETACH is reversible with ATTACH.
"DETACH TABLE d.t",
"ATTACH TABLE d.t",
Expand Down Expand Up @@ -204,3 +230,14 @@ def test_pathological_detach_input_is_fast(write_mode_without_drop):
with pytest.raises(ToolError):
_validate_query_for_destructive_ops(pathological + "PERMANENTLY")
assert time.monotonic() - start < 3.0


def test_pathological_modify_column_input_is_fast(write_mode_without_drop):
"""Repeated MODIFY COLUMN tokens are checked in linear time."""
pathological = "MODIFY COLUMN " * 20000

start = time.monotonic()
_validate_query_for_destructive_ops(pathological)
with pytest.raises(ToolError):
_validate_query_for_destructive_ops(pathological + "TTL")
assert time.monotonic() - start < 3.0
Loading
0