Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

Configuring SSL user certificate for authentication

View as MarkdownEdit this pageReport an issueSet up docs MCP
Not supported in ClickHouse Cloud

This guide provides simple and minimal settings to configure authentication with SSL user certificates. The tutorial builds on the Configuring TLS user guide.

Create SSL user certificates

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

Create a SQL user and grant permissions

  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;

Testing

  1. Copy the user certificate, user key and CA certificate to a remote node.

  2. Configure OpenSSL in the ClickHouse client config 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'

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

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.

Navigation