ClickHouse CREATE ROLE: RBAC Setup and Patterns

A role in ClickHouse is a named bundle of privileges that you grant to one or more users. Roles are the canonical way to manage access at scale: change the role, and everyone who has it picks up the change. To create one, run CREATE ROLE <name>, then attach privileges with GRANT and assign the role to users with GRANT <role> TO <user>. This guide covers the full CREATE ROLE syntax, how roles compose with settings profiles, the difference between granted roles and default roles, and common patterns that work in production.

The Quick Answer

CREATE ROLE analyst;
GRANT SELECT ON analytics.* TO analyst;
GRANT analyst TO alice;

That defines a analyst role with read access to the analytics database and grants it to alice. The role is not automatically active at login; either set it as the default with SET DEFAULT ROLE analyst TO alice or have the user run SET ROLE analyst after connecting. See the grant guide for the full privilege reference.

Full CREATE ROLE Syntax

CREATE ROLE [IF NOT EXISTS | OR REPLACE] name1 [, name2 ...]
    [ON CLUSTER cluster_name]
    [IN access_storage_type]
    [SETTINGS
        variable [= value] [MIN min] [MAX max] [READONLY | WRITABLE | CONST | CHANGEABLE_IN_READONLY]
        | PROFILE 'profile_name'
        [, ...]];

The only required part is the role name. Everything else has reasonable defaults.

Roles vs Default Roles vs Granted Roles

This trips up nearly everyone the first time. There are three related concepts:

• Granting a role to a user: GRANT analyst TO alice makes the role available to Alice, but it is not active until she explicitly enables it.
• Setting a default role: SET DEFAULT ROLE analyst TO alice (or attaching DEFAULT ROLE analyst at user creation) activates the role automatically at login.
• Activating a role for the current session: SET ROLE analyst activates a role for the connected session. SET ROLE NONE deactivates.

A common production pattern is to grant a privileged role (db_admin, support_writer) without setting it as default, so users have to opt in explicitly when they need elevated access. This is why Cannot set non-granted role is one of the more common access errors; see the dedicated guide.

Common RBAC Patterns

Read-only analyst role

CREATE ROLE analyst
SETTINGS
    readonly = 1,
    max_memory_usage = 4000000000,
    max_execution_time = 60;

GRANT SHOW TABLES, SELECT ON analytics.* TO analyst;
GRANT SHOW TABLES, SELECT ON dim.* TO analyst;

GRANT analyst TO alice, bob, carol;
ALTER USER alice DEFAULT ROLE analyst;
ALTER USER bob DEFAULT ROLE analyst;
ALTER USER carol DEFAULT ROLE analyst;

The settings on the role apply to anyone using it. This is how you bound resource usage at the role level rather than per user.

Writer role for a single table

CREATE ROLE events_writer;
GRANT SELECT, INSERT ON analytics.events TO events_writer;
GRANT events_writer TO app_etl;
ALTER USER app_etl DEFAULT ROLE events_writer;

Narrow scope is the goal. Avoid GRANT ALL on production roles unless the role really is the cluster administrator.

Layered roles

CREATE ROLE base_reader;
GRANT SHOW TABLES ON *.* TO base_reader;

CREATE ROLE analyst;
GRANT base_reader TO analyst;
GRANT SELECT ON analytics.* TO analyst;

CREATE ROLE senior_analyst;
GRANT analyst TO senior_analyst;
GRANT SELECT ON sensitive.* TO senior_analyst;

GRANT senior_analyst TO carol;

Roles can be granted to other roles, so you can compose them. senior_analyst inherits everything analyst has, which in turn inherits from base_reader. Use sparingly; deeply layered hierarchies become hard to audit.

Role with privilege to manage other users

CREATE ROLE team_lead;
GRANT CREATE USER, ALTER USER, DROP USER ON *.* TO team_lead;
GRANT CREATE ROLE, ALTER ROLE, DROP ROLE ON *.* TO team_lead;
GRANT analyst TO team_lead WITH ADMIN OPTION;

GRANT team_lead TO eve;

WITH ADMIN OPTION lets Eve grant and revoke the analyst role to other users. Without it, she can use the role but not delegate it. For privilege delegation rather than role delegation, you would use WITH GRANT OPTION on a GRANT SELECT ... instead.

SETTINGS on Roles

CREATE ROLE analyst
SETTINGS
    max_memory_usage = 4000000000 MIN 1000000000 MAX 8000000000 WRITABLE,
    max_threads = 8 READONLY,
    log_queries = 1;

Settings attached to a role merge with the user's own settings when the role is active. The MIN/MAX and READONLY/WRITABLE modifiers gate what the user can override at query time with SET. This is the right place to enforce per-team resource limits.

Alternatively, point the role at a settings profile:

CREATE SETTINGS PROFILE analyst_profile
    SETTINGS max_memory_usage = 4000000000, max_threads = 8;

CREATE ROLE analyst SETTINGS PROFILE 'analyst_profile';

Profiles are easier to reuse across roles and users.

ON CLUSTER

CREATE ROLE analyst ON CLUSTER my_cluster;

Replicates the role definition across every node in the cluster. Roles are stored in the access entity backend, so if you are using replicated access storage they propagate automatically and ON CLUSTER is redundant; for local_directory storage it is the explicit fan-out.

Inspecting Roles

-- All roles on the cluster
SELECT name, storage FROM system.roles;

-- What a role grants
SHOW GRANTS FOR analyst;

-- Reproducible definition
SHOW CREATE ROLE analyst;

-- Which users have a role
SELECT user_name, role_name, with_admin_option
FROM system.role_grants
WHERE role_name = 'analyst';

-- Which roles a user has, both default and available
SELECT role_name, is_default
FROM system.role_grants
WHERE user_name = 'alice';

Modifying and Dropping Roles

ALTER ROLE analyst SETTINGS max_memory_usage = 8000000000;
ALTER ROLE analyst RENAME TO data_analyst;
DROP ROLE analyst [, ...] [ON CLUSTER my_cluster];

DROP ROLE removes the role and revokes it from every user who had it. There is no equivalent of UNDROP for roles; rebuild from your DDL or from SHOW CREATE ROLE output captured ahead of time.

Common Errors

• Cannot set non-granted role: the user has the role available but it is not active in the current session. Run SET ROLE or update the default. See set-non-granted-role.
• Unknown role: the role name does not exist on this node. If you are on a clustered setup with local_directory storage, the role may exist on some nodes and not others; use ON CLUSTER. See unknown-role.
• Access entity already exists: pick a new name or use OR REPLACE.
• Not enough privileges to grant: you are trying to grant a privilege you do not have WITH GRANT OPTION on.

How Pulse Helps With Role Hygiene

The role graph in a real ClickHouse cluster gets messy fast: roles granted to roles granted to roles, default roles that no longer match team structure, settings profiles that drifted apart, and admin-option grants that nobody is tracking. Pulse continuously inspects the access graph and surfaces over-privileged roles, unused roles (granted to nobody), roles with admin option that should not have it, default-role mismatches versus the role each user actually uses, and settings-profile drift that produces unexpected resource limits. Connect your ClickHouse cluster to Pulse and let it watch RBAC drift before it shows up as an audit finding.

Frequently Asked Questions

Q: Do I have to use roles, or can I grant privileges directly to users?

You can grant directly to users (GRANT SELECT ON analytics.* TO alice), but it does not scale. The first time you have to add a new table to ten users, you will wish you had built a role. Use roles from day one.

Q: How do I make a role active automatically at login?

SET DEFAULT ROLE analyst TO alice, or attach DEFAULT ROLE analyst to the user at creation time. The user can override the active set in a session with SET ROLE.

Q: Can a user have multiple default roles?

Yes. SET DEFAULT ROLE analyst, qa_reviewer TO alice activates both at login. You can also use DEFAULT ROLE ALL EXCEPT admin to activate everything except a specific role.

Q: Are roles cluster-wide?

It depends on your access storage. With replicated storage backed by Keeper, roles are cluster-wide automatically. With the default local_directory storage, roles are per-node and you need ON CLUSTER to create them everywhere.

Q: What's the difference between a role and a settings profile?

A role bundles privileges (and optionally settings). A settings profile is just settings. Roles can reference profiles, which is the cleanest way to keep resource limits separate from privilege definitions.

Q: Can I revoke a role from a user?

REVOKE analyst FROM alice removes the role grant. If analyst was Alice's default role, you may also want to set a new default with SET DEFAULT ROLE.

Q: Does dropping a role affect users who have it?

Yes. DROP ROLE revokes the role from every user who had it. Any privileges that came from the role are gone immediately; privileges granted directly to the user are unaffected.