ClickHouse RBAC: Role-Based Access Control Guide

ClickHouse supports SQL-driven role-based access control (RBAC) for managing privileges across users, roles, and settings profiles. The model mirrors ANSI SQL: you grant privileges to roles, assign roles to users, and bind settings profiles to either roles or users to enforce resource quotas. This guide walks through enabling RBAC, defining a working three-role pattern (DBA, dashboard read-only, ingester read-write), and verifying access.

For specific operational details on individual objects, see also the CREATE ROLE reference and row policy guide.

Enabling RBAC

RBAC is opt-in. You need at least one administrative user in users.xml with access_management set so they can issue CREATE USER, CREATE ROLE, and GRANT statements.

<admin>
    <password></password>
    <networks>
        <ip>::/0</ip>
    </networks>
    <profile>default</profile>
    <quota>default</quota>
    <access_management>1</access_management>
</admin>

Restart the server, then connect as admin to bootstrap SQL-managed users.

Securing the default user and replication

The default user runs internal background jobs. Do not give it a password. Restrict it by network instead:

<default>
    <networks>
        <ip>127.0.0.1/8</ip>
        <ip>10.10.10.0/24</ip>
    </networks>
</default>

Inter-server replication traffic traverses the interserver HTTP port (9009 by default, or 9010 when HTTPS is enabled). When those ports are reachable from untrusted networks, set credentials in interserver_http_credentials:

<interserver_http_credentials>
    <user>replication</user>
    <password>password</password>
</interserver_http_credentials>

Cluster-aware DDL

All RBAC DDL accepts ON CLUSTER '{cluster}', propagating the change to every replica through the on-cluster DDL queue. Use it consistently so user state stays identical across nodes.

CREATE ROLE role_name ON CLUSTER '{cluster}';
GRANT SELECT ON db.table TO role_name ON CLUSTER '{cluster}';
CREATE USER username IDENTIFIED BY 'password' ON CLUSTER '{cluster}';
GRANT role_name TO username ON CLUSTER '{cluster}';

A three-role pattern

A common production layout splits users into administrators, dashboard readers, and ingestion services. Each gets a role plus a SETTINGS PROFILE that enforces resource limits with READONLY so end users cannot raise them.

DBA role

CREATE ROLE dba ON CLUSTER '{cluster}';
GRANT ALL ON *.* TO dba ON CLUSTER '{cluster}';
CREATE USER user1 IDENTIFIED BY 'pass1234' ON CLUSTER '{cluster}';
GRANT dba TO user1 ON CLUSTER '{cluster}';

Dashboard read-only role

CREATE ROLE dashboard_ro ON CLUSTER '{cluster}';
GRANT SELECT ON default.* TO dashboard_ro ON CLUSTER '{cluster}';
GRANT dictGet ON *.* TO dashboard_ro ON CLUSTER '{cluster}';

CREATE SETTINGS PROFILE profile_dashboard_ro ON CLUSTER '{cluster}'
SETTINGS
    max_concurrent_queries_for_user = 10 READONLY,
    max_threads = 16 READONLY,
    max_memory_usage_for_user = '30G' READONLY,
    max_execution_time = 60 READONLY,
    max_rows_to_read = 1000000000 READONLY,
    max_bytes_to_read = '5000G' READONLY
TO dashboard_ro;

CREATE USER dash1 IDENTIFIED BY 'pass1234' ON CLUSTER '{cluster}';
GRANT dashboard_ro TO dash1 ON CLUSTER '{cluster}';

Granting dictGet lets dashboards enrich queries with dictionary lookups without granting broader read on those source tables.

Ingester read-write role

CREATE ROLE ingester_rw ON CLUSTER '{cluster}';
GRANT SELECT, INSERT ON default.* TO ingester_rw ON CLUSTER '{cluster}';

CREATE SETTINGS PROFILE profile_ingester_rw ON CLUSTER '{cluster}'
SETTINGS
    max_concurrent_queries_for_user = 40 READONLY,
    max_threads = 10 READONLY,
    max_memory_usage_for_user = '30G' READONLY,
    max_memory_usage = '25G' READONLY,
    max_execution_time = 200 READONLY,
    max_rows_to_read = 1000000000 READONLY,
    max_bytes_to_read = '5000G' READONLY
TO ingester_rw;

CREATE USER ingester_app1 IDENTIFIED BY 'pass1234' ON CLUSTER '{cluster}';
GRANT ingester_rw TO ingester_app1 ON CLUSTER '{cluster}';

Settings profile parameters

Appending READONLY to a setting in the profile prevents the user from overriding it at session or query level.

Verifying access

Reconnect as each user and confirm enforcement:

clickhouse-client -u dash1 --password pass1234
# DDL is rejected with "dash1: Not enough privileges"

clickhouse-client -u user1 --password pass1234
# CREATE TABLE / DROP TABLE succeed under the dba role

clickhouse-client -u ingester_app1 --password pass1234
# INSERT works; large SELECTs hit max_rows_to_read / max_bytes_to_read

Listing and removing objects

SHOW PROFILES;
SHOW ROLES;
SHOW USERS;

DROP PROFILE IF EXISTS profile_dashboard_ro ON CLUSTER '{cluster}';
DROP ROLE IF EXISTS dashboard_ro ON CLUSTER '{cluster}';
DROP USER IF EXISTS dash1 ON CLUSTER '{cluster}';

Common Pitfalls

• Forgetting access_management=1 on the bootstrap admin. Without it, SQL-managed users cannot be created.
• Putting a password on default. Internal background operations run as default; an incorrect password breaks merges and replication. Restrict by network instead.
• Skipping ON CLUSTER on a multi-node cluster. The user only exists on the node where you ran the DDL, and replicated tables will reject inserts from missing users on other replicas.
• Setting max_memory_usage_for_user without READONLY. End users can override it from their session settings.
• Granting ALL to application service accounts. Use scoped grants like SELECT, INSERT ON db.* so application bugs cannot drop tables.

Frequently Asked Questions

Q: Can I mix users.xml and SQL-managed users? A: Yes. XML-defined users continue to work, and SQL-managed users live alongside them. Migrate gradually by creating SQL users first and switching application connection strings once verified.

Q: How do I see what privileges a user has? A: Run SHOW GRANTS FOR username or SHOW GRANTS FOR CURRENT_USER to list direct grants and inherited role privileges.

Q: How do I require a user to activate a role explicitly? A: Use SET DEFAULT ROLE NONE TO username and GRANT role_name TO username WITH ADMIN OPTION. The user then runs SET ROLE role_name per session.

Q: Are passwords stored in plain text? A: No. IDENTIFIED BY 'password' stores a SHA-256 hash. Use IDENTIFIED WITH sha256_hash BY 'hex' when provisioning from automation that already has a hashed value.

Q: Do settings profiles apply to background queries? A: No. Profiles attach to user sessions. Merges, mutations, and replication fetches use the server-level pools and limits.