Forum Discussion

dpombal's avatar
dpombal
Icon for Post Partisan rankPost Partisan
7 months ago

Regenerate postgresql in AWS certificates

Hi all we have a customer with Power BI reports with data source in PostgreSQL in AWS.   Data Source PostgreSQL ODBC using psqlODBC 15.0.00     The problem is that every month we need to generat...
Need Help
BeaBF's avatar
BeaBF
Icon for Super User rankSuper User
7 months ago

dpombal Hi!

you are manually extracting the server certificate every month using openssl s_client and saving it as root.crt. This works as a workaround, but it is not how PostgreSQL + AWS + Power BI are meant to be configured, and that’s why it’s so time-consuming.

If the PostgreSQL database is hosted on AWS RDS or Aurora, AWS already provides long-lived Root CA certificates (valid for several years, often until 2030 or later).

You should:

  1. Download the official AWS RDS Root CA (for example rds-ca-rsa2048-g1.pem or the combined CA bundle).

  2. Store it in a stable location (e.g. C:\certs\aws-rds-root-ca.pem).

  3. Configure the PostgreSQL ODBC driver to use this CA:

    • SSL Mode: verify-full

    • SSL Root Cert: path to the AWS CA file

    • No need to specify SSL cert or key

  4. Make sure you connect using the DNS hostname, not an IP address.

This setup works for years and does not require monthly certificate regeneration. I use it on several Clients.

 

Let me know.

BBF


💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

dpombal's avatar
dpombal
Icon for Post Partisan rankPost Partisan
7 months ago

the RDS of our customer has this certificate  below and is not created by CA root of AWS.

 

Issuer
CN = ip-10-x-xx-xx.ec2.internal

 

Version:          3 (0x02)
Serial number:    27503456679274582721496808967741904672821079495 (0x04d14c32e2ed346af57617fcba33be254c6fedc7)
Algorithm ID:     SHA256withRSA
Validity
Not Before:     13/11/2025 10:14:22 (dd-mm-yyyy hh:mm:ss) (251113101422Z)
Not After:      11/11/2035 10:14:22 (dd-mm-yyyy hh:mm:ss) (351111101422Z)
Issuer
CN = ip-10-x-xx-xx.ec2.internal
Subject
CN = ip-10-x-xx-xx.ec2.internal
Public Key
Algorithm:      RSA
Length:         2048 bits
Modulus:        af:56:b3:e1:ff:75:c2:d0:e0:c7:a5:7b:15:7a:a2:a8:
                 40:47:b7:f7:4f:7b:0b:75:6a:d5:11:35:5e:eb:22:ce:
                 83:03:68:36:b1:b8:5c:00:8c:04:c5:4b:59:87:1f:b1:
                 a1:8b:d5:69:3f:ae:27:c4:f8:cf:b4:aa:ee:32:5d:95:
                 ef:e6:0a:3b:21:90:71:6d:89:96:83:05:3a:6e:87:34:
          
Exponent:       65537 (0x10001)
Certificate Signature
Algorithm:      SHA256withRSA
Signature:      ae:12:59
     

Extensions
basicConstraints :
   {}
subjectAltName :
   dns: ip-10-0x.x.x.internal