DEV Community

Cover image for How to Configure ClickHouse SSL Connection
Bytebase profile image Mila Wu
Mila Wu for Bytebase

Posted on • Edited on • Originally published at bytebase.com

How to Configure ClickHouse SSL Connection

#beginners #tutorial #clickhouse #openssl

Overview

Follow this tutorial, you can configure the ClickHouse SSL connection and test it by using self-signed CA easily. The general steps are:

  1. Install ClickHouse and OpenSSL
  2. Generate SSL Related Files
  3. Configure ClickHouse Server
  4. Test SSL Connection from Client

Background

ClickHouse® is an open-source, high-performance columnar OLAP database management system for real-time analytics using SQL. It supports SSL connection like most databases do.

This tutorial will show you how to configure the ClickHouse SSL connection using the self-signed CA.

Installation

Install ClickHouse

Follow the ClickHouse official document. If no errors occur, you will see something like below:

Image description

Install OpenSSL

Follow the OpenSSL's official site. Depending on the machine environment, there may be different ways to achieve this. If no errors occur, you will see something like below:

$ openssl version
OpenSSL 1.1.1f  31 Mar 2020
Enter fullscreen mode Exit fullscreen mode

Generate SSL Related Files

We will generate the following certificate chain:

Image description

OpenSSL Config

To generate Root CA certificate and other peer's certificate request, you need to set up a configure file as below:

cat >req.conf <<EOF
[ req ]
distinguished_name = req_distinguished_name
x509_extensions = v3_ca
prompt = no
[ req_distinguished_name ]
C = CN
ST = GD
O = Bytebase
CN = root
[ v3_ca ]
basicConstraints = critical,CA:TRUE
subjectKeyIdentifier = hash
authorityKeyIdentifier = keyid:always,issuer:always
[ v3_req ]
keyUsage = keyEncipherment, dataEncipherment
extendedKeyUsage = serverAuth
subjectAltName = @alt_names
[ alt_names ]
IP.1 = YOUR_SERVER_IP
EOF
Enter fullscreen mode Exit fullscreen mode

Replace YOUR_SERVER_IP with your real server IP.
Note, it uses IP address directly here in order to keep this example simple. You can use other valid SAN fields to meet your needs.

Generate Root CA Key and Certificate

Generate Root CA Key. To simplify the test, you can skip specifying the passphrase.

openssl genrsa -out ca.key 2048
Enter fullscreen mode Exit fullscreen mode

Now, use this key and OpenSSL config above to generate the CA certificate:

openssl req -x509 -new -key ca.key -sha256 -days 36500 -out ca.pem -extensions 'v3_ca' -config req.conf
Enter fullscreen mode Exit fullscreen mode

Generate Server Key and Certificate

Generate Server Key without the passphrase, too:

openssl genrsa -out server.key 2048
Enter fullscreen mode Exit fullscreen mode

Use the server key and OpenSSL config above to generate the server certificate like what you have done for CA. But the difference is that at this time you need to request the CA's Key for signing.

openssl req -new -sha256 -key server.key -out server.csr -subj "/C=CN/ST=GD/O=Bytebase/CN=YOUR_SERVER_IP"
openssl x509 -req -days 36500 -sha256 -extensions v3_req -CA ca.pem -CAkey ca.key -CAcreateserial -in server.csr -out server.pem
Enter fullscreen mode Exit fullscreen mode

Replace YOUR_SERVER_IP with your real server IP.

Generate Client Key and Certificate

From the SSL authentication perspective, Client and Server are equal partners, so you use the same steps as the server to generate client-related SSL files.

openssl genrsa -out client.key 2048
openssl req -new -sha256 -key client.key -out client.csr -subj "/C=CN/ST=GD/O=Bytebase/CN=dev.testssl.com"
openssl x509 -req -days 36500 -sha256 -extensions v3_req -CA ca.pem -CAkey ca.key -CAcreateserial -in client.csr -out client.pem
Enter fullscreen mode Exit fullscreen mode

After completing the above steps, you should have the following files:

Image description

Those highlighted files will be used in the next section.

Configure ClickHouse Server

Generate dhparams

From ClickHouse config, you can see:

<clickhouse>
    <openSSL>
        <server>
            <!-- dhparams are optional. You can delete the <dhParamsFile> element.
                 To generate dhparams, use the following command:
                  openssl dhparam -out /etc/clickhouse-server/dhparam.pem 4096
                 Only file format with BEGIN DH PARAMETERS is supported.
              -->
            <!-- <dhParamsFile>/etc/clickhouse-server/dhparam.pem</dhParamsFile>-->
            ...
        </server>
    </openSSL>
</clickhouse>
Enter fullscreen mode Exit fullscreen mode

you need to generate dhparams by using the command below:

openssl dhparam -out /etc/clickhouse-server/dhparam.pem 4096
Enter fullscreen mode Exit fullscreen mode

It will take a couple minutes.

After completion, modify the configuration file to indicate the location of the dhparams file:

<clickHouse>
   <openSSL>
       <server>
           ...
           <dhParamsFile>/etc/clickhouse-server/dhparam.pem</dhParamsFile>
           …
       </server>
   </openSSL>
</clickHouse>
Enter fullscreen mode Exit fullscreen mode

Configure CA Certificate, Server Key and Server Certificate Path

You need to specify the path of CA Certificate, Server Key, Server Certificate in the configuration file, for example:

<clickHouse>
   <openSSL>
       <server>
           <caConfig>/etc/clickhouse-server/ca.pem</caConfig>
           <certificateFile>/etc/clickhouse-server/server.pem</certificateFile>
           <privateKeyFile>/etc/clickhouse-server/server.key</privateKeyFile>
           ...
       </server>
   </openSSL>
</clickHouse>
Enter fullscreen mode Exit fullscreen mode

Configure Listening Port

Then, enable SSL related port by uncommenting on the following lines:

<clickHouse>
   ...
   <https_port>8443</https_port>
   <tcp_port_secure>9440</tcp_port_secure>
   <interserver_https_port>9010</interserver_https_port>
   ...
</clickHouse>
Enter fullscreen mode Exit fullscreen mode

Then disable the default non-secure port by commenting out the corresponding lines.

<clickHouse>
   ...
   <!-- <http_port>8123</http_port> -->
   <!-- <tcp_port>9000</tcp_port> -->
   <!-- <interserver_https_port>9009</interserver_https_port> -->
   ...
</clickHouse>
Enter fullscreen mode Exit fullscreen mode

Enable Remote Login (Optional)

If you want to test the ClickHouse SSL connection on a different machine than the ClickHouse Server, you need to enable remote login for ClickHouse.
Uncommenting the listen_host tag:

<clickHouse>
   ...
   <listen_host>::</listen_host>
   ...
</clickHouse>
Enter fullscreen mode Exit fullscreen mode

Restart the ClickHouse Service

Then, restart the ClickHouse server. For example, on Ubuntu:

sudo service clickhouse-server restart
Enter fullscreen mode Exit fullscreen mode

Test SSL Connection from Client

Copy SSL Files to the Client

You don't need to do anything in this step if you only test it on the machine that Clickhouse server runs on. Copy the ca.pem, client.pem, client.key from the machine that runs ClickHouse server (i.e.: the machine generated them) to directory /etc/ssl on the machine that you will run the client.

Through ClickHouse Client

On another machine, set-up the ClickHouse client config that you will use later:

cat >clickhouse-client-ssl.xml <<EOF
<config>
       <user>default</user>
       <password>YOUR_PASSWORD</password>
       <host>YOUR_CLICKHOUSE_SERVER_IP</host>
       <secure>true</secure>
       <openSSL>
               <client>
                       <caConfig>/etc/ssl/ca.pem</caConfig>
                       <certificateFile>/etc/ssl/client.pem</certificateFile>
                       <privateKey>/etc/ssl/client.key</privateKey>
               </client>
       </openSSL>
</config>
EOF
Enter fullscreen mode Exit fullscreen mode

Replace YOUR_PASSWORD with the real default user password of your ClickHouse server and replace YOUR_CLICKHOUSE_SERVER_IP with the real IP of the machine that runs the ClickHouse server.

Run the following command, and you are expected to get some output like below:

clickhouse-client –-config=clickhouse-client-ssl.xml
Enter fullscreen mode Exit fullscreen mode

Image description

Through MySQL Client

Use MySQL client to connect the ClickHouse server via SSL. Run the following command, and you are expected to get some output like below:

mysql -u default -p -h YOUR_SERVER_IP -P 9004 --ssl-ca=/etc/ssl/ca.pem --ssl-cert=/etc/ssl/client.pem --ssl-key=/etc/ssl/client.key --execute="STATUS"
Enter fullscreen mode Exit fullscreen mode

Replace YOUR_SERVER_IP with your real server IP.

Image description

As expected, the result shows that the connection is over SSL.

Conclusion

Congratulations, you have now successfully connected to your ClickHouse server using SSL. Let’s go over the steps again:

  1. Install ClickHouse and OpenSSL
  2. Generate SSL related file a. Set up OpenSSL config b. Root CA key and certificate c. Server key and certificate d. Client key and certificate
  3. Configure ClickHouse a. Generate dhparams b. Set up SSL related files path in ClickHouse Server config c. Enable SSL related port listened by ClickHouse d. Enable remote login on ClickHouse
  4. Test SSL Connection from Client a. Copy SSL files to the Client b. Set-up ClickHouse Client config c. Use ClickHouse Client to connect to ClickHouse Server d. Use MySQL Client to connect to ClickHouse Server
Step File(s) you create File(s) you use
2.a - Set up OpenSSL config req.conf None
2.b - Generate Root CA key and certificate ca.key ca.pem ca.srl req.conf
2.c - Generate Server key and certificate server.key server.pem server.csr ca.pem
2.d - Generate Client key and certificate client.key client.pem client.csr ca.pem
3.a - Generate dhparams dhparam.pem None
3.b - Set up related file path in ClickHouse Server config None ca.pem server.key server.pem dhparam.pem
4.a - Copy SSL files to the Client None ca.pem client.key client.pem
4.b - Set-up ClickHouse Client config None None
4.c - Use ClickHouse Client to connect to ClickHouse Server None None
4.d - Use MySQL Client to connect to ClickHouse Server None ca.pem client.key client.pem

If you find this tutorial helpful, you might also be interested in our product Bytebase, an open-source, web-based schema change management tool, which helps your data team or DevOps team manage ClickHouse schema change with UI-based or VCS-based (GitOps) schema change workflow.

Top comments (0)

Read next

adzhydra profile image

Azure Functions (dotnet): The Right Way to Work with Queue Storage

Adam -

sagar25 profile image

TDoC 2024 - Day 3: Introduction to Machine Learning

Sagar Das -

dev007777 profile image

Stop Struggling Learn CSS Faster

TheDev -

msnmongare profile image

How to SSH into a Server Using IP Address, Username, and Password

Sospeter Mong'are -