No connection to postgres database with IP on the whitelist

piotrek 21 Reputation points
2020-06-02T13:25:32.417+00:00

I noticed access to the Postgres DB is temporarily unavailable (few hours a day)
My IP address is on the whitelist. And sometimes I can connect to the DB but in some other time I cannot with the message

psql: FATAL: no pg_hba.conf entry for host "my_IP_address", user "my_user", database "my_db", SSL on

I don't make any changes neither to the DB nor to my local machine.

How can I investigate in on the Azure side?

Additionally, I can say that we have another Postgres DB in Azure where connection always works.

Azure Database for PostgreSQL

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Toby HERSEY 16 Reputation points
    2020-06-08T06:28:49.597+00:00

    Hi Guys,

    I contacted Azure for support and they told me that i would need to restart the pg server, took me this long to arrange due to it being a prod instance! Just restarted it and i can now connect to the pg instance oin the same IP address i was using before so whitelist is now working as expect.

    thanks
    Toby

    1 person found this answer helpful.
    0 comments
  2. Navtej Singh Saini 4,216 Reputation points Microsoft Employee
    2020-06-11T02:12:34.553+00:00

    Hi EveryOne

    This issue has been fixed. Please restart your server for the same and it will work.

    Here are the details of the root cause and mitigation:

    https://learn.microsoft.com/en-us/answers/questions/31958/updating-firewall-rules-has-no-effect-for-azure-da.html

    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.

    Hope this helps.

    Thanks
    Navtej S

    0 comments
  3. Rabbani Azra 1 Reputation point
    2021-11-03T18:00:09.123+00:00

    Hi @Navtej Singh Saini , @Toby HERSEY , @piotrek ,

    I have Azure Postgres MI instance (primary) and Postgres on-prem (secondary) I need to enable the logical replication.

    I couldn't find a way to add my on-prem instance entry to pg_hba.conf file. Can you guys please help?

    do we have any documentation?

    Thanks

    0 comments