question

LeonGraveland-8263 avatar image
2 Votes"
LeonGraveland-8263 asked ·

Updating Firewall rules has no effect for Azure Database for PostgreSQL

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:
9026-azureerror.png


9012-pgadmin.png


azure-database-postgresql
azureerror.png (52.8 KiB)
pgadmin.png (16.3 KiB)
· 6
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

In some cases there might be a delay upto five-minutes for the firewall changes to take effect.
Is your PostgreSQL database inside a VNET?

0 Votes 0 ·

Thanks for reaching out Kalyan. I'm having this issue for over 24 hours as of now. The database is not inside a VNET

0 Votes 0 ·

I have reached out internally to check this issue.
I will update this thread once I have an update.

0 Votes 0 ·

We are experiencing this issue as well since yesterday (at least).


0 Votes 0 ·

For me as well since yesterday. Even for Ip-addresses that already had acccess it is not a problem. New firewall rules are ignored as it seems

0 Votes 0 ·

Same here since a few days. Azure services are still able to access the DB, but connecting from remote does not work. Adding full IP range or disabling SSL and waiting did not help.


0 Votes 0 ·
KalyanChanumolu-MSFT avatar image
0 Votes"
KalyanChanumolu-MSFT answered ·

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.


· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It would have been really nice if MS would have sent out an alert to impacted customers about this. Only finding out now, in the middle of a launch... by googling.

We don't normally leave open holes in the production firewall... so it requires modification every time a prod issue is raised.

4 Votes 4 ·

Thanks for sorting this out @KalyanChanumolu-MSFT! Things are working as expected again so I've accepted your answer.

0 Votes 0 ·
FrancescoRamigni-0690 avatar image
0 Votes"
FrancescoRamigni-0690 answered ·

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 !

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DanielSmith-8383 avatar image
2 Votes"
DanielSmith-8383 answered ·

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"

· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I've the same. Just created a new Azure Postgres Flexible Server.
ANy updates MS?

0 Votes 0 ·

I'm facing the same issue, also with Azure Postgres Flexible Server.

I did the same as Daniel, waited for the 5 minutes after applying the rule and had restarted the server, but no luck making it work.

I'm using a database manager (SQLPro Studio) that actually connects to the database, but once trying with my NodeJS application using pg it does not work and I couldn't figure out any difference between both setups.

Microsoft: Any update or direction on the issue?

0 Votes 0 ·

I went through a similar exercise with the same result.

I created the rule, then waited 5 minutes, checked, and it wasn't working, then came back a day later and it still wasn't working (just in case the gerbils were still hard at it).

What's happening Microsoft?

0 Votes 0 ·
Felipe-8157 avatar image
0 Votes"
Felipe-8157 answered ·

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

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.