First time (admin) login to private Postgres Flexible Server with EntraID enabled

Question

First time (admin) login to private Postgres Flexible Server with EntraID enabled

Dear Community,

I have created a Postgres Flexible Server, private (with VNET assigned) and only EntraID authentication enabled. My user is the admin.

Now, since I cant use tools like DBeaver or Azure Data Studio due to the private nature of the DB, I want to have a way to log into the DB and perform actions.

Hence I created:

• a container image that allows me to run shell commands, like psql
• an Azure Container Instance with a subnet (different subnet from the db, but same VNET)
• an AKS cluster where the nodes run in a different subnet, but same VNET

For AKS I also deployed a pod with the same container image.

Without the VNET, this setup allows me to log into the DB.

But with the VNET, I cant seem to run psql successfully. I am not even sure if my admin user already is a user inside the DB at all (just guessing a possible cause).

Here is what I am running in the console:

export PGHOST=aaa.postgres.database.azure.com
export PGUSER=xxx
export PGPORT=5432
export PGDATABASE=postgres
export PGPASSWORD=zzz
psql

For the password, I open up a Powershell console and run this:

az account get-access-token --resource-type oss-rdbms

I copy the accessToken and place it into the "zzz" above. And yes, I am sure it is the correct one, I verified by copying it again, parsing it and this token is still valid.

The first error I got is:

psql: error: connection to server at "aaa.postgres.database.azure.com" (10.0.1.4), port 5432 failed: FATAL:  password authentication failed for user "xxx"
connection to server at "aaa.postgres.database.azure.com" (10.0.1.4), port 5432 failed: FATAL:  no pg_hba.conf entry for host "10.0.0.252", user "xxx", database "postgres", no encryption

After more research I hence added this:

export PGSSLMODE=require

Resulting in the current error:

psql: error: connection to server at "aaa.postgres.database.azure.com" (10.0.1.4), port 5432 failed: FATAL:  password authentication failed for user "xxx"

I read about that in the subnet, a Service Endpoint "Microsoft.SQL" must be enabled. I did this for all 3 subnets but its still failing.

Btw for this one pod in AKS I want to use my admin account and not a workload identity. I want to use this connection method in order to add accounts, dbs and perform some administrative work.

Thoughts:

• Missing Network Configuration? But pinging the server works from both, ACI and AKS
• Something about Network Security Group that doesnt work out of the box? I didnt change anything
• My admin user is not yet in the Flexible Server? But this should be automatically setup
• Token retrieval is wrong? I something in the token so that it cant be copied over into the console correctly?
• Do I need to get my credentials in a different way?
• Do I need to specify my user other than my email? I also tried my objectId and my UPN (should not bei either of them)

Can you give me any clue or any ideas what else I could try?

Thank you and have a great day!

Answer 1

Hi Daniel,

Unable to login (with admin account) to private Azure Postgres Flexible Server with EntraID enabled.

psql: error: connection to server at "aaa.postgres.database.azure.com" (10.0.1.4), port 5432 failed: FATAL:  password authentication failed for user "xxx"

connection to server at "aaa.postgres.database.azure.com" (10.0.1.4), port 5432 failed: FATAL:  no pg_hba.conf entry for host "10.0.0.252", user "xxx", database "postgres", no encryption

To resolve this error, select the Name from the Authentication section under Security in Microsoft Entra Admin, including all extensions. This will fix the issue.

Image

Follow the below steps to log into the DB and perform administrative actions.

Step 1: Create a Virtual Network (VNet)

Image:

Step 2: Create PostgreSQL Server with VNet Integration and AAD Authentication

Image:

Image:2

Steps:3 To Create a VM with Microsoft Image (Windows Server) and Password Authentication

Image:1

Image:2

Install pgAdmin on the VM within the same VNet where the PostgreSQL Flexible Server is integrated.

Step:4 To Generate Azure Access Token

-Log in to Azure using az login to authenticate your account, then generate an access token with the command az account get-access-token --resource https://management.azure.com.

Image:

Copy the token from Azure CLI and paste it into pgAdmin on the VM to connect to the server.

Image:1

The Host name/address should be taken from the Endpoint in the Azure PostgreSQL Flexible Server overview page.

Image

The Username should be taken from the PostgreSQL Flexible Server under Security. Select Authentication, and on the right side, under Microsoft Entra Admins, choose and copy the Name, then use it as the Username in pgAdmin.

Image

By entering the correct username and password in pgAdmin on the VM, you will be able to log into the database and perform administrative tasks.