"The remote certificate is invalid according to the validation procedure" while adding a Postgres data source to Power BI

 When adding a Postgres data source to Power BI I got the following error:

This issue stems from the fact that the SSL certificate of the Postgres server is not defined as a trusted certificate on the Windows desktop (where the PowerBI Desktop is running).

In order to do solve this we need to get the certificate from the Postgres server and import it into the certificate store.

First, let's go to the Postgres configuration on the server and see what certificates are used.

The Postgres configuration file will normally be somewhere under the /etc/postgresql directory. On my server it was under /etc/postgresql/13/main/postgresql.conf.

Since we have an issue with SSL it definitely means that the Postgres is configured to work with SSL. So look for "ssl = on" directive in the config file and looks for what's near it:

The ssl certificates can be kept in different forms. On Windows a very wide-spread format is pfx. In order to create a pfx file from the ssl_cert_file and ssl_key file we may use the openssl package. If not installed - please install it on the Postgres machine.

Once you have it use the following:

openssl pkcs12 -export -out pgslqDB.pfx -inkey /etc/ssl/private/ssl-cert-snakeoil.key -in /etc/ssl/certs/ssl-cert-snakeoil.pem

Then copy the pgsqlDB.pfx file to your Windows desktop. You can do so for example with the scp utility.

Once done you will need to import the .pfx file into your Trusted Root Certificates. You can do so by right clicking on the .pfx file and selecting "Install PFX" from the menu. The please make sure you import it into the "Trusted Root Certificates" store.

et voila.