Why `SET ROLE` does not persist in the ClickHouse Cloud SQL Console

When you run SET ROLE in the ClickHouse Cloud SQL Console, the role might appear to change for one query and then revert for the next query. Use a per-user SQL Console role when permissions must persist across queries and sessions.

Symptoms

You might observe one or more of the following:

After you run SET ROLE sql_console_developer, later queries still run with sql_console_read_only.
The results of currentRoles, enabledRoles, and defaultRoles vary between queries.
Running SET ROLE and another query together does not consistently preserve the selected role.
SHOW GRANTS lists the expected roles, but their permissions are not active.

You can inspect the current user and roles with:

SELECT
    currentUser(),
    currentRoles(),
    enabledRoles(),
    defaultRoles();

Why this happens

The SQL Console sends queries over stateless HTTP connections to a multi-replica ClickHouse Cloud service. Consecutive queries are not guaranteed to use the same connection or replica. SET ROLE changes the roles enabled for the current session. It does not persist that session state for later SQL Console requests. A subsequent query can therefore run without the role that an earlier request enabled. For this reason, do not use SET ROLE as a persistent access-control mechanism in the SQL Console.

How SQL Console user roles work

When a user opens the SQL Console, ClickHouse Cloud provisions a database user with the following naming convention:

sql-console:user@example.com

ClickHouse Cloud also checks for a database role whose name uses the following convention:

sql-console-role:user@example.com

When that role exists, ClickHouse Cloud assigns it to the matching SQL Console user. This is the supported way to grant persistent custom permissions to an individual SQL Console user.

Entity	Purpose	Persistent
`sql-console:<email>`	Database user provisioned when the user opens the SQL Console	Yes, managed by ClickHouse Cloud
`sql_console_admin` and `sql_console_read_only`	Built-in SQL Console roles	Yes, managed by ClickHouse Cloud
`sql-console-role:<email>`	Custom per-user role created by an administrator	Yes, applied when the user signs in

Configure persistent permissions

Run the following statements as a user with administrative privileges on the service, such as a SQL Console user with the sql_console_admin role or another user with the ACCESS MANAGEMENT privilege.

Create the custom role

The following example creates a custom sql_console_developer role and grants it permissions on my_database:

CREATE ROLE IF NOT EXISTS sql_console_developer;

GRANT SELECT, INSERT, CREATE TABLE
ON my_database.*
TO sql_console_developer;

sql_console_developer is an example role, not a built-in ClickHouse Cloud role. You can instead use an existing custom role with the permissions that the user requires.

Create the per-user SQL Console role

Create a role whose name contains the user’s exact email address:

CREATE ROLE IF NOT EXISTS `sql-console-role:user@example.com`;

The backticks are required because the role name contains special characters.

Grant the custom role

Grant the desired role to the per-user SQL Console role:

GRANT sql_console_developer
TO `sql-console-role:user@example.com`;

You can grant multiple roles when needed:

GRANT sql_console_developer, sql_console_read_only
TO `sql-console-role:user@example.com`;

Start a new SQL Console session

Ask the user to sign out and sign back in to the SQL Console, or refresh the browser tab. In the new session, ClickHouse Cloud applies sql-console-role:user@example.com to sql-console:user@example.com; no SET ROLE statement is required.Verify the active roles:

SELECT
    currentUser(),
    currentRoles(),
    enabledRoles(),
    defaultRoles();

The results should include the permissions granted through sql-console-role:user@example.com.

Avoid modifying managed roles

Do not modify sql_console_admin or sql_console_read_only to grant custom permissions. ClickHouse Cloud manages these built-in roles. Use sql-console-role:<email> for per-user permissions instead. For general role-management examples, see Common access management queries.

​Symptoms

​Why this happens

​How SQL Console user roles work

​Configure persistent permissions

​Avoid modifying managed roles

Symptoms

Why this happens

How SQL Console user roles work

Configure persistent permissions

Avoid modifying managed roles