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 generate root.crt certificates using this command and storing them in

C:\Users\xxxxx\AppData\Roaming\postgresql, there are 2 environments DEVELOPMENT and PRODUCTION

 

 

{
openssl s_client -starttls postgres -showcerts -connect ec2-xx.compute.amazonaws.com:5432 </dev/null; o
openssl s_client -starttls postgres -showcerts -connect ec2-xx.compute.amazonaws.com:5432 </dev/null; }
2>/dev/null | sed -ne "/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p"

 

 

 

This is very time consuming and we should need some kind of  certificate for several years, not requiring generating root.crt certificates...every month.

 

Regards

Need Help

2 Replies

  • 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