HTTP Proxy Connection with SSL Certificate Results in DatabaseError Code 516

[netchose]

Hello,

I'm encountering an issue when trying to connect to ClickHouse through an HTTP proxy with SSL certificate authentication. I receive a DatabaseError with a response code of 403. Below are the details of the error and the code snippet I'm using.

File "/home/user/.cache/pypoetry/virtualenvs/project/lib/python3.10/site-packages/clickhouse_connect/driver/httpclient.py", line 366, in _error_handler
raise OperationalError(err_str) if retried else DatabaseError(err_str) from None
clickhouse_connect.driver.exceptions.DatabaseError: HTTPDriver for https://churl:443 returned response code 403)
Code: 516. DB::Exception: Invalid authentication: SSL certificate authentication requires nonempty certificate's Common Name. (AUTHENTICATION_FAILED) (version 24.5.1.1763 (official build)).

Steps to reproduce

ch_client  = clickhouse_connect.get_client(
    host=ch_url,
    port=ch_port,
    username=ch_user,
    password=ch_psw,
    https_proxy=http_proxy,
    client_cert=cert_pth,
    client_cert_key=key_pth,
    server_host_name='my_ch_host',
    secure=True,
    verify=False
)

I'm able to connect successfully using the Python requests module with the same proxy and SSL certificate settings, which suggests that the configuration and certificate are correct.
The issue seems to occur specifically within the ClickHouse-Connect HTTP client's handling of SSL certificates when going through a proxy.

Thanks a lot

[genzgd]

What does your requests configuration look like?

Looking at the code one other possibility is that it expects a ca_cert (the Certificate Authority root certificate) as most private SSL authentication does, and that doesn't appear in your configuration. It's possible that your setup does not require one, but with your configuration the client would pass an empty CA cert to the urllib3 pool manager, which might be causing your problem. If so that's a bug I can fix.

[netchose]

this code works without ca_cert_file :

ch_url = 'https://my_ch_db_url'

proxies = {
	"http": 'http://my_http_proxy:3128',
	"https": 'http://my_http_proxy:3128',
}

session = requests.Session()
session.cert = (cert_pth, key_pth)
session.proxies = proxies

headers = {}
headers["X-ClickHouse-User"] = ch_user
headers["X-ClickHouse-Key"] = ch_psw

session.headers.update(headers)

response = session.post(url=ch_url,params={"query": 'SHOW DATABASES'})
print(response .text)

I would greatly appreciate your fix :)

[genzgd]

Can you check one more thing? It looks like requests will use REQUESTS_CA_BUNDLE or CURL_CA_BUNDLE if those environment variables are set. I don't think urllib3 recognizes those environment variables, but if one of them is set in your environment that would explain the difference.

[netchose]

None of them are set

'REQUESTS_CA_BUNDLE' in os.environ
False
'CURL_CA_BUNDLE' in os.environ
False

[genzgd]

Thanks, I'll try to reproduce (since I don't completely understand why it's happening) and fix over the next few days.

The potential bug I see is in this code in /driver/httpclient/__init__:

            if not self.http and (server_host_name or ca_cert or client_cert or not verify or https_proxy):
                options = {
                    'ca_cert': ca_cert,
                    'client_cert': client_cert,
                    'verify': verify,
                    'client_cert_key': client_cert_key
                }
                if server_host_name:
                    if verify:
                        options['assert_hostname'] = server_host_name
                    options['server_hostname'] = server_host_name
                self.http = get_pool_manager(https_proxy=https_proxy, **options)

In your case this will result in an empty ca_cert being sent to the pool manager. You might try just hacking out the ca_cert line in your local copy of clickhouse_connect to see if it helps. Otherwise again I'll have to try to reproduce to find what's wrong.

[netchose]

I didn't have any successes by changing this parameter

I put a breakpoint in pycharm at line 418 of the httpclient.py file: response = self.http.request(method, url, **kwargs)
I am available to transmit the values ​​of the variables if necessary.

I also modified my query to possibly better be able to compare certain values ​​if necessary:
r = requests.post(url, cert=(cert_pth, key_pth), proxies=proxies,
params={"query": f'SHOW TABLES from my_tdb'}, headers=headers)

[genzgd]

Thanks, I'll have to dig further into trying to reproduce. I haven't had time yet this week.

[netchose]

I will try to correct it myself by comparing the two https requests: any advice in this process will be welcome.

I'll let you know if I find anything

thanks

[genzgd]

That would be extremely helpful if you can find the difference. However I think that can be challenging over TLS, but Wireshark in theory will let you intercept those if you add the certificate in (somewhere) -- I haven't done this for years.

[genzgd]

Okay, there is a lot of confusion here. Until I looked at your requests example, I thought you were trying to do mutual TLS with the ClickHouse server (where each user is assigned a specific client certificate to authenticate with ClickHouse). However, since you are using a user name and password in your requests example, and you are using the X-ClickHouse-Key parameter to pass that, I realized you are not using mutual TLS with ClickHouse.

Instead, you're using your certificates only with the HTTPS proxy. The first thing to see is if you need a separate key at all. You can try just using your certificate with your proxy (without the key), by passing it as the ca_cert instead of the client_cert to clickhouse-connect. The existing proxy test for that looks like this:

os.environ['HTTPS_PROXY'] = f'{test_config.proxy_address}'
client = clickhouse_connect.get_client(host=test_config.host,
           port=test_config.port,
           username=test_config.username,
           password=test_config.password,
           ca_cert=cert_file)

(You can also test whether you need the key in your requests example by just setting session.cert = cert_pth)

However, if that doesn't work, and you actually need your own unique certificate and key to communicate with your HTTPS proxy, there would have to be some code changes to accommodate that scenario. I would probably need some more details about the specific proxy you are using to understand how private certificates were implemented, since I don't have one with that functionality to test with (and I don't see how it could be done without the addtitional CA certificate in any case).

[netchose]

Hi Geoff,
the network schema :

i will test your example
thanks

[genzgd]

Okay, that makes a little more sense. I'm guessing my example won't work, because clickhouse-connect assumes that the cert and key are for ClickHouse and not the reverse proxy (and that it is the first proxy that is providing the CA certificate to the second proxy).

The clickhouse-connect problem in your scenario is that the driver will set the X-ClickHouse-SSL-Certificate-Auth header to on to tell ClickHouse to use mutual TLS, which is not configured for your user, and the driver will not set X-ClickHouse-Key to the user password, so authentication will fail.

So to fix clickhouse-connect in this situation we need to somehow identify that the cert and key are for the proxy only. One way is to assume that if the password is set, then ClickHouse still expects user/password authentication, and set the headers accordingly even if a client certificate and key are configured as well. I'm trying to figure how to identify that configuration without breaking any existing users.

[genzgd]

I've pushed a new release, 0.7.15, that includes a hack to try to make this work. If you set the verify argument to proxy, the correct headers should be sent to ClickHouse while the certificate and key are still available for the reverse proxy.

[netchose]

hi,
the problem is corrected. thank you very much for the time spent, your involvement and your efficiency.
This feature is very important to me