Connecting a ClickHouse client to a ClickHouse server over a secure connection SSL-TLS

ClickHouse client (clickhouse-client tool) is a command line interface to a ClickHouse server.  The default port to connect is 9000 in case of non-TLS connection and 9440 when using TLS. A secured connection is highly recommended in production environments. More information on ClickHouse network security is available here.

Connect a clickhouse-client tool securely to any ClickHouse server using the following steps.

1. Create SSL Certificates

This step is needed only if self-signed certificates are used. Skip this step if the certificates are already obtained from a public or private Certificate Authority. We use OpenSSL to create the certificates.

Generate a key that will be used to create self-signed CA certificate

openssl genrsa -out custom_ca.key 2048

Create a  self-signed CA certificate using the key created earlier

openssl req -x509 -subj "/CN=custom.local" -nodes -key custom_ca.key -days 1095 -out custom_ca.crt

Create a certificate request and a key.

openssl req -newkey rsa:2048 -nodes -subj "/CN=chserver" -keyout chserver.key -out chserver.csr

Create the server certificate and keys using the CA certificate and keys

openssl x509 -req -in chserver.csr -out chserver.crt -CAcreateserial -CA custom_ca.crt -CAkey custom_ca.key -days 90

Verify the certificate pairs

openssl verify -CAfile custom_ca.crt chserver.crt

2. Store the certificates in the ClickHouse server

Store the CA certificate (custom_ca.crt),  server certificate (chserver.crt), and key (chserver.key) in a custom location. ClickHouse docs recommend storing them in /etc/clickhouse-server/certs.

Update the ownership and permissions.

chown clickhouse:clickhouse -R /etc/clickhouse-server/certs
chmod 600 /etc/clickhouse-server/certs/*
chmod 755 /etc/clickhouse-server/certs

3. Server config update

In the ClickHouse server, open the ports meant for secure communication.

<https_port>8443</https_port>
<tcp_port_secure>9440</tcp_port_secure>
<interserver_https_port>9010</interserver_https_port>

Update the below  config in /etc/clickhouse-server/config.xml

<openSSL>
    <server>
        <certificateFile>/etc/clickhouse-server/certs/chserver.crt</certificateFile>
        <privateKeyFile>/etc/clickhouse-server/certs/chserver.key</privateKeyFile>
        <verificationMode>relaxed</verificationMode>
        <caConfig>/etc/clickhouse-server/certs/custom_ca.crt</caConfig>
        <cacheSessions>true</cacheSessions>
        <disableProtocols>sslv2,sslv3</disableProtocols>
        <preferServerCiphers>true</preferServerCiphers>
    </server>
    <client>
        <loadDefaultCAFile>false</loadDefaultCAFile>
        <caConfig>/etc/clickhouse-server/certs/custom_ca.crt</caConfig>
        <cacheSessions>true</cacheSessions>
        <disableProtocols>sslv2,sslv3</disableProtocols>
        <preferServerCiphers>true</preferServerCiphers>
        <verificationMode>relaxed</verificationMode>
        <invalidCertificateHandler>
            <name>RejectCertificateHandler</name>
        </invalidCertificateHandler>
    </client>
</openSSL>


<grpc>
    <enable_ssl>1</enable_ssl>
    <ssl_cert_file>/etc/clickhouse-server/certs/chserver.crt</ssl_cert_file>
    <ssl_key_file>/etc/clickhouse-server/certs/chserver.key</ssl_key_file>
    <ssl_require_client_auth>true</ssl_require_client_auth>
    <ssl_ca_cert_file>/etc/clickhouse-server/certs/custom_ca.crt</ssl_ca_cert_file>
    <transport_compression_type>none</transport_compression_type>
    <transport_compression_level>0</transport_compression_level>
    <max_send_message_size>-1</max_send_message_size>
    <max_receive_message_size>-1</max_receive_message_size>
    <verbose_logs>false</verbose_logs>
</grpc>

Update the client config file. This has to be done in every machine from which we are going to connect to the server via clickhouse-client. Place the custom_ca.crt file in the /etc/clickhouse-server/certs/ folder in every machine.

<openSSL>
    <client>
        <loadDefaultCAFile>false</loadDefaultCAFile>
        <caConfig>/etc/clickhouse-server/certs/custom_ca.crt</caConfig>
        <cacheSessions>true</cacheSessions>
        <disableProtocols>sslv2,sslv3</disableProtocols>
        <preferServerCiphers>true</preferServerCiphers>
        <invalidCertificateHandler>
            <name>RejectCertificateHandler</name>
        </invalidCertificateHandler>
    </client>
</openSSL>

4. Connect to the server securely from client

Connect to the server from the client using the following command.

clickhouse-client --user default --password <password> --port <tcp_port_secure> --secure --host <server host ip>

References

https://clickhouse.com/docs/en/guides/sre/configuring-ssl/

https://clickhouse.com/docs/en/interfaces/cli/#interfaces_cli_configuration

ClickHouse Network Security Guide