Configuring SSL user certificate for authentication

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/sre/user-management/ssl-user-auth.md)

Not supported in ClickHouse Cloud

Note

This page isn't applicable to [ClickHouse Cloud](https://clickhouse.com/cloud). The feature documented here isn't available in ClickHouse Cloud services.
See the ClickHouse [Cloud Compatibility](/docs/whats-new/cloud-compatibility) guide for more information.

This guide provides simple and minimal settings to configure authentication with SSL user certificates. The tutorial builds on the [Configuring TLS user guide](/docs/guides/sre/tls/configuring-tls).

Note

SSL user authentication is supported when using the `https`, `native`, `mysql`, and `postgresql` interfaces.

ClickHouse nodes need `<verificationMode>strict</verificationMode>` set for secure authentication (although `relaxed` will work for testing purposes).

If you use AWS NLB with the MySQL interface, you have to ask AWS support to enable the undocumented option:
> I would like to be able to configure our NLB proxy protocol v2 as below `proxy_protocol_v2.client_to_server.header_placement,Value=on_first_ack`.

## 1. Create SSL user certificates[​](#1-create-ssl-user-certificates "Direct link to 1. Create SSL user certificates")

Note

This example uses self-signed certificates with a self-signed CA. For production environments, create a CSR and submit to your PKI team or certificate provider to obtain a proper certificate.

1. Generate a Certificate Signing Request (CSR) and key. The basic format is the following:

   ```
   openssl req -newkey rsa:2048 -nodes -subj "/CN=<my_host>:<my_user>"  -keyout <my_cert_name>.key -out <my_cert_name>.csr
   ```

   In this example, we'll use this for the domain and user that will be used in this sample environment:

   ```
   openssl req -newkey rsa:2048 -nodes -subj "/CN=chnode1.marsnet.local:cert_user"  -keyout chnode1_cert_user.key -out chnode1_cert_user.csr
   ```

   Note

   The CN is arbitrary and any string can be used as an identifier for the certificate. It is used when creating the user in the following steps.
2. Generate and sign the new user certificate that will be used for authentication. The basic format is the following:

   ```
   openssl x509 -req -in <my_cert_name>.csr -out <my_cert_name>.crt -CA <my_ca_cert>.crt -CAkey <my_ca_cert>.key -days 365
   ```

   In this example, we'll use this for the domain and user that will be used in this sample environment:

   ```
   openssl x509 -req -in chnode1_cert_user.csr -out chnode1_cert_user.crt -CA marsnet_ca.crt -CAkey marsnet_ca.key -days 365
   ```

## 2. Create a SQL user and grant permissions[​](#2-create-a-sql-user-and-grant-permissions "Direct link to 2. Create a SQL user and grant permissions")

Note

For details on how to enable SQL users and set roles, refer to [Defining SQL Users and Roles](/docs/operations/access-rights) user guide.

1. Create a SQL user defined to use the certificate authentication:

   ```
   CREATE USER cert_user IDENTIFIED WITH ssl_certificate CN 'chnode1.marsnet.local:cert_user';
   ```
2. Grant privileges to the new certificate user:

   ```
   GRANT ALL ON *.* TO cert_user WITH GRANT OPTION;
   ```

   Note

   The user is granted full admin privileges in this exercise for demonstration purposes. Refer to the ClickHouse [RBAC documentation](/docs/operations/access-rights) for permissions settings.

   Note

   We recommend using SQL to define users and roles. However, if you're currently defining users and roles in configuration files, the user will look like:

   ```
   <users>
       <cert_user>
           <ssl_certificates>
               <common_name>chnode1.marsnet.local:cert_user</common_name>
           </ssl_certificates>
           <networks>
               <ip>::/0</ip>
           </networks>
           <profile>default</profile>
           <access_management>1</access_management>
           <!-- additional options-->
       </cert_user>
   </users>
   ```

## 3. Testing[​](#3-testing "Direct link to 3. Testing")

1. Copy the user certificate, user key and CA certificate to a remote node.
2. Configure OpenSSL in the ClickHouse [client config](/docs/interfaces/client#configuration_files) with certificate and paths.

   ```
   <openSSL>
       <client>
           <certificateFile>my_cert_name.crt</certificateFile>
           <privateKeyFile>my_cert_name.key</privateKeyFile>
           <caConfig>my_ca_cert.crt</caConfig>
       </client>
   </openSSL>
   ```
3. Run `clickhouse-client`.

   ```
   clickhouse-client --user <my_user> --query 'SHOW TABLES'
   ```

   Note

   Note that the password passed to clickhouse-client is ignored when a certificate is specified in the config.

## 4. Testing HTTP[​](#4-testing-http "Direct link to 4. Testing HTTP")

1. Copy the user certificate, user key and CA certificate to a remote node.
2. Use `curl` to test a sample SQL command. The basic format is:

   ```
   echo 'SHOW TABLES' | curl 'https://<clickhouse_node>:8443' --cert <my_cert_name>.crt --key <my_cert_name>.key --cacert <my_ca_cert>.crt -H "X-ClickHouse-SSL-Certificate-Auth: on" -H "X-ClickHouse-User: <my_user>" --data-binary @-
   ```

   For example:

   ```
   echo 'SHOW TABLES' | curl 'https://chnode1:8443' --cert chnode1_cert_user.crt --key chnode1_cert_user.key --cacert marsnet_ca.crt -H "X-ClickHouse-SSL-Certificate-Auth: on" -H "X-ClickHouse-User: cert_user" --data-binary @-
   ```

   The output will be similar to the following:

   ```
   INFORMATION_SCHEMA
   default
   information_schema
   system
   ```

   Note

   Notice that no password was specified, the certificate is used in lieu of a password and is how ClickHouse will authenticate the user.

## Summary[​](#summary "Direct link to Summary")

This article showed the basics of creating and configuring a user for SSL certificate authentication. This method can be used with `clickhouse-client` or any clients which support the `https` interface and where HTTP headers can be set. The generated certificate and key should be kept private and with limited access since the certificate is used to authenticate and authorize the user for operations on the ClickHouse database. Treat the certificate and key as if they were passwords.

- [1. Create SSL user certificates](#1-create-ssl-user-certificates)- [2. Create a SQL user and grant permissions](#2-create-a-sql-user-and-grant-permissions)- [3. Testing](#3-testing)- [4. Testing HTTP](#4-testing-http)- [Summary](#summary)

Was this page helpful?