Introduction
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.
Runbook to setup TLS-SSL for ClickHouse Server
Create 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.
Make an intermediate certificate
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.
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.
Establish secure client connection
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.
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.
To learn more about Security in ClickHouse, read the following articles:
- ClickHouse Security: Encrypting Data at Rest in ClickHouse
- Implementing Data Governance and Security in ClickHouse
- ClickHouse Security: Role-Based Access Control (RBAC) in ClickHouse
- ClickHouse Security: Implementing Auditing and Log Capture
- ClickHouse Security: ChistaDATA’s Expertise in Vulnerability Remediation
