Authentication failure in Azure postgresql database

Gokul R Dev 326

Hello everyone,

I am following these turotials to authenticate with Azure postgresql db with system assigned managed identity. I haven't deployed to code to azure app service, when i try to check the connection locally, I'm getting the error 'Password authentication failed for user <user-name>'. The problem is the username which is diplayed is the local admin of the vm from which i am trying to authenticate. I had successfuly logged in using azure cli, azure account extension to run this locally and i am using simple nodejs app and was able to get the token successfully, my vm is in same vnet as the db resides. However I haven't connected my vm to entra id which is not mentioned as a requirement in docs.

tutorial 1, tutorial2

Oury Ba-MSFT 18,616 Reputation points Microsoft Employee

2024-08-22T22:17:35.1266667+00:00
@Gokul R Dev It sounds like you’re encountering an issue with the authentication process when trying to connect to your Azure PostgreSQL database using a system-assigned managed identity.

Could you please share the below

The complete error message.

Timestamp for the error.

Is it intermittent or persistent.

When did it start to happen.

Regards,

Oury
Gokul R Dev 326 Reputation points

2024-08-23T06:30:09.6766667+00:00
Error message: password authentication failed for user "<user name>" where username displayedis the local admin account i used to sign in to vm.

also some properties along with error: length: 103,

severity: 'FATAL',

code: '28P01',

detail: undefined,

hint: undefined,

position: undefined,

internalPosition: undefined,

schema: undefined,

table: undefined,

column: undefined,

dataType: undefined,

constraint: undefined,

file: 'auth.c',

line: '326',

routine: 'auth_failed'

}

[2024-08-23T06:28:14.708Z] (recent one)

persistent

I tried this tutorial yesterday to implement this in our environment, so far i was not able to connect locally.

@Oury Ba-MSFT
Gokul R Dev 326 Reputation points

2024-08-23T06:33:57.4566667+00:00
password authentication failed for user "<username>" where username is local admin a/c I used to sign in.

[2024-08-23T06:28:14.708Z]

persistent.

yesterday onwards, the moment i started to try for the first time onwards.
Gokul R Dev 326 Reputation points

2024-08-29T09:47:15.0766667+00:00
Hi @Oury Ba-MSFT ,

I just found why this happened. the above mentioned tutorial use an env file to get database user details and the DefaultAzureCredential class from azure identity library get the token credential by sequentially trying several different credential types in the order: env file>azure cli> managed identity. I beleive this is the order and i saw it somewhere before. So I tried to mention the postgresql user initially and then entra admin account for the database later in env file.However, both of them was throwing the same error which is picking my local administrator account of the vm I'm using. Then tried to hard code the entra account user name (username@domain.com) directly in the code file and i was able to make connection to database.Now my conerns are :

I don't want to hard code the user name value in the code file, instead want to use the env file. How can we make sure the user account mentioned in the env file gets successfully connected to database when developing locally using managed identity?

When deploying the same code to azure app service, i had already attached a managed identity to app service and its connected to databse using service connector mentioned in the above tutorial. so in the env file for the database user, which user name i need to mention? Is it the one service connector creates while connecting app service with database?

Thanks for your support so far.

1 answer

Gokul R Dev 326 Reputation points

2024-08-29T09:56:23.63+00:00
Hi @Oury Ba-MSFT ,

I just found the reason for the issue. DefaultAzureCredential class from azure identity library get the token credential by sequentially trying several different credential types. I believe the order is env file> azure cli> managed identity which I saw somewhere in the documentation and I was using an env file like suggested in the tutorial where i had given the database user value a postegrsql user initially and entra admin of database later.However, both of the values threw the error I mentioned above which is picking the local admin of my VM instead of the user I mentioned in env file. Then I tried to hard code the entra admin of database in code file (username@domain.com) and I was able to successfully connect to databse locally.

How can we make sure the use of value mentioned in env file which is failing at this moment instead of hard coding?

The service connector which is connection the app service with databse create a user. Is this user I need to mention as databse user when i deploy the code to app service?
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Authentication failure in Azure postgresql database

1 answer

Your answer