Below are the steps that are taken by us for connecting our production PostgreSQL with PowerBI
Steps to be followed according to the documentation:- 1. Install Npgsql which allows .NET access to PostgresSQL. I installed it using the Package Manager Console in Visual Studio; detailed instructions are here: https://www.nuget.org/packages/Npgsql/
You need to connect over SSL to AWS which isn't setup by default (on my Windows 10 machine anyway)
5. Test access from Power BI desktop using the PostgreSQL connector and it should work
Problems encountered by us:
1) The latest version of Npgsql doesn't work as intended and nothing seemingly happens when you install it. After installing it, there is no change in machine config and powerBI remains in the same state. The only working version that worked for us is (3.1.8) version.
2) AWS Postgres URL is of format: postgres://userID:password@serverName:Port/dbName
After installing npgsql(3.1.8) for the powerBI desktop we were prompted for server and dbname. But as soon we try to connect we get the error as attached in the mail.
3) This is apparently a certificate issue but after following all the instructions found in powerBI community we were unable to make it connect to Postgres.