Updating Firewall rules has no effect for Azure Database for PostgreSQL

Question

Dear Community,

However i've set up the firewall to allow all traffic, i'm unable to access the database over pgadmin. I've tried multiple user accounts.
Is this a current known issue?

Please see the firewall rules and the error i receive on pgadmin:

Answer 1

I have confirmed that this was indeed an issue and has been fixed now.
Please restart the server for the changes to take effect.

Here is a complete RCA.

Description:
New firewall rules does not take effect after the recent maintenance on Azure Databases for PostgreSQL

Impact:
Customers were not able to connect to their server after adding/updating firewall rules for Azure Database for PostgreSQL after the recent maintenance

Root cause:
We found a bug in handling the caching of Postgres hba conf file which was causing the cache to not update even after there were changes in the hba conf file. We suspect that there are some corner cases where the directory change notification on the file share (where the hba conf file resides) fails and doesn’t update the cache. This was a new enhancement that was introduced with recent updates and we are currently debugging this issue further but as a mitigation we have disable this feature.

Mitigation & solution:
The caching changes were controlled with a feature switch (a configuration setting). To mitigate this issue we have disabled this feature switch and restart of the server will get these changes into effect.

Answer 2

Hello.

today trying to test out Flexible servers.

We have the Network Connections set to All Public access allowed.

I am seeing the same no pg_hba.conf error.. .

i have tried adding in Firewall rules (which shouldnt do anything since its set to PUBLIC) and i have restarted the server and it doesnt seem to make a difference (yes - waited past 5 minutes after rules application and i have done the restart).

I am surprised to have this error with Public Access enabled, perhaps im missing something?

Error message:

Could not initialize database (db config: {postgresql jdbc:postgresql://xxxxxxxx.postgres.database.azure.com:5432/xxxxxx}): pq: no pg_hba.conf entry for host "<client external Egress IP>", user "ouruser", database "oudbname"

Answer 3

The solution in my case was to activate SSL in the connection. As I'm programmatically connecting using pg library in a NodeJS application this is the code that fixed the issue:

const client = new pg.Client({
    user: "admin",
    password: "guest",
    database: "Employees",
    port: 5432,
    host: "localhost",
    ssl: true
}); 
client.connect();

Source: https://stackoverflow.com/questions/25000183/node-js-postgresql-error-no-pg-hba-conf-entry-for-host

Answer 4

Same here, all external connections failing, old and new entries in the firewall rules (but not the internal ones from other Azure services), until we re-started the PSQL service. So I guess all related to this issue.
Again, it would be better if we receive formal Microsoft communication via the usual channels, rather than googling !