Manage ClickHouse® users
In this article, you'll learn how to:
Before you start
-
Install the clickhouse-client:
DockerDEB-basedRPM-based-
Open your terminal.
-
(Optional) Start Docker
sudo service docker start
-
Pull the clickhouse-client
docker pull clickhouse/clickhouse-client
-
Open your terminal.
-
Connect to the ClickHouse® official DEB repository
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
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
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:
-
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
-
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
-
Update the list of trusted certificates:
sudo update-ca-trust
-
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>
-
-
Connect to your cluster with a connection string:
-
Go to the Clusters overview
-
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-clientdocker run --network host --rm -it clickhouse/<Native interface connection string>
The complete Docker command structuredocker 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:
-
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
SELECT
queries 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>