PostgreSQL error in Fabric Pipelines – No pg_hba.conf Entry for Host

I came across a bit of a weird error when I was trying to connect to a PostGreSQL database (hosted as an Azure DB for PostGreSQL), where “xx.xx.xx.xxx” is some IP address:

No pg_hba.conf entry for host “xx.xx.xx.xxx”

Some context to get us started. I’m using Pipelines in Microsoft Fabric to copy data from an Azure DB for PostgreSQL database to a Fabric warehouse. I connected to the development environment, copied the tables and everything worked just fine. A few days later, I needed to do the same for the production environment. I duplicated my development pipeline (I haven’t implemented CI/CD yet and this is for a proof-of-concept. Things need to go fast :)). Unfortunately, you cannot duplicate a connection in Fabric (duplicate and then change the connection string to production, and update the username and password), so I had to create a new connection in my Copy activity.

When you click on More in the connection dropdown, you are taken to a wizard that helps you create a new connection. You can either reuse an existing connection, or create a new one.

I searched for “postgres” and was presented with two options:

I chose the “Azure Database for PostgreSQL” option and filled out the connection details:

The connection is created, but when testing it fails with the stated error.

However, I can connect perfectly fine to the PostgreSQL database with the same connection info using a tool like Azure Data Studio. What’s going on? It isn’t the database firewall, as it has a public endpoint and Azure services can connect to it.

After searching a bit, people suggest the error is thrown when there’s something misconfigured with the encryption properties of the connection. This is what I see when opening the connection to the dev database (which worked) in the Manage Connections and Gateways section in Fabric :

However, when I open the new connection to the prod database, this option is missing:

That seems odd. And then I saw the issue. The connection type for production is “AzurePostgreSQL”, while the connection type for the working dev connection is “PostgreSQL”.

When creating the new connection, I should have chosen the left option!

This seems rather counterintuitive, I know. When you choose this option, you need to specify the same connection details and there’s no encryption option there either:

However, when you create this type of connection, it seems the encryption option is added by default. When this connection is selected in the Copy activity, you actually get a dropdown where you can select the type of PostGreSQL database:

So to make the connection work, you need to complete ignore the Azure Database option when you create the connection. You can specify this after the connection is created in your activity.

For people who land on this blog post searching for the error, but you are not using Fabric Pipelines (or ADF or something similar), check the encryption/SSL settings of the connection or database.