PostgreSql Entra group member sync

Question

Hi,

We're trying to use Azure database for PostgreSql flexible server with Microsoft Entra authentication, but are having some challenges and questions.

We got a server up and running and set it so only Microsoft Authentication is allowed. We got a security group that contains all our database administrator and set that as the administrator of the server. As we would like people to have access with their own account and not under the group identity we enabled the 'pgaadauth.enable_group_sync' server setting. The sync then synchronizes the group members to server and group members are now able to authenticate and access the database with their personal account. So far so good.

But what I also see happening is that the sync not only syncs from the Azure resource to the server, but the individual group members are now also set as administrator on the azure resource. Is this intended behaviour? As it's not ideal for us, the whole reason we use groups for this is that we don't tie it to individual users that you then have to manage all over the place. As when people leave the organization, change role we don't want to have to manage their personal accounts on multiple databases.

Second question is about removing users from a group. I see them hints here and there that the sync doesn't remove users or at least removes access for them if they're removed from the group. Not sure if this is related to the point above. I could how as the users are now also added their with their personal account the next sync keeps them in on that basis. But I would expect that if a user got access via group membership that the sync should rectify this if that membership is revoked. Is this also intended behaviour or is there a way to keep it in sync with current Microsoft Entra group membership?

A bit more context on this that we also use a system in our production environment where with PIM you get elevated and temporary group membership that manages access to production resources. This works fine for a couple of other database solutions we use on the Azure platform. But would this approach also work with PostgreSql flexible server?

Kind regards,

Gerben

Answer 1

Hey @Gerben Maaijen ,

You've basically run into the fact that Entra group sync for Azure Database for PostgreSQL is helpful but not fully "magic" honestly. It solves one half of the problem (getting people in) and leaves most of the lifecycle clean-up to you.

First thing to clarify: when you turn on pgaadauth.enable_group_sync the service is not supposed to go and rewrite Azure RBAC or the resources admin list. What it does is: keep a Postgres role for the Entra group and create Postgres roles/mappings for the individual group members so they can log in with their own Entra identities. Thats why after enabling group sync your DB suddenly "knows" about all those individual users: theyre database roles, not Azure resource admins in the RBAC sense.

So when you say "the individual group members are now also set as administrator on the Azure resource" thats the part that doesnt really fit the documented behavior. The Entra admin list for the PostgreSQL server (Authentication blade) and IAM are governed by Azure RBAC, group sync is scoped to database roles. If you see those users as admins in the portal its usually because: some other automation/role assignment/PIM process is granting them a server-level role, or the portal is simply showing you all objects that have some kind of admin-ish mapping even though the source of truth is still the group.

On your second question about removed group members: The group sync docs do say that membership changes are synchronized. Groups are auto-synced every 30 minutes and you can trigger a sync manually with:

SELECT * FROM pgaadauth_sync_roles_for_group_members();

with pgaadauth.enable_group_sync set to on. That process is supposed to handle both newly added members and removed members theoretically.

But theres a known limitation here honestly: in practice the sync works primarily additively. It creates roles for new members reliably but doesnt remove roles for departed members automatically or consistently. The Postgres role object persists in PostgreSQL even after Entra group membership is revoked. Authentication should fail because Entra wont issue a valid token but the database role remains until manually cleaned up.

There are two more subtleties: The Postgres role object for a user might still exist after they leave the group but the Entra mapping is gone. In that case the role is basically a dead shell, they cant successfully authenticate anymore even though the name still appears in pg_roles. In a PIM scenario access actually disappears only after the next sync. If you need near-real-time revocation youll want a small job that calls pgaadauth_sync_roles_for_group_members() on a tighter schedule.

If youre seeing a user removed from the Entra group who can still authenticate successfully to the database long after the next sync window thats no longer "by design", thats something youd need to raise with support.

A practical pattern Ive seen work best is: Treat the Entra group as the single source of truth for "who may administer this DB" and run PIM on that group. Leave pgaadauth.enable_group_sync on so people can log in with their own identities but periodically run a maintenance script that compares current Entra group membership with pg_roles and drops roles that no longer correspond to group members. Manage who is an "Azure admin" of the resource via Azure RBAC (IAM/Authentication blade) and keep that limited to the group itself not all individual users.

So yeah what youre seeing (easy add, messy removal) reflects the current reality: group sync is additive and DB-focused, not a full lifecycle manager for Azure admins. For a clean PIM story you still need a bit of automation around sync cadence and role clean-up.

Answer 2

Hi Can,

Thanks for the response. We're going to raise a ticket with support then. Because when we enable the group sync not only are those group members added as principals and roles on the PostgreSQL server, but also as administrators on the Azure resource.

We basically ran the following scenario a couple of times:

• Created a Azure database for PostgreSQL flexible server.
• Added group A as administrator. Group A has user B as a member.
• Enable the group sync and trigger it manually.
• Confirm principals/roles are created on the PostgreSQL server and user B now has access to the database with this personal Microsoft Entra account.
• However: not only Group A is now listed as administrator on the Azure resource, but also user B.
• Remove user B from the Microsoft Entra group.
• Rerun the pg_aadauth sync. Nothing happens.
• As soon as I manually remove user B as administrator and rerun the sync it's also fixed on the PostgreSQL server side of things and user B loses access.

Will keep you updated if we find out more.

Kind regards,

Gerben