ClickHouse MCP and the Limits of Read-Only: Structured Access for Agents

Nearly every tutorial on wiring an AI agent to a database finishes with one reassurance: use a read-only connection. Turn on readonly=1, grant only SELECT, and the agent can inspect data but never alter it. That's reasonable, but it's the answer to a question that wasn't the real worry.

ClickHouse recently benchmarked 28 models on 201 genuine questions drawn from its internal analytics agent. The top model was right 76.6% of the time. Few of the wrong answers were failed queries. Most were queries that ran without complaint and returned an incorrect figure, and a read-only connection can't prevent a single one.

Read-only decides whether an agent can write. It says nothing about what the agent can ask. Handing a model a SQL tool delegates query authorship: the agent picks the tables, the joins, the grain of aggregation, the filters and the number of rows returned. Read-only touches none of that. It keeps your data from being destroyed and does nothing to keep it from being wrong. The axis that matters isn't read versus write. It's arbitrary versus structured.

What the official ClickHouse MCP server does

The official server, ClickHouse/mcp-clickhouse, ships with list_databases, list_tables and run_query. By default it can only read. Enabling writes takes an environment variable, and dangerous statements like DROP need yet another one. For HTTP and SSE transports, authentication is mandatory out of the box (bearer tokens and OAuth/OIDC both work), and startup fails until it's set up. The documentation advises treating the MCP database account like any outside client with minimal privileges.

Two details deserve attention. The query timeout is enforced on the client side, so the MCP server stops waiting, but the query keeps running on your cluster. Also, readonly=1 applies per query and promises little. Connect as a ClickHouse user with a loose profile and wide grants, and that flag protects nothing.

Even with everything configured perfectly, a locked-down read-only connection still leaves an agent composing arbitrary SQL against your warehouse. That's the actual problem.

The failure no setting fixes

Correctness comes first, because no ClickHouse option helps with it. Giving an agent SQL assumes models have become good at writing it. That's half true, and the false half is the dangerous one.

The best evidence comes from ClickHouse itself. Its Agentic Analytics Benchmark, published on 1 September 2026, replayed 201 questions from the production traffic of its internal analytics assistant against a synthetic reconstruction of its own warehouse. The top model scored 76.6%.

Those questions were asked by employees of the company, with someone who knows the domain reading each result. When about a quarter of answers are off, a person in the loop will catch most of the consequential ones. Move the same model behind a customer-facing feature and nobody can sanity-check the output or even suspects it might be wrong. The accuracy is the same, and the risk is far greater.

The distribution of failures is more telling than the top score. Runtime errors have all but vanished, with just four of 28 models producing any. Across all models the biggest cause of failure was the plan: picking the wrong approach explained 53% to 82% of misses. ClickHouse's own reading is that syntax and execution are solved, and what counts now is data modeling and schema context.

The top performer illustrates it. Fable 5.1 showed the smallest wrong-plan share of the leading ten, yet the worst rate of wrong-data failures overall. It builds a good plan and then selects the wrong field or the wrong grain. Neither is something a smarter model fixes. Both disappear when the available dimensions, measures and time grains are predefined.

That's why these errors are dangerous, not just irritating. A wrong plan that executes cleanly yields a query that parses, runs, returns rows and produces a plausible-looking number. There's no exception and no red line in a log. Someone reads it in a dashboard or a Slack summary and acts on it.

ClickHouse itself names a caveat. The reference answers were produced when three models from different vendors agreed, not by human audit; only about half of those agreements were unanimous, and the judging panel is called lenient. Consensus isn't proof, so 76.6% is uncertain and likely a touch high. The case here depends on what the errors look like, not on the exact percentage.

ClickHouse also makes things harder than many databases. Joins are costly and tricky, its documentation advises denormalizing and limiting how many joins you use, and for most join algorithms the right-hand table sits in memory as a hash table. With a dictionary-based join across a one-to-many relationship, ClickHouse quietly keeps one matching row and drops the rest, so you get a smaller wrong answer and no error. An agent poking around your schema will walk into such traps with confidence. Neither readonly=1, a settings profile nor a quota helps. Only taking query authorship away from the model does.

Tenant scope as a parameter, never a boundary

Failure number two is the type that lands in a breach disclosure. In a multi-tenant product, tenant identity is the most important predicate in every query. With an arbitrary SQL tool, the agent writes that predicate into a WHERE clause composed at runtime, so the model can omit it, get it wrong or be talked out of it by content it read earlier in the conversation.

A deeper structural problem lurks here. Give an MCP server a connection string and it logs in as a single ClickHouse user bound to a single set of row policies, with no notion of which customer is behind each request. That knowledge lives in your application, in the authenticated session, and a generic SQL tool has lost it before the agent writes a line.

Discovery hands over the schema

The third problem is subtler. To query a warehouse, an agent first has to see it, so it calls list_tables, which hands back every table with its columns, row counts and complete CREATE TABLE statement. Column names, your business's internal vocabulary and tables you'd forgotten existed all land in a model's context, then flow into the logs, traces and external inference providers beyond it. This isn't a theoretical leak, since it's the required first step. Allowing arbitrary SQL and exposing the full schema are one and the same decision.

Structured access

A tighter SQL tool isn't the alternative. Having none is. Write down your analytics vocabulary one time, namely dimensions, measures, named metrics, tenant rules and how time works, and offer the agent only that. The model picks from a catalog of allowed questions and never writes a query.

@hypequery/mcp, a library for this, offers four tools:

• list_datasets: registered datasets with counts of their dimensions, measures and metrics.
• get_dataset_schema: the fields, named metrics, filters and limits of one dataset.
• query_metric: runs one named metric.
• query_dataset: an ad hoc query over chosen dimensions and measures.

There's no run_sql, not even a restricted one. A call looks like this:

{
  "name": "query_metric",
  "arguments": {
    "dataset": "orders",
    "metric": "revenue",
    "dimensions": ["country"],
    "filters": [
      { "field": "status", "operator": "eq", "value": "completed" }
    ],
    "orderBy": [{ "field": "revenue", "direction": "desc" }],
  }
}

The agent selected a metric, a grouping and a filter. It didn't decide what revenue means. That definition sits in your codebase, in TypeScript, reviewed and versioned like everything else, and your API and dashboard use the same one. Filter operators and time grains come from fixed sets, and fields are validated before anything runs. The registry is the boundary:

export const datasets = {
  orders: {
    ...Orders,
    metrics: { revenue },
  },
};

A dataset absent from that object can't be listed, introspected or queried, so the set of questions an agent can ask becomes a reviewable object in your repository. Tenant scope also stops being something the model supplies:

await createMCPServer({
  datasets,
  analytics,
  tenantId: 'tenant_123',
});

That value comes from the host process, which already knows who's authenticated. Register a tenant-keyed dataset without supplying it and the server refuses to start:

MCP server tenantId is required for tenant-scoped datasets

Generated SQL and SQL-backed field expressions are also stripped from responses by default, so the model sees numbers and field names and not your query structure.

A word on cost

Cost is a side issue. The official server forwards only readonly to the client, with no max_rows_to_read, max_bytes_to_read or max_memory_usage, and no server-enforced execution timeout. On usage-billed compute, an agent retrying full-table scans in a loop is a billing incident, and a bounded set of queries limits the damage. But LIMIT restricts rows returned, not bytes scanned, and a grouped query across a long time range still reads all it needs. Structured access limits which queries exist, not what each one costs. Whatever you pick, put real caps on rows, bytes, memory and execution time in a settings profile for the MCP user, and add a quota.

What it doesn't solve

None of this is new. Simon Willison's lethal trifecta and the 2025 Supabase MCP demonstration both argued that read-only isn't a security boundary, and Cube and dbt have long pushed the semantic layer for agents.

The honest trade-off is flexibility. A structured surface answers only what someone modeled, so a question outside the catalog gets nothing where an agent with SQL might have improvised something useful. It doesn't close everything else either. Dataset labels and descriptions reach the model and shape its behavior, a narrower version of the tool-poisoning surface and not an eliminated one, though those strings sit in reviewed TypeScript and not on a third-party server. And a governed metric won't stop a model from misreading a correct number in its summary.

Starting small

Modeling the whole warehouse isn't required. Pick one metric your product depends on, the one where a wrong answer would hurt, define it once, register it and give the agent that alone. Then compare what the agent returns with what your dashboard shows. If the numbers match, the definition now lives somewhere both read from, and that's the whole idea.