postgresqlazureazure-active-directorymicrosoft-entra-idazure-database-postgresql

Why can't I login to Azure Database for Postgres, when I'm member of admin group

I've assigned a Group (Microsoft Entra) as Postgres server admin. Screenshot of azure portal - db administrator

I'm member of the group.

I'm able to access the DB with groupname as user:

$ ACCESSTOKEN="$(az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken)"
$ psql "host=commonuksouth.postgres.database.azure.com port=5432 dbname=MyDatabase user=MyGroup password='${ACCESSTOKEN}' sslmode=require"

but not using my identity name:

$ ACCESSTOKEN="$(az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken)"
$ psql "host=commonuksouth.postgres.database.azure.com port=5432 dbname=MyDatabase user=myname@mydomain.com password='${ACCESSTOKEN}' sslmode=require"
psql: error: connection to server at "mydatabase.postgres.database.azure.com" (4.234.11.101), port 5432 failed: FATAL:  password authentication failed for user "myname@mydomain.com"

Why is that? It seems counterintuitive for the users (db-admins) to know what security group they should use to login to database.

Solution

  • Why can't I login to Azure Database for Postgres, when I'm member of admin group

    Important considerations when connecting as a Microsoft Entra group member:

    Use the exact name of the Entra group you're trying to connect with — spelling and capitalization matter.

    Don’t use a group member’s name or alias, just the group name itself.

    If the group name has spaces, put a backslash () before each space to make it work.

    The sign-in token you use is only good for 5 to 60 minutes, so it’s best to grab a fresh one right before you log in to the PostgreSQL database.

    Refer the below Link: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-configure-sign-in-azure-ad-authentication#use-a-token-as-a-password-for-signing-in-with-psql-or-pgadmin