SSL certificate authentication
When connecting to ADQM by the SSL protocol, SSL certificates of incoming connections are required to be checked — only connections with trusted certificates can be established. Thus, ADQM can uniquely authenticate an incoming connection, and can identify a user by the corresponding user certificate. You can set multiple certificates for one user.
This article describes the sequence of steps required to enable SSL certificate authentication. To run commands below, you need openssl installed — it is a command-line tool for using functions of the OpenSSL cryptographic library.
Create an SSL user certificate
Generate a key and create a certificate signing request (CSR) file:
openssl req -newkey rsa:2048 -nodes -subj "/CN=<user_cert_identifier>" -keyout <user_cert_name>.key -out <user_cert_name>.csr
The CN (Common Name) certificate identifier can be any unique value. This value will be used when creating an ADQM user.
Create and sign a user certificate that will be used for authentication:
openssl x509 -req -in <user_cert_name>.csr -out <user_cert_name>.crt -CAcreateserial -CA <ca>.crt -CAkey <ca>.key -days 365
where <ca_cert>.crt and <ca_cert>.key are files used when configuring ADQM for secure connections over SSL (for details, see SSL encryption):
<ca_cert>.crt — public root certificate of the certificate authority (CA). It should be stored on each ADQM server of the cluster and specified in the ADQMDB service configuration settings (in the ADCM interface: ADQM cluster → Services → ADQMDB → Configuration → Enable SSL → Certificate authority file).
<ca_cert>.key — private key of the certification authority. It should be kept secret.
In the ADCM interface, go to the ADQMDB service configuration window and activate the Validate client certificate option in the Enable SSL section. Click Save and run Reconfig and restart for the ADQMDB service to save and apply the parameter value change.Option to enable verification of user SSL certificates
Create a user
To create an ADQM user that will authenticate with an SSL certificate, use the
IDENTIFIED WITH ssl_certificate clause in a
CREATE USER query and specify the user certificate identifier (CN):
CREATE USER <user_name> IDENTIFIED WITH ssl_certificate CN '<user_cert_identifier>';
If you define a user in the users.xml configuration file, specify the
common_name element with the user certificate identifier in the
certificates section for the user (the list of certificates can include more than one element):
<users> <user_name> <certificates> <common_name>user_cert_identifier</common_name> <!-- More names --> </certificates> <!-- Other settings --> </user_name> </users>
This example shows how to create and use an SSL certificate for authentication of a user that connects to an ADQM cluster for which the SSL encryption of connections is enabled as the SSL encryption article describes.
Generate a key and create a certificate signing request file:
openssl req -newkey rsa:2048 -nodes -subj "/CN=arenadata:john" -keyout arenadata_john.key -out arenadata_john.csr
Create and sign a certificate for user authentication:
openssl x509 -req -in arenadata_john.csr -out arenadata_john.crt -CAcreateserial -CA arenadata_ca.crt -CAkey arenadata_ca.key -days 365
In this query, arenadata_ca.crt is the root CA certificate and arenadata_ca.key is the private CA key.
Make sure that the Validate client certificate option is enabled in the ADQMDB service settings.
Create a user that ADQM will authenticate by the provided certificate:
CREATE USER john IDENTIFIED WITH ssl_certificate CN 'arenadata:john';
Grant privileges to the user (for example, full administrator privileges):
GRANT ALL ON *.* TO john WITH GRANT OPTION;
Check the connection to ADQM as the created user. For example, run the query on one of the ADQM servers (adqm-host-1.arenadata.local):
echo 'SHOW TABLES' | curl 'https://adqm-host-1.arenadata.local:8443' --cert arenadata_john.crt --key arenadata_john.key --cacert arenadata_ca.crt -H "X-ClickHouse-SSL-Certificate-Auth: on" -H "X-ClickHouse-User: john" --data-binary @-
If the user passes authentication, the query will return a list of database tables.