Add optional write access mode with DROP protection by joe-clickhouse · Pull Request #93 · ClickHouse/mcp-clickhouse

joe-clickhouse · 2025-10-23T20:10:29Z

Summary

This PR enables LLMs to perform write operations on ClickHouse databases with built-in safety controls to prevent accidental data loss.

Write Access Mode

New CLICKHOUSE_ALLOW_WRITE_ACCESS flag (default: false) enables DDL and DML operations (INSERT, UPDATE, CREATE, ALTER, DROP)
When enabled, queries run with readonly=0 instead of the default readonly=1

Two-Tier `DROP` Protection

CLICKHOUSE_ALLOW_DROP (default: false) provides additional safety for destructive operations
Both flags must be true to allow DROP operations
Other write operations (INSERT, CREATE, etc.) only require CLICKHOUSE_ALLOW_WRITE_ACCESS=true

Usage

Enable write access:

"env": {
  "CLICKHOUSE_ALLOW_WRITE_ACCESS": "true"
}

Enable write access including DROP:

"env": {
  "CLICKHOUSE_ALLOW_WRITE_ACCESS": "true",
  "CLICKHOUSE_ALLOW_DROP": "true"
}

Closes #24

Copilot

Pull Request Overview

This PR adds optional write access capabilities to the ClickHouse MCP server with built-in safety mechanisms. By default, the server maintains read-only behavior, but can now be explicitly configured to allow write operations with DROP protection.

Key changes:

Renamed run_select_query to run_query to reflect broader SQL support
Added CLICKHOUSE_ALLOW_WRITE_ACCESS flag to enable DDL/DML operations
Added CLICKHOUSE_ALLOW_DROP flag for additional protection against destructive operations

Reviewed Changes

Copilot reviewed 6 out of 6 changed files in this pull request and generated 2 comments.

Show a summary per file

File	Description
mcp_clickhouse/mcp_env.py	Added two new configuration properties (`allow_write_access` and `allow_drop`) to control write permissions
mcp_clickhouse/mcp_server.py	Implemented DROP validation, refactored readonly setting logic, renamed query function, and added dynamic tool description
mcp_clickhouse/init.py	Updated exports to reflect function rename
tests/test_tool.py	Renamed function references and added comprehensive test coverage for write mode, DROP protection, and read-only mode
tests/test_mcp_server.py	Updated function calls to use new `run_query` name
README.md	Updated documentation to reflect new function name and write access features

_{Tip: Customize your code reviews with copilot-instructions.md. Create the file or learn how to get started.}

laeg

LGTM

Copilot

Pull request overview

Copilot reviewed 6 out of 6 changed files in this pull request and generated 3 comments.

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

Copilot · 2025-12-08T18:42:08Z

+        return
+
+    # Simple pattern matching for DROP operations
+    drop_pattern = r'\bDROP\s+(TABLE|DATABASE|VIEW|DICTIONARY)\b'


The regex pattern for DROP detection may not catch all variations. Consider ClickHouse-specific DROP operations like DROP TEMPORARY TABLE, DROP DETACHED TABLE, or DROP TABLE IF EXISTS. The pattern should include the optional TEMPORARY, DETACHED, and IF EXISTS keywords to ensure comprehensive DROP detection.

Suggested change

drop_pattern = r'\bDROP\s+(TABLE|DATABASE|VIEW|DICTIONARY)\b'

# Updated pattern to match DROP [TEMPORARY] [DETACHED] [IF EXISTS] (TABLE|DATABASE|VIEW|DICTIONARY)

drop_pattern = (

r'\bDROP\s+'

r'(TEMPORARY\s+)?'

r'(DETACHED\s+)?'

r'(IF\s+EXISTS\s+)?'

r'(TABLE|DATABASE|VIEW|DICTIONARY)\b'

)

Fair enough. Will allow arbitrary words between DROP and the object type.

Copilot · 2025-12-08T18:42:08Z

+    drop_pattern = r'\bDROP\s+(TABLE|DATABASE|VIEW|DICTIONARY)\b'
+    if re.search(drop_pattern, query, re.IGNORECASE):


The DROP detection can be bypassed using comments or string literals. For example, SELECT '/* DROP TABLE foo */' FROM bar or SELECT 'DROP TABLE' FROM foo would trigger false positives, while DR/**/OP TABLE foo could bypass detection. Consider using a proper SQL parser or at minimum stripping comments and string literals before pattern matching.

Full SQL parsing is overkill. Won't do.

Copilot · 2025-12-08T18:42:08Z

+    def setUpClass(cls):
+        """Set up the environment before tests."""
+        cls.env_patcher = patch.dict(os.environ, {"CLICKHOUSE_ALLOW_WRITE_ACCESS": "false"})
+        cls.env_patcher.start()


The environment patcher is started in setUpClass but there's a risk it won't be stopped if setUpClass raises an exception before completing. Consider using addClassCleanup() instead of relying solely on tearDownClass() to ensure cleanup happens even on setup failures.

Suggested change

cls.env_patcher.start()

cls.env_patcher.start()

cls.addClassCleanup(cls.env_patcher.stop)

Good call. Will fix.

joe-clickhouse added 2 commits October 23, 2025 12:24

implement a write mode

7c6e96e

change write access env var name

51787e3

joe-clickhouse linked an issue Oct 23, 2025 that may be closed by this pull request

Optional write access? #24

Closed

joe-clickhouse requested a review from Copilot October 23, 2025 20:10

Copilot AI reviewed Oct 23, 2025

View reviewed changes

Comment thread mcp_clickhouse/mcp_server.py Outdated

Comment thread mcp_clickhouse/mcp_server.py

joe-clickhouse added 4 commits October 23, 2025 13:36

address pr suggestions

ce1a4c7

change tool desc to be static, not run on import

0afed23

Merge branch 'main' into joe/24-optional-write-access

7263fa1

update tests to reflect list_tables change

e1f8f1f

laeg approved these changes Dec 8, 2025

View reviewed changes

laeg requested a review from Copilot December 8, 2025 18:41

Copilot AI reviewed Dec 8, 2025

View reviewed changes

joe-clickhouse added 6 commits February 18, 2026 11:50

Merge branch 'main' into joe/24-optional-write-access

12b7a80

allow validation error msg to reach user

e64661e

add truncate protection

c06824d

Merge branch 'main' into joe/24-optional-write-access

55e6925

use addClassCleanup

80ef646

allow arbitrary words between DROP and object

833cea0

joe-clickhouse merged commit d82fc87 into main Feb 23, 2026
2 checks passed

mshustov mentioned this pull request Apr 20, 2026

run_select_query executes arbitrary DDL/DML — DROP TABLE, INSERT, CREATE all succeed despite readonly default #131

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add optional write access mode with DROP protection#93

Add optional write access mode with DROP protection#93
joe-clickhouse merged 12 commits into
mainfrom
joe/24-optional-write-access

joe-clickhouse commented Oct 23, 2025

Uh oh!

Copilot AI left a comment

Uh oh!

Uh oh!

Uh oh!

laeg left a comment

Uh oh!

Copilot AI left a comment

Uh oh!

Copilot AI Dec 8, 2025

Uh oh!

joe-clickhouse Feb 19, 2026

Uh oh!

Copilot AI Dec 8, 2025

Uh oh!

joe-clickhouse Feb 19, 2026

Uh oh!

Copilot AI Dec 8, 2025

Uh oh!

joe-clickhouse Feb 19, 2026

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

-    drop_pattern = r'\bDROP\s+(TABLE|DATABASE|VIEW|DICTIONARY)\b'
+    # Updated pattern to match DROP [TEMPORARY] [DETACHED] [IF EXISTS] (TABLE|DATABASE|VIEW|DICTIONARY)
+    drop_pattern = (
+        r'\bDROP\s+'
+        r'(TEMPORARY\s+)?'
+        r'(DETACHED\s+)?'
+        r'(IF\s+EXISTS\s+)?'
+        r'(TABLE|DATABASE|VIEW|DICTIONARY)\b'
+    )

		drop_pattern = r'\bDROP\s+(TABLE\|DATABASE\|VIEW\|DICTIONARY)\b'
		if re.search(drop_pattern, query, re.IGNORECASE):

	cls.env_patcher.start()
	cls.env_patcher.start()
	cls.addClassCleanup(cls.env_patcher.stop)

Conversation

joe-clickhouse commented Oct 23, 2025

Summary

Write Access Mode

Two-Tier DROP Protection

Usage

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull Request Overview

Reviewed Changes

Uh oh!

Uh oh!

Uh oh!

laeg left a comment

Choose a reason for hiding this comment

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull request overview

Uh oh!

Copilot AI Dec 8, 2025

Choose a reason for hiding this comment

Uh oh!

joe-clickhouse Feb 19, 2026

Choose a reason for hiding this comment

Uh oh!

Copilot AI Dec 8, 2025

Choose a reason for hiding this comment

Uh oh!

joe-clickhouse Feb 19, 2026

Choose a reason for hiding this comment

Uh oh!

Copilot AI Dec 8, 2025

Choose a reason for hiding this comment

Uh oh!

joe-clickhouse Feb 19, 2026

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Two-Tier `DROP` Protection