Manage Managed ClickHouse® users and roles

Create Managed ClickHouse® users and roles and grant them specific privileges to efficiently control or restrict access to your service.

Manage users

Add a user

Create a user account for your service in one of two ways.

To create a user using a hash and salt, run:

CREATE USER username IDENTIFIED WITH sha256_hash BY 'hash' SALT 'salt';

To create a user using a password directly, run:

CREATE USER username IDENTIFIED BY 'password';

Users’ password digests are stored securely in ZooKeeper, out of reach of service users. The password digests and salts are stored in backup files so they can be recovered.

Configure user settings

Configure user settings to control resource usage and query behavior. You can set limits at the user level (applying to all queries by that user) or configure per-query constraints.

Set user resource limits

Configure user settings, for example, to restrict resource use per user. This can help you:

• Prevent single users from monopolizing CPU threads and starving other users.
• Ensure fair distribution of system resources among all active users.
• Maintain system stability by preventing service overload.

1. Create a user named limited_user.

   CREATE USER limited_user IDENTIFIED WITH SHA256_PASSWORD BY 'PASSWORD';

2. Set resource limit max_threads = 1 for this user.

   ALTER USER limited_user SETTINGS max_threads = 1;

3. Grant read-only access to the default database to limited_user.

   GRANT SELECT ON default.* TO limited_user;

   Note

   On Managed ClickHouse®, data privileges cannot be granted globally with ON *.*. Grant them per database instead, repeating the statement for each database the user needs. Access management privileges such as CREATE USER are global and use ON *.*.

4. Log in as limited_user to test the configuration.

   clickhouse-client --host "$CH_HOST" --port "$CH_PORT" --user limited_user --password PASSWORD --secure

   Replace PASSWORD with the password you set above. $CH_HOST and $CH_PORT are the variables exported in Connect to Managed ClickHouse® with clickhouse-client.

5. Verify the resource limit setting.

   SHOW SETTINGS LIKE 'max_threads';

   This displays the max_threads setting with a value of 1 for the limited_user.

Set per-query resource limits

Constrain resources on a per-query basis for specific users by setting query-level limits in their user profile.

1. Create a user with per-query memory and execution time limits.

   CREATE USER query_limited_user IDENTIFIED WITH SHA256_PASSWORD BY 'PASSWORD'
   SETTINGS max_memory_usage = 1000000000, max_execution_time = 30;

2. Grant appropriate permissions.

   GRANT SELECT ON default.* TO query_limited_user;

3. Test the per-query limits by logging in and running a query.

   clickhouse-client --host "$CH_HOST" --port "$CH_PORT" --user query_limited_user --password PASSWORD --secure

4. Verify the query-level settings.

   SHOW SETTINGS LIKE '%max_%';

Common per-query settings:

• max_memory_usage: Maximum memory per query (in bytes)
• max_execution_time: Maximum query execution time (in seconds)
• max_rows_to_read: Maximum rows a query can examine
• max_result_rows: Maximum rows in query result

Manage roles and privileges

Apart from the platform roles that carry the privileges of the main service user (aiven_admin_role and aiven_readonly_role), Managed ClickHouse has no predefined roles. All roles you create are custom roles you design for your purposes by granting specific privileges to particular roles. For example: you can create a role that allows only reading a single database, table, or column; or you can create another role that allows only inserting data, not deleting it.

ClickHouse® supports a Role Based Access Control model and allows you to configure access privileges by using SQL statements. You can rely on the command-line interface.

The upstream ClickHouse documentation includes detailed documentation for access rights.

Create a role

To create a role named auditor, run the following command:

CREATE ROLE auditor;

Find more information on creating roles in the upstream ClickHouse documentation.

Grant privileges

You can grant privileges both to specific roles and to individual users. The grants can be also granular, targeting specific databases, tables, columns, or rows.

As an example, the following request grants the auditor role privileges to select data from the transactions database:

GRANT SELECT ON transactions.* TO auditor;

You can limit the grant to a specified table:

GRANT SELECT ON transactions.expenses TO auditor;

Or to particular columns of a table:

GRANT SELECT(Date, Title, Amount) ON transactions.expenses TO auditor

To grant several roles to several users at once, first make sure they all exist:

CREATE ROLE external;
CREATE USER mary_anderson IDENTIFIED WITH sha256_password BY 'Password1?';
CREATE USER james_miller IDENTIFIED WITH sha256_password BY 'Password2?';

Then run:

GRANT auditor, external TO mary_anderson, james_miller;

To allow a role, such as auditor, to create new users:

GRANT CREATE USER ON *.* TO auditor

There are a variety of privileges that you can grant. Find the full list in the upstream ClickHouse documentation.

Find all details on how the GRANT statement is supported in ClickHouse in the upstream ClickHouse documentation.

Set roles

A single user can be assigned different roles, either individually or simultaneously.

SET ROLE auditor;

You can also specify a role to be activated by default when the user logs in:

SET DEFAULT ROLE auditor, external TO mary_anderson, james_miller;

Delete a role

If you no longer need a role, you can remove it:

DROP ROLE auditor;

Revoke privileges

Remove all or specific privileges from users or roles:

REVOKE SELECT ON transactions.expenses FROM mary_anderson;

Revoke all privileges to a table or database simultaneously:

REVOKE ALL PRIVILEGES ON transactions.expenses FROM external;

See the ClickHouse documentation for more information on revoking privileges.

Check privileges

Run the following commands to see all available grants, users, and roles:

SHOW GRANTS;

SHOW USERS;

SHOW ROLES;