Manage ClickHouse® users

In this article, you'll learn how to:

Before you start

  1. Install the clickhouse-client:

    1. Open your terminal.

    2. (Optional) Start Docker if needed:

      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 Linux terminal and run a command to connect to your cluster:

      <Native interface connection string>
      
      The complete structure of the Native interface connection string
      clickhouse-client --host <FQDN of the host> \
                        --secure \
                        --user <Cluster user name> \
                        --password <Cluster user password> \
                        --port 9440 
      
      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 admin \
                  --password <Cluster user password> \
                  --port 9440 
      

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 <user_name> ON CLUSTER <cluster ID> IDENTIFIED BY '<password>'
    
  2. Assign the read-only rights to the newly created user:

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

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

  3. For convenience, create a custom connection string for the new user:

    clickhouse-client --host <FQDN of the host> \
                      --secure \
                      --user <Cluster user name> \
                      --password <Cluster user password> \
                      --port 9440 
    

Delete a ClickHouse® user

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

docker run --network host --rm -it clickhouse/<Native interface connection string> \
--query="DROP USER <user_name>"
DROP USER <user_name>

See also: