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
  • SSL authentication can only be used if a user connects to ADQM through a client that supports the HTTPS interface.

  • In this article, a self-signed CA certificate is used to create a user certificate for demonstration and testing purposes. For production systems, use a trusted certificate authority to sign certificates.

Create an SSL user certificate

  1. 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.

  2. 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.

  3. In the ADCM interface, go to the ADQMDB service configuration page and set a method of checking user SSL certificates (relaxed, strict, or once) 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
    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.

  1. 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
  2. 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.

  3. Make sure that a method of checking user certificates is set via the Client certificate verification mode option in the ADQMDB service settings.

  4. Create a user that ADQM will authenticate by the provided certificate:

    CREATE USER john IDENTIFIED WITH ssl_certificate CN 'arenadata:john';
  5. Grant privileges to the user (for example, full administrator privileges):

    GRANT ALL ON *.* TO john WITH GRANT OPTION;
  6. 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.

Found a mistake? Seleсt text and press Ctrl+Enter to report it