Postgre "Remote certificate invalid according to the validation procedure"

[DmitryKo]

I am trying to connect to a postgresql database using powerbi desktop and getting the message outlined in the title.

I have received and imported root certificate into local user's trusted root certificates store. I have set certificate revocation check to "none". Neither did help and I'm still getting the aforementioned error message.
I've enabled trace logging in hopes of finding information about certificate being presented by posgresql - but it's not being logged anywhere, rendering this tracing useless.
Similar threads result in suggestions to install third-party packages for postgre. Then, why there's even a built-in option for PosgreSQL connection when it doesn't work from the box and requires third-party stuff?

Questions:
- why is option to never validate certificates being ignored by the app?

- why importing root ca's certificate referring to the postgre certificate didn't solve the issue about supposedly untrusted certificate?

- is there any way to get information about specific certificate, through trace logging or otherwise?

[Pilares]

Hi Dmitry, I've tried the proposed solutions, including using a qualified aws domain and physical ip address. The standard Postgres adapter in Power BI is still not working. There are no issues when connecting via database management tools, like PGAdmin or DBeaver, or other BI tools, like Qlik Sense, to the AWS Postgres database without using 3rd party ODBC tools. I also don't understand why a PBI gateway is necessary while the AWS Postgres database is running in the cloud. So, I think @Microsoft is probably doing this for a competitive reason, since they also offering an Azure Postgres solution, which seems to be working without any issues.

Regards Frank van Zuilen

[DmitryKo]

Hello Frank.

Yes it looks that you are correct here and Microsoft is simply not interested in customers who take security seriously (thus do not consider cloud at all).

Microsoft thinks that by not resolving postgre-related issues in their on premises PBI product they are acting smart and pushing their customers into PBI cloud where these issues do not exist.

In reality, they are pushing their customers towards alternative vendors such as Visiology because no serious enterprise would ever pay for a product where a declared product feature (such as out of the box postgre connectivity) has been essentially broken for YEARS without vendor response.

[Anonymous]

Hi @DmitryKo 

I think the option used in the Security parameters is for certificate's revocation date validation , not for certificate authority's validation. More details here.

According to the error message, it seems that something is missing in the Certificate Authority (CA) store. Maybe, intermediate CAs...? To troubleshoot PostgreSQL SSL issue, I use an ODBC PostgreSQL client: psqlODBC. It enables me to choose the SSL mode I want and then, to check if the connection error comes from the SSL check or not:

Hope it will help for your troubleshooting.

Have an amazing day!

[DmitryKo]

I do not understand why we're talking about ODBC connectivity. The tool - Power BI Desktop - lists PostgreSQL as a native connector. So I'm connecting there using that native connector.

Is it supposed to be working out of the box? Because if it's not, then I don't understand the reason why it's included as a native connector (and why 99 out of 100 questions related to connecting to postgresql result in advices about installing third-party tools and switching to ODBC).

I am 100% sure that the certificate used by the Postgresql server comes from the correct chain, and root certificate of that chain is installed both in trusted root certificates store for the current user (so that I can do manual checks, import and design in PBI Desktop) and local computer (so it works correctly when machine connects there to import data on a schedule). PBI Desktop is not the only client connecting there, and all other clients are able to "accept" the certificate by configuring appropriate root certificate as a trusted root - all other but PBI Desktop.

PS. So far the only working configuration is the configuration that uses ODBC, requires installation of third-party postgre driver for ODBC, *and* requires using configuration option in ODBC connection string that forces to just accept all certificates without validating them. This is NOT a solution.

[Anonymous]

Hi @DmitryKo 

To connect without SSL, you should try to uncheck "Encrypt the connection" in the data source properties (cf. screenshot below)

Keep us informed.

Have an amazing day!

[DmitryKo]

I do not want to connect without connection encryption. I want to connect using encryption.

However, I want the tool to either obey certificate trust/validity as set by the operation system (e.g. if postgre's certificate is from the chain that originates from the root certificate that is in the Trusted Root store, it should be trusted), or to obey "certificate checks: none" setting in the client. Currently, it does neither.