Skip to content

Authentication & Roles

CH-UI authenticates users with ClickHouse credentials against a connection.

Who becomes admin: only the first ClickHouse admin to sign in becomes the CH-UI admin. A ClickHouse user counts as admin when it can read system.users (access management) or has grants like ALL, CREATE or DROP. Every ClickHouse admin who signs in after that is a viewer in CH-UI until a CH-UI admin grants the role in Admin > Users. If no CH-UI admin can sign in, run ch-ui reset-admin on the server. See Recover the admin role.

Roles are per connection: a role override for a ClickHouse user applies on the connection it was set for, not to the same username on other connections. A role override for an SSO person applies on all connections. ClickHouse usernames starting with sso: cannot sign in with a password.

Single Sign-On: CH-UI also supports OIDC SSO (Okta, Entra ID, Google, Keycloak, …) alongside password login, or instead of it with SSO only. See Single Sign-On.

The server authenticates against ClickHouse credentials over the selected connection tunnel.

  1. User picks a connection.
  2. User provides ClickHouse username/password.
  3. CH-UI verifies credentials through tunnel.
  4. CH-UI issues chui_session cookie.

When the server runs with allow_login_url on (off by default), the connection picker also offers Other ClickHouse URL. The user types a ClickHouse URL instead of picking a connection, and the server finds or creates a direct connection for that URL, then checks the credentials as above. The connection is kept, so it shows in the picker next time. When there are no connections at all, the login page opens straight in this mode.

A connection added this way never grants the CH-UI admin role: users get at most analyst there, never receive a role override, and never become the first admin. Sign in on a saved connection to administer CH-UI.

The CH-UI server makes that connection, not the browser, so turn it on only on trusted networks. Setup, limits and the risk are in Configuration.

curl http://localhost:3488/api/auth/session \
-H "Cookie: chui_session=..."

Response includes:

  • authenticated
  • user (the person’s email for SSO sessions, the ClickHouse user otherwise)
  • user_role
  • via_sso (whether the session was created through SSO)
  • active connection info
  • app version

The /api/auth/config endpoint (unauthenticated) reports which login methods are available so the login page can render the right options:

curl http://localhost:3488/api/auth/config
# { "password_login": true, "sso_only": false, "oidc_enabled": true, "setup_open": false, "login_url_allowed": false, "oidc_login_url": "/api/auth/oidc/login" }

sso_only is true (and password_login false) while SSO only is in force; the login page then shows only Sign in with SSO.

setup_open is true while first-run setup accepts a setup code, which is what shows Set up ClickHouse connection on the login page. login_url_allowed is true when sign-in with a ClickHouse URL is on, which is what shows Other ClickHouse URL in the picker.

CH-UI supports app-level roles, overridable per user by an admin:

Role Admin panel & settings Workspace objects (dashboards, pipelines, models, saved queries, schedules) Run queries
admin ✓ create / edit / delete / run ✓ (per ClickHouse grants)
analyst No create / edit / delete / run ✓ (per ClickHouse grants)
viewer No read-only ✓ (per ClickHouse grants)

Admin-only routes are guarded server-side (RequireAdmin); workspace writes are guarded by RequireWriter (admin or analyst). Schedules follow the same rule: creating, editing, deleting and running a schedule needs admin or analyst.

Queries you run yourself always use your own ClickHouse grants, whatever your CH-UI role. Shared background jobs are different: an admin can give a worker (scheduled queries, models, pipeline sinks, governance, Cluster Health, telemetry monitors, performance scans, operations reports) its own ClickHouse account for a connection. Those jobs then run with that account’s grants, so any admin or analyst who can create or edit the jobs can use those grants through them. Keep background accounts no broader than your writers should have. See Background Accounts.

Common statuses returned by /api/auth/login:

  • 400 bad request body or missing username, or (with clickhouse_url) both a URL and a connection were sent, or the URL failed validation. Also connectionId is required when more than one connection exists: leaving out connectionId only works when the login picker shows a single connection, or Connection not found for an unknown one
  • 403 clickhouse_url was sent but sign-in with a ClickHouse URL is off, or Cross-origin request denied: the browser’s Origin is not this app or an allowed origin (see Security)
  • 415 the body was not sent with Content-Type: application/json
  • 401 ClickHouse rejected the credentials, or the error could not be classified. Counts toward the rate limit.
  • 429 IP/user rate limit (retryAfter in seconds in the body), or the login page has already created 20 connections and the URL matches none of them
  • 503 the connection’s tunnel agent is offline, or ClickHouse could not be reached (timeout, refused, network or TLS error). Does not count toward the rate limit, so an outage does not lock healthy accounts out.

Click the status dot at the bottom of the sidebar, or run Switch connection from the command palette (Cmd/Ctrl+K). The list shows every connection with its status; offline ones cannot be picked.

  • Password sessions: the form is pre-filled with your ClickHouse user and Use my current password is ticked. Untick it to sign in to the other connection as a different user. Your current password is only reused when the other connection points at the same ClickHouse server (two direct connections with the same scheme, host, port and URL path, or the same connection). For a different server or any agent connection the password field opens with Different server, enter the password for <user>; nothing is sent to that connection until you type it. If the same server refuses your current password, the password field opens too; neither case counts toward the login rate limit.
  • SSO sessions: nothing to type. The switch uses the target connection’s SSO service account and keeps your email and role. On a connection with per-person ClickHouse users it creates or re-syncs your own user first, from the groups of your last sign-in. Connections without a service account show SSO not set up.

A switch reloads the app. Open tabs, pinned results, recent palette commands, open dashboard folders and log columns are kept per person and connection in the browser, so switching back restores what you had there. Theme, split sizes and the row limit stay the same everywhere. Other open CH-UI tabs in the same browser are not reloaded, so their unsaved work is not lost. They show This tab’s connection changed in another tab with Reload and Not now; until you reload, their requests fail with 409 connection_changed. Signing in or out in one tab works the same way. Tabs that are signed out just reload.

curl -X POST http://localhost:3488/api/auth/switch-connection \
-H "Content-Type: application/json" \
-H "Cookie: chui_session=..." \
-d '{"connection_id":"<id>"}'

Leave out username and password to reuse the current ones (same ClickHouse server only); send both to sign in as someone else. The session cookie is replaced with a new token in one step, so a failed switch leaves you signed in where you were.

Statuses:

  • 400 missing or unknown connection, or the embedded connection that setup replaced (it is hidden from sign-in too)
  • 401 ClickHouse rejected the credentials. needs_password: true when it was your current password; only typed passwords count toward the rate limit. Also needs_password: true with reason: "different_server" when the current password was left out but the target is another ClickHouse server or an agent connection; the target is not contacted
  • 403 the Origin header is not CH-UI’s own origin or an allowed origin
  • 409 sso_not_configured (SSO session, target has no service account), sso_account_rejected (ClickHouse refused that service account), or session_changed (another request switched or ended this session first). On a per-person target also sso_reauth_required (the session predates per-person mode; sign in again), sso_no_roles (your groups map to no role there), sso_user_exists (a ClickHouse user with your name exists that CH-UI did not create) or sso_user_rejected (ClickHouse refused the user CH-UI just provisioned)
  • 502 sso_provisioning_failed: creating or syncing your per-person user failed; the audit log has the error
  • 415 the body is not application/json
  • 429 the shared login rate limit (retryAfter in the body), or more than 20 switch requests a minute from one IP
  • 503 the target is offline or ClickHouse could not be reached
curl -X POST http://localhost:3488/api/auth/logout \
-H "Cookie: chui_session=..."

This deletes the server session and clears cookie state.

Both successful and failed logins are written to the immutable audit trail (with user, IP, and timestamp), so brute-force and credential-stuffing attempts are visible. A connection switch is written as user.switch_connection with the old and new connection; a refused or unreachable switch is a user.login_failed row naming the switch. Audit events can be forwarded to your SIEM. See Monitoring & SIEM.

  • In production, cookie is Secure when app runs in production mode.
  • Set strong APP_SECRET_KEY.
  • Keep ALLOWED_ORIGINS strict to your UI origin(s).
  • Terminate TLS natively or at a proxy. See Security.