Managing ClickHouse® users

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To get a list of cluster users, run the following command:

yc managed-clickhouse user list
   --cluster-name=<cluster_name>

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

To get a list of users, use the list REST API method for the User resource or the UserService/List gRPC API call and provide the cluster ID in the clusterId request parameter.

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

1. Connect to a cluster using the admin account.

2. Get a list of users:

   SHOW USERS;
   

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To create a user in a cluster, run the command:

yc managed-clickhouse user create <username> \
   --cluster-name=<cluster_name> \
   --password=<user_password> \
   --permissions=<DB_list> \
   --quota=<list_of_single_quota_settings_for_user> \
   --settings=<list_of_ClickHouse®_settings_for_user>

Where --permissions is a list of DBs the user must have access to.

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 with an infrastructure plan.

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

2. Add a user section to the Managed Service for ClickHouse® cluster description:

   resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
     ...
     user {
       name     = "<username>"
       password = "<password>"
       ...
     }
   }
   

   Note

   The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter or underscore.

   The password must be from 8 to 128 characters long.

3. Make sure the settings are correct.

   1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

   2. Run the command:

      terraform validate
      

      If there are errors in the configuration files, Terraform will point to them.

4. Confirm updating the resources.

   1. Run the command to view planned changes:

      terraform plan
      

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

   2. If you are happy with the planned changes, apply them:

      1. Run the command:

         terraform apply
         

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

For more information, see the Terraform provider documentation.

resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
  ...
  timeouts {
    create = "1h30m" # 1 hour 30 minutes
    update = "2h"    # 2 hours
    delete = "30m"   # 30 minutes
  }
}

To create a user, use the create REST API method for the User resource or the UserService/Create gRPC API call and provide the following in the request:

• Cluster ID in the clusterId parameter. You can get the cluster ID with a list of clusters in the folder.
• New username, in the userSpec.name parameter.
• New user password, in the userSpec.password parameter.
• (Optional) List of databases to grant the user access to, in the userSpec.permissions[] parameter.
• (Optional) List of ClickHouse® user settings, in the userSpec.settings parameter.
• (Optional) List of quota user settings, in the userSpec.quotas[] parameter.

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.

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To change the user password, run this command:

yc managed-clickhouse user update <username> \
   --cluster-name=<cluster_name> \
   --password=<new_password>

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

1. Open the current Terraform configuration file with an infrastructure plan.

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

2. In the Managed Service for ClickHouse® cluster description, find the user block for the required user.

3. Change the value of the password field:

   resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
     ...
     user {
       name     = "<username>"
       password = "<new_password>"
       ...
     }
   }
   

   Note

   The password must be between 8 and 128 characters.

4. Make sure the settings are correct.

   1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

   2. Run the command:

      terraform validate
      

      If there are errors in the configuration files, Terraform will point to them.

5. Confirm updating the resources.

   1. Run the command to view planned changes:

      terraform plan
      

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

   2. If you are happy with the planned changes, apply them:

      1. Run the command:

         terraform apply
         

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

For more information, see the Terraform provider documentation.

resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
  ...
  timeouts {
    create = "1h30m" # 1 hour 30 minutes
    update = "2h"    # 2 hours
    delete = "30m"   # 30 minutes
  }
}

To update a password, use the update REST API method for the User resource or the UserService/Update gRPC API call and provide the following in the request:

• Cluster ID in the clusterId parameter. To find out the cluster ID, get a list of clusters in the folder.

• New password in the password parameter.

  Note

  The password must be between 8 and 128 characters.

• List of user configuration fields to update (password in this case) in the updateMask parameter.

1. Open the current Terraform configuration file with an infrastructure plan.

   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. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

   2. Run the command:

      terraform validate
      

      If there are errors in the configuration files, Terraform will point to them.

4. Confirm updating the resources.

   1. Run the command to view planned changes:

      terraform plan
      

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

   2. If you are happy with the planned changes, apply them:

      1. Run the command:

         terraform apply
         

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

For more information, see the Terraform provider documentation.

resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
  ...
  timeouts {
    create = "1h30m" # 1 hour 30 minutes
    update = "2h"    # 2 hours
    delete = "30m"   # 30 minutes
  }
}

To update the admin user's password, use the update REST API method for the Cluster resource or the ClusterService/Update gRPC API call and provide the following in the request:

• Cluster ID in the clusterId parameter. To find out the cluster ID, get a list of clusters in the folder.

• New password in the configSpec.adminPassword parameter.

  Note

  The password must be between 8 and 128 characters.

• List of user configuration fields to update (configSpec.adminPassword in this case) in the updateMask parameter.

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

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

1. To set up the user's permissions to access certain databases, run the command, listing the database names in the --permissions parameter:

   yc managed-clickhouse user update <username> \
      --cluster-name=<cluster_name> \
      --permissions=<DB_list>
   

   You can request the cluster name with 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 does not reset the values of the other settings. This is how changing ClickHouse® settings differs from changing quota settings.

   You cannot use this command to delete an existing setting. You can only explicitly set it to its default value (specified for each setting).

1. Open the current Terraform configuration file with an infrastructure plan.

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

2. To configure the user's permissions to access certain databases, add the required number of permission sections to the cluster user description, one for each database:

   resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
     ...
     user {
       name     = "<username>"
       password = "<password>"
       permission {
         database_name = "<database_1>"
       }
       ...
       permission {
         database_name = "<database_N>"
       }
     }
   }
   

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

3. To change quota settings for the user, add the required number of quota blocks to the cluster user description.

   When describing quotas, only the interval_duration field is required.

   resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
     ...
     user {
       name     = "<username>"
       password = "<password>"
       ...
       quota {
         interval_duration = <interval_in_milliseconds>
         ...
       }
     }
   }
   

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

   resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
     ...
     user {
       name     = "<username>"
       password = "<password>"
       ...
       settings {
         <DBMS_settings_for_specific_user>
       }
     }
   }
   

5. Make sure the settings are correct.

   1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

   2. Run the command:

      terraform validate
      

      If there are errors in the configuration files, Terraform will point to them.

6. Confirm updating the resources.

   1. Run the command to view planned changes:

      terraform plan
      

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

   2. If you are happy with the planned changes, apply them:

      1. Run the command:

         terraform apply
         

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

For more information, see the Terraform provider documentation.

resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
  ...
  timeouts {
    create = "1h30m" # 1 hour 30 minutes
    update = "2h"    # 2 hours
    delete = "30m"   # 30 minutes
  }
}

To update user settings, use the update REST API method for the User resource or the UserService/Update gRPC API call and provide the following in the request:

• Cluster ID in the clusterId parameter. To find out the cluster ID, get a list of clusters in the folder.
• Username with the settings to be changed, in the userName parameter. To find out the name, get a list of users.
• (Optional) List of databases to grant the user access to, in the userSpec.permissions[] parameter.
• (Optional) List of ClickHouse® user settings, in the userSpec.settings parameter.
• (Optional) List of quota user settings, in the userSpec.quotas[] parameter.
• List of user configuration fields to update in the updateMask parameter.

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

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To remove a user, run:

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

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

1. Open the current Terraform configuration file with an infrastructure plan.

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

2. Delete the user block with the user's description from the Managed Service for ClickHouse® cluster description.

3. Make sure the settings are correct.

   1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

   2. Run the command:

      terraform validate
      

      If there are errors in the configuration files, Terraform will point to them.

4. Confirm updating the resources.

   1. Run the command to view planned changes:

      terraform plan
      

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

   2. If you are happy with the planned changes, apply them:

      1. Run the command:

         terraform apply
         

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

For more information, see the Terraform provider documentation.

resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
  ...
  timeouts {
    create = "1h30m" # 1 hour 30 minutes
    update = "2h"    # 2 hours
    delete = "30m"   # 30 minutes
  }
}

To delete a user, use the delete REST API method for the User resource or the UserService/Delete gRPC API call and provide the following in the request:

• Cluster ID in the clusterId parameter. To find out the cluster ID, get a list of clusters in the folder.
• Username with the settings to be changed, in the userName parameter. To find out the name, get a list of users.

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.

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 with an infrastructure plan.

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

2. Add the user section to the cluster description.

   resource "yandex_mdb_clickhouse_cluster" "mych" {
     name = "mych"
   
     database {
       name = "db1"
     }
   
     user {
       name     = "ro-user"
       password = "Passw0rd"
       permission {
         database_name = "db1"
       }
       settings {
         readonly = 1
       }
     }
     ...
   }
   

3. Make sure the settings are correct.

   1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

   2. Run the command:

      terraform validate
      

      If there are errors in the configuration files, Terraform will point to them.

4. Confirm updating the resources.

   1. Run the command to view planned changes:

      terraform plan
      

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

   2. If you are happy with the planned changes, apply them:

      1. Run the command:

         terraform apply
         

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

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;