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 https://whatsmychaincert.com/ 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”.