Configuring Secure Channel Encryption for ClickHouse Clusters

Constraints

• Disabling HTTPS will pose risks to enterprise services.
• The HTTPS option is enabled during cluster creation and cannot be disabled later.
• If the HTTPS option is not enabled during cluster creation, it cannot be enabled later.
• The cluster restarts after the secure channels are enabled on the cluster details page.
• The secure and non-secure channels cannot be disabled after being enabled concurrently.

Enabling the Secure Channel

1. Log in to the CloudTable console.
2. Select a region in the upper left corner.
3. On the Cluster Management page, click Buy Cluster in the upper right corner. The Buy Cluster page is displayed.
4. Check whether Enable Channel Encryption (which is toggled on by default) is toggled on after completing other configurations.

   Additionally, you can toggle Enable Secure and Non-secure Channels on the cluster details page post-creation. This enables both secure and non-secure channels.

5. Complete the parameter setting and click Next.
6. Confirm the cluster specification order information on the displayed page and submit the order. After the cluster is created, go to its details page to view the channel status.

Downloading a Certificate

1. After the cluster is created, go to the cluster details page and click Download certificates on the right of Channel Status in the cluster information area.
2. Use the SSH login tool to remotely log in to the Linux ECS through the EIP.

   For details, see "ECSs> Logging In to a Linux ECS > Login Using an SSH Password" in the Elastic Cloud Server User Guide.

3. Configure the certificate.

   Customize the certificate path by updating the certificate storage path in the following configuration file. Save the file to the root directory.

   <config>
       <secure>true</secure>
       <openSSL>
         <client>
           <caConfig>/etc/ssl/certificate.crt</caConfig>
         </client>
       </openSSL>
   </config>
   
   

   • <caConfig>/etc/ssl/certificate.crt</caConfig> indicates the path where certificates are stored.
   • root indicates the path for storing the configuration file.
   • The certificate can be downloaded only once per minute.

Using the ClickHouse Client to Connect to a Cluster

1. After the certificate is configured, download the client. Log in to the CloudTable management console. In the navigation pane on the left, choose Help. On the right of the page, click Download Client and Client Verification File to download the client installation package and client verification file.
2. Install the client.
   1. Use the SSH login tool to remotely log in to the Linux ECS through the EIP.

      For details, see "ECSs> Logging In to a Linux ECS > Login Using an SSH Password" in the Elastic Cloud Server User Guide.

   2. Go to the root directory of the SSH login tool.

      cd /
      
      

   3. Create a folder in the root directory.

      mkdir Folder name
      
      

   4. Go to the directory of the created folder.

      cd /Folder name/
      
      

   5. Place the client in the directory.
   6. Decompress the client package.

      tar  -zxf   Client package name
      
      

   7. Decompress the client verification file to the same directory as the client.
      1. Decompress the client verification file.

         cd <Path for storing the client verification file >
         tar -xzvf Client_sha256.tar.gz
         
         

      2. Obtain the client verification code.

         sha256sum ClickHouse_Client_23.3.tar.gz
         
         

      3. Check the verification code in the client verification file and compare it with the client verification code. A match indicates no tampering, while a mismatch suggests tampering.

         less ClickHouse_Client_23.3.tar.gz.sha256
         
         

   8. Go to the clickhouse folder and load the .so file.

      sh install.sh
      
      

   9. Go to the bin directory.

      cd bin/
      
      

      Grant the 700 permission to the directory.

      chmod 700 clickhouse
      
      

3. Connect to the cluster.

   ./clickhouse client --host Private IP address of the cluster --port 9440 --user admin --password Password --secure --config-file /root/config.xml
   
   

HTTPS Connection

1. Click the name of the target secure cluster to download the certificates on its details page.
2. Download a certificate by referring to Downloading a Certificate and customize the storage path.
3. Execute the sample SQL statement through HTTPS.

   echo 'select 1' | curl -H 'X-ClickHouse-User: user' -H 'X-ClickHouse-Key: password' --cacert /clickhouse/client/client/bin/certificate.crt 'https://host:port/?' --data-binary @-
   
   

   • select 1 indicates the executed SQL statement.
   • user indicates the username.
   • password indicates the password created during cluster creation.
   • /clickhouse/client/client/bin/certificate.crt indicates the path for storing the certificates.

   • host indicates the private IP address and port indicates the HTTPS port.

JDBC Connection

public void run()
        throws InterruptedException {
    final ClickHouseProperties clickHouseProperties = new ClickHouseProperties();
    // There will be security risks if the password used for authentication is directly written into code. Encrypt the password in the configuration file or environment variables for storage;
    // In this example, the password is stored in environment variables for identity authentication. Before running the code in this example, configure environment variable CK_PASSWORD.
    String password = System.getenv("CK_PASSWORD");
    clickHouseProperties.setSslRootCertificate("/etc/ssl/certificate.crt");
    clickHouseProperties.setSsl(true);
    clickHouseProperties.setSslMode("strict");
    clickHouseProperties.setUser("test");
    clickHouseProperties.setPassword(password);
    clickHouseProperties.setSocketTimeout(2 * 3600 * 1000);
    final BalancedClickhouseDataSource dataSource = new BalancedClickhouseDataSource("xxxx.mycloudtable.com:8443/default?ssl=true", clickHouseProperties);
    try {
        final ClickHouseConnection conn = dataSource.getConnection();
        conn.createStatement().executeQuery("select now()");
    } catch (Throwable e) {
        e.printStackTrace();
    }
}