Help setting up PostgreSQL root.crt for sslmode=verify-full

Hello Timescale community!

I followed the instructions here to construct a certificate bundle for use with sslmode=verify-full: Timescale Documentation | Connect with a stricter SSL mode

openssl s_client -showcerts -partial_chain -starttls postgres \
             -connect $SERVICE_URL_WITH_PORT < /dev/null 2>/dev/null | \
             awk '/BEGIN CERTIFICATE/,/END CERTIFICATE/{ print }' > bundle.crt

However, the next instruction to generate a full certificate chain using doesn’t work. The error is:

An error occurred when building the chain for this certificate.  The certificate might lack necessary metadata or its certificate authority might be malfunctioning.  Error message with details elided:

* The chain contains an untrusted certificate without standard CA issuer information (subject = "ST=service, O=..., CN=..."; issuer = "CN=... Project CA"; error code = 20)

Presumably this happens because my service URL is not publicly accessible.

I see that there is a CA Certificate file ca.pem file available for download in the Managed Timescale portal “overview” page for my managed database. I know only a tiny bit about OpenSSL and certificates (I think these are X.509 certs?), but I figured that maybe this was the missing piece.

Now I have this bundle.crt and ca.pem file. Are these actually the pieces I need to correctly construct my ~/.postgresql/root.crt file?

I took a wild guess by converting the ca.pem file to ca.crt with openssl x509 -outform der -in ca.pem -out ca.crt and then concatenating my two CRT files together with cat ca.crt bundle.crt > root.crt but that didn’t work when I tried to connect with psql sslmode=verify-full, saying “certificate verify failed”.

Hi Gregory, welcome! It looks like nobody from community could help you here yet. As you’re a customer, or have any urgency, please, mail [email protected] to guarantee someone from the support can help you.

The official support has a different pipeline to prioritize their work and they don’t look to the Timescale Community forum that often.

Thank you, I will contact support.

1 Like