SSL certificates for connection

[dzarlax]

Hi,
I'm trying to connect to Yandex.Cloud MDB Clickhouse from Metabase, but gets an error:

ru.yandex.clickhouse.except.ClickHouseUnknownException: ClickHouse exception, code: 1002, host: {host}, port: 8443; sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

In additional parameters I add:

ssl=true&sslrootсert=/usr/local/share/ca-certificates/Yandex/YandexInternalRootCA.crt

[dzarlax]

Here is an error on clickhouse side:

{ "component": "ServerErrorHandler", "hostname": "{host}", "message": "Poco::Exception. Code: 1000, e.code() = 0, e.displayText() = SSL Exception: error:14037416:SSL routines:ACCEPT_SR_KEY_EXCH:sslv3 alert certificate unknown (version 19.14.7.15 (official build)", "severity": "Error", "thread": "50", "query_id": "" }

[dzarlax]

I've found where this error was fixed - ClickHouse/clickhouse-java#365
Maybe JDBC should be updated. @enqueue

[enqueue]

Hi @dzarlax, good job identifying the cause of the issue! 👏 In trying to make the driver work with Metabase 0.34, we will also be updating JDBC driver. Hopefully this will solve your SSL issue. If you want, you can try version 0.7-BETA. It is BETA because

1. the code uses proprietary JDBC driver code
2. the driver does not pass all MetaBase integration tests, yet.

[enqueue]

@dzarlax did you try to connect using driver version 0.7.0? I personally do not have any experience setting up SSL connection between Metabase and ClickHouse.

[dzarlax]

Finally YES, thanks a lot!!

[vmoshikov]

@dzarlax I invite you to share your experience with the community. Getting connection errors in Docker and local Metabase

[dzarlax]

@dzarlax I invite you to share your experience with the community. Getting connection errors in Docker and local Metabase

How can I help?

[vmoshikov]

@dzarlax What are the use of the SSL parameters to connect ClickHouse cluster in Yandex.Cloud?
You use .pem or .crt file?

[JeroniMan]

Hi, I'm trying to connect to Yandex.Cloud MDB Clickhouse from Metabase, but gets an error:

ru.yandex.clickhouse.except.ClickHouseUnknownException: ClickHouse exception, code: 1002, host: {host}, port: 8443; sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

In additional parameters I add:

ssl=true&sslrootсert=/usr/local/share/ca-certificates/Yandex/YandexInternalRootCA.crt

Hi.
I have the same problem, thhx for prompt, but be careful in your parameter line - the seventeenth character ('c') is Cyrillic.

[JeroniMan]

@dzarlax What are the use of the SSL parameters to connect ClickHouse cluster in Yandex.Cloud? You use .pem or .crt file?

If still relevant, the certificate can be taken from the documentation (https://storage.yandexcloud.net/cloud-certs/CA.pem), and the parameters were specified above:
ssl=true&sslrootcert=/usr/local/share/ca-certificates/Yandex/CA.pem

[vmoshikov]

All perfectly. I threw the certificate into docker and registered the parameters
ssl=true&sslrootcert=/usr/local/share/ca-certificates/Yandex/YandexInternalRootCA.crt

[egorperesada]

All perfectly. I threw the certificate into docker and registered the parameters ssl=true&sslrootcert=/usr/local/share/ca-certificates/Yandex/YandexInternalRootCA.crt

Where can I put this parameters? I don't see any "additional parameters" or etc to add them in metabase 0.43.3 and driver 0.8.1

Ω

[kartaris]

All perfectly. I threw the certificate into docker and registered the parameters ssl=true&sslrootcert=/usr/local/share/ca-certificates/Yandex/YandexInternalRootCA.crt

Where can I put this parameters? I don't see any "additional parameters" or etc to add them in metabase 0.43.3 and driver 0.8.1 Ω

Still have no idea how to configure ClickHouse database with setting an SSL certificate through UI =(
There is no such option.

[slvrtrn]

@kartaris
can you please try this:

My tests show no issues connecting to a secured ClickHouse instance with Basic TLS. I also verified that it works from the UI using the latest release (1.0.1).

I run Metabase as a Docker container, and a sample docker-compose might look like this:

  metabase:
    image: metabase/metabase:v0.45.1
    container_name: metabase-with-clickhouse-driver
    ports:
      - '3000:3000'
    volumes:
      - '/absolute/path/to/clickhouse.metabase-driver.jar:/plugins/clickhouse.jar'
      - '/absolute/path/to/ca.crt:/certs/ca.crt'

So, the server cert is mounted as /certs/ca.crt, so I'll just put

sslrootcert=/certs/ca.crt

In the additional options input. Make sure that you have "Use secure connection (SSL)" ticked as well.

[kartaris]

@kartaris can you please try this:

My tests show no issues connecting to a secured ClickHouse instance with Basic TLS. I also verified that it works from the UI using the latest release (1.0.1).

I run Metabase as a Docker container, and a sample config might look like this:

  metabase:
    image: metabase/metabase:v0.45.1
    container_name: metabase-with-clickhouse-driver
    ports:
      - '3000:3000'
    volumes:
      - '../../../resources/modules/clickhouse.metabase-driver.jar:/plugins/clickhouse.jar'
      - './.docker/clickhouse/single_node_tls/certificates/ca.crt:/certs/ca.crt'

So, the server cert is mounted as /certs/ca.crt, so I'll just put

sslrootcert=/certs/ca.crt

In the additional options input. Make sure that you have "Use secure connection (SSL)" ticked as well.

Yep. I found the way to do it using API(by adding the ssl=true&sslrootcert=/usr/local/share/ca-certificates/Yandex/YandexInternalRootCA.crt to the additional-options in POST request). But there is no such option to set the path to the certificate through UI even if "Use secure connection (SSL)" ticked.

And as an example how it's done in PostgreSQL. User may set the path to the certificate and even upload it through API.

[slvrtrn]

@kartaris what plugin version do you use?
Additional JDBC options (and advanced connection settings) were restored in 0.8.3 IIRC.

[kartaris]

@kartaris what plugin version do you use?
Additional JDBC options (and advanced connection settings) were restored in 0.8.3 IIRC.

Sorry, my bad. I were using 0.8.1 version. Updated to 1.0.1 and now I see it can be done through UI. But it's not as clear as in PostgreSQL plugin

[slvrtrn]

@kartaris, for sure, I see that we should go Postgres way here.

I've added an issue to the backlog: #136

If you have time, please feel free to contribute.