ClickHouse DB::Exception: OpenSSL error - SSL/TLS operation failed

The DB::Exception: OPENSSL_ERROR is raised when an SSL/TLS operation fails within ClickHouse. This can happen during encrypted connections between clients and the server, inter-node communication in clusters, or when ClickHouse connects to external services over HTTPS. The underlying cause is typically a certificate verification failure, an expired certificate, or a protocol version mismatch.

Impact

SSL errors prevent secure connections from being established. Depending on where the failure occurs, the consequences vary:

Client connections over the secure native port (9440) or HTTPS port (8443) will be refused.
Replicated tables that communicate over encrypted inter-node links will stop syncing.
Queries to external HTTPS endpoints via url(), s3(), or dictionary sources will fail.
Distributed queries across shards using TLS will time out or error immediately.

Common Causes

The SSL certificate has expired or is not yet valid.
The certificate chain is incomplete --- an intermediate CA certificate is missing from the configuration.
The server's certificate does not match the hostname being connected to.
The client and server cannot agree on a TLS protocol version (e.g., one side requires TLS 1.3 while the other only supports TLS 1.2).
File permissions prevent ClickHouse from reading the certificate or private key files.
Self-signed certificates are used without being added to the trusted CA store.
The private key does not match the certificate.

Troubleshooting and Resolution Steps

Check the ClickHouse server log for the full OpenSSL error message, which usually includes a reason code:
```
grep -i "openssl\|ssl\|tls" /var/log/clickhouse-server/clickhouse-server.err.log | tail -20
```

Verify that the certificate and key files are valid and match:

openssl x509 -noout -dates -in /etc/clickhouse-server/server.crt
openssl x509 -noout -modulus -in /etc/clickhouse-server/server.crt | md5sum
openssl rsa -noout -modulus -in /etc/clickhouse-server/server.key | md5sum

The two md5sum outputs must be identical.

Confirm the certificate chain is complete:

openssl verify -CAfile /etc/clickhouse-server/ca-bundle.crt /etc/clickhouse-server/server.crt

Review the SSL configuration in config.xml or the equivalent config.d override:

<openSSL>
    <server>
        <certificateFile>/etc/clickhouse-server/server.crt</certificateFile>
        <privateKeyFile>/etc/clickhouse-server/server.key</privateKeyFile>
        <caConfig>/etc/clickhouse-server/ca-bundle.crt</caConfig>
    </server>
</openSSL>

Ensure the ClickHouse process user has read permissions on all certificate files:
```
ls -la /etc/clickhouse-server/server.crt /etc/clickhouse-server/server.key
```
Test connectivity with openssl s_client to isolate whether the issue is server-side or client-side:
```
openssl s_client -connect your-clickhouse-host:9440
```
If using self-signed certificates, add them to the CA configuration or disable verification for testing only:
```
<openSSL>
    <client>
        <verificationMode>none</verificationMode>
    </client>
</openSSL>
```
Never disable verification in production.

Best Practices

Automate certificate renewal using tools like certbot or your organization's PKI to avoid expiration-related outages.
Store certificates in a dedicated directory with restricted permissions (readable only by the clickhouse user).
Always include the full certificate chain in your certificate file.
Pin minimum TLS versions in your configuration to avoid downgrade attacks.
Monitor certificate expiration dates with alerting set for at least 30 days before expiry.

Frequently Asked Questions

Q: Can I run ClickHouse without SSL entirely?
A: Yes. SSL is optional in ClickHouse. If you only use the non-secure ports (8123 for HTTP and 9000 for native), SSL is not involved. However, encrypted connections are strongly recommended for production environments.

Q: How do I enable TLS 1.3 in ClickHouse?
A: ClickHouse supports TLS 1.3 if compiled against OpenSSL 1.1.1 or later. You can restrict the minimum protocol version in the openSSL configuration section using <requireTLSv1_2>true</requireTLSv1_2> or similar directives.

Q: Why does the error mention a different hostname than I expected?
A: This usually indicates a certificate Subject Alternative Name (SAN) mismatch. The certificate must include the exact hostname or IP that clients use to connect.