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.
NOTE
|
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 → Primary 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 page and set a method of checking user SSL certificates (
relaxed
,strict
, oronce
) using the Client certificate verification mode 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>
Example
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 a method of checking user certificates is set via the Client certificate verification mode option 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.