CHouseUI
Access & security

Security model

Encrypted credentials, Argon2id hashing, JWT verification, SQL parsing against data access rules and audit coverage.

CHouse UI is built for teams that share ClickHouse. It assumes browsers and tokens can leak, that several people use the same clusters, and that every change must be traceable.

Layers

LayerHow
No credentials in the browserClickHouse passwords live only on the server; the browser, CLI and agents never receive them
Encrypted at restConnection and remediation passwords and AI provider keys use AES-256-GCM (RBAC_ENCRYPTION_KEY + RBAC_ENCRYPTION_SALT)
Password hashingArgon2id for CHouse UI user passwords; tokens are stored hashed
SessionsShort-lived access JWTs and refresh tokens, signed with jose — see Sessions & JWT
Sign-in protectionRate limiting per client IP (from X-Forwarded-For — your proxy must set it); optional SSO-only sign-in
Request limitsQueries (300 a minute), AI calls (60 a minute) and the rest of the API (1,000 a minute) are counted per user, whether they come from the browser or an API or MCP token; signed-out requests count per client IP. Each server replica keeps its own count
PermissionsEvery API, CLI and MCP request is checked against the caller's permissions, live
Data accessClickHouse parses each query, and every database and table it touches is checked against the caller's data access policies; what can't be fully checked is refused
Schema preflightDDL that would break dependents is stopped unless someone with schema:override confirms
Approved changesFixes come from a closed catalog, need approval (two people for high-impact ones), and run with a separate remediation credential
AuditSign-ins, admin changes, queries, fixes and every MCP call are recorded
Browser hardeningContent security policy, strict CORS in production (CORS_ORIGIN), dangerouslySetInnerHTML only through DOMPurify

Request path

text
client ──▶ authenticate (JWT or PAT, checked live)
       ──▶ permission check
       ──▶ parse SQL ──▶ data access (first matching rule by priority; no match = deny)
       ──▶ schema preflight for DDL
       ──▶ ClickHouse, with the connection's credentials
       ──▶ audit entry

Each layer stands alone: a role change applies on the next request, a revoked token fails on its next call, and a stolen session still can't reach tables its user's policies deny. ClickHouse's own grants on the connection user remain the outer limit.

Who a statement runs as

Every statement CHouse UI sends to ClickHouse goes through one server module, and that module needs to know who the statement is for: a signed-in person, or a named background purpose such as the collector. It then sets three things on the request that the caller's code cannot change:

On the requestValueWhat it is for
query_idchouse:p:<account id>:<random> for a person, chouse:s:<purpose>:<random> for background workA ClickHouse administrator can see in system.query_log and system.processes which CHouse account a statement was sent for. A statement cannot change its own query id.
log_commentJSON with the account or purpose, plus context such as the dashboard or jobContext for reading the log. A statement can overwrite it with its own SETTINGS log_comment, so treat it as a hint, not as proof.
Quota key (X-ClickHouse-Quota header)The account id, or chouse-<purpose>Lets a ClickHouse quota created with KEYED BY client_key count each CHouse account separately. ClickHouse 23.8 refuses a quota key next to the way CHouse UI authenticates, so no key is sent to such a server; 24.8 and 26.5 accept it. Nothing changes until you create such a quota. On ClickHouse 26.5.7 an account that had already sent statements kept its earlier quota after a keyed one was created; we did not measure for how long, so create the quota before relying on it and check system.quotas_usage. With such a quota in place, ClickHouse 26.5.7 refused an INSERT … VALUES that it ran as an asynchronous insert (error 496, "requires a client supplied key"), even though the request carried the key; the same insert with SETTINGS async_insert = 0 ran. If you create a keyed quota, turn async_insert off for the connection's ClickHouse user.

A query id proves who sent a statement only on rows whose ClickHouse user is the connection's own login. Someone with their own ClickHouse login can choose any id for statements they send directly.

CHouse UI's own reports read the account from the query id, not from log_comment: Live Queries, the Logs page, lineage, table usage and the AI tools that list your own queries. A statement that sets its own log_comment does not change who it is shown for, and cannot hide itself from those reports. On rows logged before 3.18, which have no such id, the account is still read from log_comment.

Three rules apply to everyone, administrators included:

  • EXECUTE AS is refused. CHouse UI decides which ClickHouse user a statement runs as. The check reads ClickHouse's own parse of the statement, so a comment or a wrapper cannot hide the keyword.
  • Every typed statement is parsed by ClickHouse before it runs. Administrators still skip permission and data access checks, but a statement ClickHouse cannot parse, or cannot parse right now, is not run.
  • Typed text runs only after it is checked. The server sends a typed statement to ClickHouse only as the exact text, parameters, connection and account that the access check approved. This holds for the SQL editor, saved and scheduled queries, dashboard panels, notebook cells and the SQL the AI tools write.

Reads are sent read-only

CHouse UI also asks ClickHouse to refuse a write, so a mistake in CHouse UI's own check cannot turn a read into a write:

Statementreadonly on the request
A read (SELECT, SHOW, DESCRIBE, EXISTS, CHECK, EXPLAIN), from anyone2
Anything from a person whose roles hold no permission to change data, tables or settings2
Every statement of a feature that only reads: dashboard panels, data health checks, notebook cells, the fleet AI's investigation queries1, or 2 for scheduled queries
A write from a person who may writenone

Whether a statement is a read comes from ClickHouse's parse of it, not from its first word. With readonly = 2 ClickHouse refuses every write (error 164, shown as "You may only read here") but still lets a statement set its own settings, such as SETTINGS max_threads = 4; it cannot switch readonly off.

A connection whose ClickHouse login is itself limited with readonly = 2 keeps working. A login limited with readonly = 1 has never worked with CHouse UI, because ClickHouse then refuses the settings CHouse UI sends with every request.

On a role-ceiling connection the request also names a ClickHouse role: the one mapped to the person's CHouse role, or the system role for CHouse UI's own work. The stored ClickHouse user holds no grant of its own there, so ClickHouse refuses whatever that role is not granted, for administrators too.

On a personal connection a person's statement is sent as EXECUTE AS <their ClickHouse user>, with a role that may only act as the users a ClickHouse administrator allowed. ClickHouse checks that user's own grants. readonly cannot go on such a request, so a read carries readonly = 1 in its own SETTINGS clause. CHouse UI sends the wrapped statement only after ClickHouse's parse shows the person's linked user as the target and the typed statement unchanged under it.

The design and the stages that follow it are in ADR 0033.

AI

  • The Doctor and in-tab AI investigate with single SELECT statements on system.* under readonly=1.
  • AI never runs SQL you didn't review: rewrites and drafts are suggestions, and AI-drafted fixes go through the same approval — and can't be approved by whoever submitted them.
  • Using AI needs ai:optimize, ai:chat or doctor:run. Prompts go only to the providers you configure; use a self-hosted model to keep them in your network (AI models).

Tokens, CLI and agents

  • Personal access tokens act as their owner, can be narrowed with scopes and given an expiry, and are re-checked on every call.
  • MCP is off by default, and so is sign-in for agents, whose tokens work only on /mcp; only read tools are on until an administrator turns on more, destructive tools are approved in the client, and token, user, policy, SSO and secret management are never exposed as tools. AI Governance adds budgets and a pause switch.
  • The CLI asks before any change (--yes without a terminal) and never prints tokens, even with --debug.

Reporting vulnerabilities

See SECURITY.md for responsible disclosure.

Operations

Edit this page on GitHub ↗