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.
.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.