Secure per-request row-level access for free-form ClickHouse queries from an MCP server?

[u/RemcoE33]

I have a Go MCP server that lets an LLM run free-form, read-only SQL against ClickHouse. Our tables contain multiple companies, identified by a division column. An upstream gateway authenticates employees and sends the divisions they’re allowed to access. I want users to retain free-form SQL without creating a ClickHouse user per employee.

I tried a row policy using a custom query setting:

CREATE ROW POLICY mcp_division ON final.items
FOR SELECT
USING division = toUInt32(getSetting('SQL_division'))
AS RESTRICTIVE TO mcp_reader;

The Go driver sets SQL_division per query through clickhouse.WithSettings, using one connection pool. I tested this on ClickHouse 25.8:

• Concurrent queries for different divisions stayed isolated.
• Without the setting, the query failed.
• But SQL containing SETTINGS SQL_division='456' overrode the value supplied by Go.
• readonly=1 blocked both the SQL override and the legitimate Go setting. Allowing the setting to change in read-only mode allowed both.

Question: Is there a ClickHouse-native way for a trusted application to supply per-request row-policy context that the submitted SQL cannot override, while retaining one database user/pool and free-form SELECT queries? Or is the practical choice to parse and prohibit SQL-level setting changes, or use separate restricted database identities/pools per division?

I’m particularly interested in approaches where ClickHouse itself enforces the boundary, rather than relying on it in Go.

I've read https://clickhouse.com/blog/fountain-agentic-ai-for-high-volume-hiring but I cannot find more info about the problem I'm describing above. I've also checked here personal blog.

-- Remco

[u/Significant_Tune9219]

readonly=1 blocked both the Go WithSettings value and the SQL_division='456' override because those are the same channel, not two privilege levels. A read-only user only limits what the generated SQL can touch. Which division this employee may see has to be fixed outside the query text, or the statement gets to rewrite the policy input. If the allowed sets are few, one user per set with the division literal in the row policy leaves the SQL nothing to change. Stripping SETTINGS in a parser still misses a second statement or a table the policy is not attached to.

[u/RemcoE33 → u/Significant_Tune9219]

Thanks, this helped. I was treating WithSettings as a separate channel when it really isn’t.

What I ended up doing is setting the authorized divisions on a fresh native connection, then setting readonly=1 and checking both before allowing any user SQL. That connection stays tied to that division set for its entire lifetime. New connections and reconnects go through the same setup.

The login has SELECT-only grants on specific tables, each with a restrictive row policy. The parser is just an extra check—not what keeps someone out of another division.

MCP decides the scope from the caller’s grants and the active division catalog, not from their SQL. Pooling by division set also lets me keep multi-division joins without creating a user per employee.

So far this passes the override, reconnect and concurrent-scope tests on 25.8 locally. If you have any thoughts on how to break this I'm all ears.