Users & Roles
RBAC, GRANT, default user
ClickHouse's access control looks a lot like Postgres's: users authenticate, roles bundle up privileges, and users get roles assigned to them rather than having privileges granted to them one by one. The part that surprises people coming from a single-tenant analytics mindset is how fine-grained the grants can get — down to specific columns and row filters, not just whole databases.
Users, roles, and grants
A user is an identity that authenticates (password, certificate,
LDAP, Kerberos, etc.). A role is a named bundle of privileges that
gets assigned to one or more users. Privileges are granted with
GRANT, either straight to a user or to a role that users then
inherit:
-- a role for dashboards / BI tools: read-only, one database
CREATE ROLE analytics_reader;
GRANT SELECT ON analytics.* TO analytics_reader;
-- a role for an ingestion service: write-only, one table
CREATE ROLE events_writer;
GRANT INSERT ON analytics.events TO events_writer;
-- users get roles, not raw grants
CREATE USER dashboard_svc IDENTIFIED WITH sha256_password BY 'change_me';
GRANT analytics_reader TO dashboard_svc;
CREATE USER ingest_svc IDENTIFIED WITH sha256_password BY 'change_me';
GRANT events_writer TO ingest_svc;This separation matters in practice: when the ingestion pipeline
changes, you touch events_writer once and every user holding that
role picks up the change, instead of hunting down every individual
grant.
Checking what a user can actually do
SHOW GRANTS FOR dashboard_svc;This is the first thing to run when a query fails with an access
denied error, or — more worryingly — when you're trying to confirm a
user can't do something it shouldn't. REVOKE works symmetrically
with GRANT to take a privilege back.
Two ways grants get stored
By default, users, roles, and grants created with SQL are persisted
internally (backed by the same coordination storage used for
replication — ClickHouse Keeper in a clustered setup, local disk
otherwise), which is what the examples above assume. Older
deployments — and some still today — instead define users in XML
configuration files (users.xml) loaded at server startup. Both
mechanisms can coexist, but mixing them for the same user is a common
source of confusion about which definition actually won.
This repo's own Docker setup creates exactly
one user — the admin user from .env — with full access to
everything. That's the right amount of ceremony for a local
learning environment. It is the wrong shape for anything shared or
production: every application and every human that touches the
cluster should get its own scoped user, with a role that grants
only what that specific workload needs.
Prefer granting roles over granting privileges directly to users, even for a single user. It costs nothing up front, and it's the difference between updating one role definition and auditing every user account by hand when access requirements change six months later.