ClickHouse DB::Exception: Unknown role

The "DB::Exception: Unknown role" error in ClickHouse appears when a statement references a role that does not exist on the server. The UNKNOWN_ROLE error code is raised during operations like GRANT role TO user, SET ROLE, or REVOKE role when ClickHouse cannot find a role matching the given name.

Impact

The operation that references the missing role fails. This prevents privilege assignment, role activation, or role revocation from completing. Automated provisioning scripts or migration tools will stop at the point of failure, leaving access control in a partially configured state.

Common Causes

1. The role was never created on this ClickHouse server or cluster
2. A typo in the role name
3. The role was dropped and the referencing configuration or script was not updated
4. Migrating from another ClickHouse cluster without exporting role definitions
5. The role is defined in XML configuration on some nodes but not on the node being queried
6. Case mismatch in the role name (role names are case-sensitive in ClickHouse)

Troubleshooting and Resolution Steps

1. List all existing roles:

   SHOW ROLES;
   

   Verify the role name and check for case sensitivity differences.

2. Create the missing role:

   CREATE ROLE my_role;
   

3. Grant privileges to the new role:

   GRANT SELECT, INSERT ON my_database.* TO my_role;
   

4. Assign the role to users:

   GRANT my_role TO my_user;
   

5. If migrating, export roles from the source cluster:

   SHOW CREATE ROLE my_role;
   

   Then execute the resulting CREATE ROLE statement on the target cluster.

6. Check XML-based role definitions:

   • If roles are defined in users.xml or users.d/ files, ensure those files are present and correctly formatted on all nodes.

Best Practices

• Create roles before referencing them in GRANT statements or user configurations.
• Include role creation in your database migration and provisioning scripts.
• Use consistent naming conventions for roles across all ClickHouse clusters.
• When decommissioning roles, audit all user assignments and scripts that reference them first.
• In clustered environments, ensure role definitions are synchronized across all nodes, or use ON CLUSTER for SQL-based access control.

Frequently Asked Questions

Q: Are role names case-sensitive in ClickHouse?
A: Yes, role names are case-sensitive. analyst and Analyst are treated as different roles.

Q: Can I create a role that automatically applies to all users?
A: There is no built-in "auto-assign" for roles. You must explicitly grant a role to each user. However, you can set it as a default role so it activates on login for assigned users.

Q: How do I replicate roles across a ClickHouse cluster?
A: Use the ON CLUSTER clause when creating roles: CREATE ROLE my_role ON CLUSTER my_cluster. This ensures the role is created on all cluster nodes.

Q: What happens to users when I drop a role they depend on?
A: The role assignment is removed, and users lose the privileges that role provided. They will not see an error until they try to use a privilege that was only available through that role.