SSL: CERTIFICATE_VERIFY_FAILED with 0.4.8 version and Superset 2.0.0

[gervarela]

Hi all,

We are using clickhouse-connect with a custom Superset 2.0.0 docker image, and since updating to 0.4.8 version of clickhouse-connect we are unable to establish a connection to a ClickHouse server (version 2.11) with SSL enabled using a self signed certificate.

We were using 0.4.7 version without problems by specifing the 'ca_cert' path in the url connection params:

clickhousedb+connect://database:XXXXXX@ch:8443/database?ca_cert=%2Fusr%2Flocal%2Fshare%2Fca-certificates%2Fcustom_ca.crt&secure=true

After updating to the clickhouse-connect package version 0.4.8 (either rebuilding the image, or updating it directly via pip inside a container) we started getting the next error:

2023-01-11 21:00:02,539:DEBUG:urllib3.connectionpool:Starting new HTTPS connection (128): ch:8443
2023-01-11 21:00:02,546:ERROR:clickhouse_connect.driver.httpclient:Unexpected Http Driver Exception
Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/urllib3/connectionpool.py", line 699, in urlopen
httplib_response = self._make_request(
File "/usr/local/lib/python3.8/site-packages/urllib3/connectionpool.py", line 382, in _make_request
self._validate_conn(conn)
File "/usr/local/lib/python3.8/site-packages/urllib3/connectionpool.py", line 1010, in validate_conn
conn.connect()
File "/usr/local/lib/python3.8/site-packages/urllib3/connection.py", line 411, in connect
self.sock = ssl_wrap_socket(
File "/usr/local/lib/python3.8/site-packages/urllib3/util/ssl.py", line 449, in ssl_wrap_socket
ssl_sock = ssl_wrap_socket_impl(
File "/usr/local/lib/python3.8/site-packages/urllib3/util/ssl.py", line 493, in _ssl_wrap_socket_impl
return ssl_context.wrap_socket(sock, server_hostname=server_hostname)
File "/usr/local/lib/python3.8/ssl.py", line 500, in wrap_socket
return self.sslsocket_class._create(
File "/usr/local/lib/python3.8/ssl.py", line 1040, in _create
self.do_handshake()
File "/usr/local/lib/python3.8/ssl.py", line 1309, in do_handshake
self._sslobj.do_handshake()
ssl.SSLCertVerificationError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1131)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/requests/adapters.py", line 439, in send
resp = conn.urlopen(
File "/usr/local/lib/python3.8/site-packages/urllib3/connectionpool.py", line 755, in urlopen
retries = retries.increment(
File "/usr/local/lib/python3.8/site-packages/urllib3/util/retry.py", line 574, in increment
raise MaxRetryError(_pool, url, error or ResponseError(cause))
urllib3.exceptions.MaxRetryError: HTTPSConnectionPool(host='ch', port=8443): Max retries exceeded with url: / (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1131)')))

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/clickhouse_connect/driver/httpclient.py", line 308, in _raw_request
response: Response = self.session.request(method, self.url,
File "/usr/local/lib/python3.8/site-packages/requests/sessions.py", line 542, in request
resp = self.send(prep, **send_kwargs)
File "/usr/local/lib/python3.8/site-packages/requests/sessions.py", line 655, in send
r = adapter.send(request, **kwargs)
File "/usr/local/lib/python3.8/site-packages/requests/adapters.py", line 514, in send
raise SSLError(e, request=request)
requests.exceptions.SSLError: HTTPSConnectionPool(host='ch', port=8443): Max retries exceeded with url: / (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1131)')))
SupersetErrorsException
Traceback (most recent call last):
File "/app/superset/databases/commands/test_connection.py", line 123, in run
raise DBAPIError(None, None, None)
sqlalchemy.exc.DBAPIError: (builtins.NoneType) None
(Background on this error at: https://sqlalche.me/e/14/dbapi)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1516, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1502, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/security/decorators.py", line 89, in wraps
return f(self, *args, **kwargs)
File "/app/superset/views/base_api.py", line 114, in wraps
raise ex
File "/app/superset/views/base_api.py", line 111, in wraps
duration, response = time_function(f, self, *args, **kwargs)
File "/app/superset/utils/core.py", line 1566, in time_function
response = func(*args, **kwargs)
File "/app/superset/utils/log.py", line 244, in wrapper
value = f(*args, **kwargs)
File "/app/superset/views/base_api.py", line 84, in wraps
return f(self, *args, **kwargs)
File "/app/superset/databases/api.py", line 709, in test_connection
TestConnectionDatabaseCommand(item).run()
File "/app/superset/databases/commands/test_connection.py", line 148, in run
raise DatabaseTestConnectionFailedError(errors) from ex
superset.databases.commands.exceptions.DatabaseTestConnectionFailedError: [SupersetError(message='(builtins.NoneType) None\n(Background on this error at: https://sqlalche.me/e/14/dbapi)', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'ClickHouse Connect', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]
2023-01-11 21:00:02,553:WARNING:superset.views.base:SupersetErrorsException
Traceback (most recent call last):
File "/app/superset/databases/commands/test_connection.py", line 123, in run
raise DBAPIError(None, None, None)
sqlalchemy.exc.DBAPIError: (builtins.NoneType) None
(Background on this error at: https://sqlalche.me/e/14/dbapi)

We tried three different ways to specify the CA certificate, resulting in the same error:

• Specify the 'ca_cert' param in the URL as said before.
• Insert the CA cert contents in the Advanced > Security > Root Cert field from the Superset DB connection wizard.
• Trust the CA cert at system level.

Al these three ways work well with the 0.4.7 version, but don't with 0.4.8 version (from pip package reposity).

Thank you.

[genzgd]

Thanks for reporting this! It looks like parameter passing to the driver was broken in 0.4.8; I will include a fix in the upcoming 0.5.0 release.

As understand the requests library, it does not by default trust the OS certificate store, but instead uses the Python certifi package for trusted root certs, so I'm not sure why that method worked before.

Hopefully you can fall back to 0.4.7 unless the 0.5.0 release is ready.

[gervarela]

Hello @genzgd.

Yes, we are going to continue using the 0.4.7, and will try the 0.5.0 when available.

Thank you very much.