Manage ClickHouse® users

In this article, you'll learn how to:

• Create a ClickHouse® user for the Viewer role

• Delete a ClickHouse® user

Before you start

1. Install the clickhouse-client:

   Docker

   DEB-based

   RPM-based

   1. Open your terminal.

   2. (Optional) Start Docker if needed:

      sudo service docker start
      

   3. Pull the clickhouse-client Docker image:

      docker pull clickhouse/clickhouse-client
      

   1. Open your terminal.

   2. Connect to the ClickHouse® official DEB repository from your Linux system:

      sudo apt update && sudo apt install -y apt-transport-https ca-certificates dirmngr && \
      sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754 && \
      echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee \
      /etc/apt/sources.list.d/clickhouse.list
      

   3. Refresh the package list and install the clickhouse-client :

      sudo apt update && sudo apt install -y clickhouse-client
      

   1. Open your terminal.

   2. Connect to a ClickHouse® official RPM repository from your Linux system:

      sudo yum install -y yum-utils
      sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
      

   3. Install the clickhouse-client :

      sudo yum install -y clickhouse-client
      

   Warning

   If you run a RedHat 7-based Linux distribution, including Cent OS 7, Oracle Linux 7 and others, you need to download and install trusted certificates and manually add the path to them in the clickhouse-client configuration file as follows:

   1. Install the root certificate:

      curl https://letsencrypt.org/certs/isrg-root-x2-cross-signed.pem > \ 
      /etc/pki/ca-trust/source/anchors/isrg-root-x2-cross-signed.pem
      

   2. Install the intermediate certificate:

      curl https://letsencrypt.org/certs/lets-encrypt-r3-cross-signed.pem > \
      /etc/pki/ca-trust/source/anchors/lets-encrypt-r3-cross-signed.pem
      

   3. Update the list of trusted certificates:

      sudo update-ca-trust
      

   4. Locate your clickhouse-client configuration file (by default, you can find it at /etc/clickhouse-client/config.xml) and add the path to the certificates into the <openSSL> section:

      <client> <!-- Used for connection to server's secure tcp port -->
         <loadDefaultCAFile>true</loadDefaultCAFile>
         <cacheSessions>true</cacheSessions>
         <disableProtocols>sslv2,sslv3</disableProtocols>
         <preferServerCiphers>true</preferServerCiphers>
         <caConfig>/etc/ssl/certs/ca-bundle.crt</caConfig>
         <!-- Use for self-signed: <verificationMode>none</verificationMode> -->
         <invalidCertificateHandler>
         <!-- Use for self-signed: <name>AcceptCertificateHandler</name> -->
         <name>RejectCertificateHandler</name>
         </invalidCertificateHandler>
      </client>
      

2. Connect to your cluster with a connection string:

   1. Go to the Clusters overview page.

   2. Under Connection strings, find the Native interface string and click Copy.

   3. Open your terminal and run a command to connect to your cluster:

      Docker

      Native clickhouse-client

      docker run --network host --rm -it clickhouse/<Native interface connection string>
      

      The complete Docker command structure

      docker run --network host --rm -it \ 
                  clickhouse/clickhouse-client \
                  --host <FQDN of your cluster> \
                  --secure \
                  --user <cluster user name> \
                  --password <cluster user password> \
                  --port 9440 
      

      <Native interface connection string>
      

Use named collections

If you don't want to expose your credentials when accessing your data with the S3 engine, you can use named collections .

To create a named collection, use an admin account to send the following via a ClickHouse® query:

• Collection name
• AWS key
• AWS secret key
• Path to your Amazon S3 bucket.

For example:

CREATE NAMED COLLECTION s3_demo AS
access_key_id = 'AWS KEY',
secret_access_key = 'AWS SECRET KEY',
url = 'https://<bucket-name>.s3.eu-central-1.amazonaws.com/';

Now you can access your Amazon S3 data without having to expose the credentials:

SELECT *
FROM s3(s3_demo, filename = 'weather_json/sensors.2021.json.gz', format = 'JSONEachRow', structure = 'snowfall Nullable(String)')
LIMIT 2

Create a ClickHouse® user for the Viewer role

The Viewer role doesn't allow access to connection strings of your Managed ClickHouse® clusters. If you want to provide access to a cluster, create a standalone user with read-only rights for each Managed ClickHouse® cluster on your project:

1. Create a user and its password:

   CREATE USER IF NOT EXISTS <new_user_name> ON CLUSTER <cluster ID> IDENTIFIED BY '<new_user_password>'
   

2. Assign the read-only rights to the newly created user:

   GRANT SHOW TABLES, SELECT ON database.* TO <new_user_name>
   

   Now the user can see the tables and perform SELECT queries with all the databases on the cluster.

3. The newly created user can connect to the cluster with the following string:

   clickhouse-client --host <FQDN of the cluster where you created this user> \
                     --secure \
                     --user <new_user_name> \
                     --password <new_user_password> \
                     --port 9440 
   

Delete a ClickHouse® user

To delete a user from your ClickHouse® cluster, send the following query:

DROP USER <user_name>

See also

• User permissions