Contact UsGet started
© 2025 Direct Cursus Technology L.L.C.

User management ClickHouse®

Written by
Updated at July 2, 2025

Managed Service for ClickHouse® provides two ways for you to manage users and their individual settings:

  • Using native Yandex Cloud interfaces, such as the management console, CLI, Terraform, or API . 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.

Warning

You cannot disable the SQL user management setting once it is enabled.

In a cluster with user management via SQL enabled:

  • User management via standard Yandex Cloud interfaces (management console, CLI, API, Terraform) is unavailable.
  • The existing users as well as user settings made with the standard Yandex Cloud interfaces will be saved.
  • User management is performed using 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

  1. In the management console, navigate to the folder dashboard and select Managed Service for ClickHouse.
  2. Click the name of the cluster you need and select the Users tab.

If you do not have the Yandex Cloud CLI installed yet, install and initialize it.

By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command 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 the list of clusters in the folder.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the User.list method and send the following request, e.g., via cURL:

    curl \
    --request GET \
    --header "Authorization: Bearer $IAM_TOKEN" \
    --url 'https://mdb.api.cloud.yandex.net/managed-clickhouse/v1/clusters/<cluster_ID>/users'

    You can request the cluster ID with the list of clusters in the folder.

  3. View the server response to make sure the request was successful.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Clone the cloudapi repository:

    cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi

    Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

  3. Use the UserService.List call and send the following request, e.g., via gRPCurl:

    grpcurl \
    -format json \
    -import-path ~/cloudapi/ \
    -import-path ~/cloudapi/third_party/googleapis/ \
    -proto ~/cloudapi/yandex/cloud/mdb/clickhouse/v1/user_service.proto \
    -rpc-header "Authorization: Bearer $IAM_TOKEN" \
    -d '{
            "cluster_id": "<cluster_ID>"
        }' \
    mdb.api.cloud.yandex.net:443 \
    yandex.cloud.mdb.clickhouse.v1.UserService.List

    You can request the cluster ID with the list of clusters in the folder.

  4. View the server response to make sure the request was successful.

  1. Connect to a cluster using the admin account.

  2. Get a list of users:

    SHOW USERS;

Creating a user

  1. In the management console, navigate to the folder dashboard and select Managed Service for ClickHouse.

  2. Click the cluster name and open the Users tab.

  3. Click Create user.

  4. Enter a database user name.

    The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter or underscore. The name can be up to 32 characters long.

  5. Select how to set a password:

    • Enter manually: Enter your own password. The password must be from 8 to 128 characters long.

    • Generate: Generate a password using Connection Manager.

    To view the password, select the Users tab on the cluster page and click View password in the new user row. This will open the page of the Yandex Lockbox secret that stores the password. To view passwords, you need the lockbox.payloadViewer role.

  6. Select one or more databases that the user should have access to:

    1. Click and select a database from the drop-down list.
    2. Repeat the previous step until all the required databases are selected.
    3. To delete a database added by mistake, click to the right of the database name.

  7. Configure additional settings for the user:

    1. Set quotas in Additional settings → Quotas:
      1. To add a quota, click . You can add multiple quotas that will be valid at the same time.
      2. To delete a quota, click to the right of the quota name and select Delete.
      3. To change a quota, set the required values of its settings.
    2. Configure ClickHouse® in Additional settings → Settings.

  8. Click Create.

See the example of creating a user with read-only access.

If you do not have the Yandex Cloud CLI installed yet, install and initialize it.

By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command 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:

  • --cluster-name: Cluster name.

  • --password: User password. The password must be from 8 to 128 characters long.

    You can also generate a password using Connection Manager. To do this, specify --generate-password instead of --password=<password>.

    To view the password, select the cluster you need in the management console, go to the Users tab and click View password in the new user row. This will open the page of the Yandex Lockbox secret that stores the password. To view passwords, you need the lockbox.payloadViewer role.

  • --permissions: List of DBs the user must have access to.

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 the list of clusters in the folder.

See the example of creating a user with read-only access.

  1. Open the current Terraform configuration file that defines your infrastructure.

    For more information about creating this file, see Creating clusters.

  2. Add the yandex_mdb_clickhouse_user resource:

    resource "yandex_mdb_clickhouse_user" "<username>" {
  cluster_id = "<cluster_ID>"
  name       = "<username>"
  password   = "<password>"
  permission {
    database_name = "<DB_name>"
  }
  settings {
    <parameter_1_name> = <value_1>
    <parameter_2_name> = <value_2>
    ...
  }
}

    The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter or underscore. The name can be up to 32 characters long.

    The password must be from 8 to 128 characters long.

    You can also generate a password using Connection Manager. To do this, specify generate_password = true instead of password = "<password>".

    To view the password, select the cluster you need in the management console, go to the Users tab, and click View password in the new user row. This will open the page of the Yandex Lockbox secret that stores the password. To view passwords, you need the lockbox.payloadViewer role.

    If you create a cluster with the help of Terraform at the same time as creating a user, specify a link to the new cluster's name instead of cluster ID in the yandex_mdb_clickhouse_user resource:

    
resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
  name = "<cluster_name>"
  ...
}

resource "yandex_mdb_clickhouse_user" "<username>" {
  cluster_id = yandex_mdb_clickhouse_cluster.<cluster_name>.id
  name       = "<username>"
  ...
}

  3. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  4. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

For more information, see the Terraform provider documentation.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the User.create method and send the following request, e.g., via cURL:

    1. Create a file named body.json and add the following contents to it:

      {
  "userSpec": {
    "name": "<username>",
    "password": "<user_password>",
    "permissions": [
      {
        "databaseName": "<DB_name>"
      }
    ],
    "settings": {<ClickHouse®_settings>},
    "quotas": [
      {
        "intervalDuration": "<quota_interval>",
        "queries": "<total_number_of_queries>",
        "errors": "<number_of_failed_queries>",
        "resultRows": "<number_of_rows_of_result>",
        "readRows": "<number_of_source_rows>",
        "executionTime": "<total_execution_time>"
      },
      { <similar_settings_for_quota_2> },
      { ... },
      { <similar_settings_for_quota_N> }
    ]
  },
  { <similar_settings_for_new_user_2> },
  { ... },
  { <similar_settings_for_new_user_N> }
}

      Where userSpec is an array with settings for the new users. One array element contains settings for a single user and has the following structure:

      • name: Username. It may contain Latin letters, numbers, hyphens, and underscores, and must start with a letter or underscore. The name can be up to 32 characters long.

      • password: User password. The password must be from 8 to 128 characters long.

        You can also generate a password using Connection Manager. To do this, specify "generatePassword": true instead of "password": "<user_password>".

        To view the password, select the cluster you created in the management console, go to the Users tab and click View password in the user's row. This will open the page of the Yandex Lockbox secret that stores the password. To view passwords, you need the lockbox.payloadViewer role.

      • permissions: List of DBs the user must have access to.

        The list appears as an array of databaseName parameters. Each parameter contains the name of a separate database.

      • settings: List of ClickHouse® settings for the user.

        Settings are specified as comma-separated key: value pairs.

      • quotas: Array with quota settings. One array element contains settings for a single quota.

    2. Run this request:

      curl \
  --request POST \
  --header "Authorization: Bearer $IAM_TOKEN" \
  --header "Content-Type: application/json" \
  --url 'https://mdb.api.cloud.yandex.net/managed-clickhouse/v1/clusters/<cluster_ID>/users' \
  --data '@body.json'

      You can request the cluster ID with the list of clusters in the folder.

  3. View the server response to make sure the request was successful.

See the example of creating a user with read-only access.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Clone the cloudapi repository:

    cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi

    Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

  3. Use the ClusterService.Create call and send the following request, e.g., via gRPCurl:

    1. Create a file named body.json and add the following contents to it:

      {
  "cluster_id": "<cluster_ID>",
  "user_spec": {
    "name": "<username>",
    "password": "<user_password>",
    "permissions": [
      {
        "database_name": "<DB_name>"
      }
    ],
    "settings": {<ClickHouse®_settings>},
    "quotas": [
      {
        "interval_duration": "<quota_interval>",
        "queries": "<total_number_of_queries>",
        "errors": "<number_of_failed_queries>",
        "result_rows": "<number_of_rows_of_result>",
        "read_rows": "<number_of_source_rows>",
        "execution_time": "<total_execution_time>"
      },
      { <similar_settings_for_quota_2> },
      { ... },
      { <similar_settings_for_quota_N> }
    ]
  },
  { <similar_settings_for_new_user_2> },
  { ... },
  { <similar_settings_for_new_user_N> }
}

      Where user_spec is an array with settings for the new users. One array element contains settings for a single user and has the following structure:

      • name: Username. It may contain Latin letters, numbers, hyphens, and underscores, and must start with a letter or underscore. The name can be up to 32 characters long.

      • password: User password. The password must be from 8 to 128 characters long.

        You can also generate a password using Connection Manager. To do this, specify "generate_password": true instead of "password": "<user_password>".

        To view the password, select the cluster you created in the management console, go to the Users tab and click View password in the user's row. This will open the page of the Yandex Lockbox secret that stores the password. To view passwords, you need the lockbox.payloadViewer role.

      • permissions: List of DBs the user must have access to.

        The list appears as an array of database_name parameters. Each parameter contains the name of a separate database.

      • settings: List of ClickHouse® settings for the user.

        Settings are specified as comma-separated key: value pairs.

      • quotas: Array with quota settings. One array element contains settings for a single quota.

      You can request the cluster ID with the list of clusters in the folder.

    2. Run this request:

      grpcurl \
  -format json \
  -import-path ~/cloudapi/ \
  -import-path ~/cloudapi/third_party/googleapis/ \
  -proto ~/cloudapi/yandex/cloud/mdb/clickhouse/v1/user_service.proto \
  -rpc-header "Authorization: Bearer $IAM_TOKEN" \
  -d @ \
  mdb.api.cloud.yandex.net:443 \
  yandex.cloud.mdb.clickhouse.v1.UserService.Create \
  < body.json

  4. View the server response to make sure the request was successful.

See the example of creating a user with read-only access.

  1. Connect to a cluster using the admin account.

  2. 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.

  1. In the management console, navigate to the folder dashboard and select Managed Service for ClickHouse.

  2. Click the cluster name and open the Users tab.

  3. Click and select Change password.

  4. Select how to set a new password:

    • Enter manually: Enter your own password. The password must be from 8 to 128 characters long.

    • Generate: Generate a password using Connection Manager.

  5. Click Edit.

To view the new password, select the Users tab on the cluster page and click View password in the user's row. This will open the page of the Yandex Lockbox secret that stores the password. The new password version is labeled as Current.

To view passwords, you need the lockbox.payloadViewer role.

If you do not have the Yandex Cloud CLI installed yet, install and initialize it.

By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command 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>

The password must be from 8 to 128 characters long.

You can also generate a new password using Connection Manager. To do this, specify --generate-password instead of --password=<new_password>.

To view the new password, select the cluster in the management console, go to the Users tab, and click View password in the the user's row. This will open the page of the Yandex Lockbox secret that stores the password. The new password version is labeled as Current.

To view passwords, you need the lockbox.payloadViewer role.

You can request the cluster name with the list of clusters in the folder.

  1. Open the current Terraform configuration file that defines your infrastructure.

    For more information about creating this file, see Creating clusters.

  2. Locate the user's yandex_mdb_clickhouse_user resource.

  3. Change the value of the password field:

    resource "yandex_mdb_clickhouse_user" "<username>" {
  ...
  name     = "<username>"
  password = "<password>"
  ...
}

    The password must be from 8 to 128 characters long.

    You can also generate a new password using Connection Manager. To do this, specify generate_password = true instead of password = "<new_password>".

    To view the new password, select the cluster in the management console, go to the Users tab, and click View password in the the user's row. This will open the page of the Yandex Lockbox secret that stores the password. The new password version is labeled as Current.

    Note

    If the old password was generated, you cannot regenerate it using Terraform due to provider limitations.

  4. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  5. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

For more information, see the Terraform provider documentation.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the User.update method and send the following request, e.g., using cURL:

    Warning

    The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the updateMask parameter as a single comma-separated string.

    curl \
    --request PATCH \
    --header "Authorization: Bearer $IAM_TOKEN" \
    --header "Content-Type: application/json" \
    --url 'https://mdb.api.cloud.yandex.net/managed-clickhouse/v1/clusters/<cluster_ID>/users/<username>' \
    --data '{
              "updateMask": "password",
              "password": "<new_password>"
            }'

    Where:

    • updateMask: List of parameters to update as a single string, separated by commas.

      Here only one parameter is specified: password.

    • password: New password. The password must be from 8 to 128 characters long.

      You can also generate a password using Connection Manager. To do this, edit the contents of the data field:

      {
  "updateMask": "generatePassword",
  "generatePassword": true
}

      To view the new password, select the cluster in the management console, go to the Users tab, and click View password in the the user's row. This will open the page of the Yandex Lockbox secret that stores the password. The new password version is labeled as Current.

      To view passwords, you need the lockbox.payloadViewer role.

    You can request the cluster ID with the list of clusters in the folder. You can request the user name with the list of users in the cluster.

  3. View the server response to make sure the request was successful.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Clone the cloudapi repository:

    cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi

    Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

  3. Use the UserService.Update call and send the following request, e.g., via gRPCurl:

    Warning

    The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the update_mask parameter as an array of paths[] strings.

    Format for listing settings
    "update_mask": {
    "paths": [
        "<setting_1>",
        "<setting_2>",
        ...
        "<setting_N>"
    ]
}

    grpcurl \
    -format json \
    -import-path ~/cloudapi/ \
    -import-path ~/cloudapi/third_party/googleapis/ \
    -proto ~/cloudapi/yandex/cloud/mdb/clickhouse/v1/user_service.proto \
    -rpc-header "Authorization: Bearer $IAM_TOKEN" \
    -d '{
          "cluster_id": "<cluster_ID>",
          "user_name": "<username>",
          "update_mask": {
            "paths": [
              "password"
            ]
          },
          "password": "<new_password>"
        }' \
    mdb.api.cloud.yandex.net:443 \
    yandex.cloud.mdb.clickhouse.v1.UserService.Update

    Where:

    • update_mask: List of parameters to update as an array of paths[] strings.

      Here only one parameter is specified: password.

    • password: New password. The password must be from 8 to 128 characters long.

      You can also generate a password using Connection Manager. To do this, edit the contents of the d parameter:

      {
  "cluster_id": "<cluster_ID>",
  "user_name": "<username>",
  "update_mask": {
    "paths": [
      "generate_password"
    ]
  },
  "generate_password": true
}

      To view the new password, select the cluster in the management console, go to the Users tab, and click View password in the the user's row. This will open the page of the Yandex Lockbox secret that stores the password. The new password version is labeled as Current.

      To view passwords, you need the lockbox.payloadViewer role.

    You can request the cluster ID with the list of clusters in the folder. You can request the user name with the list of users in the cluster.

  4. View the server response to make sure the request was successful.

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 CLI installed yet, install and initialize it.

By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command 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_admin_user_password>

Note

The password must be between 8 and 128 characters.

You can request the cluster ID and name with the 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.

  1. Open the current Terraform configuration file that defines your infrastructure.

    For more information about creating this file, see Creating clusters.

  2. 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.

  3. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  4. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

For more information, see the Terraform provider documentation.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the Cluster.Update method and send the following request, e.g., via cURL:

    Warning

    The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the updateMask parameter as a single comma-separated string.

    curl \
    --request PATCH \
    --header "Authorization: Bearer $IAM_TOKEN" \
    --header "Content-Type: application/json" \
    --url 'https://mdb.api.cloud.yandex.net/managed-clickhouse/v1/clusters/<cluster_ID>' \
    --data '{
              "updateMask": "configSpec.adminPassword",
              "configSpec": {
                "adminPassword": "<new_password>"
              }
            }'

    Where:

    • updateMask: List of parameters to update as a single string, separated by commas.

      Here only one parameter is specified: configSpec.adminPassword.

    • configSpec.adminPassword: New user password.

      The password must be from 8 to 128 characters long.

    You can request the cluster ID with the list of clusters in the folder.

  3. View the server response to make sure the request was successful.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Clone the cloudapi repository:

    cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi

    Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

  3. Use the ClusterService.Update call and send the following request, e.g., via gRPCurl:

    Warning

    The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the update_mask parameter as an array of paths[] strings.

    Format for listing settings
    "update_mask": {
    "paths": [
        "<setting_1>",
        "<setting_2>",
        ...
        "<setting_N>"
    ]
}

    grpcurl \
    -format json \
    -import-path ~/cloudapi/ \
    -import-path ~/cloudapi/third_party/googleapis/ \
    -proto ~/cloudapi/yandex/cloud/mdb/clickhouse/v1/cluster_service.proto \
    -rpc-header "Authorization: Bearer $IAM_TOKEN" \
    -d '{
          "cluster_id": "<cluster_ID>",
          "update_mask": {
            "paths": [
              "config_spec.admin_password"
            ]
          },
          "config_spec": {
            "admin_password": "<new_password>"
          }
        }' \
    mdb.api.cloud.yandex.net:443 \
    yandex.cloud.mdb.clickhouse.v1.ClusterService.Update

    Where:

    • update_mask: List of parameters to update as an array of paths[] strings.

      Here only one parameter is specified: config_spec.admin_password.

    • config_spec.admin_password: New user password.

      The password must be from 8 to 128 characters long.

    You can request the cluster ID with the list of clusters in the folder.

  4. View the server response to make sure the request was successful.

Changing user settings

  1. In the management console, navigate to the folder dashboard and select Managed Service for ClickHouse.
  2. Click the cluster name and open the Users tab.
  3. Click and select Configure.
  4. Configure user permissions to access certain databases:
    1. To grant access to the required databases:
      1. Click and select a database from the drop-down list.
      2. Repeat the previous step until all the required databases are selected.
    2. To delete a database, click to the right of the database name.
  5. Set quotas for the user in Additional settings → Quotas:
    1. To add a quota, click . You can add multiple quotas that will be valid at the same time.
    2. To delete a quota, click to the right of the quota name and select Delete.
    3. To change a quota, set the required values of its settings.
  6. Edit the user ClickHouse® settings under Additional settings → Settings.
  7. Click Save.

If you do not have the Yandex Cloud CLI installed yet, install and initialize it.

By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command using the --folder-name or --folder-id parameter.

You can change the user's settings from the command line interface:

  1. To set up the user's permissions to access particular databases, run the command by 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 the 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.

  2. 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 the 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).

  3. 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 the 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 the readonly 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).

  1. Open the current Terraform configuration file that defines your infrastructure.

    For more information about creating this file, see Creating clusters.

  2. Locate the user's yandex_mdb_clickhouse_user resource.

  3. To set up the user's permissions to access particular databases, add the required number of permission sections, one for each database:

    resource "yandex_mdb_clickhouse_user" "<username>" {
  ...
  name       = "<username>"
  password   = "<password>"
  permission {
    database_name = "<DB_1_name>"
  }
  ...
  permission {
    database_name = "<DB_N_name>"
  }
  ...
}

    In the database_name field, specify the name of the database to grant access to.

  4. 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_user" "<username>" {
  ...
  name       = "<username>"
  password   = "<password>"
  ...
  quota {
    interval_duration = <interval_in_milliseconds>
    ...
  }
}

  5. To edit a user's ClickHouse® settings add a settings section to its description.

    resource "yandex_mdb_clickhouse_user" "<username>" {
  ...
  name       = "<username>"
  password   = "<password>"
  ...
  settings {
    <parameter_1_name> = <value_1>
    <parameter_2_name> = <value_2>
    ...
  }
}

  6. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  7. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

For more information, see the Terraform provider documentation.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the User.update method and send the following request, e.g., using cURL:

    Warning

    The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the updateMask parameter as a single comma-separated string.

    curl \
    --request PATCH \
    --header "Authorization: Bearer $IAM_TOKEN" \
    --header "Content-Type: application/json" \
    --url 'https://mdb.api.cloud.yandex.net/managed-clickhouse/v1/clusters/<cluster_ID>/users/<username>' \
    --data '{
              "updateMask": "<list_of_settings_to_update>",
              "permissions": [ <updated_DB_list> ],
              "settings": { <ClickHouse®_settings> },
              "quotas": [ <updated_list_of_quota_settings> ]
            }'

    Where updateMask is the list of parameters to update as a single string, separated by commas.

    Specify the required parameters to update individual categories of settings:

    • To update the list of databases available to the user, provide the updated list in the permissions parameter.

      The list is arranged as an array of databaseName parameters. Each parameter contains the name of a separate database.

      Warning

      The current DB list in the cluster will be completely overwritten by the list provided in the permissions parameter.

      Before executing your request, make sure the list includes all the required databases, including existing ones.

    • To update ClickHouse® settings for a user, provide the required settings with updated values in the settings parameter.

    • To update quota settings, provide the updated list of settings in the quotas parameter.

      The list is arranged as an array. One array element contains settings for a single quota.

      Warning

      The current list of quota settings in the cluster will be completely overwritten by the list provided in the quotas parameter.

      Before executing your request, make sure the list includes all the required quota settings, including existing ones.

    You can request the cluster ID with the list of clusters in the folder. You can request the user name with the list of users in the cluster.

  3. View the server response to make sure the request was successful.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Clone the cloudapi repository:

    cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi

    Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

  3. Use the UserService.Update call and send the following request, e.g., via gRPCurl:

    Warning

    The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the update_mask parameter as an array of paths[] strings.

    Format for listing settings
    "update_mask": {
    "paths": [
        "<setting_1>",
        "<setting_2>",
        ...
        "<setting_N>"
    ]
}

    grpcurl \
    -format json \
    -import-path ~/cloudapi/ \
    -import-path ~/cloudapi/third_party/googleapis/ \
    -proto ~/cloudapi/yandex/cloud/mdb/clickhouse/v1/user_service.proto \
    -rpc-header "Authorization: Bearer $IAM_TOKEN" \
    -d '{
          "cluster_id": "<cluster_ID>",
          "user_name": "<username>",
          "update_mask": {
            "paths": [
              <list_of_settings_to_update>
            ]
          },
          "permissions": [ <updated_DB_list> ],
          "settings": { <ClickHouse®_settings> },
          "quotas": [ <updated_list_of_quota_settings> ]
        }' \
    mdb.api.cloud.yandex.net:443 \
    yandex.cloud.mdb.clickhouse.v1.UserService.Update

    Where update_mask is the list of parameters to update as an array of paths[] strings.

    Specify the required parameters to update individual categories of settings:

    • To update the list of databases available to the user, provide the updated list in the permissions parameter.

      The list is arranged as an array of database_name parameters. Each parameter contains the name of a separate database.

      Warning

      The current DB list in the cluster will be completely overwritten by the list provided in the permissions parameter.

      Before executing your request, make sure the list includes all the required databases, including existing ones.

    • To update ClickHouse® settings for a user, provide the required settings with updated values in the settings parameter.

    • To update quota settings, provide the updated list of settings in the quotas parameter.

      The list is arranged as an array. One array element contains settings for a single quota.

      Warning

      The current list of quota settings in the cluster will be completely overwritten by the list provided in the quotas parameter.

      Before executing your request, make sure the list includes all the required quota settings, including existing ones.

    You can request the cluster ID with the list of clusters in the folder. You can request the user name with the list of users in the cluster.

  4. View the server response to make sure the request was successful.

  1. Connect to a cluster using the admin account.

  2. 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>;

  3. 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>;

  4. 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

  1. In the management console, navigate to the folder dashboard and select Managed Service for ClickHouse.
  2. Click the cluster name and open the Users tab.
  3. Click and select Delete.

If you do not have the Yandex Cloud CLI installed yet, install and initialize it.

By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command 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 the list of clusters in the folder.

  1. Open the current Terraform configuration file that defines your infrastructure.

    For more information about creating this file, see Creating clusters.

  2. Delete the yandex_mdb_clickhouse_user resource with the user's description.

  3. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  4. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

For more information, see the Terraform provider documentation.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the User.delete method and send the following request, e.g., via cURL:

    curl \
    --request DELETE \
    --header "Authorization: Bearer $IAM_TOKEN" \
    --url 'https://mdb.api.cloud.yandex.net/managed-clickhouse/v1/clusters/<cluster_ID>/users/<username>'

    You can request the cluster ID with the list of clusters in the folder. You can request the user name with the list of users in the cluster.

  3. View the server response to make sure the request was successful.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Clone the cloudapi repository:

    cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi

    Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

  3. Use the UserService.Delete call and send the following request, e.g., via gRPCurl:

    grpcurl \
    -format json \
    -import-path ~/cloudapi/ \
    -import-path ~/cloudapi/third_party/googleapis/ \
    -proto ~/cloudapi/yandex/cloud/mdb/clickhouse/v1/user_service.proto \
    -rpc-header "Authorization: Bearer $IAM_TOKEN" \
    -d '{
            "cluster_id": "<cluster_ID>",
            "user_name": "<username>"
        }' \
    mdb.api.cloud.yandex.net:443 \
    yandex.cloud.mdb.clickhouse.v1.UserService.Delete

    You can request the cluster ID with the list of clusters in the folder. You can request the user name with the list of users in the cluster.

  4. View the server response to make sure the request was successful.

  1. Connect to a cluster using the admin account.

  2. 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 Passw0rd password to the existing mych cluster with the cat0adul1fj0******** ID, 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.
  1. In the management console, navigate to the folder dashboard and select Managed Service for ClickHouse.
  2. Click the mych cluster and select the Users tab.
  3. Click Create user.
  4. Enter ro-user as the DB username and Passw0rd as the password.
  5. Click and select the db1 database from the drop-down list.
  6. Select Additional settings → Settings → Readonly.
  7. Set the Readonly field value to 1.
  8. 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:

  1. Connect to the mych cluster as the ro-user you created.

  2. Try changing a setting, for example, disable read-only mode:

    SET readonly=0

    As a result, the command should return a message stating that you cannot change the setting in read-only mode:

    DB::Exception: Cannot modify 'readonly' setting in readonly mode.

  1. Open the current Terraform configuration file that defines your infrastructure.

    For more information about creating this file, see Creating clusters.

  2. Add the yandex_mdb_clickhouse_user resource:

    resource "yandex_mdb_clickhouse_user" "ro-user" {
  cluster_id = "cat0adul1fj0********"
  name = "ro-user"
  password = "Passw0rd"
  permission {
    database_name = "db1"
  }
  settings {
    readonly = 1
  }
}

  3. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  4. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Make a request using cURL:

    curl \
    --request POST \
    --header "Authorization: Bearer $IAM_TOKEN" \
    --header "Content-Type: application/json" \
    --url 'https://mdb.api.cloud.yandex.net/managed-clickhouse/v1/clusters/cat0adul1fj0********/users' \
    --data '{
              "userSpec": {
                "name": "ro-user",
                "password": "Passw0rd",
                "permissions": [
                  {
                    "databaseName": "db1"
                  }
                ],
                "settings": {
                  "readonly": "1"
                }
              }
            }'

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Clone the cloudapi repository:

    cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi

    Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

  3. Make a request using gRPCurl:

    grpcurl \
    -format json \
    -import-path ~/cloudapi/ \
    -import-path ~/cloudapi/third_party/googleapis/ \
    -proto ~/cloudapi/yandex/cloud/mdb/clickhouse/v1/user_service.proto \
    -rpc-header "Authorization: Bearer $IAM_TOKEN" \
    -d '{
          "cluster_id": "cat0adul1fj0********",
          "user_spec": {
            "name": "ro-user",
            "password": "Passw0rd",
            "permissions": [
              {
                "database_name": "db1"
              }
            ],
            "settings": {
              "readonly": "1"
            }
          }
        }' \
    mdb.api.cloud.yandex.net:443 \
    yandex.cloud.mdb.clickhouse.v1.UserService.Create

  1. Connect to the mych cluster using the admin account.

  2. Create a user:

    CREATE USER ro-user IDENTIFIED WITH sha256_password BY 'Passw0rd';

  3. 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.

Previous
Database management
Next
Managing a custom geobase