Group Types¶

The PostgreSQL Permissions Model supports three distinct group membership models -- internal, external, and hybrid -- to accommodate different authentication architectures. A group's type determines how its members are resolved: through direct database records, through identity provider mappings, or through a combination of both.

All groups are stored in the auth.user_group table. The is_external boolean column is the primary flag that distinguishes group types, while additional columns like is_synced and create_missing_users_on_sync further refine external group behavior.

The `auth.user_group` Table¶

Column	Type	Default	Description
`user_group_id`	integer	auto-generated	Primary key
`tenant_id`	integer	--	FK to `auth.tenant`; scopes the group to a tenant
`title`	text	--	Display name
`code`	text	--	Short code (auto-generated from title on insert); unique per tenant
`is_system`	boolean	`false`	System groups cannot be deleted
`is_external`	boolean	`false`	`false` = internal or hybrid; `true` = external
`is_assignable`	boolean	`true`	Whether users can be manually added as members
`is_active`	boolean	`true`	Inactive groups are excluded from permission resolution
`is_default`	boolean	`false`	Whether users are auto-added on registration
`can_members_manage_others`	boolean	`false`	Whether group members can add/remove other members
`can_members_see_others`	boolean	`true`	Whether group members can view the member list
`is_synced`	boolean	`false`	Whether member list is synchronized from a provider
`create_missing_users_on_sync`	boolean	`false`	Whether sync creates user accounts for unknown members
`source`	text	`null`	Application module that created this group (used by ensure functions)

Database Constraints¶

The table enforces several integrity rules through check constraints:

Constraint	Rule	Rationale
`user_group_check`	External groups cannot be default	External groups rely on provider mappings, not manual assignment
`must_be_external`	Synced groups must be external	Syncing pulls members from a provider, which requires external mode
`must_be_synced`	`create_missing_users_on_sync` requires `is_synced`	Auto-creating users only makes sense during a sync operation

Internal Groups¶

Internal groups use traditional, database-stored membership. A user is a member of the group if and only if a row exists in auth.user_group_member linking them to that group with member_type_code = 'manual'.

This is the default group type: is_external = false and no group mappings are configured.

When to Use¶

Your application manages its own user base without an external identity provider
Administrators manually assign users to groups through your application UI
You need full control over who belongs to which group at the database level

How Members Are Managed¶

Members are added and removed through direct function calls:

-- add user 1001 to group 5
select * from auth.create_user_group_member('admin', 1, null, 5, 1001, _tenant_id := 1);

-- remove user 1001 from group 5
perform auth.delete_user_group_member('admin', 1, null, 5, 1001, _tenant_id := 1);

When a member is added, the system:

Validates the group exists and is active
Checks that the group is assignable and not external (is_assignable = true and is_external = false)
Inserts a row into auth.user_group_member with member_type_code = 'manual'
Invalidates the user's permission cache so the new group permissions take effect immediately

Note

Attempting to manually add a member to a purely external group raises error 33013 ("User group is not assignable or is external").

Creating an Internal Group¶

select * from auth.create_user_group(
    'admin',        -- _created_by
    1,              -- _user_id
    null,           -- _correlation_id
    'Project Leads' -- _title
    -- is_external defaults to false
);

External Groups¶

External groups derive their membership entirely from identity provider mappings. Instead of storing members directly, the system maps external group names or role names from a provider (AzureAD, Google, LDAP, etc.) to internal groups through the auth.user_group_mapping table. When a user logs in, their provider_groups and provider_roles from the identity provider are matched against these mappings to determine group membership.

An external group has is_external = true and relies on one or more rows in auth.user_group_mapping.

When to Use¶

Your organization uses an external identity provider (AzureAD, Google Workspace, LDAP, KeyCloak, etc.) as the source of truth for group membership
You want group membership to be determined dynamically at login time without manual administration
You need to map provider-specific group IDs or role names to your application's permission groups

How Membership Is Resolved¶

The membership resolution flow for external groups works as follows:

User logs in through an identity provider
The provider returns provider_groups and provider_roles arrays, which are stored on auth.user_identity
The system marks this identity as the user's "last used" provider (auth.user_info.last_used_provider_code)
During permission resolution, the system matches the user's provider_groups and provider_roles against auth.user_group_mapping entries
If a match is found, the user is treated as a member of the mapped internal group -- without any row existing in auth.user_group_member

flowchart LR
    A["User logs in<br/>via AzureAD"] --> B["Provider returns<br/>groups & roles"]
    B --> C["Stored on<br/>user_identity"]
    C --> D{"Match against<br/>user_group_mapping?"}
    D -->|"mapped_object_id<br/>matches provider_groups"| E["User gets group<br/>permissions"]
    D -->|"mapped_role<br/>matches provider_roles"| E
    D -->|No match| F["No membership<br/>in this group"]

Creating an External Group¶

The convenience function auth.create_external_user_group() creates the group and its first mapping in a single call:

select * from auth.create_external_user_group(
    'admin',                          -- _created_by
    1,                                -- _user_id
    null,                             -- _correlation_id
    'Corporate Admins',               -- _title
    'azure_ad',                       -- _provider (provider code)
    _mapped_object_id := 'aad-group-guid-here',
    _mapped_object_name := 'Azure AD Admin Group'
);

This is equivalent to calling auth.create_user_group() with _is_external := true followed by auth.create_user_group_mapping().

Provider must allow group mapping

The provider referenced in the mapping must have allows_group_mapping = true. If it does not, the system raises an error. Configure this flag with auth.update_provider().

Synced Groups¶

External groups can optionally be configured for synchronization (is_synced = true). Synced groups do not just resolve membership dynamically at login -- they also support a background process that imports member lists from the provider into auth.user_group_member with member_type_code = 'synced'.

Why Synced Groups Exist¶

With standard external groups, membership is only resolved at login time -- a user must sign in before the system knows they belong to a group. This creates a problem: you cannot assign a project, set up permissions, configure preferences, or prepare anything for a user who has never logged in. Without synced groups, the only way to handle this is through an invitation workflow -- send an invite, wait for the user to accept and log in, and only then configure their access.

Synced groups eliminate this need. When your organization already has an external directory (AzureAD, LDAP, etc.) that defines who should belong to which group, the sync process can:

Import the member list from the external directory into the system
Create user_info records for users who have never logged in (when create_missing_users_on_sync = true)
Establish group membership with member_type_code = 'synced'

Once a user record exists in the system -- even before the user has ever signed in -- you can:

Assign them to projects or resources via resource-level ACL
Grant them individual permissions or permission sets
Set up tenant preferences
Pre-configure any application-specific data

When the user eventually logs in for the first time, everything is already in place. No invitation required, no manual setup needed.

When to use sync vs standard external groups

Use standard external groups (without sync) when you only need to grant permissions at login time and don't need to reference users before they sign in. Use synced groups when your application needs to set up resources, assignments, or configuration for users ahead of their first login -- leveraging the external directory as the source of truth for who those users are.

How Synchronization Works¶

The sync process uses the stage.external_group_member staging table as an intermediary:

Your application queries the external provider's API for the group member list
Load the results into stage.external_group_member (columns: member_upn, member_display_name, member_email, user_group_mapping_id)
Call auth.process_external_group_member_sync_by_mapping() to reconcile

The reconciliation compares the staging data against existing members and:

Creates group memberships for users who exist in the system but are not yet members
Ensures user accounts for people in the external directory who don't yet have a user_info record (when create_missing_users_on_sync = true), using auth.ensure_user_info() with the provider code from the mapping
Updates existing members to member_type_code = 'synced' if they were previously added through other means

-- find all groups that need synchronization
select * from auth.get_user_groups_to_sync(1, null);

-- after loading stage.external_group_member, process a specific mapping
select * from auth.process_external_group_member_sync_by_mapping(
    'sync_job', 1, null, _user_group_mapping_id := 42
);

-- or process all mappings for a group at once
select * from auth.process_external_group_member_sync(
    'sync_job', 1, null, _user_group_id := 5
);

Provider must allow group sync

The provider referenced in the mapping must have allows_group_sync = true. If provider_sync_requires_mapping = true on the provider, then only mappings with a matching mapped_object_id or mapped_role are eligible for sync.

Hybrid Groups¶

Hybrid groups combine both membership models. They accept manually added members (like internal groups) and resolve additional members through provider mappings (like external groups). A hybrid group is one where is_external = false and group mappings exist in auth.user_group_mapping.

When to Use¶

You have an external provider for most users but need to manually add a few users who are not in the provider
You are migrating from internal to external groups and need both mechanisms during the transition
Your organization has a mix of provider-managed and locally-managed users

How Membership Is Resolved¶

Hybrid groups resolve members from two sources simultaneously:

flowchart TB
    G["Hybrid Group"]

    subgraph manual ["Manual Members"]
        M1["user_group_member<br/>member_type_code = 'manual'"]
    end

    subgraph external ["External Members"]
        M2["user_group_mapping<br/>matched against<br/>provider_groups / provider_roles"]
    end

    G --> manual
    G --> external
    manual --> R["Combined<br/>membership"]
    external --> R

A user is considered a member of a hybrid group if either:

They have a direct membership row in auth.user_group_member, or
Their last-used identity's provider_groups or provider_roles match a mapping in auth.user_group_mapping for that group

Converting to Hybrid¶

Use auth.set_user_group_as_hybrid() to convert an external group to hybrid by clearing the is_external flag while keeping its mappings intact:

perform auth.set_user_group_as_hybrid('admin', 1, null, 5, _tenant_id := 1);

This sets is_external = false on the group, which allows manual members to be added alongside the existing provider mappings.

Group Mappings¶

Group mappings connect identity providers to internal groups. Each mapping is stored as a row in the auth.user_group_mapping table.

The `auth.user_group_mapping` Table¶

Column	Type	Description
`user_group_mapping_id`	integer	Auto-generated primary key
`user_group_id`	integer	FK to `auth.user_group`
`provider_code`	text	FK to `auth.provider`; identifies which provider this mapping applies to
`mapped_object_id`	text	External group ID to match against `provider_groups` (e.g., an AzureAD group GUID)
`mapped_object_name`	text	Human-readable name for the mapped object (informational only)
`mapped_role`	text	External role name to match against `provider_roles`

A unique index on (user_group_id, provider_code, mapped_object_id, mapped_role) prevents duplicate mappings.

Mapping by Group Name vs. Role Name¶

Each mapping can match on either mapped_object_id (matched against the user's provider_groups array) or mapped_role (matched against the user's provider_roles array), or both. At least one of mapped_object_id or mapped_role must be provided -- the system raises error 31004 if both are null.

-- map by external group ID (matched against provider_groups)
select * from auth.create_user_group_mapping(
    'admin', 1, null,
    5,                    -- _user_group_id
    'azure_ad',           -- _provider_code
    _mapped_object_id := 'aad-group-guid'
);

-- map by role name (matched against provider_roles)
select * from auth.create_user_group_mapping(
    'admin', 1, null,
    5,                    -- _user_group_id
    'azure_ad',           -- _provider_code
    _mapped_role := 'global_admin'
);

Case normalization

Both mapped_object_id and mapped_role are stored in lowercase. The matching is performed against the provider's groups and roles arrays as-is, so ensure that the identity provider data is also normalized to lowercase when stored in auth.user_identity.

Cache Invalidation on Mapping Changes¶

When a mapping is created, the system automatically invalidates the permission cache for all users whose provider_groups or provider_roles match the new mapping. When a mapping is deleted, the cache is invalidated for all users who were members through that mapping. This ensures permissions are recalculated immediately.

Group Type Conversion¶

The system provides functions to convert groups between types. These conversions have important side effects.

Internal to External¶

perform auth.set_user_group_as_external('admin', 1, null, 5, _tenant_id := 1);

Destructive: deletes manual members

Converting to external permanently deletes all manually-added members (member_type_code = 'manual') from the group. Only members added through sync or mapping are retained. This operation cannot be undone -- the manual membership data is lost.

What happens:

All rows in auth.user_group_member where member_type_code = 'manual' for this group are deleted
The group's is_external flag is set to true

After conversion, you must add at least one group mapping for the group to have any members.

External to Internal¶

perform auth.set_user_group_as_internal('admin', 1, null, 5, _tenant_id := 1);

Destructive: deletes external members and all mappings

Converting to internal permanently deletes all non-manual members (those with member_type_code other than 'manual') and deletes all group mappings from auth.user_group_mapping. The is_synced and create_missing_users_on_sync flags are also reset to false.

What happens:

All rows in auth.user_group_member where member_type_code <> 'manual' for this group are deleted
All rows in auth.user_group_mapping for this group are deleted
The group is updated: is_external = false, is_synced = false, create_missing_users_on_sync = false

External to Hybrid¶

perform auth.set_user_group_as_hybrid('admin', 1, null, 5, _tenant_id := 1);

This is the least destructive conversion. It simply sets is_external = false while keeping all existing mappings and external/synced members intact. After conversion, manual members can be added alongside the provider-mapped members.

Summary of Conversions¶

Conversion	Function	Members Deleted	Mappings Deleted	Safe?
Internal to External	`set_user_group_as_external()`	Manual members	None	No
External to Internal	`set_user_group_as_internal()`	Non-manual members	All mappings	No
External to Hybrid	`set_user_group_as_hybrid()`	None	None	Yes

All conversion functions require the groups.update_group permission.

Default Groups¶

Groups marked with is_default = true are automatically assigned to users when they are registered or when auth.assign_user_default_groups() is called. This provides a way to give every new user a baseline set of group memberships (and therefore permissions) without manual intervention.

How It Works¶

When auth.assign_user_default_groups() is called for a user in a given tenant:

The system finds all active groups in that tenant where is_default = true
It filters out groups the user is already a member of
For each remaining default group, it calls unsecure.create_user_group_member() to add the user with member_type_code = 'manual'

-- add user 1001 to all default groups in tenant 1
select * from auth.assign_user_default_groups('admin', 1, null, 1001, _tenant_id := 1);

The function returns the complete list of group memberships for the user in that tenant after the operation.

Constraints¶

External groups cannot be default: The user_group_check constraint enforces that is_external = true and is_default = true cannot both be set on the same group. Default groups add members as manual, which is incompatible with the external membership model.
Default groups must be active (is_active = true) to be included in the automatic assignment.
The function is idempotent: calling it multiple times does not create duplicate memberships.

Typical Usage¶

Default groups are typically called during user registration. The auth.register_user() function can trigger this automatically, or your application can call it explicitly after creating a user:

-- register a new user (may add to default groups depending on implementation)
select * from auth.register_user(
    'system', 1, null,
    'john.doe', 'john@example.com', 'John Doe',
    _provider_code := 'email'
);

-- explicitly add to default groups (if not done during registration)
select * from auth.assign_user_default_groups('system', 1, null, 1001, _tenant_id := 1);

Membership Resolution Overview¶

The following diagram summarizes how the system resolves group membership across all three types:

flowchart TB
    U["User"]

    subgraph internal ["Internal Group"]
        IM["user_group_member<br/>member_type_code = 'manual'"]
    end

    subgraph external ["External Group"]
        EM["user_group_mapping<br/>matched via provider_groups<br/>or provider_roles"]
    end

    subgraph hybrid ["Hybrid Group"]
        HM["user_group_member<br/>member_type_code = 'manual'"]
        HE["user_group_mapping<br/>matched via provider"]
    end

    U -->|"direct membership"| IM
    U -->|"provider login<br/>identity matching"| EM
    U -->|"direct membership<br/>OR provider login"| hybrid

    IM --> P["Permission<br/>Resolution"]
    EM --> P
    HM --> P
    HE --> P

Member Type Codes¶

The auth.user_group_member.member_type_code column tracks how each membership was created:

Code	Description
`manual`	Added directly through `create_user_group_member()` or default group assignment
`external`	Added through identity provider mapping resolution
`synced`	Added through background group synchronization

These codes reference the const.user_group_member_type table.

Group Types¶

The auth.user_group Table¶

Database Constraints¶

Internal Groups¶

When to Use¶

How Members Are Managed¶

Creating an Internal Group¶

External Groups¶

When to Use¶

How Membership Is Resolved¶

Creating an External Group¶

Synced Groups¶

Why Synced Groups Exist¶

How Synchronization Works¶

Hybrid Groups¶

When to Use¶

How Membership Is Resolved¶

Converting to Hybrid¶

Group Mappings¶

The auth.user_group_mapping Table¶

Mapping by Group Name vs. Role Name¶

Cache Invalidation on Mapping Changes¶

Group Type Conversion¶

Internal to External¶

External to Internal¶

External to Hybrid¶

Summary of Conversions¶

Default Groups¶

How It Works¶

Constraints¶

Typical Usage¶

Membership Resolution Overview¶

Member Type Codes¶

See Also¶

The `auth.user_group` Table¶

The `auth.user_group_mapping` Table¶