Analytics PipelineClickHouse & Materialized Views
Guide ClickHouse Core Architecture & Analytics Fundamentals

Enforcing TLS and Encrypted Connections

A default ClickHouse install listens for plaintext client traffic on 8123 (HTTP) and 9000 (native), which means every query, every INSERT payload, and every credential crosses the network in the clear — fine on a laptop, negligent between availability zones or over any link an attacker can tap. Enforcing TLS is not one toggle; it is opening the encrypted listeners (8443 for HTTPS, 9440 for the native protocol), wiring a server certificate and CA into config.xml, closing the plaintext ports so nothing can silently downgrade, and — where you need it — pinning specific users to certificate authentication so a stolen password is useless without the matching client key. This guide walks the full path and shows how to prove, from system.session_log, that connections actually arrived encrypted rather than assuming they did.

Prerequisites

What “enforcing” TLS actually requires

Opening a secure port is necessary but not sufficient. As long as 8123 and 9000 remain open, a misconfigured client — or an attacker probing for the easy path — can still connect in plaintext, and you will not notice because the query succeeds. Real enforcement is three moves at once: bind the encrypted listeners, present a trusted certificate chain, and remove the plaintext listeners so the only reachable ports are the encrypted ones. Layered on top, per-user IDENTIFIED WITH ssl_certificate turns TLS from a transport detail into an authentication factor, because that identity can only be asserted after a mutual-TLS handshake completes.

TLS enforcement: encrypted listeners open, plaintext listeners closed, certificate chain validated Clients reach ClickHouse only through port 8443 HTTPS and port 9440 native TLS. Each connection completes a TLS handshake against the server certificate and CA loaded from config.xml, with strict verification requiring a client certificate. The plaintext 8123 and 9000 listeners are closed, removing any downgrade path. CLIENTS clickhouse-connect HTTPS · secure=True clickhouse-client native protocol · TLS ENCRYPTED LISTENERS 8443 · HTTPS https_port 9440 · native TLS tcp_port_secure TLS handshake CLOSED · no downgrade path 8123 · 9000 plaintext refused SERVER TRUST openSSL · config.xml certificateFile privateKeyFile caConfig verificationMode strict → mutual TLS validate chain

Step 1 — Place the certificate material

Stage the server certificate, its private key, and the CA certificate where the clickhouse user can read them, and lock down the key so only that user can.

bash
sudo install -d -o clickhouse -g clickhouse -m 0750 /etc/clickhouse-server/certs
sudo install -o clickhouse -g clickhouse -m 0640 server.crt /etc/clickhouse-server/certs/server.crt
sudo install -o clickhouse -g clickhouse -m 0600 server.key /etc/clickhouse-server/certs/server.key
sudo install -o clickhouse -g clickhouse -m 0640 ca.crt     /etc/clickhouse-server/certs/ca.crt

A private key readable by other users is the one mistake that quietly voids the whole exercise; 0600 and clickhouse ownership are non-negotiable.

Step 2 — Configure the encrypted listeners and certificate chain

Add a drop-in at /etc/clickhouse-server/config.d/tls.xml. It opens the two secure ports, points OpenSSL at the certificate material, and sets the verification mode. verificationMode of strict requires and validates a client certificate (mutual TLS); use relaxed if you only need server-side encryption without client certs.

xml
<clickhouse>
    <!-- Encrypted listeners -->
    <https_port>8443</https_port>
    <tcp_port_secure>9440</tcp_port_secure>

    <openSSL>
        <server>
            <certificateFile>/etc/clickhouse-server/certs/server.crt</certificateFile>
            <privateKeyFile>/etc/clickhouse-server/certs/server.key</privateKeyFile>
            <caConfig>/etc/clickhouse-server/certs/ca.crt</caConfig>
            <!-- strict = require + validate client cert (mTLS); relaxed = server-side only -->
            <verificationMode>strict</verificationMode>
            <loadDefaultCAFile>false</loadDefaultCAFile>
            <cacheSessions>true</cacheSessions>
            <disableProtocols>sslv2,sslv3,tlsv1,tlsv1_1</disableProtocols>
            <preferServerCiphers>true</preferServerCiphers>
            <invalidCertificateHandler>
                <name>RejectCertificateHandler</name>
            </invalidCertificateHandler>
        </server>
    </openSSL>
</clickhouse>

Disabling the legacy protocols (sslv2 through tlsv1_1) forces modern TLS and is the kind of hardening the security and access control boundaries model expects at the perimeter.

Step 3 — Close the plaintext ports

Enforcement means removing the alternatives. In config.xml, comment out or delete the plaintext listeners so the server never binds them.

xml
<!-- <http_port>8123</http_port> -->
<!-- <tcp_port>9000</tcp_port> -->

Leaving these open is the single most common way “we enabled TLS” turns out to be false in an audit — the encrypted port works, so nobody notices the plaintext one still answers. Coordinate with the network layer too: the firewall rules covered in configuring ClickHouse network security groups should also drop 8123/9000 at the perimeter as defense in depth.

Step 4 — Require certificate auth for sensitive users

For accounts where a leaked password would be catastrophic, bind the user to a client-certificate identity. IDENTIFIED WITH ssl_certificate admits the user only when the mutual-TLS handshake presents a certificate whose Common Name matches — there is no password to steal.

sql
CREATE USER etl_svc
    IDENTIFIED WITH ssl_certificate CN 'etl-svc.internal'
    SETTINGS readonly = 0;

GRANT role_pipeline TO etl_svc;

Because that identity is only assertable over TLS, this user physically cannot connect in plaintext. Grant its capabilities through a role, following managing RBAC roles and grants, so the certificate authenticates who while the role decides what.

Step 5 — Restart and point clients at the secure ports

New listener ports require a full restart; a config reload will not bind them.

bash
sudo systemctl restart clickhouse-server

clickhouse-connect speaks HTTPS on 8443. Pass secure=True to enable TLS and verify=True with a ca_cert so the client validates the server chain instead of trusting it blindly:

python
import clickhouse_connect

client = clickhouse_connect.get_client(
    host="clickhouse.internal",
    port=8443,
    username="analyst",
    password="…",
    secure=True,                       # use TLS
    verify=True,                       # validate the server certificate
    ca_cert="/etc/ssl/certs/ca.crt",   # trust anchor for the server chain
)
print(client.query("SELECT 1").result_rows)

For the certificate-authenticated etl_svc user, present the client key pair instead of a password — this is the mutual-TLS path:

python
client = clickhouse_connect.get_client(
    host="clickhouse.internal", port=8443, secure=True, verify=True,
    ca_cert="/etc/ssl/certs/ca.crt",
    client_cert="/etc/ssl/certs/etl-svc.crt",      # CN = etl-svc.internal
    client_cert_key="/etc/ssl/private/etl-svc.key",
    username="etl_svc",
)

The native protocol on 9440 is what clickhouse-client --secure --port 9440 and the native clickhouse-driver use; clickhouse-connect itself is an HTTP client and always targets 8443.

Verification

First prove the handshake at the wire level, before trusting any client library. openssl s_client shows the negotiated protocol and the presented certificate:

bash
openssl s_client -connect clickhouse.internal:9440 -CAfile ca.crt </dev/null 2>/dev/null \
  | grep -E 'Protocol|Verify return code'
# Expect a TLSv1.2/1.3 protocol line and "Verify return code: 0 (ok)".

curl -sv --cacert ca.crt https://clickhouse.internal:8443/ping 2>&1 | grep -E 'SSL connection|^Ok'

Confirm the plaintext ports are truly gone — a connection refused here is success:

bash
curl -s http://clickhouse.internal:8123/ping ; echo "exit=$?"
# Expect a non-zero exit / connection refused, not "Ok."

Then audit from inside the server. system.session_log records how each session authenticated, including the certificate subject presented on a mutual-TLS connection. Flush logs first, then confirm sensitive sessions carried a certificate:

sql
SYSTEM FLUSH LOGS;

SELECT event_time, user, auth_type, interface,
       client_address, certificate_subjects
FROM system.session_log
WHERE type = 'LoginSuccess'
  AND event_time > now() - INTERVAL 15 MINUTE
ORDER BY event_time DESC;

For the etl_svc account, auth_type should read SSL_CERTIFICATE and certificate_subjects should list its CN — an empty certificate_subjects for that user means it authenticated some other way and the enforcement leaked. A quick aggregate surfaces any account that logged in without a certificate where you expected one:

sql
SELECT user, auth_type, count() AS logins,
       countIf(length(certificate_subjects) = 0) AS without_cert
FROM system.session_log
WHERE type = 'LoginSuccess' AND event_time > now() - INTERVAL 1 DAY
GROUP BY user, auth_type
ORDER BY without_cert DESC;

Gotchas & edge cases

  • Config reload does not open new ports. SYSTEM RELOAD CONFIG refreshes users and remote_servers but never rebinds listeners. Adding https_port/tcp_port_secure and reloading leaves the server on its old ports while clients fail to reach the new ones. Always systemctl restart after changing any port.
  • Interserver replication has its own TLS switch. Encrypting client ports does nothing for replica-to-replica fetches, which flow over interserver_http_port (9009) by default. To encrypt that traffic you must set interserver_https_port (9010) and an <interserver_http_credentials> block; otherwise your data still crosses the network in the clear between nodes even though every client is on TLS.
  • verificationMode strict breaks server-only clients. Setting strict requires every connecting client to present a valid certificate. A tool that only wants server-side encryption (no client cert) will fail the handshake. Use relaxed for mixed fleets and reserve strict for deployments where all clients are provisioned with certificates, or scope the requirement to specific users via IDENTIFIED WITH ssl_certificate.
  • verify=False silently defeats the point. clickhouse-connect with secure=True, verify=False encrypts the channel but accepts any certificate, so a man-in-the-middle with a self-signed cert is undetected. Always ship the CA and set verify=True in production; verify=False is a lab-only shortcut.

Up: Security & Access Control Boundaries