Azure Postgres: no pg_hba.conf entry for host "0.0.0.0", user "UserName", database "orders", SSL on

▲
4
▼

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
postgresql
azure
azure-web-sites
npgsql
asked on Stack Overflow Oct 24, 2017 by Brad Mathews

3 Answers

▲
5
▼

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

answered on Stack Overflow Oct 25, 2017 by driushkin
▲
0
▼

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!

answered on Stack Overflow Nov 17, 2017 by user8956555
▲
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://docs.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.

answered on Stack Overflow Jul 23, 2019 by Tomasz Swider

User contributions licensed under CC BY-SA 3.0