DirectQuery with PostgreSQL Connector Failing Due to Certificate Validation Error

[JoshuaOD]

I'm working with a PostgreSQL database to import data into Power BI. Currently, I'm using an ODBC connector which defaults to importing entire datasets from the database. However, for my current project, I need to use the DirectQuery option.

When attempting to use the PostgreSQL connector provided by Power BI, I consistently encounter the following error:

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

I have attached screenshots showing the error message and the ODBC configuration I am using.

Does anyone have any suggestions on how to resolve this issue? My goal is to use DirectQuery with PostgreSQL instead of relying on ODBC connectors which default to Import Mode.

Default ODBCFailing Postgres Connector

Thank you in advance for your help!

[rajendraongole1 · Accepted solution]

Hi @JoshuaOD - Make sure you have the latest version of the Npgsql driver installed, as it supports SSL connections. You can download it from Npgsql official website.

or 

Releases · npgsql/npgsql (github.com) direct link for connector

Install the SSL certificate used by your PostgreSQL server on your client machine where Power BI is running. This can often be done by importing the certificate into the system's trusted certificate store.

Solved: Connector Postgre SQL DATABASE - Microsoft Fabric Community

Ensure postgresql.config file on your PostgreSQL server to enable SSL.

ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

Hope it helps.