ClickHouse DB::Exception: Cannot set non-granted role

The "DB::Exception: Cannot set non-granted role" error in ClickHouse occurs when a user attempts to activate a role using SET ROLE that has not been granted to them. The SET_NON_GRANTED_ROLE error code means the role exists on the server, but the current user is not among its recipients.

Impact

The user cannot activate the desired role, which means they lack whatever privileges that role carries. Queries and operations that depend on those privileges will fail. This is particularly disruptive when users rely on roles for database access, table creation, or other critical operations.

Common Causes

The role was never granted to the user
The role was granted to a different user or to a role that the user does not hold
An administrator revoked the role and the user was not notified
A typo in the role name when issuing SET ROLE
The user was recently created and role assignments have not yet been configured
Using SET ROLE with a role intended for a different team or environment

Troubleshooting and Resolution Steps

Check which roles are granted to the user:
```
SHOW GRANTS FOR my_user;
```
Look for GRANT role_name TO my_user entries.
Verify the role exists:
```
SHOW ROLES;
```
Make sure the role name is spelled correctly and exists on the server.
Grant the role to the user:
```
GRANT my_role TO my_user;
```
Activate the role:
```
SET ROLE my_role;
```
Optionally set the role as a default so it activates automatically on login:
```
SET DEFAULT ROLE my_role TO my_user;
```
Check for indirect role assignments:
- Roles can be granted to other roles. If the user expects to inherit a role through a chain, verify every link:
```
SHOW GRANTS FOR intermediate_role;
```

Best Practices

Assign default roles to users at creation time so they do not need to run SET ROLE manually.
Use SET DEFAULT ROLE to ensure users automatically activate the roles they need on login.
Maintain a clear mapping of which roles are assigned to which users, documented alongside your infrastructure configuration.
Prefer role hierarchies where a small set of base roles are composed into higher-level roles, making assignments predictable.
When onboarding new users, include role grants as part of the account creation process.

Frequently Asked Questions

Q: What is the difference between GRANT role TO user and SET ROLE?
A: GRANT role TO user is an administrative action that makes a role available to the user. SET ROLE is a session-level command the user runs to activate a granted role for their current session.

Q: Can a user have multiple roles active at once?
A: Yes. A user can activate multiple roles simultaneously with SET ROLE role1, role2 and benefit from the combined privileges of all active roles.

Q: How do I make a role activate automatically when a user logs in?
A: Use SET DEFAULT ROLE my_role TO my_user. Default roles are activated at the start of every session without the user needing to run SET ROLE.

Q: Can I see which roles are currently active in my session?
A: You can check the current roles with SELECT currentRoles() which returns the list of roles active in the current session.