Managing ClickHouse® users
Managed Service for ClickHouse® provides two ways for you to manage users and their individual settings:
- Using native Yandex Cloud interfaces, such as the CLI, API, or management console. Select this method to create, update, and delete users and custom user settings using Yandex 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.
Note
Creating a new ClickHouse® cluster automatically creates service users to administer and monitor the service.
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 Yandex Cloud interfaces (CLI, API, management console) is unavailable.
- You cannot enable user management using standard Yandex Cloud interfaces (CLI, API, or management console).
- The existing users as well as user settings made with the standard Yandex Cloud 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 you need and select the Users tab.
If you do not have the Yandex Cloud 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:
yc managed-clickhouse user list
--cluster-name=<cluster_name>
You can request the cluster name with a list of clusters in the folder.
To get a list of users, use the list REST API method for the User resource or the UserService/List gRPC API call and provide the cluster ID in the clusterId
request parameter.
You can get the cluster ID with a list of clusters in the folder.
-
Connect to a cluster using the
admin
account. -
Get a list of users:
SHOW USERS;
Creating 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 Create user.
-
Enter the database username and password.
Note
The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter or underscore.
The password must be from 8 to 128 characters long.
-
Select one or more databases that the user should have access to:
- Click
and select a database from the drop-down list. - 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
. You can add multiple quotas that will be valid at the same time. - To delete a quota, click
to the right of the quota name and select Delete. - 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 Create.
If you do not have the Yandex Cloud 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:
yc managed-clickhouse user create <username> \
--cluster-name=<cluster_name> \
--password=<user_password> \
--permissions=<DB_list> \
--quota=<list_of_single_quota_settings_for_user> \
--settings=<list_of_ClickHouse®_settings_for_user>
Where --permissions
is a list of DBs the user must have access to.
Note
The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter or underscore.
The password must be from 8 to 128 characters long.
For more information about quotas and query-level settings, see ClickHouse® settings.
To set multiple quotas, list them using the required number of --quota
parameters in the command:
yc managed-clickhouse user create <username> \
...
--quota="<quota_0_settings>" \
--quota="<quota_1_settings>" \
...
You can request the cluster name with a list of clusters in the folder.
-
Open the current Terraform configuration file with an infrastructure plan.
For more information about creating this file, see Creating clusters.
-
Add the
user
section to the Managed Service for ClickHouse® cluster description:resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" { ... user { name = "<username>" password = "<password>" ... } }
Note
The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter or underscore.
The password must be from 8 to 128 characters long.
-
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
For more information, see the Terraform provider documentation
Time limits
A Terraform provider sets the timeout for Managed Service for ClickHouse® cluster operations:
- Creating a cluster, including by restoring one from a backup: 60 minutes.
- Editing a cluster: 90 minutes.
- Deleting a cluster: 30 minutes.
Operations exceeding the set timeout are interrupted.
How do I change these limits?
Add the timeouts
block to the cluster description, for example:
resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
...
timeouts {
create = "1h30m" # 1 hour 30 minutes
update = "2h" # 2 hours
delete = "30m" # 30 minutes
}
}
To create a user, use the create REST API method for the User resource or the UserService/Create gRPC API call and provide the following in the request:
- Cluster ID in the
clusterId
parameter. You can get the cluster ID with a list of clusters in the folder. - New username, in the
userSpec.name
parameter. - New user password, in the
userSpec.password
parameter. - (Optional) List of databases to grant the user access to, in the
userSpec.permissions[]
parameter. - (Optional) List of ClickHouse® user settings, in the
userSpec.settings
parameter. - (Optional) List of quota user settings, in the
userSpec.quotas[]
parameter.
-
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 underscore.
The password must be from 8 to 128 characters long.
For more information about creating users, see the ClickHouse® documentation
Changing a password
We recommend that you use the Yandex Cloud interfaces listed below. Do not use SQL to change your password; otherwise, the password may reset 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
and select Change password. - Set a new password and click Edit.
Note
The password must be between 8 and 128 characters.
If you do not have the Yandex Cloud 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 password, run this command:
yc managed-clickhouse user update <username> \
--cluster-name=<cluster_name> \
--password=<new_password>
Note
The password must be between 8 and 128 characters.
You can request the cluster name with a list of clusters in the folder.
-
Open the current Terraform configuration file with an infrastructure plan.
For more information about creating this file, see Creating clusters.
-
In the Managed Service for ClickHouse® cluster description, find the
user
block for the required user. -
Change the value of the
password
field:resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" { ... user { name = "<username>" password = "<new_password>" ... } }
Note
The password must be between 8 and 128 characters.
-
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
For more information, see the Terraform provider documentation
Time limits
A Terraform provider sets the timeout for Managed Service for ClickHouse® cluster operations:
- Creating a cluster, including by restoring one from a backup: 60 minutes.
- Editing a cluster: 90 minutes.
- Deleting a cluster: 30 minutes.
Operations exceeding the set timeout are interrupted.
How do I change these limits?
Add the timeouts
block to the cluster description, for example:
resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
...
timeouts {
create = "1h30m" # 1 hour 30 minutes
update = "2h" # 2 hours
delete = "30m" # 30 minutes
}
}
To update a password, use the update REST API method for the User resource or the UserService/Update gRPC API call and provide the following in the request:
-
Cluster ID in the
clusterId
parameter. To find out the cluster ID, get a list of clusters in the folder. -
New password in the
password
parameter.Note
The password must be between 8 and 128 characters.
-
List of user configuration fields to update (
password
in this case) in theupdateMask
parameter.
Warning
This API method overrides all parameters of the object being modified that were not explicitly passed in the request to the default values. To avoid this, list the settings you want to change in the updateMask
parameter (one line separated by commas).
Changing the admin password
We recommend that you use the Yandex Cloud interfaces listed below. Do not use SQL to change your password; otherwise, the password may reset to the previous one after maintenance.
If you do not have the Yandex Cloud 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:
yc managed-clickhouse cluster update <cluster_name_or_ID> \
--admin-password <new_password_for_admin_user>
Note
The password must be between 8 and 128 characters.
You can request 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.
-
Open the current Terraform configuration file with an infrastructure plan.
For more information about creating this file, see Creating clusters.
-
Change the value of the
admin_password
field:resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" { ... admin_password = "<admin_user_password>" ... }
Note
The password must be between 8 and 128 characters.
-
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
For more information, see the Terraform provider documentation
Time limits
A Terraform provider sets the timeout for Managed Service for ClickHouse® cluster operations:
- Creating a cluster, including by restoring one from a backup: 60 minutes.
- Editing a cluster: 90 minutes.
- Deleting a cluster: 30 minutes.
Operations exceeding the set timeout are interrupted.
How do I change these limits?
Add the timeouts
block to the cluster description, for example:
resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
...
timeouts {
create = "1h30m" # 1 hour 30 minutes
update = "2h" # 2 hours
delete = "30m" # 30 minutes
}
}
To update the admin
user's password, use the update REST API method for the Cluster resource or the ClusterService/Update gRPC API call and provide the following in the request:
-
Cluster ID in the
clusterId
parameter. To find out the cluster ID, get a list of clusters in the folder. -
New password in the
configSpec.adminPassword
parameter.Note
The password must be between 8 and 128 characters.
-
List of user configuration fields to update (
configSpec.adminPassword
in this case) in theupdateMask
parameter.
Warning
This API method overrides all parameters of the object being modified that were not explicitly passed in the request to the default values. To avoid this, list the settings you want to change in the updateMask
parameter (one line separated by commas).
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
and select Configure. - Set up user permissions to access certain databases:
- To grant access to the required databases:
- Click
and select a database from the drop-down list. - 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
. You can add multiple quotas that will be valid at the same time. - To delete a quota, click
to the right of the quota name and select Delete. - To change a quota, set the required values of its settings.
- To add a quota, click
- Edit the user ClickHouse® settings under Additional settings → Settings.
- Click Save.
If you do not have the Yandex Cloud 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:yc managed-clickhouse user update <username> \ --cluster-name=<cluster_name> \ --permissions=<DB_list>
You can request the cluster name 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):yc managed-clickhouse user update <username> \ --cluster-name=<cluster_name> \ --quota=<quota_0_settings_(unchanged)> \ --quota=<quota_1_settings_(unchanged)> \ --quota=<quota_2_settings_(changed)> \ --quota=<quota_3_settings_(unchanged)> \ --quota=<quota_4_settings_(changed)> \ --quota=<quota_5_settings_(new_quota)> ...
You can request the cluster name with a list of clusters in the folder.
This command overwrites all existing user quota settings with the new ones you provided 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 have not 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 1,000 milliseconds (e.g.,1s500ms
is incorrect). -
To edit a user's ClickHouse® settings, run the command below listing the changed setting using the
--settings
option:yc managed-clickhouse user update <username> \ --cluster-name=<cluster_name> \ --settings=<list_of_ClickHouse®_settings>
You can request the cluster name 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 does not 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).
-
Open the current Terraform configuration file with an infrastructure plan.
For more information about creating this file, see Creating clusters.
-
To configure the user's permissions to access certain databases, add the required number of
permission
sections to the cluster user description, one for each database:resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" { ... user { name = "<username>" password = "<password>" permission { database_name = "<database_1>" } ... permission { database_name = "<database_N>" } } }
In the
database_name
field, specify the name of the database to grant access to. -
To change quota settings for the user, add the required number of
quota
blocks to the cluster user description.When describing quotas, only the
interval_duration
field is required.resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" { ... user { name = "<username>" password = "<password>" ... quota { interval_duration = <interval_in_milliseconds> ... } } }
-
To edit a user's ClickHouse® settings add a
settings
section to its description.resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" { ... user { name = "<username>" password = "<password>" ... settings { <DBMS_settings_for_specific_user> } } }
-
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
For more information, see the Terraform provider documentation
Time limits
A Terraform provider sets the timeout for Managed Service for ClickHouse® cluster operations:
- Creating a cluster, including by restoring one from a backup: 60 minutes.
- Editing a cluster: 90 minutes.
- Deleting a cluster: 30 minutes.
Operations exceeding the set timeout are interrupted.
How do I change these limits?
Add the timeouts
block to the cluster description, for example:
resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
...
timeouts {
create = "1h30m" # 1 hour 30 minutes
update = "2h" # 2 hours
delete = "30m" # 30 minutes
}
}
To update user settings, use the update REST API method for the User resource or the UserService/Update gRPC API call and provide the following in the request:
- Cluster ID in the
clusterId
parameter. To find out the cluster ID, get a list of clusters in the folder. - Username with the settings to be changed, in the
userName
parameter. To find out the name, get a list of users. - (Optional) List of databases to grant the user access to, in the
userSpec.permissions[]
parameter. - (Optional) List of ClickHouse® user settings, in the
userSpec.settings
parameter. - (Optional) List of quota user settings, in the
userSpec.quotas[]
parameter. - List of user configuration fields to update in the
updateMask
parameter.
Warning
This API method overrides all parameters of the object being modified that were not explicitly passed in the request to the default values. To avoid this, list the settings you want to change in the updateMask
parameter (one line separated by commas).
-
Connect to a cluster using the
admin
account. -
To change a set of user privileges and roles, use the GRANT
and REVOKE queries. For example, grant the user read rights to all objects in a specific database:GRANT SELECT ON <DB_name>.* TO <username>;
-
To edit a user's quota settings, use the CREATE QUOTA
, ALTER QUOTA , and DROP QUOTA queries. 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 the user account, use the ALTER USER
query. 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
and select Delete.
If you do not have the Yandex Cloud 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:
yc managed-clickhouse user delete <username> \
--cluster-name=<cluster_name>
You can request the cluster name with a list of clusters in the folder.
-
Open the current Terraform configuration file with an infrastructure plan.
For more information about creating this file, see Creating clusters.
-
Delete the user block with a description of the required
user
from the Managed Service for ClickHouse® cluster description. -
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
For more information, see the Terraform provider documentation
Time limits
A Terraform provider sets the timeout for Managed Service for ClickHouse® cluster operations:
- Creating a cluster, including by restoring one from a backup: 60 minutes.
- Editing a cluster: 90 minutes.
- Deleting a cluster: 30 minutes.
Operations exceeding the set timeout are interrupted.
How do I change these limits?
Add the timeouts
block to the cluster description, for example:
resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
...
timeouts {
create = "1h30m" # 1 hour 30 minutes
update = "2h" # 2 hours
delete = "30m" # 30 minutes
}
}
To delete a user, use the delete REST API method for the User resource or the UserService/Delete gRPC API call and provide the following in the request:
- Cluster ID in the
clusterId
parameter. To find out the cluster ID, get a list of clusters in the folder. - Username with the settings to be changed, in the
userName
parameter. To find out the name, get a list of users.
-
Connect to a cluster using the
admin
account. -
Delete the user:
DROP USER <username>;
To learn more 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 is not 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 Create user.
- Enter
ro-user
as the DB username andPassw0rd
as the password. - Click
and select thedb1
database from the drop-down list. - Select Additional settings → Settings → Readonly.
- Set the Readonly field value to
1
. - Click Create.
Run the command:
yc 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 the
mych
cluster as thero-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 cannot change the setting in read-only mode:
DB::Exception: Cannot modify 'readonly' setting in readonly mode.
-
Open the current Terraform configuration file with an infrastructure plan.
For more information about creating this file, see Creating clusters.
-
Add the
user
section to the cluster description.resource "yandex_mdb_clickhouse_cluster" "mych" { name = "mych" database { name = "db1" } user { name = "ro-user" password = "Passw0rd" permission { database_name = "db1" } settings { readonly = 1 } } ... }
-
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
-
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;
ClickHouse® is a registered trademark of ClickHouse, Inc