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/
- 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.