Overview
Follow this tutorial, you can configure the ClickHouse SSL connection and test it by using self-signed CA easily. The general steps are:
- Install ClickHouse and OpenSSL
- Generate SSL Related Files
- Configure ClickHouse Server
- 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:
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:
Generate SSL Related Files
We will generate the following certificate chain:
OpenSSL Config
To generate Root CA certificate and other peer's certificate request, you need to set up a configure file as below:
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.
Now, use this key and OpenSSL config above to generate the CA certificate:
Generate Server Key and Certificate
Generate Server Key without the passphrase, too:
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.
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.
After completing the above steps, you should have the following files:
Those highlighted files will be used in the next section.
Configure ClickHouse Server
Generate dhparams
From ClickHouse config, you can see:
you need to generate dhparams by using the command below:
It will take a couple minutes.
After completion, modify the configuration file to indicate the location of the dhparams file:
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:
Configure Listening Port
Then, enable SSL related port by uncommenting on the following lines:
Then disable the default non-secure port by commenting out the corresponding lines.
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:
Restart the ClickHouse Service
Then, restart the ClickHouse server. For example, on Ubuntu:
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:
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:
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:
Replace YOUR_SERVER_IP with your real server IP.
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:
- Install ClickHouse and OpenSSL
- 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 - 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 - 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.