Split execute_query into Read and Write tools

[BorisTyshkevich]

Summary

Introduce two distinct SQL execution tools in altinity-mcp:

1. read_query — allows safe, read-only statements (e.g., SELECT, DESCRIBE, EXPLAIN, SHOW).
2. write_query — allows mutating/DDL/DCL statements (e.g., INSERT, ALTER, OPTIMIZE, TRUNCATE, CREATE, DROP, RENAME, ATTACH, DETACH, SYSTEM commands where applicable).

When the server is started with read-only enabled, only read_query should be announced to hosts (MCP capability exchange and OpenAPI). This preserves current safe defaults while enabling explicit opt‑in for state‑changing operations.

Related: Issue #26 (tool split request) — #26

Motivation

• Reduce confirmation friction in UIs: Many hosts prompt for extra confirmation when a tool is marked consequential. Splitting read vs write lets read_query be non‑consequential while write_query remains consequential, reducing prompt fatigue for safe selects while preserving caution for writes.
• Better principle of least privilege: Environments frequently want browsing/analysis without risk of mutations. A dedicated read‑only tool that’s the only one advertised under --read-only matches this need.
• Closer parity with ecosystem: Other DB MCP servers expose explicit read‑only tools or separate read/write operations. Clear separation improves portability for users/gpts across servers.
• Operational safety: Clear auditing and allow‑list controls can be applied per tool. For example, hosts can disable or hide write tools in sensitive contexts while still enabling reads.

Prior art & current behavior

• Current altinity-mcp exposes a single execute_query tool that runs any SQL, with a global --read-only switch in the CLI and config (see README), and OpenAPI action /openapi/execute_query.
• ClickHouse/mcp-clickhouse ships a dedicated run_select_query tool and explicitly enforces readonly = 1 on queries, offering a clean read‑only surface.
• StarRocks/mcp-server-starrocks advertises separate read_query and write_query tools in its README, illustrating the value of split responsibilities.

Proposal

1. Introduce two tools

   • read_query — non‑consequential; intended for SELECT, DESCRIBE, EXPLAIN, SHOW.
   • write_query — consequential; intended for INSERT, ALTER, CREATE, DROP, TRUNCATE, RENAME, OPTIMIZE, SYSTEM, etc.

2. Conditional tool announcement

   • If --read-only (or clickhouse.read_only: true) is set, do not advertise write_query in the MCP capabilities nor in OpenAPI. Only advertise read_query.
   • If not read‑only, advertise both tools.

3. Server‑side validation

   • Enforce a whitelist of allowed statements for read_query using one or more strategies:

     • Set ClickHouse session readonly = 1 and fail on disallowed statements.
     • Perform a quick SQL verb check (first non‑comment token).

   • For write_query, run with the configured privileges and keep is_consequential: true in tool schema.

4. OpenAPI

   • Create two endpoints mirroring the tools: /openapi/read_query and /openapi/write_query.
   • Respect JWE token path‑scoping exactly as current /openapi/execute_query does.

5. Backwards compatibility

   • Keep existing /openapi/execute_query and execute_query tool enabled by default for one minor release, but mark as deprecated in README and server logs. Optionally make it an alias to read_query in read‑only mode.
   • Provide a server flag to disable legacy tool and endpoint early for security‑strict environments.

Proposed tool JSON definitions

read_query (non‑consequential)

{
  "name": "read_query",
  "description": "Execute a read-only SQL statement (SELECT/DESCRIBE/EXPLAIN/SHOW) against ClickHouse and return the results.",
  "is_consequential": false,
  "params": {
    "type": "object",
    "properties": {
      "query": {
        "type": "string",
        "description": "Read-only SQL (SELECT, DESCRIBE, EXPLAIN, SHOW)"
      },
    "required": ["query"]
  },
  "return_type": {
    "type": "object",
    "properties": {
      "content": {"type": "array"},
      "structuredContent": {"type": ["object", "null"]},
      "isError": {"type": "boolean"}
    },
    "required": ["content"]
  },
  "supported_auth": [{"type": "NONE"}],
  "is_read_only": true,
  "is_open_world": true,
  "is_destructive": false
}

write_query (consequential)

{
  "name": "write_query",
  "description": "Execute insert, or mutation, or DDL/DCL SQL statement (INSERT/ALTER/CREATE/DROP/OPTIMIZE/TRUNCATE/etc.) against ClickHouse.",
  "is_consequential": true,
  "params": {
    "type": "object",
    "properties": {
      "query": {
        "type": "string",
        "description": "Write/DDL/DCL SQL. Explicitly disallowed when server runs with read-only enabled."
      }
    },
    "required": ["query"]
  },
  "return_type": {
    "type": "object",
    "properties": {
      "content": {"type": "array"},
      "structuredContent": {"type": ["object", "null"]},
      "isError": {"type": "boolean"}
    },
    "required": ["content"]
  },
  "supported_auth": [{"type": "NONE"}],
  "is_read_only": false,
  "is_open_world": true,
  "is_destructive": true
}

OpenAPI additions

• GET /{jwe_token}/openapi/read_query?query=... — always present.
• GET /{jwe_token}/openapi/write_query?query=... — omitted when server is read‑only.

Implementation sketch

• Session/statement gating:

  • For read_query, set session readonly=1 and fail fast on non‑read statements. Optionally pre‑check the first token and return a clear error like: “Statement type not allowed in read‑only mode; use write_query and ensure the server is not read‑only.”
  • For write_query, require server not in read‑only; otherwise return HTTP 403 / tool error.

• Telemetry & audit: Add a distinct action name in logs/metrics for read vs write. Consider emitting a summary of affected objects for write operations (e.g., target database/table).

• Docs: Update README sections Available Tools, OpenAPI, and CLI Reference to document split behavior and the --read-only interplay.

Acceptance criteria

• With --read-only enabled, hosts only see: discovery tools, resources, prompts, and read_query; any attempt to call write_query returns a clear error.
• With --read-only disabled, both tools are advertised; read_query rejects mutating statements, and write_query succeeds for permitted statements.
• OpenAPI reflects the same conditional availability.
• README updated with examples and security notes.

Benefits

• Less UI confirmation friction for harmless queries, improving UX in ChatGPT/Agents and similar hosts.
• Clearer mental model and safer defaults, while preserving a powerful path for administrators and power users.
• Aligns altinity-mcp with other database MCP servers that separate read and write responsibilities.

References

• Altinity MCP README (tools, CLI --read-only, OpenAPI): https://github.com/Altinity/altinity-mcp
• ClickHouse official MCP server (read-only tool run_select_query): https://github.com/ClickHouse/mcp-clickhouse
• StarRocks MCP server (separate read_query and write_query tools): https://github.com/StarRocks/mcp-server-starrocks
• Issue describe query return OK instead of data #26: describe query return OK instead of data #26