Skip to main content

Resolving SSL certificate verify error in ClickHouse

Learn how to resolve the SSL Exception CERTIFICATE_VERIFY_FAILED error.

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 doesn't link it into OpenSSL's default location until you run the bundled Install Certificates.command script. Until then, the client can't 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 install):

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

Alternatively, point your client at the certifi bundle directly. clickhouse-connect doesn't 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​

View https://clickhouse.com/docs/interfaces/cli/#configuration_files

· 2 min read