Skip to main content

Manage Aiven for ClickHouse® users and roles

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

Manage users​

Add a user​

To create a user account for your service:

  1. Log in to the Aiven Console and choose your Aiven for ClickHouse service.

  2. In the service sidebar, click Users.

    This shows a list of all users that are currently available in your service. The default avnadmin user has all available access grants to the service.

    tip

    To view the roles and grants for any of the listed users, click View details & grants for that user.

  3. On the Users page, click Add user.

  4. In the Create a service user window, enter a name for the new user and click a role.

    The role that you select defines the access grants that are assigned to the user. For more information on roles, see Manage roles and privileges.

  5. Click Add user.

    This creates the new user and shows you a summary of the information.

  6. Copy the password on screen to a safe place. You can't access it again later, but you can reset it if needed.

Users' password digests are stored securely in ZooKeeper. Only a super admin can access them. For users created in the console, their passwords are also salted with a random value. 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 all databases and tables to limited_user.

    GRANT SELECT ON *.* TO limited_user;

  4. Log in as limited_user to test the configuration.

    clickhouse-client \
      --host YOUR_HOST \
      --port YOUR_PORT \
      --user limited_user \
      --password PASSWORD \
      --secure

    Replace YOUR_HOST, YOUR_PORT, and PASSWORD with your service connection details.

  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 *.* TO query_limited_user;

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

    clickhouse-client \
      --host YOUR_HOST \
      --port YOUR_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​

Aiven for 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 either use the query editor or 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.

important

You cannot grant additional privileges to the main service user. Aiven may grant privileges to the main service user during maintenance updates when adding new features for the service.

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,description,amount) ON transactions.expenses TO auditor

To grant the auditor and external roles to several users, run:

GRANT auditor, external TO Mary.Anderson, James.Miller;

To allow the creation of new users:

GRANT CREATE USER ON transactions.* TO administrator

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

note

You can grant privileges to a table that does not yet exist.

note

Users can grant privileges according to their privileges. If the user lacks the required privileges for a requested operation, they receive a Not enough privileges exception.

warning

Privileges are not revoked when a table or database is removed. They continue to be active for any new table or database that is created with the same name.

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 database.table FROM external;

For more information about revoking privileges, see the ClickHouse documentation.

Check privileges​

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

SHOW GRANTS;
SHOW USERS;
SHOW ROLES;

Preview users and roles in the console​

You can also see the users, their roles, and privileges in the Aiven Console.

  1. Log in to the Aiven Console and choose your Aiven for ClickHouse service.
  2. In the service sidebar, click Users.
  3. Click View details & grants next to one of the users listed on the page. This shows you a list of all grants for the selected user.

Manage using Terraform​

You can also manage user roles and access using the Aiven Provider for Terraform. Learn how to manage multiple grants while avoiding conflicts with the ClickHouse grants example.