19

I have an Azure website in development that connects to a new Azure Postgresql database.

This error seemed to have started over the weekend. It was working fine the last time I tried it on Friday. The IP address of my Azure Site Plan seemed to change and I edited the Azure Postgres Firewall rules.

The really weird part is the "0.0.0.0" host. How is the web server looking like IP address "0.0.0.0" to the database? I did add "0.0.0.0" to the firewall as well but that did not have any effect. I of course cannot edit the pg_hba.conf file directly on Azure.

Also, it works fine when running from my dev computer. I tried redeploying my web site, no joy.

Here is the full stacktrace:

[PostgresException (0x80004005): 28000: no pg_hba.conf entry for host "0.0.0.0", user "UserName", database "orders", SSL on]
Npgsql.NpgsqlConnector.DoReadMessage(DataRowLoadingMode dataRowLoadingMode, Boolean isPrependedMessage) +310
Npgsql.NpgsqlConnector.ReadMessageWithPrepended(DataRowLoadingMode dataRowLoadingMode) +195
Npgsql.NpgsqlConnector.HandleAuthentication(String username, NpgsqlTimeout timeout) +57
Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout) +299
Npgsql.ConnectorPool.Allocate(NpgsqlConnection conn, NpgsqlTimeout timeout) +573
Npgsql.NpgsqlConnection.OpenInternal() +366
Npgsql.NpgsqlConnection.Open() +4
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +120
System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +160
System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +108
MapHazardsPro4.Functs.GetUserTable() in C:\inetpub\wwwroot\maphazardspro_q3_2017\Functs.vb:539
MapHazardsPro4.Global.Application_Start(Object sender, EventArgs e) in C:\inetpub\wwwroot\maphazardspro_q3_2017\Global.asax.vb:49

[HttpException (0x80004005): 28000: no pg_hba.conf entry for host "0.0.0.0", user "UserName", database "orders", SSL on]

System.Web.HttpApplicationFactory. EnsureAppStartCalledForIntegratedMode(HttpContext context, HttpApplication app) +10104513
System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) +118
System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) +173
System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +336
System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +296

[HttpException (0x80004005): 28000: no pg_hba.conf entry for host "0.0.0.0", user "UserName", database "orders", SSL on]
System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +10085804
System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +95
System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +254
Brad Mathews
  • 1,567
  • 2
  • 23
  • 45

3 Answers3

15

I had the same problem. Added firewall rule 0.0.0.0 - 255.255.255.255

driushkin
  • 3,531
  • 1
  • 24
  • 25
  • That did it! Thanks. But I am not sure that is the best solution. That essentially removes all locational access controls (that may not be the right term) – Brad Mathews Oct 25 '17 at 16:08
  • I was playing with different IP ranges trying to tighten things down a bit and no matter what I tried, it kept working, even if I removed the rule entirely. So I republished the website to make sure the application is reset and it went down gain with the original error. So something is getting cached on the web server. I hope that gives more clues as to the cause of the problem. – Brad Mathews Oct 25 '17 at 17:52
  • 4
    If you go to the Properties section of your Web App it will give you the Virtual IP Address and Outbound IP Addresses of your application. Add those to the firewall rules. – Stephen Gilboy Nov 04 '17 at 06:22
3

I resolved it the following way:

1: Close all ports inyour psql firewall rules 2: Try connecting(it will fail, of course) 3: From Azure console, open your last postgres server logs file.

Here you should say something like this:

2017-11-17 08:12:03 UTC-5a0e99d3.713c-LOG:  connection received: host=xxx.xxx.xxx.xxx port=0
2017-11-17 08:12:04 UTC-5a0e99d3.713c-FATAL:  no pg_hba.conf entry for host "0.0.0.0", user "smile", database "smiledb", SSL on

Now you can create a rule for the xxx.xxx.xxx.xxx address you saw in the logs, and things should work... Hope it helps!

user8956555
  • 31
  • 1
  • 1
    Never, never ever open your database to any IP address! Please Microsoft, try to make your RDS work like aws's!!! – user8956555 Nov 17 '17 at 08:49
  • when I run SELECT * FROM pg_hba_file_rules(); on one of my azure postgres instances, I am getting 500 lines of rules many of them are for networks not for ips it is open to thousands of ips :( Go Microsoft !!!! – Tomasz Swider Jul 22 '19 at 12:27
0

You can set up a VNet rule for the VNet that you application nodes are running in.

This VNet has to have a subnet with service endpoint Microsoft.SQL.

Yes also for the Postgres DB. This is some sort of all SQL databases service endpoint. When the VNet is setup with the service endpoint, then you can add this VNet in connection security section of Azure portal Postgres view, and your application nodes will be able to talk to the Postgres.

https://learn.microsoft.com/en-us/azure/postgresql/howto-manage-vnet-using-portal

I think the key problem here is to know about the service endpoints in VNets.

Tomasz Swider
  • 2,314
  • 18
  • 22