Read-Only ClickHouse Access for AI Agents in Five Minutes
2026-11-06 - 11 min read
Pointing Claude Code or Cursor at ClickHouse? Give the agent its own read-only user first. It takes five minutes in the Cloud Console or SQL, and the database enforces it.
I give AI agents access to ClickHouse on most projects, and every agent gets its own login that can't write a byte. The setup takes about five minutes, in the ClickHouse Cloud console or in SQL. Once it's done, the database enforces it, not the MCP server in front of it.
ClickHouse's official MCP server, mcp-clickhouse, gives the agent a run_query tool and sends readonly=1 with every query it runs. That's a sensible default, but it only covers the server's own requests, and the README says plainly where the real boundary is:
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.
mcp-clickhouse README, Destructive Operation Protection
So I put the guards where every client has to pass them. A notebook, a BI tool or a script using the agent's login skips the MCP server entirely, but it can't skip the user, the role, the settings profile or the quota.
What I ran. The setup, the five failing queries, the quota and the timeout run were on a ClickHouse Cloud 26.6 service with two replicas. The failing queries ran as the agent's user over HTTPS, and the timeout run went through mcp-clickhouse 0.7.0 over stdio, the way Claude Code starts it. The readonly = 2 variant, HOST IP, the HTTP transport and the 25.6-second run come from a self-managed ClickHouse 26.4 server in Docker. The errors are the ones ClickHouse returned.
The five-minute setup
You need one role, one settings profile and one user. On ClickHouse Cloud, open your service's SQL console and run the SQL there. On a self-managed server, paste it into clickhouse-client.
The Cloud console also has a Users and roles page, but that manages the people who sign in to the console. The agent's database login comes from SQL either way.
I create the user with a salted hash, so the password itself never appears in the console. Generate the password, a salt and the hash in a terminal:
PASSWORD=$(openssl rand -base64 24) # this goes to mcp-clickhouse, not to the console
SALT=$(openssl rand -hex 8)
printf '%s' "$PASSWORD$SALT" | shasum -a 256 | cut -d' ' -f1
echo "$SALT"Then run this as an admin, with your hash and salt filled in:
-- What the agent may read
CREATE ROLE agent_reader;
GRANT SELECT ON analytics.* TO agent_reader;
-- Limits on every query it runs
CREATE SETTINGS PROFILE agent_limits SETTINGS
readonly = 1,
max_execution_time = 25,
max_result_rows = 2000,
max_rows_to_read = 1000000000,
max_memory_usage = 4000000000,
max_concurrent_queries_for_user = 4
TO agent_reader;
-- The login mcp-clickhouse uses
CREATE USER mcp_agent
IDENTIFIED WITH sha256_hash BY '<hash>' SALT '<salt>';
GRANT agent_reader TO mcp_agent;
Replace analytics with the databases the agent needs. If you'd rather type the password itself, IDENTIFIED WITH sha256_password BY '<password>' works too, and ClickHouse stores only the hash either way. On ClickHouse Cloud a typed password must also meet its complexity rules.
The limits live on the role. The profile attaches to agent_reader, and so does the quota below, so a second agent login gets all of it with one GRANT.
Never hand an agent the default user
The mcp-clickhouse README asks you to treat the MCP user like any external client, with the minimum privileges, and never a default or administrative user. On ClickHouse Cloud, default can create users, drop tables and change grants, and an agent on that login can do all of it too.
Point the ClickHouse MCP server at it
For Claude Code it's one command. You need uv, and I pin the version:
claude mcp add \
--transport stdio \
--env CLICKHOUSE_HOST=your-service.us-east-2.aws.clickhouse.cloud \
--env CLICKHOUSE_USER=mcp_agent \
--env CLICKHOUSE_PASSWORD="$PASSWORD" \
--scope user \
mcp-clickhouse -- \
uv run --with mcp-clickhouse==0.7.0 --python 3.12 mcp-clickhouseClients that read an mcpServers JSON file, such as Claude Desktop and Cursor, take the same launch command. ClickHouse's setup guide covers Codex, ChatGPT and Windsurf too:
{
"mcpServers": {
"mcp-clickhouse": {
"command": "uv",
"args": ["run", "--with", "mcp-clickhouse==0.7.0", "--python", "3.12", "mcp-clickhouse"],
"env": {
"CLICKHOUSE_HOST": "your-service.us-east-2.aws.clickhouse.cloud",
"CLICKHOUSE_USER": "mcp_agent",
"CLICKHOUSE_PASSWORD": "your-password"
}
}
}
}mcp-clickhouse talks to ClickHouse's HTTP interface, over TLS on port 8443 by default. Your Cloud service's Connect dialog shows the same endpoint under HTTPS, so on Cloud the host is the only setting to change.
For a self-managed server on plain HTTP, set CLICKHOUSE_SECURE=false and the port becomes 8123. Ports 9000 and 9440 belong to the native protocol clickhouse-client uses, and they won't work here. Keep the password out of any config file you commit.
Then ask the agent to list the databases. Mine got ["analytics"] and nothing else.
Check it: five queries that should fail
Before the agent does real work, I run the queries it must not be able to run, as the agent's own user. These are the replies from ClickHouse Cloud:
INSERT INTO analytics.events VALUES (...)
→ 497 ACCESS_DENIED: necessary to have the grant INSERT(ts, user_id, event) ON analytics.events
SELECT count() FROM system.query_log
→ 497 ACCESS_DENIED: necessary to have the grant SELECT ON system.query_log
SELECT count() FROM analytics.events SETTINGS max_execution_time = 0
→ 164 READONLY: Cannot modify 'max_execution_time' setting in readonly mode
SELECT * FROM url('https://example.com', 'LineAsString')
→ 497 ACCESS_DENIED: necessary to have the grant READ ON URL
SELECT * FROM analytics.events
→ 396 TOO_MANY_ROWS_OR_BYTES: max rows: 2.00 thousand, current rows: 49.15 thousandEach failure comes from a different guard:
- Grants did most of the work. The
INSERTfailed for the missing grant beforereadonlycame into it, and so did the read fromsystem. ClickHouse's docs back this up:readonlyalone doesn't stopBACKUPor a localREVOKE(permissions for queries). readonly = 1keeps the limits in place. At that level a query can't change any setting, so the agent can't lift its own time or row limit.- Table functions need their own grants. Without
READ ON URL, the agent can't make ClickHouse fetch a URL for it. - Limits overshoot. ClickHouse checks most limits per block of data, not per row, so the 2,000-row cap stopped the query at 49,150 rows. Treat every number in the profile as a ceiling with some give in it.
The table shows what each limit in the profile stops and the error the agent gets back:
| Setting | What it limits | What the agent gets back |
|---|---|---|
readonly = 1 | Writes, DDL and any change to settings | Code 164, READONLY |
max_execution_time = 25 | Seconds per query | Code 159, TIMEOUT_EXCEEDED |
max_result_rows = 2000 | Rows in the result | Code 396, TOO_MANY_ROWS_OR_BYTES |
max_rows_to_read = 1000000000 | Rows read from tables | Code 158, TOO_MANY_ROWS |
max_memory_usage = 4000000000 | Bytes of memory per query | Code 241, MEMORY_LIMIT_EXCEEDED |
max_concurrent_queries_for_user = 4 | Queries running at once for the user | Code 202, TOO_MANY_SIMULTANEOUS_QUERIES |
Keep result_overflow_mode at its default, throw. With break, ClickHouse returns the rows it has so far, and run_query gives the agent no sign the result was cut short, so it would analyze a partial result as if it were complete. With throw it gets an error and can add a LIMIT or aggregate instead.
If a client needs to change settings
readonly = 1 refuses every SET, and some clients need to set their own. readonly = 2 allows that, so pin each limit with CONST (see constraints on settings):
ALTER SETTINGS PROFILE agent_limits SETTINGS
readonly = 2 CONST,
max_execution_time = 25 CONST,
max_result_rows = 2000 CONST,
max_rows_to_read = 1000000000 CONST,
max_memory_usage = 4000000000 CONST,
max_concurrent_queries_for_user = 4 CONST
TO agent_reader;On my 26.4 server, SETTINGS max_threads = 2 then worked and SETTINGS max_execution_time = 0 failed with code 452, SETTING_CONSTRAINT_VIOLATION. readonly = 2 also allows temporary tables and RESTORE, so I stay on 1 unless a client needs 2. mcp-clickhouse works with both.
What I add after the first five minutes
A quota, because a profile only limits one query
An agent in a loop can run hundreds of queries that each pass the profile. A quota counts them over a window:
-- A ceiling on the total per hour
CREATE QUOTA agent_quota
KEYED BY user_name
FOR INTERVAL 1 hour MAX queries = 500, read_rows = 20000000000
TO agent_reader;When it runs out, every query fails until the interval ends. I lowered queries to 2 to see what the agent gets:
Code: 201. DB::Exception: Quota for user `mcp_agent` for 3600s has been exceeded: queries = 3/2. Interval will end at 2026-11-06 19:00:00. Name of quota template: `agent_quota`. (QUOTA_EXCEEDED)The error tells the agent when it can try again.
On ClickHouse Cloud, check how many replicas your service has. Quotas are counted on the server that receives the query:
For distributed query processing, the accumulated amounts are stored on the requestor server. So if the user goes to another server, the quota there will "start over".
ClickHouse docs, Quotas
My service has two replicas, and the agent's queries landed on both, each counting against its own 500:
So the real ceiling is up to the quota times the replica count. Size the per-hour numbers with that in mind.
KEYED BY user_name gives each ClickHouse user its own count. If one hosted server uses one login for everyone, that's one shared budget, and per-person limits belong in the MCP layer.
Keep ClickHouse's timeout under the server's
mcp-clickhouse waits 30 seconds by default, and the profile stops queries at 25. In that order, ClickHouse ends a slow query itself and the agent reads why.
The other way round, the server gives up first. I set CLICKHOUSE_MCP_QUERY_TIMEOUT=5 and ran a slow query. At 5 seconds the server told the agent "Query timed out" and sent KILL QUERY, and ClickHouse refused it, because the agent's user can't read system.processes:
WARNING - Query 5d3dddc5-4759-4a6a-84d8-6069c82f8d3e timed out after 5 seconds: SELECT sum(sleepEachRow(0.00004)) FROM numbers(1000000)
INFO - Cancelling query 5d3dddc5-4759-4a6a-84d8-6069c82f8d3e via KILL QUERY
WARNING - Failed to cancel query 5d3dddc5-4759-4a6a-84d8-6069c82f8d3e: ... Code: 497. DB::Exception: mcp_agent: Not enough privileges. To execute this query, it's necessary to have the grant SELECT ON system.processes. (ACCESS_DENIED)What happened to the query depended on where it ran:
On my self-managed 26.4 server, system.query_log showed the query running on until max_execution_time stopped it at 25.6 seconds. On Cloud it was cancelled at 10.5 seconds, with code 394:
Cloud turns on a setting that cancels a read-only HTTP query when its client goes away, and self-managed ClickHouse leaves it off. I didn't pin down what closed the connection, so don't count on it. ClickHouse 26.10 also changed KILL QUERY so a user can cancel its own query without that grant.
Either way, the profile's limit is the one that holds. Don't widen the agent's grants just to make the server's cancel work.
Size the limits from your query log
The quota's numbers and the profile's memory, read and concurrency limits above are starting values. Base yours on queries that look like the agent's work: an analyst's login, or the agent's own after a trial week with a tight quota. On Cloud, read every replica and every renamed log table at once:
-- Size the limits from what the agent actually ran
SELECT count() AS queries,
quantiles(0.5, 0.99)(query_duration_ms) AS duration_ms,
quantiles(0.5, 0.99)(read_rows) AS read_rows,
quantiles(0.5, 0.99)(result_rows) AS result_rows,
quantiles(0.5, 0.99)(memory_usage) AS memory_bytes
FROM clusterAllReplicas('default', merge('system', '^query_log'))
WHERE type = 'QueryFinish'
AND query_kind = 'Select'
AND user = 'mcp_agent'
AND event_date >= today() - 14
SETTINGS skip_unavailable_shards = 1;Set each limit above the 99th percentile, so it stops runaway queries and leaves normal work alone. For the quota, group the same rows by hour and look at the busiest hours. On a self-managed cluster, use your cluster's name in place of default.
ClickHouse Cloud quirks I've hit
A few things work differently on Cloud than on a server you run yourself:
- The SQL console runs as you, not
default. Queries run assql-console:followed by your email, and errors name that user. You can grant only what that user holds. - A grant on everything is refused.
GRANT SELECT ON *.*fails because the console user lackssystem.zookeeper. The error suggests the fix,GRANT CURRENT GRANTS(SELECT ON *.*), which grants what you hold. For most agents, grant named databases instead. clusterAllReplicasneedsREAD ON REMOTE. An agent that should read every replica's system tables needs it granted the same way, withGRANT CURRENT GRANTS(READ ON REMOTE).- A multi-statement run keeps going after an error. When I reran my setup script, the console reported the first "already exists" error and still ran the statements after it. Run the script once, or use
IF NOT EXISTS.
Limit who can reach each end
There are two connections to lock down: from the MCP server to ClickHouse, and from the agent to the MCP server.
From the MCP server to ClickHouse. On a self-managed server, add HOST IP '10.20.0.0/16' (your MCP server's range) to CREATE USER. When I connected from outside the range, the login failed with code 516, AUTHENTICATION_FAILED, the same message as a wrong password, so the error doesn't reveal that the user exists. On ClickHouse Cloud, each service has its own IP access list, and its default is open:
When you create a ClickHouse Cloud service, the default setting for the IP allow list is 'Allow from anywhere.' We strongly recommend restricting access to specific IP addresses or ranges as soon as possible.
ClickHouse Cloud docs, Setting IP filters
Leave CLICKHOUSE_SECURE and CLICKHOUSE_VERIFY at their default, true, so the connection uses TLS and checks the certificate.
From the agent to the MCP server. Over stdio there's no port to protect: the server runs as a child of the agent's client, with the ClickHouse password on that machine. That's fine for one person. For a team it means a ClickHouse login on every laptop, which is the setup this hosted MCP server replaced.
The HTTP transport lets one server serve everyone. It binds to 127.0.0.1 unless you change CLICKHOUSE_MCP_BIND_HOST, and it won't start until you choose an authentication mode. The simplest is a static bearer token:
CLICKHOUSE_MCP_SERVER_TRANSPORT=http
CLICKHOUSE_MCP_BIND_HOST=127.0.0.1
CLICKHOUSE_MCP_BIND_PORT=8000
CLICKHOUSE_MCP_AUTH_TOKEN=<from your secrets manager>With that setup, a request with no token or the wrong one got 401, a request with a foreign Host header got 421, and one with a foreign Origin got 403. /health answers without a token, on purpose, for load balancers. Never set CLICKHOUSE_MCP_AUTH_DISABLED=true on anything but your own machine.
A static token is one shared secret: you can't revoke one person without rotating it for everyone. The README describes OAuth through FASTMCP_SERVER_AUTH for teams, which I haven't run. Even then, ClickHouse sees one user, mcp_agent, for everyone. That fits a small team that should all see the same data. If people have different rights inside the database, a shared login erases them.
ClickHouse Cloud also runs a managed remote MCP server, enabled per service, with its own sign-in. I haven't tested it.
When raw SQL stops being the right tool
Everything above assumes the callers are your own agents, or a team you trust with the same data. That's where a read-only login and run_query work well: the agent can ask anything, and ClickHouse decides what it gets back.
When the callers are customers, raw SQL is the wrong interface. They need curated tools with the tenant fixed on the server, per-person limits and an audit trail. That's the customer-facing design in the hosted MCP server case study.
If you're connecting agents to a ClickHouse cluster and want a second opinion on the access model, get in touch.
Related
- A Secure Remote MCP Server for AI Agent Database Access: one hosted MCP server in production, with permissions outside the model.
- Reduce MCP Server Memory Use Across Coding-Agent Sessions
- ClickHouse Migrations in Plain SQL:
ch-migrate0.5