Azure-docs: Firewall Rule "name" must be AllowAllAzureIps AND startIP, endIP = 0.0.0.0 to allow access from Azure services

[carterwilliamson]

I've confirmed this with Azure functions. I used the name "azure" and 0.0.0.0 for both start and end IP. This appears to work in the portal it shows the Connection Security -> Allow access toggle is marked as "Enabled". However I get an error when I run my app saying that the IP isn't allowed. After changing the rule name to AllowAllAzureIps it seems to work.

Using Azure functions (Windows, NodeJS)
Azure PostgreSql

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 8b816ba0-f204-e174-06b0-e6159f42054f
• Version Independent ID: 73abcfb7-f90b-df73-57eb-4ecb84688fc1
• Content: Azure Database for PostgreSQL Server firewall rules
• Content Source: articles/postgresql/concepts-firewall-rules.md
• Service: postgresql
• GitHub Login: @rachel-msft
• Microsoft Alias: raagyema

[SaurabhSharma-MSFT]

@carterwilliamson Thanks for your feedback! We will investigate and update as appropriate.

[SaurabhSharma-MSFT]

@carterwilliamson Thanks for the feedback ! I have assigned this issue to content author to investigate and update the document as appropriate.

[carterwilliamson]

Turns out I was wrong in this. I'm still getting the following error.

Failed to submit request. Reason: no pg_hba.conf entry for host "51.143.102.21", user "<dbAdmin>", database "<dbname>", SSL on

I'm using automation to create a PostgresDB and hook it up to an Azure Functions App. I consistently get this error. I can fix this by going into the Azure Portal and turning off the Allow Access to Azure Services toggle under the Connection Security tab for the Postgres Server and then turning it back on.

This may need to be reassigned to a technical issue.

[rachel-msft]

in-progress

[afmorris]

☝️ that's my details above.

I've seen this issue using Azure Database for PostgreSQL and attempting to access from Azure App Services and Azure DevOps both, for more clarity.

[rachel-msft]

+@ajlam as fyi

[darivuki]

Is there a workaround for this which doesn't involve manually switching the toggle on and off in the azure portal? I would like to deploy the database through an Azure DevOps release pipeline where I also deploy a WebApp that connects to the database. The WebApp is unable to connect to the database because of that firewall issue.

[rachel-msft]

So far I haven't been able to reproduce the issue. I will try some more tests.

[rachel-msft]

Still not able to reproduce. If you currently have a server that's got 0.0.0.0 set using Azure CLI or REST but your Azure resources are not able to connect, please @ me.

This is what I experienced:
Here is the Connection Security page for my server, with no firewall rules set

I try to connect to Postgres using psql via Azure Cloud Shell and it fails because no IPs are allowed. (I'm using cloud shell in this example because it's an Azure resource whose IP I don't know).

I create a firewall rule for Azure using the following command. Note that I made up the name of the rule. It doesn't have to be a specific name.
az postgres server firewall-rule create -g groupname -s servername -n azureall --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

Going back to the Connection Security page, I see that the rule I created translates in the portal as the Allow access to Azure services button now ON.

I can also use CLI to list and see the firewall rule:

Now I can connect to Postgres using psql via Azure Cloud Shell.

And when I query pg_hba conf (the home of Postgres firewall rules) I see that the Azure IPs have been whitelisted.

I'm closing this item based on the above. Will follow up if anyone can send me a repro of the issue.

[rachel-msft]

please-close

[afmorris]

Thanks for the investigation @rachel-msft! I think I've got this issue re-created successfully, this time with just the Azure CLI (rather than also having Terraform in play). Here's the reproduction steps:

1. Create a resource group: az group create --location eastus --name azure-postgresql-test-rg
2. Create a PostgreSQL server: az postgres server create --admin-password xxx --admin-user tmorris --name azure-postgresql-test-db --resource-group azure-postgresql-test-rg --sku-name B_Gen5_1 --location eastus
3. Create a database on that server: az postgres db create --name testdb --resource-group azure-postgresql-test-rg --server-name azure-postgresql-test-db
4. Create the Azure IP firewall rule: az postgres server firewall-rule create --end-ip-address 0.0.0.0 --name AllowAllAzureIps --resource-group azure-postgresql-test-rg --server-name azure-postgresql-test-db --start-ip-address 0.0.0.0

From here, if you check in the Azure Cloud Shell, you'll be able to connect to the PostgreSQL server using psql, just like you listed above. All is good here, until...

Add a new firewall rule: az postgres server firewall-rule create --end-ip-address 1.2.3.4 --name testrule --resource-group azure-postgresql-test-rg --server-name azure-postgresql-test-db --start-ip-address 1.2.3.4

You are now no longer able to connect to the PostgreSQL server using psql from the Azure Cloud Shell. This continues to be broken until you wipe out all the 0.0.0.0 rule and re-create it.

Based on my testing, it appears that this bug exists when the Azure-specific 0.0.0.0 is not the final firewall rule you add. You can add any number of firewall rules before adding the 0.0.0.0 one, and it'll still continue to work. However, if you wish to add any firewall rules after adding the 0.0.0.0 one, all Azure traffic will cease to work.

@darivuki :point_up: this may help you work around your situation.

This is also the case when using the Terraform Azure provider, which makes sense, as (I think) it just uses the Azure CLI under the hood.

Let me know if there's any other details I can provide, or if my instructions to reproduce are unclear.

[rachel-msft]

@afmorris Really appreciate you taking the time to get the repro for this. I'm able to reproduce it as well. I'm opening an internal ticket.

[darivuki]

@afmorris Thank you very much for taking the time to look into this deeply. This definitely helps me as a workaround.

[ATGardner]

We seem to be having a similar issue.
We are creating a PostgreSQL server, database and AllowAllWindowsAzureIps firewall rule using an ARM template.
We also create an AKS resource that runs our micro services, and they connect to PostgreSQL during their run.
Later, we add a firewall rule (for our office's IP) using az-cli. From that point onward, our micro services fail to connect to the PostgreSQL server for about 25 minutes (not an exact time, but it always reconnects at the end).

I am quite sure we also had the same bug when we added the firewall rule using the Azure Portal, but checking again now, that seems to be working fine. So this can be a workaround for us for now.

Just now I also tried disabling the "Allow access to Azure services" toggle (AllowAllWindowsAzureIps) and re-enabling it, from the Portal, and that also seems to have fixed the issue without having to wait for 25 minutes.

[solutionsmonkey]

Do we have an ETA on this fix?

[rachel-msft]

We expect to have the fix rolled out in June.

[solutionsmonkey]

@rachel-msft, thanks for an ETA but June is an awful long time out. This worked before and looks like a bug was introduced into this change. I have several customers who are using automation with Terraform and Kubernetes which this will not work well. I know this occurs only on the Basic server, but going up a level defeats the purpose of cost optimization. Also, these are migrations from AWS to Azure so the customer experience has not been the greatest due to this bug.

[rachel-msft]

@solutionsmonkey I think you may be referring to something else. There isn't any question of basic servers here. Can you email [email protected] so we can discuss?

[rachel-msft]

This fix has been rolled out. Please comment if you run into any issues with this.

[afmorris]

@rachel-msft thanks for the update! I just tested it for our application, and everything seems to be working from what I can tell!

[rachel-msft]

Thanks for the confirmation @afmorris. And thank you to all for your patience and help.

[36roentgen]

I experienced this issue 04/17/2020. The IP was added through the portal gui. Toggling the above setting allowed a normal connection otherwise "psql: FATAL: no pg_hba.conf entry for host"

[rachel-msft]

@36roentgen, could you please add more information of the steps you took or a screenshot?

What IP did you add in the portal, your personal IP? What did you toggle? I'm not exactly sure what you're trying to achieve.

[framled]

HI I have the same issue that @36roentgen

@rachel-msft what I did was:
Go to the connection security setting of my psql server on azure portal.

I added a 0.0.0.0 - 255.255.255.255 just clicking in the suggestion that ui does. here a screenshot.

Save the settings

and wait for almost an 1 hour aprox, and did not change anything.

thanks in advance.

[rachel-msft]

Hi @framled. There is a firewall bug this month that is affecting a subset of customers. We have rolled out a fix that takes effect after you restart your server. Could you restart your server and let me know whether your new firewall rules are taking effect.

Also please note that 0 to 255 should only be used for brief tests, as this essentially removes your firewall.

[framled]

@rachel-msft thanks, I restart the server and works great.