1. Home
  2. Knowledge Base
  3. ClickHouse
  4. Connecting a ClickHouse client to a ClickHouse server over a secure connection SSL-TLS
  1. Home
  2. Knowledge Base
  3. ClickHouse DBA
  4. Connecting a ClickHouse client to a ClickHouse server over a secure connection SSL-TLS
  1. Home
  2. Knowledge Base
  3. ClickHouse Support
  4. Connecting a ClickHouse client to a ClickHouse server over a secure connection SSL-TLS

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

Was this article helpful?

Related Articles

CHISTADATA IS COMMITTED TO OPEN SOURCE SOFTWARE AND BUILDING HIGH PERFORMANCE COLUMNSTORES

In the spirit of freedom, independence and innovation. ChistaDATA Corporation is not affiliated with ClickHouse Corporation 

Need Support?

Can't find the answer you're looking for?
Contact Support

ChistaDATA Inc. Knowledge base is licensed under the Apache License, Version 2.0 (the “License”)

Copyright 2022 ChistaDATA Inc

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.