a year ago
hi!
I'm trying to connect to my Postgres instance (built from the standard postgres-ssl:latest image, service id: ceb08892-498d-4f63-ac08-192c3995428a) with Fivetran and I'm getting the following error when I attempt to connect:
SSL error: Certificate [CN=[localhost](localhost)] is invalid with validity period from Tue Feb 20 00:11:48 GMT 2024 to Thu Mar 21 00:11:48 GMT 2024. Please reissue your certificate.
When the connection is attempted, my instance deploy log prints:
LOG: could not accept SSL connection: tlsv1 alert internal error
Additional info:
My SSLCERTDAYS environment variable is set to 1020
The original deployment date for this service was Tue Feb 20 at 00:11:48 GMT, so the cert appears to only have been valid for 30 days
I attempted redeploying the service today, but the certificate dates did not change.
Fivetran doesn't have an option to disable SSL on the connection, so unfortunately that's not a viable workaround.
Any ideas on where the issue is coming from or how I can get the certificate to issue to specify for the number of days specified in SSLCERTDAYS?
4 Replies
a year ago
Hey! The SSLCERTDAYS env var applies upon initial deploy when the cert is generated. I checked the cert on your instance and it was made valid for 820 days from your initial deploy so unsure where that date range is coming from in the error.. If you deploy a brand new Postgres and try to connect to it from Fivetran, does it work? I can try to do some testing with Fivetran <-> Postgres if not and see they don't like how we're generating them.
a year ago
Thanks Melissa,
I was able to deploy a new Postgres instance and connect it to Fivetran.
On the original instance, I also ran openssl s_client -starttls postgres -connect : and found that the root cert is in fact valid until 2026, but the localhost cert is the culprit, expiring in 30 days.
---
Certificate chain
0 s:CN=localhost
i:CN=root-ca
a:PKEY: rsaEncryption, 2048 (bit); sigalg: RSA-SHA256
v:NotBefore: Feb 20 00:11:48 2024 GMT; NotAfter: Mar 21 00:11:48 2024 GMT
1 s:CN=root-ca
i:CN=root-ca
a:PKEY: rsaEncryption, 2048 (bit); sigalg: RSA-SHA256
v:NotBefore: Feb 20 00:11:47 2024 GMT; NotAfter: May 20 00:11:47 2026 GMT
---Checking the init-ssl.sh code from the template, I see the SSLCERTDAYS argument is passed for the root certificate on line 14, but not to the Server/localhost on line 17/18:
https://github.com/railwayapp-templates/postgres-ssl/blob/main/init-ssl.sh
Would that be the cause?
a year ago
ah maybe so, good catch! let me test the theory and can cut a new image to fix if so.
a year ago
Alrighty, tested and confirmed your theory. I also published a new image so you can test/fix your other DB. Simply swap the source in your Postgres service settings to: [ghcr.io/railwayapp-templates/postgres-ssl:16](ghcr.io/railwayapp-templates/postgres-ssl:16). Then add a new service variable REGENERATE_CERTS=true. Once the certs have been regenerated, you can remove that env var or set it to false.
I'll push the changes to the rest of the image versions after I test a bit more.