Reach TimescaleDB with Hasura API: "CA certificate and CA private key do not match" when using self-signed server certificate / private key

I follow the guide at timescaleDB for APIs with Certificate authentication --> 2. Generate keys and certificates for nodes:

For an SSL connection, you need four environment variables:

1. SSL Root Certificate
2. SSL Certificate (for the node)
3. SSL Key (for the node)
4. SSL Password (for the node)

As to 1.), you get it from timescale when starting a database as a text or as a download.

As to 2.), you seem to be able to create it yourself as a self-signed certificate with:

openssl req -out server.csr -new -newkey rsa:2048 -nodes \
-keyout server.key

I filled "country" and skipped the rest of the entries only with "." (this was suggested for skipping). I also filled the "challenge password". Not sure how much relevant it is to fill this and whether the password is 4.) in the list above or not.

As to 3.), you first need to change your openssl.cnf which is not mentioned in the timescale guide. That is done by searching with whereis openssl.cnf, or in my case, the location was shown in the terminal output when the parameters were still wrong in there, which showed /usr/lib/ssl/openssl.cnf as the location. Change the config with sudo nano /usr/lib/ssl/openssl.cnf so that the private key “server.key” and the root certificate “ca.pem” are found. After this, run the command of the guide:

openssl ca -extensions v3_intermediate_ca -days 3650 -notext \
-md sha256 -in server.csr -out server.crt

but then, you get the error:

Using configuration from /usr/lib/ssl/openssl.cnf
CA certificate and CA private key do not match
140718372717888:error:0B080074:x509 certificate routines:X509_check_private_key:key values mismatch:../crypto/x509/x509_cmp.c:299:

The script still creates server.key and server.csr, but the needed server.crt cannot be created.

And only How to convert .csr to .cer (or whatever usable on Windows) seems to be able to help me out to get the server.crt with:

openssl x509 -req -in server.csr -signkey server.key -out server.crt

Entering this in the SSL connection settings at Hasura:

But then, when trying to connect to my database at timescale using Hasura, I get the error Missing Env Var:

Adding data source failed
Inconsistent object: Missing Env Var: -----BEGIN CERTIFICATE----- MIICoTCCAYkCFAHx6x6kVHJ023/ui0aLoWLqA8RyMA0GCSqGSIb3DQEBCwUAMA0x CzAJBgNVBAYTAkRFMB4XDTIxMDkyMTE4MDQzNVoXDTIxMTAyMTE4MDQzNVowDTEL MAkGA1UEBhMCREUwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDWGxsz
…
g98irlkrtDn17jIzmZMjlJEQ75P9L78lJMT2eYCFRqVoCm+7x5IUS18UlnGUtwR4 BuIZOg4= -----END CERTIFICATE-----

I guess that this is just a follow-up problem as the CA certificate and the CA private key obviously do not match? Therefore, the question is mainly about CA certificate and CA private key do not match, instead.

Answer timescale:

While we provide the CA root because the SSL Certs for each service are internally signed, so this will allow you to create trust, but we do not provide the private keys or SSL passwords for them.

The documentation you linked to is for creating certificate auth between nodes in an on-premise multi-node environment. Timescale cloud does not allow for certificate auth, so they wouldn't be relevant here, and you wouldn't be able to combine certificates you create with the CA you downloaded from the cloud portal.

As a workaround, you can also drop the SSL encryption since it is only optional:

simply providing the entire connection string (as copied from the Service URI field in the Timescale Cloud Portal) was sufficient to connect.

I tested this, it is true, you can run it without SSL although SSL is prepared to be used. I used the root certificate to SSL-connect with psql or pgAdmin to the PostgreSQL timescaleDB backend, and they only asked for the SSL root certificate --> 1.) above, and not for node CA certificate and node CA private key --> 2.) - 4.) above.

Connecting with just the SSL root certificate also worked with node-postgres / react-postgres / Express / React App, see:

• How to create a pool object in javascript that can connect to a database backend using SSL?
• TypeError: Cannot read property 'rows' of undefined
• "Cannot PUT /mytable" when running UPDATE SQL command from react-postgres "React App" localhost:MYPORT on PostgreSQL / node.js backend

Yet, the Hasura Application Programming Interface settings ask four SSL connection environment variables of the list at the top, how can I get them so that I can connect to Timescale?