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
| Layer | How |
|---|---|
| No credentials in the browser | ClickHouse passwords live only on the server; the browser, CLI and agents never receive them |
| Encrypted at rest | Connection and remediation passwords and AI provider keys use AES-256-GCM (RBAC_ENCRYPTION_KEY + RBAC_ENCRYPTION_SALT) |
| Password hashing | Argon2id for CHouse UI user passwords; tokens are stored hashed |
| Sessions | Short-lived access JWTs and refresh tokens, signed with jose — see Sessions & JWT |
| Sign-in protection | Rate limiting per client IP (from X-Forwarded-For — your proxy must set it); optional SSO-only sign-in |
| Request limits | Queries (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 |
| Permissions | Every API, CLI and MCP request is checked against the caller's permissions, live |
| Data access | ClickHouse 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 preflight | DDL that would break dependents is stopped unless someone with schema:override confirms |
| Approved changes | Fixes come from a closed catalog, need approval (two people for high-impact ones), and run with a separate remediation credential |
| Audit | Sign-ins, admin changes, queries, fixes and every MCP call are recorded |
| Browser hardening | Content security policy, strict CORS in production (CORS_ORIGIN), dangerouslySetInnerHTML only through DOMPurify |
Request path
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 entryEach 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 request | Value | What it is for |
|---|---|---|
query_id | chouse:p:<account id>:<random> for a person, chouse:s:<purpose>:<random> for background work | A 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_comment | JSON with the account or purpose, plus context such as the dashboard or job | Context 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 ASis 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:
| Statement | readonly on the request |
|---|---|
A read (SELECT, SHOW, DESCRIBE, EXISTS, CHECK, EXPLAIN), from anyone | 2 |
| Anything from a person whose roles hold no permission to change data, tables or settings | 2 |
| Every statement of a feature that only reads: dashboard panels, data health checks, notebook cells, the fleet AI's investigation queries | 1, or 2 for scheduled queries |
| A write from a person who may write | none |
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
SELECTstatements onsystem.*underreadonly=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:chatordoctor: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 (
--yeswithout a terminal) and never prints tokens, even with--debug.
Reporting vulnerabilities
See SECURITY.md for responsible disclosure.
Operations
- Production checklist — the gate before exposing CHouse UI
- Secrets — generating and rotating them
- Audit event catalog — what is recorded