Setting up TLS-SSL for ClickHouse Server

Since databases are where data is stored in systems, they are among the most valuable and secure parts of the system. The results of the studies show that database security is not given much weight globally. Database security is compromised when it is not properly maintained, putting financial information, customer information, health records, and more at risk.

What is TLS?

A cryptographic technology called Transport Layer Security (TLS) offers security for communications over network layers. It is also known as Secure Sockets Layer (SSL), which was its previous name. Enabling TLS support in client and server applications means that communication between the ClickHouse database and clients, as well as communication between servers in the ClickHouse cluster, is encrypted.

The following main features could be provide in a connection that is TLS-secured between a client and a server:

  • Confidentiality means nobody can reach the content of your communication
  • Integrity means nobody has the ability to change the content of your data while it is on the way
  • Authentication means the identities of both the client and the server can be authenticated

A certificate chain is made up of a number of connected certificates that are connected by digital signatures. The preceding certificate in the chain digitally signs each certificate that follows it. Between the root and server certificates, the certificate chain may include any number of intermediate certificates. The chain’s root certificate may be issued by a trusted Certificate Authority or self-signed (CA).

Important information concerning the usage of CA certificates

This article provides examples of simple SSL/TLS settings. To create the appropriate certificates for your company, seek advice from your PKI/security team. Get a Certification Authority certificate and include it when connecting to your database if you want the certificate to be trusted.

Let’s start with creating self-signed root certificate:

To build the connections with the proper settings for this demonstration, a self-signed Certificate Authority (CA) certificate and key are created with node certificates.

openssl genrsa -out root-test-ca.key 4096

This command generates the private root key, which has 4096 characters.

# NOT FOR PRODUCTION USE. OpenSSL configuration file for testing.
# For the CA policy
[ policy_match ]
organizationName = match
commonName = supplied
[ req ]
default_bits = 4096
default_keyfile = myTestCertificateKey.pem    
default_md = sha256                          
distinguished_name = req_dn
req_extensions = v3_req
x509_extensions = v3_ca # The extentions to add to the self signed cert
[ v3_req ]
subjectKeyIdentifier  = hash
basicConstraints = CA:FALSE
keyUsage = critical, digitalSignature, keyEncipherment
nsComment = "OpenSSL Generated Certificate for TESTING only.  NOT FOR PRODUCTION USE."
extendedKeyUsage  = serverAuth, clientAuth
[ req_dn ]
organizationName = Organization Name (eg, company)
organizationName_default = TestCertificateOrgName
organizationName_max = 64
commonName = Common Name (eg, YOUR name)
commonName_max = 64
[ v3_ca ]
# Extensions for a typical CA
subjectKeyIdentifier=hash
basicConstraints = critical,CA:true
authorityKeyIdentifier=keyid:always,issuer:always

The sha256 algorithm is used to produce a configuration file for a key and need to create a file as root-test-ca.cnf and paste the above content.

openssl req -new -x509 -days 1826 -key root-test-ca.key -out root-test-ca.crt -config root-test-ca.cnf

It is possible to create a root certificate valid for 5 years using with this command. It expects the organization name and common name information during execution and after then, root certificate is ready for use.

openssl x509 -in root-test-ca.crt -text

We may view the self-signed root certificate’s details.

Making an Intermediate certificate is the next step

It’s time to create intermediate certificate using the self-signed root certificate.

openssl genrsa -out intermediate-test-ia.key 4096

This command generates the intermediate private key, which has 4096 characters.

openssl req -new -key intermediate-test-ia.key -out intermediate-test-ia.csr -config root-test-ca.cnf

In this step, Certificate Signing Request file is created. It expects the organization name and common name information during execution and after then, intermediate request file is ready to use.

openssl x509 -sha256 -req -days 730 -in intermediate-test-ia.csr -CA root-test-ca.crt -CAkey root-test-ca.key -set_serial 01 -out intermediate-test-ia.crt -extfile root-test-ca.cnf -extensions v3_ca

The intermediate certificate is prepared which is valid for 2 years. This is a critical certificate that will be used to generate server and client side.

openssl x509 -in intermediate-test-ia.crt -text

We can see all of the details of the intermediate certificate with above command. When looking at their specifics, we can see where they are signed in the issuer section of intermediate certificates.

cat root-test-ca.crt intermediate-test-ia.crt  > test-ca.pem

If necessary, we can construct a test-ca.pem file by combining the root and intermediate certificates (optional).

openssl verify -CAfile root-test-ca.crt intermediate-test-ia.crt

It is time to verify the compatibility of the intermediate and root certificates.

Let’s provide each server with a separate certificate

Up until this moment, it was enough to go on a single server for standalone or cluster database environments. However, if you have a cluster environment, you must generate unique server certificates for each server in the cluster. To generate a server certificate as unique, intermediate-test-ia.crt and intermediate-test-ia.key files must be transferred to each server. Therefore, each server must go through the next procedures independently.

openssl genrsa -out clickhouse01-test-server.key 4096

This command generates the server private key, which has 4096 characters.

# NOT FOR PRODUCTION USE. OpenSSL configuration file for testing.
[ req ]
default_bits = 4096
default_keyfile = myTestServerCertificateKey.pem    
default_md = sha256
distinguished_name = req_dn
req_extensions = v3_req
[ v3_req ]
subjectKeyIdentifier  = hash
basicConstraints = CA:FALSE
keyUsage = critical, digitalSignature, keyEncipherment
nsComment = "OpenSSL Generated Certificate for TESTING only.  NOT FOR PRODUCTION USE."
extendedKeyUsage  = serverAuth, clientAuth
subjectAltName = @alt_names
[ alt_names ]
DNS.1 = clickhouse01
IP.1 = 192.168.64.43
[ req_dn ]
organizationName = Organization Name (eg, company)
organizationName_default = TestServerCertificateOrg
organizationName_max = 64
commonName = Common Name (eg, YOUR name)
commonName_max = 64

Each server certificate must contain the hostname and IP address of its own server information. Once more the sha256 algorithm here and need to create a file as clickhouse01-test-server.cnf and paste the above content.

openssl req -new -key clickhouse01-test-server.key -out clickhouse01-test-server.csr -config clickhouse01-test-server.cnf

It expects the organization name and common name information during execution and after then, server request file is ready to use.

openssl x509 -sha256 -req -days 365 -in clickhouse01-test-server.csr -CA intermediate-test-ia.crt -CAkey intermediate-test-ia.key -CAcreateserial -out clickhouse01-test-server.crt -extfile clickhouse01-test-server.cnf -extensions v3_req

clickhouse01-test-server.crt file is ready to use. Here we have implemented it for the first server as an example. It is necessary to prepare certificates for other servers as well and using these crt and key files on config.xml as indicated in the diagram below, makes our cluster secure.

openssl x509 -in  clickhouse01-test-server.crt -text

Examining all of the details of the server certificate with above command is possible. When looking at their specifics, we can see where they are signed in the issuer section of server certificates.

Consider a cluster of three servers. After completing the above tasks, the files required on the servers are as follows. These files must be mentioned in the database configuration files on each server in order for secure inter-server communication. Optionally, the test-ca.pem certificate, which is a combination of root and intermediate certificates, can be used instead of the intermediate certificate. Briefly, with the applications made so far, the communication between the servers in the cluster has been secured. Meanwhile, the files we created earlier need to be kept in a safe place outside the server because anyone with access to them will be able to generate certificates instantly.

What about client connections?

It is possible to establish a secure connection between the ClickHouse database and the client.

openssl genrsa -out client-test-client.key 4096

This command generates the client private key, which has 4096 characters.

# NOT FOR PRODUCTION USE. OpenSSL configuration file for testing.
[ req ]
default_bits = 4096
default_keyfile = myTestClientCertificateKey.pem    ## The default private key file name.
default_md = sha256
distinguished_name = req_dn
req_extensions = v3_req
[ v3_req ]
subjectKeyIdentifier  = hash
basicConstraints = CA:FALSE
keyUsage = critical, digitalSignature, keyEncipherment
nsComment = "OpenSSL Generated Certificate for TESTING only.  NOT FOR PRODUCTION USE."
extendedKeyUsage  = serverAuth, clientAuth
[ req_dn ]
organizationName = Organization Name (eg, company)
organizationName_default = TestClientCertificateOrg
organizationName_max = 64
commonName = Common Name (eg, YOUR name)
commonName_max = 64

The sha256 algorithm is used to produce a configuration file for a key and need to create a file as client-test-client.cnf and paste the above content.

openssl req -new -key client-test-client.key -out client-test-client.csr -config client-test-client.cnf

During execution, it needs the organization name and common name information, and then the client request file is ready to use.

openssl x509 -sha256 -req -days 365 -in client-test-client.csr -CA intermediate-test-ia.crt -CAkey intermediate-test-ia.key -CAcreateserial -out client-test-client.crt -extfile client-test-client.cnf -extensions v3_req

Client informations are ready to share. Clients can connect to the ClickHouse database by using the files client-test-client.crt and client-test-client.key. The clients can access all nodes in the cluster.

cat client-test-client.crt client-test-client.key > test-client.pem

Alternatively, the clients can connect to the database using the pem file rather than both files.

In conclusion

TLS is intended to protect data from hackers and preserve the security of sensitive informations. In this article, we looked at what TLS is, how certificates work, and how TLS is implemented in ClickHouse for testing purposes. To activate TLS in ClickHouse, make sure to implement required certifications and set <verificationMode>strict</verificationMode> in config.xml.

About Ilkay Cetindag 4 Articles
Ilkay has been administering databases including NoSQL and RDBMS for over 7 years. He is experienced working high-scale banking databases. He is currently working at ChistaDATA Inc. as Database Administrator.
Contact: Website