How to connect to a postgreSQL instance on AWS RDS

[EylesIT]

I have a postgreSQL database instance hosted on AWS RDS, and wish to connect to it from my local machine.

I have the postgreSQL tool called PGAdmin installed on my local machine. It can successfully connect to my postgreSQL database and execute queries.

In Power BI Desktop I have tried these steps:

• Get Data
• More...
• Database
• PostgreSQL database
• Connect

I then am prompted for the Server and Database, so I enter the RDS endpoint and database name, and click OK.

I get the error "Unable to connect"

I do not know why it is complaining about a certificate - the PGAdmin tool worked without needing any certificate.

There are other posts on this forum suggesting that I uncheck the "Enable encryption" option in the data source settings. I have tried that, but it did not work. In fact, after unchecking the "Enable encryption" option, Power BI went and changed it back to checked again whern I tried to connect. Very strange. See my description of the details about what happened to me in my reply to this post below.

https://community.fabric.microsoft.com/t5/Desktop/AWS-Postgres-The-remote-certificate-is-invalid/m-p...

I have also tried importing the AWS RDS certificate bundle (eu-west-2-bundle.p7b) by following the steps in this article. But it did not work, I still receive the same error "Unable to connect: the remote certificate is invalid according to the validation procedure"

https://support.globalsign.com/ssl/ssl-certificates-installation/import-and-export-certificate-micro...

If anyone has managed to connect to a postgreSQL database on AWS RDS, please let me know how you solved this issue.

[Anonymous · Accepted solution]

Hi, @EylesIT 

According to your description, you are trying to connect to postgreSQL using Power BI, but it has not been successful, you need to confirm that:

1.Whether NpgSQL is the latest version, and whether the GAC option is enabled during installation.

2.configure security groups: you may need to create a security group in AWS's VPC to allow internet access to the database port (default value is 5432).

You can try to use the Postgres connector to fetch data from the data source you deployed on AWS. You will then need to use the gateway to manage the data source refresh (turn on the option to use the gateway to handle cloud data sources) and install Remote Access Credentials (which is used to authenticate with AWS credentials) on the device where you installed the gateway and handled these operations.

Related Link: Merge or append on-premises and cloud data sources 

BTW, you can take refer to the following blog if it helps:

Connection of Power BI with RDS PostgreSQL on AWS from Scratch 

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum