Newly created PostgreSQL Flexible server is not available

Question

Newly created PostgreSQL Flexible server is not available

Oleg Katiuk 0

Newly created PostgreSQL Flexible server is not available:

Have several environments:

-DEV, PostgreSQL Flexible server version - 14.8, Connectivity method - Public Acess

-QA, PostgreSQL Flexible server version - 14.8, Connectivity method - Public Acess

"Allow public access from any Azure service within Azure to this server" option is enabled on both environments.

Connection string is located as secret in Azure Key Vault, separately for DEV and QA.

Issue is that App service (API solution on .net core) cant reach PostreSQL Flexible server on QA environment.

Information from API Log Stream ->

2023-09-07T08:06:57.244808610Z Unhandled exception. System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (00000005, 0xFFFDFFFF): Name or service not known
2023-09-07T08:06:57.244881509Z at System.Net.Dns.GetHostEntryOrAddressesCore(String hostName, Boolean justAddresses, AddressFamily addressFamily, ValueStopwatch stopwatch)
2023-09-07T08:06:57.244890709Z at System.Net.Dns.GetHostAddresses(String hostNameOrAddress, AddressFamily family)
2023-09-07T08:06:57.244896209Z at Npgsql.Internal.NpgsqlConnector.Connect(NpgsqlTimeout timeout)
2023-09-07T08:06:57.244901409Z at Npgsql.Internal.NpgsqlConnector.RawOpen(SslMode sslMode, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken, Boolean isFirstAttempt)
2023-09-07T08:06:57.244906809Z at Npgsql.Internal.NpgsqlConnector.<Open>g__OpenCore|203_1(NpgsqlConnector conn, SslMode sslMode, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken, Boolean isFirstAttempt)
2023-09-07T08:06:57.244912809Z at Npgsql.Internal.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
2023-09-07T08:06:57.244917909Z at Npgsql.UnpooledDataSource.Get(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
2023-09-07T08:06:57.244923109Z at Npgsql.NpgsqlConnection.<Open>g__OpenAsync|45_0(Boolean async, CancellationToken cancellationToken)
2023-09-07T08:06:57.244928409Z at Npgsql.NpgsqlConnection.Open()
2023-09-07T08:06:57.244933209Z at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected)
2023-09-07T08:06:57.244938209Z at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)
2023-09-07T08:06:57.244943109Z at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
2023-09-07T08:06:57.244948009Z at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlDatabaseCreator.Exists(Boolean async, CancellationToken cancellationToken)
2023-09-07T08:06:57.244953009Z at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlDatabaseCreator.Exists(Boolean async, CancellationToken cancellationToken)
2023-09-07T08:06:57.244958009Z at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlDatabaseCreator.Exists()
2023-09-07T08:06:57.244962909Z at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
2023-09-07T08:06:57.244967809Z at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
2023-09-07T08:06:57.244974208Z at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)

If any other information is required just let me know.
Pretty interesting issue I faced.
What could be the reason and possible solution?

Thanks

Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2023-09-07T22:38:02.3566667+00:00

@Oleg Katiuk Thank you for reaching out.

Can you please provide me with the steps that you have executed so that I can try to reproduce it.

Regards,

Oury
Oleg Katiuk 0 Reputation points

2023-09-07T23:04:39.3133333+00:00
Hi @Oury Ba-MSFT , sure.
So ,

created PostgreSQL Flexible Server - 14.8 version, set Connectivity method - Public access and Allow public access from any Azure service within Azure to this server

is enabled.

put connection string with such pattern - Server=[HOSTNAME OF POSTGRESQLSERVER];Database=[NAMEOFDATABASE];Port=5432;User Id=[USERNAME];Password=[PASSWORD];Command Timeout=300;Ssl Mode=Require;Trust Server Certificate=true; in Key Vault Secret
By the way, view connection string option doesnt work currently

Spin up App Service that fetch conection string from Key Vault, connected to POSTGRESQLSERVER and start working. App servie is public available.

Hope it helps
Oleg Katiuk 0 Reputation points

2023-09-07T23:40:11.05+00:00

@Oury Ba-MSFT small update from my side.
I go to Diagnose and Solve problems in PostgreSQL Flexible Server to Connection to Server block.

The result was indeed I had Failed connection and such tip (see in screenshot below)

How can I check that traffic from App Service to PostgreSQL Flexible Server is used TLS 1.2+ ?
Oleg Katiuk 0 Reputation points

2023-09-08T15:54:57.47+00:00

@Oury Ba-MSFT one more update from my side.
I changed DNS name of PostgreSQL server to its IP and backend can reach database after this.

Currently it is more workaround than fix.

But definetely Application/App Service cant resolve DNS name of newly created PostgreSQL server for some reasons.
I tested the App Service from different environments with existed PostgreSQL Flexible server (that was created few months ago) and everything works fine.

From my vision - Application-Code itself and App Service looks good.
Smth could be with PostgreSQL Flexible server. I assume there were some changes inside PostgreSQL Flexible server Azure Service, that impact DNS somehow.

May be you have idea what could be the root cause of issue and possible resolution of it.

Thanks
Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2023-09-08T23:24:48.8633333+00:00

Thank you for sharing the information, Oleg Katiuk

From the app service. Looks like you have already checked Connection String that's part of diagnose and solve problems blade.

Could you please use nameresolver to see if the app service can look up the database server.

Regards,

Oury
Oleg Katiuk 0 Reputation points

2023-09-09T11:04:42.1766667+00:00

hey @Oury Ba-MSFT thanks for your reply.

I forgot to mention, I have Linux based App Service (Debian 11, bullseye).

As I understand nameresolver is Windows specific app, so I use nslookup for checking.
Tests passed succesfully, DNS name resolved to server IP.

So, I tried to switch back to DNS name in Connection string, but after switching Backend crashed with the same error that was listed in comment above.

I wonder, can it be some specific server parameter set in PostgreSQL Flexible server?

And is there option to verify what TLS version is used by APP Service and PostgreSQL Flexible server, for instance via some cli commands?

2 answers

Your answer

Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2023-09-07T22:38:02.3566667+00:00

@Oleg Katiuk Thank you for reaching out.

Can you please provide me with the steps that you have executed so that I can try to reproduce it.

Regards,

Oury
Oleg Katiuk 0 Reputation points

2023-09-07T23:04:39.3133333+00:00

Hi @Oury Ba-MSFT , sure.
So ,

created PostgreSQL Flexible Server - 14.8 version, set Connectivity method - Public access and Allow public access from any Azure service within Azure to this server

is enabled.

put connection string with such pattern - Server=[HOSTNAME OF POSTGRESQLSERVER];Database=[NAMEOFDATABASE];Port=5432;User Id=[USERNAME];Password=[PASSWORD];Command Timeout=300;Ssl Mode=Require;Trust Server Certificate=true; in Key Vault Secret
By the way, view connection string option doesnt work currently

Spin up App Service that fetch conection string from Key Vault, connected to POSTGRESQLSERVER and start working. App servie is public available.

Hope it helps
Oleg Katiuk 0 Reputation points

2023-09-07T23:40:11.05+00:00

@Oury Ba-MSFT small update from my side.
I go to Diagnose and Solve problems in PostgreSQL Flexible Server to Connection to Server block.

The result was indeed I had Failed connection and such tip (see in screenshot below)

How can I check that traffic from App Service to PostgreSQL Flexible Server is used TLS 1.2+ ?
Oleg Katiuk 0 Reputation points

2023-09-08T15:54:57.47+00:00

@Oury Ba-MSFT one more update from my side.
I changed DNS name of PostgreSQL server to its IP and backend can reach database after this.

Currently it is more workaround than fix.

But definetely Application/App Service cant resolve DNS name of newly created PostgreSQL server for some reasons.
I tested the App Service from different environments with existed PostgreSQL Flexible server (that was created few months ago) and everything works fine.

From my vision - Application-Code itself and App Service looks good.
Smth could be with PostgreSQL Flexible server. I assume there were some changes inside PostgreSQL Flexible server Azure Service, that impact DNS somehow.

May be you have idea what could be the root cause of issue and possible resolution of it.

Thanks
Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2023-09-08T23:24:48.8633333+00:00

Thank you for sharing the information, Oleg Katiuk

From the app service. Looks like you have already checked Connection String that's part of diagnose and solve problems blade.

Could you please use nameresolver to see if the app service can look up the database server.

Regards,

Oury
Oleg Katiuk 0 Reputation points

2023-09-09T11:04:42.1766667+00:00

hey @Oury Ba-MSFT thanks for your reply.

I forgot to mention, I have Linux based App Service (Debian 11, bullseye).

As I understand nameresolver is Windows specific app, so I use nslookup for checking.
Tests passed succesfully, DNS name resolved to server IP.

So, I tried to switch back to DNS name in Connection string, but after switching Backend crashed with the same error that was listed in comment above.

I wonder, can it be some specific server parameter set in PostgreSQL Flexible server?

And is there option to verify what TLS version is used by APP Service and PostgreSQL Flexible server, for instance via some cli commands?

Answer 1

Oleg Katiuk 0

Hi @Oury Ba-MSFT any updates?

May be there is standard approach how to check connection to PostgreSQL database from APP Service?

Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2023-09-18T19:20:56.93+00:00

Oleg Katiuk

Sorry for the delay in responding the above follow up question.

I would suggest creating a support ticket so we can further investigate this issue.

Please let us know if you don't have a support plan.

Regards,

Oury

Answer 2

RahulRandive 10,486 Volunteer Moderator

Hi Oleg Katiuk •

To answer your questions- how to check connection to PostgreSQL database from APP Service?

Hopefully, the document and blog link below will be helpful

https://learn.microsoft.com/en-us/samples/azure-samples/serviceconnector-webapp-postgresql-django/serviceconnector-webapp-postgresql-django/

Connection handling best practice with PostgreSQL - Microsoft Community Hub

https://learn.microsoft.com/en-us/azure/static-web-apps/database-postgresql?tabs=bash&pivots=static-web-apps-rest

Thank you!

Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2023-09-25T15:54:15.9933333+00:00

Oleg Katiuk

Did the answer from @RahulRandive helped. If yes, please don't forget to mark as accept answer.

Regards,

Oury
Oleg Katiuk 0 Reputation points

2023-10-03T16:33:56.5866667+00:00

Hi @Oury Ba-MSFT , @RahulRandive this link return 404 - https://learn.microsoft.com/en-us/samples/azure-samples/serviceconnector-webapp-postgresql-django/serviceconnector-webapp-postgresql-django/

Other links are not helpful in my case, so I cant mark as answer.
Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2023-10-03T20:34:34.0533333+00:00

Oleg Katiuk

Thank you for letting us know. This requires a support case for troubleshooting so we can look into it to learn more.

Please do let us know if you don't have a support plan.

Regards,

Oury
Oleg Katiuk 0 Reputation points

2023-10-12T08:24:59.3833333+00:00

@Oury Ba-MSFT thanks, will create support ticket.
Thanks for your participation

Share via

Newly created PostgreSQL Flexible server is not available

2 answers

Your answer