Manage ClickHouse® users
In this article, you'll learn how to:
Before you start
Install the clickhouse-client:DockerDEB-basedRPM-based
Open your terminal.
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
Refresh the package list and install the clickhouse-client :
sudo apt update && sudo apt install -y clickhouse-client
Open your terminal.
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
Install the clickhouse-client :
sudo yum install -y clickhouse-client
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:
curl https://letsencrypt.org/certs/isrg-root-x2-cross-signed.pem > \ /etc/pki/ca-trust/source/anchors/isrg-root-x2-cross-signed.pem
curl https://letsencrypt.org/certs/lets-encrypt-r3-cross-signed.pem > \ /etc/pki/ca-trust/source/anchors/lets-encrypt-r3-cross-signed.pem
Update the list of trusted certificates:
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
<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>
Connect to your cluster with a connection string:
Go to the Clusters overview page.
Under Connection strings, find the Native interface string and click Copy.
Open your terminal and run a command to connect to your cluster:DockerNative 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
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.
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
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:
Create a user and its password:
CREATE USER IF NOT EXISTS <new_user_name> ON CLUSTER <cluster ID> IDENTIFIED BY '<new_user_password>'
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
SELECTqueries with all the databases on the cluster.
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>