ClickHouse MCP Server: Limits an Agent Can't Turn Off

In our test of mcp-clickhouse, the official MCP server that lets Claude or Cursor query ClickHouse, an agent on a readonly = 2 user removed its own 1-million-row cap with SETTINGS max_rows_to_read = 0. The server's default, readonly = 1 on every query, refuses that (code 164). Put readonly = 1 and your caps in the agent's ClickHouse user.

We ran the test on October 22, 2026, with ClickHouse 26.11.1.536, mcp-clickhouse 0.7.0 and a 10-million-row events table. A script played the agent. It called the server's tools over stdio the way Claude or Cursor does, so every query was one we picked, not one a model came up with. The errors quoted below are what ClickHouse sent back.

On ClickHouse, any query can carry its own SETTINGS clause, so on a user that allows SET, a limit the agent's user doesn't pin is a limit the agent can change. Our own docs say "Querio should use a dedicated read-only account whenever possible." For an agent, that account is also where the limits belong.

How the agent took its cap off

Our first profile for the agent's user set readonly = 2 CONST and capped rows read with max_rows_to_read = 1000000 MAX 1000000: a MAX, no MIN. A full scan, SELECT sum(revenue) FROM analytics.events, failed with code 158, as it should. Asking for a 100-million-row cap failed too: "Setting max_rows_to_read shouldn't be greater than 1000000." (code 452).

Then the agent asked for zero. SELECT sum(revenue) FROM analytics.events SETTINGS max_rows_to_read = 0 came back with 63125000.0, the sum over all 10 million rows. In ClickHouse, 0 means no limit, and 0 isn't greater than 1,000,000, so the MAX constraint let it through. A MAX stops the agent raising a cap. It doesn't stop it removing one.

MIN 1 closes the gap. With max_rows_to_read = 1000000 MIN 1 MAX 1000000, the same query failed: "Setting max_rows_to_read shouldn't be less than 1. (SETTING_CONSTRAINT_VIOLATION)". We ran the zero test only on max_rows_to_read. Our final profile puts MIN 1 on the time and result caps too, and setting either of those to 0 got the same 452.

The trick needs readonly = 2, because that mode allows SET. The server sends readonly = 1 with every query unless the user's profile already sets readonly; then it sends the user's value.

Use readonly = 1 for an agent

Pick readonly = 1 unless a tool needs to change settings. Per ClickHouse's permissions docs, readonly = 1 allows read queries and refuses SET, unless a changeable_in_readonly constraint opens up a setting. readonly = 2 also allows SET, CREATE TEMPORARY TABLE and RESTORE, plus most table functions that readonly = 1 refuses. RESTORE can create a table, so readonly = 2 on its own doesn't stop writes. Grants still apply.

We tested readonly = 1 with no constraints at all. SETTINGS max_rows_to_read = 0 got "Code: 164. DB::Exception: Cannot modify 'max_rows_to_read' setting in readonly mode." No MIN, MAX or CONST needed. The price is that the agent can't change any setting, which for an agent is the point.

On readonly = 2, the agent can change every setting you didn't pin. Our timeout test set max_block_size = 1 on that user and ClickHouse accepted it. Every limit on a readonly = 2 user needs MIN and MAX, or CONST.

There's a second reason to put readonly in the profile. The readonly = 1 the server sends depends on the server's config. Set CLICKHOUSE_ALLOW_WRITE_ACCESS=true and that readonly = 1 is gone: on the default user, CREATE TABLE and INSERT then went through. We turned write access on against our readonly = 1 user too, and INSERT and CREATE TABLE still failed with "Code: 164. DB::Exception: agent_ro1: Cannot execute query in readonly mode." The profile held with the server's write switch on.

The readonly = 1 profile that held

This is the agent user we'd copy: a settings profile, the user and its grants, password swapped out. These are the final settings. Our first try had max_result_rows = 1000, and that broke every call (first mistake below).

CREATE SETTINGS PROFILE agent_profile_ro1 SETTINGS
    readonly = 1,
    max_execution_time = 10,
    max_rows_to_read = 1000000,
    max_result_rows = 10000,
    result_overflow_mode = 'throw';

CREATE USER agent_ro1 IDENTIFIED WITH sha256_password BY 'STRONG_PASSWORD' SETTINGS PROFILE 'agent_profile_ro1';

GRANT SELECT ON analytics.* TO agent_ro1;
GRANT SHOW DATABASES, SHOW TABLES, SHOW COLUMNS ON *.* TO agent_ro1;

Through the server, as this user:

• A count over a 10-minute range on the table's sort key answered: 60,000 events.
• A full scan stopped: "Code: 158. DB::Exception: Limit for rows (controlled by 'max_rows_to_read' setting) exceeded, max rows: 1.00 million, current rows: 8.90 million. (TOO_MANY_ROWS)".
• An INSERT got code 497, ACCESS_DENIED, because the user has no INSERT grant.

One million rows read and 10,000 rows returned are sized to our 10-million-row test table. Set yours from the biggest query an analyst runs on purpose, or the agent fails on real work. The 10 seconds is a ClickHouse limit. The server has its own, a client-side wait, CLICKHOUSE_MCP_QUERY_TIMEOUT, 30 seconds by default, after which it tries a KILL QUERY.

The readonly = 2 alternative

If a tool needs SET, pin every limit. Here's our final readonly = 2 profile, with its user, quota and grants:

CREATE SETTINGS PROFILE agent_profile SETTINGS
    readonly = 2 CONST,
    max_execution_time = 10 MIN 1 MAX 10,
    max_rows_to_read = 1000000 MIN 1 MAX 1000000,
    read_overflow_mode = 'throw' CONST,
    max_result_rows = 10000 MIN 1 MAX 10000,
    result_overflow_mode = 'throw' CONST;

CREATE USER agent IDENTIFIED WITH sha256_password BY 'STRONG_PASSWORD' SETTINGS PROFILE 'agent_profile';

CREATE QUOTA agent_quota
    FOR INTERVAL 1 hour MAX queries = 500, read_rows = 50000000
    TO agent;

GRANT SELECT ON analytics.* TO agent;
GRANT SHOW DATABASES, SHOW TABLES, SHOW COLUMNS ON *.* TO agent;

This user gets different errors, and that's how you tell which mode you're in:

• A full scan: code 158 at "current rows: 7.78 million".
• Raising a cap: code 452, "Setting max_rows_to_read shouldn't be greater than 1000000."
• Changing a CONST setting: code 452, "Setting read_overflow_mode should not be changed."
• SELECT number FROM numbers(50000): code 396, "Limit for result exceeded, max rows: 10.00 thousand, current rows: 50.00 thousand."
• A query built to run 15 seconds: code 159, "Timeout exceeded: elapsed 10080.424 ms, maximum: 10000.000 ms."
• SELECT 1; DROP TABLE analytics.events: code 62, "Multi-statements are not allowed".

The CONST, timeout and multi-statement errors came from the first version of this profile, which already had the CONST and time settings. Neither mode lets the agent change readonly itself: SETTINGS readonly = 0 got code 164 on both users. And a 10-minute range on the sort key still answered, in 4 rows.

Three setup mistakes that broke our test

A 1,000-row result cap broke every query. With max_result_rows = 1000 and result_overflow_mode = 'throw', all 8 run_query calls failed, SELECT number FROM numbers(500) included: "Code: 396. DB::Exception: Limit for result exceeded, max rows: 1.00 thousand, current rows: 1.97 thousand." Those 1.97 thousand rows weren't our query. When the server connects, the clickhouse-connect driver inside it runs SELECT name, value, readonly FROM system.settings LIMIT 10000, and system.settings has 1,967 rows on 26.11.1.536. At 10,000, the calls worked. We tried only those two values, so keep the cap well above your version's settings count.

break isn't a cap. The first profile set max_result_rows = 1000 with result_overflow_mode = 'break'. SELECT number FROM numbers(5000) returned all 5,000 rows, five times the cap. Issue #223 says the cause is that break stops at a block boundary; its reporter asked for 10 rows and got 16,384. Use throw.

ALTER SETTINGS PROFILE ... SETTINGS replaced the whole list. We ran ALTER SETTINGS PROFILE agent_profile SETTINGS max_result_rows = 10000 MIN 1 MAX 10000 to change one cap, and every other setting in the profile went with it. The full-table sum from the first section came back again, this time with no SETTINGS clause at all. Setting changes now failed with 164 instead of 452, so readonly = 2 CONST was gone too, and only the server's own readonly = 1 kept the agent read-only. Every ALTER has to repeat every setting.

A note on quotas. We gave a user a quota of 3 queries an hour. SELECT 1 through SELECT 4 all ran, because constant SELECTs read system.one, which ClickHouse's quota docs leave out of the count. Other queries count, failed ones too, and each new connection spends one on the driver's system.settings read. With the budget gone, the next connection failed before the query ran: "Code: 201. DB::Exception: Quota for user agent_q for 3600s has been exceeded". A quota limits a set of queries over an interval, not one query, so treat it as an hourly budget on top of the per-query caps.

What the server's own read-only mode covers

The default does what it says. Pointed at the default user with writes off, INSERT, CREATE TABLE and DROP TABLE all failed with "Code: 164. DB::Exception: default: Cannot execute query in readonly mode. (READONLY)". So did SETTINGS readonly = 0 and SETTINGS max_execution_time = 600. max_rows_to_read = 0 and max_result_rows = 0 went through, because the default user already had those values.

Writes take two switches. CLICKHOUSE_ALLOW_WRITE_ACCESS=true allows CREATE and INSERT. DROP, TRUNCATE, DELETE, UPDATE and the rest also need CLICKHOUSE_ALLOW_DROP=true. When we tried a DROP without it, the server's reply called that gate "a best-effort accident guard, not a security boundary" and told us to "restrict the ClickHouse user's grants for real enforcement." We agree with the server.

What the default leaves open:

• Result size. The server's code has no row or result cap. In issue #223, still open, one SELECT * on a 3-million-row table returned 231.6 MiB in 5.2 seconds through run_query, inside the timeout.
• Cancelling. Read-only mode still lets the user cancel queries, including ones from other connections that use the same user (issue #248; the README now says so).
• Who's asking. The server connects as the one ClickHouse user in its environment variables, whoever reaches it over MCP. A static MCP token doesn't tell people apart.

One capped user, many people asking

That one user is everybody. The caps above apply to everyone's questions together, and a quota is one budget for all of them.

Querio puts the people back in. You connect ClickHouse once, as the SELECT-only, non-human user our ClickHouse setup docs have you create. People and agents connect to Querio's MCP endpoint, /mcp on the same origin as your Querio webapp, and sign in. From there, our MCP docs say, "the signed-in user's existing permissions govern every API operation."

Caps are the easy half, though. A row cap stops the expensive question, not the wrong one: a capped agent still writes its own SQL, so it still decides which events count as revenue. That's the half we built Querio for. Metrics, joins and business rules are written once in a git repo you own and reviewed like code, and every agent that connects over MCP reads the same ones. ClickHouse is moving the same way: mcp-clickhouse's main branch, not in a release yet, tells the agent to read AGENTS.ROOT before writing SQL when list_databases shows an AGENTS database. If you're checking the SQL an AI writes against ClickHouse today, our guide to querying ClickHouse with AI covers that.

ClickHouse is on our integrations list, and Querio's MCP server is on every plan, Free included.

Set it up: the two official servers and Claude

ClickHouse ships two MCP servers for SQL. Our test covers the open-source one.

	mcp-clickhouse (open source)	ClickHouse Cloud remote MCP
Version or address	0.7.0, released September 21, 2026	https://mcp.clickhouse.cloud/mcp
Runs where	Your machine; the client starts it over stdio	ClickHouse's side, Streamable HTTP, Cloud services only
Setup	Install with uv	Per service, under Connect > MCP (needs control-plane:service:manage-mcp)
Sign-in	A ClickHouse user and password in environment variables	OAuth 2.0, or a Cloud API key as HTTP Basic
Tools	run_query, list_databases, list_tables; run_chdb_select_query with CHDB_ENABLED=true	13, all read-only, including run_select_query, get_organization_cost and get_services_list
Writes	Refused by default; two environment variables turn them on	All tools read-only; run_select_query permits only SELECT
Row and time limits	The ClickHouse user's profile, which you set	The docs don't say which database user runs the query

For Claude Code with the open-source server, this is the command from ClickHouse's Claude guide, with our readonly = 1 user filled in:

claude mcp add --transport stdio --env CLICKHOUSE_HOST=your-host --env CLICKHOUSE_USER=agent_ro1 --env CLICKHOUSE_PASSWORD=STRONG_PASSWORD --scope user mcp-clickhouse -- uv run --with mcp-clickhouse --python 3.10 mcp-clickhouse

Leave CLICKHOUSE_PORT unset and the server picks 8443 when CLICKHOUSE_SECURE=true and 8123 when it's false. It talks to ClickHouse's HTTP interface, so the native ports 9000 and 9440 don't work. A "Port 9000 is for clickhouse-client program" reply means you're pointed at the native protocol. That rule is for mcp-clickhouse only: Querio's own ClickHouse connection lists 9440.

For the Cloud remote server, ClickHouse's remote MCP setup guide gives claude mcp add --transport http clickhouse-cloud https://mcp.clickhouse.cloud/mcp, then /mcp in Claude Code to sign in. In Claude on the web, add it under Customize > Connectors > Add custom connector.

To check the open-source setup, ask the agent to list your databases, then to sum a column over a table bigger than the row cap. On the readonly = 1 user, the second answer should be code 158. For the same steps across other warehouses, see our guide to connecting Claude over MCP. On BigQuery the limit that matters is bytes, and we covered its servers in BigQuery MCP servers compared.

FAQ

Is the tool called run_query or run_select_query?

In mcp-clickhouse 0.7.0 it's run_query. The Cloud remote server's SQL tool is run_select_query, and ClickHouse's Cloud docs table, Docker Hub's page and Google's AI Overview use that name for the open-source server too. A config or prompt that calls run_select_query on the local server won't find it on 0.7.0.

Is there a Docker image?

There's mcp/clickhouse on Docker Hub, but issue #161 reports it's outdated, with a last push on October 31, 2025. The Docker Hub page still lists run_select_query. Run the server with uv instead.

Can each person query as their own ClickHouse user?

Not out of the box. The README describes custom middleware that sets CLIENT_CONFIG_OVERRIDES_KEY to pass per-person credentials. In issue #155, still open, a ClickHouse maintainer says this works on main and recommends OAuth or OIDC plus trusted middleware.