Managing ClickHouse users
Managed Service for ClickHouse lets you manage users and their individual settings in two ways:
- Using Nebius AI standard interfaces (CLI or management console). Select this method to create, update, and delete users and custom user settings using Managed Service for ClickHouse features.
- SQL queries to the cluster. Select this method to use your existing solutions to create and manage users or if you are using RBAC
.
Warning
In a Managed Service for ClickHouse cluster, you can only use one user management method at a time: either via standard interfaces or via SQL queries.
Warning
In a Managed Service for ClickHouse cluster, you can only use one user management method at a time: either using standard interfaces or via SQL queries.
Managing users via SQL
To enable management, activate the User management via SQL option when creating or reconfiguring a cluster.
In a cluster with user management via SQL enabled:
- User management using the standard Nebius AI interfaces (CLI, management console) is unavailable.
- The existing users as well as user settings made with the standard Nebius AI interfaces will be saved.
- Users are managed under the
admin
account. You set its password when you select the User management via SQL option.
For more information about managing users via SQL, see the ClickHouse documentation
Getting a list of users
- In the management console
, go to the folder page and select Managed Service for ClickHouse. - Click the name of the cluster and select the Users tab.
If you don't have the Nebius AI command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To get a list of cluster users, run the following command:
ncp managed-clickhouse user list
--cluster-name=<cluster name>
The cluster name can be requested with a list of clusters in the folder.
-
Connect to a cluster using the
admin
account. -
Get a list of users:
SHOW USERS;
Adding a user
-
In the management console
, go to the folder page and select Managed Service for ClickHouse. -
Click the cluster name and open the Users tab.
-
Click Add.
-
Enter the database username and password.
Note
The username may contain Latin letters, numbers, hyphens, and underscores, but must begin with a letter or an underscore.
The password must be between 8 and 128 characters.
-
Select one or more databases that the user should have access to:
- Click
- Repeat the previous step until all the required databases are selected.
- To delete a database added by mistake, click
to the right of the database name.
- Click
-
Configure additional settings for the user:
- Set quotas in Additional settings → Quotas:
- To add a quota, click
or the + Quotas button. You can add multiple quotas that will be valid at the same time. - To delete a quota, click
- To change a quota, set the required values of its settings.
- To add a quota, click
- Configure ClickHouse in Additional settings → Settings.
- Set quotas in Additional settings → Quotas:
-
Click Add.
If you don't have the Nebius AI command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To create a user in a cluster, run the command:
ncp managed-clickhouse user create <username> \
--cluster-name=<cluster name> \
--password=<user password> \
--permissions=<list of databases to grant the user access to> \
--quota=<list if single-quota user settings> \
--settings=<list of ClickHouse user settings>
Note
The username may contain Latin letters, numbers, hyphens, and underscores, but must begin with a letter or an underscore.
The password must be between 8 and 128 characters.
For more information about quotas and user-level settings, see ClickHouse settings.
To set multiple quotas, list them using the required number of --quota
parameters in the command:
ncp managed-clickhouse user create <username> \
...
--quota="<quota 0 settings>" \
--quota="<quota 1 settings>" \
...
The cluster name can be requested with a list of clusters in the folder.
-
Connect to a cluster using the
admin
account. -
Create a user:
CREATE USER <username> IDENTIFIED WITH sha256_password BY '<user password>';
Note
The username may contain Latin letters, numbers, hyphens, and underscores, but must begin with a letter or an underscore.
The password must be between 8 and 128 characters.
For more information about creating users, see the ClickHouse documentation
Changing a password
We recommend that you use the Nebius AI interfaces listed below. Do not use SQL to change your password; otherwise, the password may revert to the previous one after maintenance.
- In the management console
, go to the folder page and select Managed Service for ClickHouse. - Click the cluster name and open the Users tab.
- Click
- Set a new password and click Edit.
Note
The password must be between 8 and 128 characters.
If you don't have the Nebius AI command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To change the user's password, run the command:
ncp managed-clickhouse user update <username>\
--cluster-name=<cluster name>\
--password=<new password>
Note
The password must be between 8 and 128 characters.
The cluster name can be requested with a list of clusters in the folder.
Changing the admin password
We recommend that you use the Nebius AI interfaces listed below. Do not use SQL to change your password; otherwise, the password may revert to the previous one after maintenance.
If you don't have the Nebius AI command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To change the admin
password, run the command below:
ncp managed-clickhouse cluster update <cluster ID or name> \
--admin-password <new admin account password>
Note
The password must be between 8 and 128 characters.
You can query the cluster ID and name with a list of clusters in the folder.
Tip
- For increased security, instead of
--admin-password
, use the--read-admin-password
parameter: you will need to enter the new password using the keyboard, and it will not be saved in the command history. - To generate a password automatically, use
--generate-admin-password
. The command output will contain the new password.
Changing user settings
- In the management console
, go to the folder page and select Managed Service for ClickHouse. - Click the cluster name and open the Users tab.
- Click
- Set up user permissions to access certain databases:
- To grant access to the required databases:
- Click
- Repeat the previous step until all the required databases are selected.
- Click
- To delete a database, click
to the right of the database name.
- To grant access to the required databases:
- Set quotas for the user in Additional settings → Quotas:
- To add a quota, click + Quotas. You can add multiple quotas that will be valid at the same time.
- To delete a quota, click
- To change a quota, set the required values of its settings.
- Edit the user ClickHouse settings under Additional settings → Settings.
- Click Save.
If you don't have the Nebius AI command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
You can change the user settings from the command line interface:
-
To set up the user's permissions to access certain databases, run the command, listing the database names in the
--permissions
parameter:ncp managed-clickhouse user update <username> \ --cluster-name=<cluster name> \ --permissions=<list of databases to grant a user access to>
The cluster name can be requested with a list of clusters in the folder.
This command grants the user access rights to the databases listed.
To revoke access to a specific database, remove its name from the list and send the updated list to the command.
-
To change the user's quota settings, run the command with a list of all quotas, using
--quota
parameters (one parameter per quota):ncp managed-clickhouse user update <username> \ --cluster-name=<cluster name> \ --quota=<quota 0 settings (no change)> \ --quota=<quota 1 settings (no change)> \ --quota=<quota 2 settings (changes)> \ --quota=<quota 3 settings (no change)> \ --quota=<quota 4 settings (changes)> \ --quota=<quota 5 settings (new quota)> ...
The cluster name can be requested with a list of clusters in the folder.
This command overwrites all existing user quota settings with the new settings that you passed to the command.
Before running the command, make sure that you included the settings for new and changed quotas and the settings for existing quotas that haven't changed.To delete one or more user quotas, exclude their settings from the list and send the updated list of
--quota
parameters to the command.When setting an interval, you can use an entry with units: hours (
h
), minutes (m
), seconds (s
), and milliseconds (ms
). Sample entry:3h20m10s7000ms
(the resulting value is still represented in milliseconds:12017000
). The interval value must be a multiple of 1000 milliseconds (a value like1s500ms
is incorrect). -
To edit a user's ClickHouse settings, run the command below listing the changed setting using the
--settings
option:ncp managed-clickhouse user update <username> \ --cluster-name=<cluster name> \ --settings=<list of ClickHouse settings>
The cluster name can be requested with a list of clusters in the folder.
The command only changes the settings that are explicitly specified in the
--settings
parameter. For example, the command with the parameter--settings="readonly=1"
only changes thereadonly
setting and doesn't reset the values of the other settings. This is how changing ClickHouse settings differs from changing quota settings.You cannot use this command to delete an existing setting. You can only explicitly set it to its default value (specified for each setting).
-
Connect to a cluster using the
admin
account. -
To alter the set of user roles and privileges, use the GRANT
and REVOKE statements. For example, grant the user read rights to all objects in a specific database:GRANT SELECT ON <database name>.* TO <username>;
-
To update user quota settings, use the CREATE QUOTA
, ALTER QUOTA , and DROP QUOTA statements. For example, limit the total number of user requests for a 15-month period:CREATE QUOTA <quota name> FOR INTERVAL 15 MONTH MAX QUERIES 100 TO <username>;
-
To change a user account, use the ALTER USER
statement. To edit the ClickHouse settings, for instance, run the command below listing the settings to modify:ALTER USER <username> SETTINGS <list of ClickHouse settings>;
Deleting a user
- In the management console
, go to the folder page and select Managed Service for ClickHouse. - Click the cluster name and open the Users tab.
- Click
If you don't have the Nebius AI command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To remove a user, run:
ncp managed-clickhouse user delete <username>\
--cluster-name <cluster name>
The cluster name can be requested with a list of clusters in the folder.
-
Connect to a cluster using the
admin
account. -
Delete the user:
DROP USER <username>;
For more information about deleting objects, see the ClickHouse documentation
Examples
Creating a read-only user
Let's say you need to add a new user named ro-user
with the password Passw0rd
to the existing mych
cluster, and:
- The user has access to the
db1
database of the cluster. - The access is read-only, so the user isn't allowed to change any settings.
- In the management console
, go to the folder page and select Managed Service for ClickHouse. - Click the
mych
cluster and select the Users tab. - Click Add.
- Enter
ro-user
as the DB username andPassw0rd
as the password. - Click
db1
database from the drop-down list. - Select Additional settings → Settings → Readonly.
- Set the Readonly field value to
1
. - Click Add.
Run the command:
ncp managed-clickhouse user create "ro-user" \
--cluster-name="mych" \
--password="Passw0rd" \
--permissions="db1" \
--settings="readonly=1"
After creating the user, check that it is actually in read-only mode:
-
Connect to a ClickHouse cluster called
mych
with thero-user
user you created. -
Try changing a setting, for example, disable read-only mode:
SET readonly=0
As a result, the command should display a message stating that you can't change the setting in read-only mode:
DB::Exception: Cannot modify 'readonly' setting in readonly mode.
-
Connect to the
mych
cluster using theadmin
account. -
Create a user:
CREATE USER ro-user IDENTIFIED WITH sha256_password BY 'Passw0rd';
-
Grant the user read rights to all objects in the
db1
database:GRANT SELECT ON db1.* TO ro-user;