User management ClickHouse®

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 get the cluster name with the list of clusters in the folder.

1. Get an IAM token for API authentication and save it as an 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 get the cluster ID with the list of clusters in the folder.

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

1. Get an IAM token for API authentication and save it as an 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 get the cluster ID with the list of clusters in the folder.

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

1. Connect to the cluster as admin.

2. Get a list of users:

   SHOW USERS;
   

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 this 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 with 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 your password. To view passwords, you need the lockbox.payloadViewer role.

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

For more information about quotas and query-level settings, see ClickHouse® settings.

To set multiple quotas, list them by specifying the --quota parameter once for each quota in the following command:

yc managed-clickhouse user create <username> \
   ...
   --quota="<quota_0_settings>" \
   --quota="<quota_1_settings>" \
   ...

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

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

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

   For more information about creating this file, see this guide.

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 your password. To view passwords, you need the lockbox.payloadViewer role.

   When creating a cluster with Terraform at the same time as creating a user, specify a name for the new cluster rather than 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 this Terraform provider article.

1. Get an IAM token for API authentication and save it as an 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 paste the following code into 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_result_rows>",
              "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 the array of the new users' settings. 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 with 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 your password. To view passwords, you need the lockbox.payloadViewer role.

      • permissions: List of DBs the user should 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 of 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 get the cluster ID with the list of clusters in the folder.

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

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

1. Get an IAM token for API authentication and save it as an 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 paste the following code into 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_result_rows>",
              "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 the array of the new users' settings. 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 with 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 your password. To view passwords, you need the lockbox.payloadViewer role.

      • permissions: List of DBs the user should 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 of quota settings. One array element contains settings for a single quota.

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

   2. Run this query:

      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 your request was successful.

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

1. Connect to your cluster as admin.

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 this ClickHouse® article.

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 a 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 with 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 your password. The new password version is labeled as Current.

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

You can get 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 this guide.

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 with 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 your 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. Validate your configuration.

   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 this Terraform provider article.

1. Get an IAM token for API authentication and save it as an environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Use the User.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>/users/<username>' \
       --data '{
                 "updateMask": "password",
                 "password": "<new_password>"
               }'
   

   Where:

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

     Here, we only specified a single parameter, password.

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

     You can also generate a password with 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 your password. The new password version is labeled as Current.

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

   You can get the cluster ID with the list of clusters in the folder. You can get the username with the list of users in the cluster.

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

1. Get an IAM token for API authentication and save it as an 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, we only specified a single parameter, password.

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

     You can also generate a password with 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 your password. The new password version is labeled as Current.

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

   You can get the cluster ID with the list of clusters in the folder. You can get the username with the list of users in the cluster.

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

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

   For more information about creating this file, see this guide.

2. Change the value of the admin_password field:

   resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
     ...
     admin_password = "<admin_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 this Terraform provider article.

1. Get an IAM token for API authentication and save it as an 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, we only specified a single parameter, configSpec.adminPassword.

   • configSpec.adminPassword: New user password.

     The password must be from 8 to 128 characters long.

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

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

1. Get an IAM token for API authentication and save it as an 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, we only specified a single parameter, config_spec.admin_password.

   • config_spec.admin_password: New user password.

     The password must be from 8 to 128 characters long.

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

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

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 user settings from the command line interface:

1. To set up user permissions to access specific 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 get the cluster name with the list of clusters in the folder.

   This command grants the user access permissions for the listed databases.

   To revoke access to a specific database, remove its name from the list and provide the updated list to the command.

2. To change user's quota settings, run the following command, listing all quotas by specifying the --quota parameter for each one:

   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 get 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 you included the settings for new and changed quotas as well as the settings for existing quotas that have not changed.

   To delete one or more user quotas, exclude their settings from the list and provide the updated list of --quota parameters to the command.

   When setting a quota interval, you can use units hours (h), minutes (m), seconds (s), and milliseconds (ms) in the value, e.g., 3h20m10s7000ms. The resulting value is still expressed in milliseconds, e.g., 12017000. The interval value must be a multiple of 1,000 milliseconds. For example, 1s500ms is incorrect.

3. To update user's ClickHouse® settings, run the command below, listing the changed settings using the --settings parameter:

   yc managed-clickhouse user update <username> \
      --cluster-name=<cluster_name> \
      --settings=<list_of_ClickHouse®>_settings
   

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

   The command only updates the settings that are explicitly specified in the --settings parameter. For example, the command with the --settings="readonly=1" parameter will only update the readonly setting and will 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).

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

   For more information about creating this file, see this guide.

2. Locate the user's yandex_mdb_clickhouse_user resource.

3. To set up user permissions to access specific databases, add the required number of permission sections, one per 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 update user's quota settings, add the required number of quota sections 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 update user's ClickHouse® settings, add the settings section to the cluster user 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 this Terraform provider article.

1. Get an IAM token for API authentication and save it as an environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Use the User.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>/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 appears 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 sending your request, make sure the list covers all the required databases, including existing ones.

   • To update user's ClickHouse® settings, 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 appears 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 sending your request, make sure the list covers all the required quota settings, including existing ones.

   You can get the cluster ID with the list of clusters in the folder. You can get the username with the list of users in the cluster.

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

1. Get an IAM token for API authentication and save it as an 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 appears 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 sending your request, make sure the list covers all the required databases, including existing ones.

   • To update user's ClickHouse® settings, 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 appears 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 sending your request, make sure the list covers all the required quota settings, including existing ones.

   You can get the cluster ID with the list of clusters in the folder. You can get the username with the list of users in the cluster.

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

1. Connect to your cluster as admin.

2. To change a set of user privileges and roles, use the GRANT and REVOKE statements. For example, grant the user read permissions for all objects in a specific database:

   GRANT SELECT ON <DB_name>.* TO <username>;
   

3. To update user's quota settings, use the CREATE QUOTA, ALTER QUOTA, and DROP QUOTA statements. For example, limit the total number of user queries for a 15-month period:

   CREATE QUOTA <quota_name> FOR INTERVAL 15 MONTH MAX QUERIES 100 TO <username>;
   

4. To change a user account, use the ALTER USER statement. For example, to update ClickHouse® settings, run the command below, listing the settings to update:

   ALTER USER <username> SETTINGS <list_of_ClickHouse®>_settings;
   

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 delete a user, run this command:

yc managed-clickhouse user delete <username> \
   --cluster-name=<cluster_name>

You can get 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 this guide.

2. Delete the yandex_mdb_clickhouse_user resource with the user description.

3. Validate your configuration.

   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 this Terraform provider article.

1. Get an IAM token for API authentication and save it as an 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 get the cluster ID with the list of clusters in the folder. You can get the username with the list of users in the cluster.

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

1. Get an IAM token for API authentication and save it as an 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 get the cluster ID with the list of clusters in the folder. You can get the username with the list of users in the cluster.

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

1. Connect to your cluster as admin.

2. Delete the user:

   DROP USER <username>;
   

To learn more about deleting objects, see this ClickHouse® article.

Run this command:

yc managed-clickhouse user create "ro-user" \
   --cluster-name="mych" \
   --password="Passw0rd" \
   --permissions="db1" \
   --settings="readonly=1"

Once you create the user, make sure it actually has read-only access:

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

2. Try changing any setting, e.g., 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 this guide.

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 save it as an 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 save it as an 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 as admin.

2. Create a user:

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

3. Grant the user read permissions for all objects in the db1 database:

   GRANT SELECT ON db1.* TO ro-user;