Skip to main content
Skip to main content
Edit this page

Configuring SSL User Certificate for Authentication

Not supported in ClickHouse Cloud
Note

This page is not applicable to ClickHouse Cloud. The feature documented here is not available in ClickHouse Cloud services. See the ClickHouse 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 SSL-TLS user guide.

Note

SSL user authentication is supported when using the https or native interfaces only. It is not currently used in gRPC or PostgreSQL/MySQL emulation ports.

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

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:

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

    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:

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

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 user guide.

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

  2. Grant privileges to the new certificate user:

    Note

    The user is granted full admin privileges in this exercise for demonstration purposes. Refer to the ClickHouse RBAC documentation for permissions settings.

    Note

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

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

  3. Run clickhouse-client.

    Note

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

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:

    For example:

    The output will be similar to the following:

    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​

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.