Resolving SSL certificate verify error in ClickHouse

Resolving code 210 SSL certificate verify error in ClickHouse

The error is typically reported as: Code: 210. DB::NetException: SSL Exception: error:1000007d:SSL routines:OPENSSL_internal:CERTIFICATE_VERIFY_FAILED

Cause of the error

This error occurs while trying to connect to a ClickHouse server using clickhouse-client. The cause of the error is either:

the client configuration file config.xml is missing the root certificate in the machine CA default store, or
there is a self-signed or internal CA certificate that is not configured

Solution

If using an internal or self-signed CA, configure the CA root certificate in config.xml in the client directory (e.g. /etc/clickhouse-client) and disable the loading of the default root CA certificates from the default location. Here is an example configuration:

<openSSL>
    <client>
        <loadDefaultCAFile>false</loadDefaultCAFile>
        <caConfig>/etc/clickhouse-server/certs/marsnet_ca.crt</caConfig>
        <cacheSessions>true</cacheSessions>
        <disableProtocols>sslv2,sslv3</disableProtocols>
        <preferServerCiphers>true</preferServerCiphers>
        <invalidCertificateHandler>
            <name>RejectCertificateHandler</name>
        </invalidCertificateHandler>
    </client>
</openSSL>

Python clients on macOS

Python clients report this error differently, typically as:

ssl.SSLCertVerificationError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate

On macOS, the Python build from python.org verifies certificates through OpenSSL’s default paths rather than the system keychain. On a fresh install, those paths point to no root certificate bundle: the installer ships certifi but does not link it into OpenSSL’s default location until you run the bundled Install Certificates.command script. Until then, the client cannot validate the ClickHouse Cloud server certificate, even though that certificate is valid. This affects python.org macOS builds generally (Python 3.6 and later), not just Python 3.11. Run Install Certificates.command to link certifi into OpenSSL’s default certificate path. Adjust the version in the path to match your installation:

open "/Applications/Python 3.11/Install Certificates.command"

Alternatively, point your client at the certifi bundle directly. ClickHouse Connect does not fall back to certifi on its own, so pass the bundle through the ca_cert parameter:

import certifi
import clickhouse_connect

client = clickhouse_connect.get_client(
    host='HOSTNAME.clickhouse.cloud',
    port=8443,
    username='default',
    password='YOUR_SECRET_PASSWORD',
    ca_cert=certifi.where(),
)

Additional resources

See the clickhouse-client configuration documentation.

​Resolving code 210 SSL certificate verify error in ClickHouse

​Cause of the error

​Solution

​Python clients on macOS

​Additional resources

Resolving code 210 SSL certificate verify error in ClickHouse

Cause of the error

Solution

Python clients on macOS

Additional resources