Can't login to postgresql database with service principal in Azure security group.

Teemu Kumpumäki 0

Hi,

I'm having a problem with logging to postgresql with service principal. Service principal is the backend web app service principal.

Following steps are done to enable login:

Azure group with [OID_A] is created and contains one normal user and a service principal with [OID_B]

Then in Postgresql principal is created with:
select pg_catalog.pgaadauth_create_principal_with_oid('db_users', 'OID_A', 'group', false, false);

Now logging into database with a normal user that is in the group with oid OID_A and user name 'db_users' works fine. But when trying to log in from C# with generated token it fails with:

28000: Azure AD principal with [OID_B] is not a member of the provided Azure AD group.

Have tried all kind of voodoo for days, but nothing seems to get it working. I would appreciate if somebody has ideas how to fix it or how login could be debugged from the postgresql side, token, etc. Now in the postgresql logs is just this:

2024-07-22 12:00:43 UTC-669e49e9.815-LOG:  [AAD] Initializing AADAuth library
2024-07-22 12:00:43 UTC-669e49e9.815-LOG:  [AAD] AADAuth library initialization returns 0
2024-07-22 12:00:43 UTC-669e49e9.815-FATAL:  Azure AD principal with oid[6ac190e8-a17f-4be0-99ec-16727e32dca6] is not a member of the provided Azure AD group.

Oury Ba-MSFT 18,021 Reputation points Microsoft Employee

2024-07-23T18:31:50.4133333+00:00

@Teemu KumpumäkiWe had a current limitation in the implementation of AAD in Azure database for PostgreSQL that the AAD administrator has to be a user and not a service principle or the add user validation will not work. If the admin is a service principal, and you are adding individual users, there is a possible workaround if they are willing to add the user with their OID or appId explicitly. You can try to do:

set aad_validate_oids_in_tenant = off;

and then:

create role myaaduser with login password 'aadOidorAppIdHere' in role azure_ad_user;

There are two downsides here:

The admin user has to provide the AAD OID because the PostgreSQL server does not look it up. You do this by using the syntax: "create role xxx with login password 'aadOidHere' in role azure_ad_user". For users, use the AAD user OID. For applications, use the AAD applicationId/clientId (NOT the service principal ID).

Because the PostgreSQL server also does not look up the object type, it makes an assumption that you are adding an individual (user or application, it doesn't matter). You cannot add an AAD group this way.
Teemu Kumpumäki 0 Reputation points

2024-07-24T06:42:07.37+00:00
Thanks for the answer. I decided to just have the admins group as the database/table owner and then add the service principals as limited privileges users and granting privileges individually to them instead of limited privilege group in Azure side.

However for the future it seems that the documentation is bit thin on this topic.

For example https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-manage-azure-ad-users page only covers some functions and there is also:

pg_catalog.pgaadauth_create_principal(roleName text, objectId text, objectType text, isAdmin boolean, isMfa boolean)

which seems to be incorrect compared to what exist on postgresql, that should be

pgaadauth_create_principal_with_oid

Lots of other related undocumented functions also pops up with:

FROM pg_proc JOIN pg_namespace ON pg_proc.pronamespace = pg_namespace.oid WHERE pg_namespace.nspname = 'pg_catalog' AND proname LIKE 'pga%' ORDER BY proname;

I guess some examples would help explaining things because pgaadauth_create_principal_with_oid seems a bit rich on the possibilities you can do with the arguments...

Share via

Can't login to postgresql database with service principal in Azure security group.