Steps covering new Postgres-SSL cert creation and import into Windows for connecting to PowerBI.

powerbi-postgres-cert-steps.md

Essentially, the problem is that the default SSL cert for Postgres /etc/ssl/certs/ssl-cert-snakeoil.pem does not have enough information when copied over to the Guest machine running PowerBI which is trying to connect to Postgres, specifically, the Common Name field.

There are various posts out there about this e.g. https://community.powerbi.com/t5/Desktop/PostgreSQL-powerbi-desktop-connection-error/m-p/90689. This one was good to point me at npgsql 3.1.8 specifically https://blogs.msdn.microsoft.com/chmitch/2018/06/04/complete-guide-to-setting-up-power-bi-connecting-to-postgres-w-refresh-enabled/

1. create a new SSL certificate (roughly) following these guides: https://uit.stanford.edu/service/ssl/selfsigned + https://www.postgresql.org/docs/9.1/static/ssl-tcp.html be sure to set the Common Name field value to the IP address of the server which is hosting the Postgres instance you're connecting to from PowerBI. -days 3650 will set the expiration in 10 years, default is 30 days if not specified.

Exact steps below:

openssl req -days 3650 -new -text -out server.req
openssl rsa -in privkey.pem -out server.key
rm privkey.pem
openssl req -days 3650 -x509 -in server.req -text -key server.key -out server.crt
chmod og-rwx server.key
chown root:ssl-cert /etc/ssl/private/server.key
chmod 640 /etc/ssl/private/server.key
cp /etc/ssl/private/server.crt /etc/ssl/certs/

skip to #5

2. Change the appropriate group of the private key chown root:ssl-cert /etc/ssl/private/server.key.
3. Change the approprite permissions of the private key chmod 640 /etc/ssl/private/server.key.
4. Copy the cert to where Postgres expects it: ssl_cert_file = '/etc/ssl/certs/server.crt'.

5. Restart the server service postgresql restart.

6. Copy the certificate over to the (Windows) machine running PowerBI.

7. Steps below to add certificate:

   • Administrators is the minimum group membership required to complete this procedure.
   • To add certificates to the Trusted Root Certification Authorities store for a local computer.
   • Click Start, click Start Search, type mmc, and then press ENTER.
   • On the File menu, click Add/Remove Snap-in.
   • Under Available snap-ins, click Certificates, and then click Add.
   • Under This snap-in will always manage certificates for, click Computer account, and then click Next.
   • Click Local computer, and click Finish.
   • If you have no more snap-ins to add to the console, click OK.
   • In the console tree, double-click Certificates.
   • Right-click the Trusted Root Certification Authorities store.
   • Click Import to import the certificates and follow the steps in the Certificate Import Wizard.

8. Retry your connection in PowerBI - hopefully it works!