How to set up signed SSL Root and Leaf certificates on a PostgreSQL 11 database on Ubuntu and Windows 10 Power BI plus PostgreSQL?

Question

I have Power BI and PostgreSQL 11 installed on Windows 10 and my primary PostgreSQL 11 database on Ubuntu 18.10 Linux. I edited the postgresql.conf and pg_hba.conf files correctly to the IP Adresses my Windows 10 computer uses with PostgreSQL 11 and Power BI to connect to my PostgreSQL 11 Ubuntu database. However, I got errors in Power BI while I could manually connect via command line.

After troubleshooting with Microsoft Support, I was told:

This indicates that the Postgresql server does not have its certificates configured and therefore cant connect to PowerBI. To allow the client to verify the identity of the server, place a root certificate on the client and a leaf certificate signed by the root certificate on the server. To allow the server to verify the identity of the client, place a root certificate on the server and a leaf certificate signed by the root certificate on the client. One or more intermediate certificates (usually stored with the leaf certificate) may be used.

However, I am confused by the tutorials at

• https://www.postgresql.org/docs/current/libpq-ssl.html and
• https://www.gab.lc/articles/postgresql_with_ssl/.

Error messages

The error messages I received were:

"ConnectionId":"bcfc620b-0fda-4db9-bda6-d9ac4aabcc4c","Exception":"Exception:\r\nExceptionType: System.Security.Authentication.AuthenticationException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\r\nMessage: The remote certificate is invalid according to the validation procedure.\r\nStackTrace:\n at System.Net.Security.SslState.StartSendAuthResetSignal(ProtocolToken message, AsyncProtocolRequest asyncRequest, Exception exception)\r\n at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)\r\n at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)\r\n at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)\r\n at System.Net.Security.SslState.CheckCompletionBef

and

Details: "An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.'"

and

Details: "Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remost host.)"

Questions

I can't tell what steps need to be done on my Ubuntu PC and what steps need to be done on my Windows PC. I don't know if special steps are needed to configure Power BI with the certificates. How do I set up my bidirectional certificates?

Can someone offer a very detailed tutorial, tell me 'how' to read a tutorial, or explain the steps in an answer please?

Answer 1

I believe that PowerBI uses npgsql to connect, which defaults to the behavior that libpq/psql gives you under 'PGSSLMODE=verify-full'. Can you connect to your server from Windows psql if you use 'PGSSLMODE=verify-full'?

The tutorial you linked assumes that you use the same machine for the certificate authority and for the database server, which will probably work for you. But it also assumes the client machine is debian/ubuntu, not Windows. So you will have do everything before "Configure the client" on your ubuntu machine. But you probably already did that, based on the error message you are getting.

And you will have to do everything after "Configure the client" on your Windows machine, except you have to do it using Windows mechanisms, not linux mechanisms. How to do that is a question of how your infrastructure is set up--you need some way to copy the file ca-cert.pem from the cert authority to the Windows machine, but that mechanism could be scp (if you have that installed on Windows, perhaps via cygwin) or a CIFS mount, or just copy the text of the .pem files out of putty and paste it into notepad, or use a thumbdrive.

Note that the tutorial is for using certs in both directions--for the client to verify the identity of the server, and for the server to verify the client too. But it is rare for people to actually use client certs--they usually use server certs only. If you are not using client certs, then you only need to install one cert on the client, which is the "ca-cert.pem" file (which should get named "root.crt" when installed on the client). The other two are only needed for client certs. And on Windows, the path it needs to be installed in is %APPDATA%\postgresql\root.crt, rather than ~/.postgresql/root.crt.

Answer 2

A humble advice from a beginner.

I recommend starting with pgAdmin on Windows to understand whether you have a basic mistake here or not.

Install the recent version of pgAdmin.

Then create a new server (= a new connection):

and load your certificates step by step. The ca-cert.pem is probably the "Root certificate". I used Require instead of Verify-Full here.

If that works, it should also work on the Power BI frontend - with some patience. Try imitating what you are doing in pgAdmin.

With some luck, it is also enough doing this in pgAdmin on Windows to unlock Power BI on Windows without having to load certificates in Power BI. At least on Linux, I noticed that you do not need to verify the connection with certificates every time you query PostgreSQL. Verification seems only needed after a longer time. It is the the same server and what you change in pgAdmin will also be available in Power BI, perhaps the certificates are loaded Windows-internally (system-wide).