- Install Npgsql 4.0.10.0 or earlier (download .msi link here) with GAC enabled.
- Add SSL Certificate used by PostgreSQL to Windows Trusted Root Certification; if you are on AWS you can download the bundle here, if not you'll have to bring your own or find the correct one.
Out of the box PowerBI is not able to connect to a PostgreSQL database; this limitation applies also to the On-premises data gateway that needs to be installed in order to connect to the RDS from outside of a VPC (more info here). Assuming that the host on which the two applications are running have network connectivity with the database there are a few steps needed in order to solve the following issues:
- PostgreSQL Data Provider is not installed by default and so PowerBI cannot connect to the database.
- SSL Certificate used by the database is not trusted by Windows, this causes TLS errors upon connection.
- VM running Windows (tested on Windows Server 2019 Datecenter) in a subnet that has connectivity with the RDS Cluster if the DB is in a private VPC.
- PowerBI Desktop (PRO license needed to publish reports).
- On-premises data gateway (this is needed only if your database is in a private VPC and you plan on publishing your reports to a shared Workspace).
While this is not immediately clear from the error message returned by PowerBI, the solution is spelled loud and clear in the diagnostic logs of the On-premises data gateway which encourage the us to install Npgsql version 4.0.10.0 or earlier.
Npgsql is a an open source ADO.NET Data Provider for PostgreSQL that allows programs written in C#, Visual Basic, F# to access PostgreSQL database servers.
The .msi installer can be downloaded from the GitHub release page of the package, make sure to respect the <= 4.0.10.0 version requirement as installing a more recent version will not work out of the box as it targets a C# version not compatible with PowerBI/data gateway. While installing also make sure to mark for installation the optional GAC extension.
In this case the error message provided is a bit more helpful as it states that PowerBI cannot trust that the SSL certificate used is legit and hence refuses to establish a secure TLS connection with the database; this already tells that the host is able to reach the DB but also that PowerBI is finally able to talk with PostgreSQL.
While it would be temping to disable TLS or certificate validation altogheter, the proper way is to allow Windows to independently verify the certificate by adding the root CA to Windows. If your PostgreSQL database is on RDS you can simply download the bundle certificate from AWS here, in case your database is hosted elsewhere you will either have to find the certificate used or bring your own in case you manage it.
Once you have downloaded the TLS certificate on the host open the Windows tray and type mmc to open the Trusted Root Certification settings. In the program go to File > Add/Remove Snap-in and then move the Certificate item from the Available snap-ins column on the left to the Selected snap-ins on the right and when prompted choose Computer account and Local computer and then Finish. When back to the Console Root tree, right-click the Trusted Root Certification Authorities item, select All Tasks > Import... and then follow the steps in the Certificate Import Wizad.
To be extra cautious restart both PowerBI and the On-premises data gateway before finally connecting to your PostgreSQL database.