Skip to content

Identity Providers

Identity providers are external authentication systems that the PostgreSQL Permissions Model integrates with to authenticate users. The system is provider-agnostic -- it works with any identity provider that can supply a user identifier, and optionally group and role information. Providers are registered in the auth.provider table, and each user can have multiple identities linked to different providers through the auth.user_identity table.

This architecture separates authentication (who is the user?) from authorization (what can the user do?). The identity provider handles authentication; the permissions model handles everything else.

Provider Configuration

Every external authentication system that your application integrates with must be registered as a provider. Providers are stored in the auth.provider table with capability flags that control what the system allows for each provider.

The auth.provider Table

Column Type Default Description
provider_id integer auto-generated Primary key
code text -- Unique short identifier (e.g., azure_ad, google, email)
is_active boolean true Whether the provider can be used for authentication
allows_group_mapping boolean false Whether external group/role mappings can reference this provider
allows_group_sync boolean false Whether background member synchronization is enabled

Capability Flags

The two capability flags control how the provider participates in the group system:

  • allows_group_mapping -- When true, group mappings in auth.user_group_mapping can reference this provider. This enables external and hybrid groups to resolve membership based on the provider's group and role data.

  • allows_group_sync -- When true, background synchronization processes can pull the provider's member lists into the database. This flag requires allows_group_mapping to also be true, enforced by the provider_sync_requires_mapping constraint.

Constraint: sync requires mapping

The database enforces that allows_group_sync can only be true if allows_group_mapping is also true. Attempting to set sync without mapping will violate the provider_sync_requires_mapping check constraint.

Creating and Managing Providers

-- create a provider with group mapping enabled
select * from auth.create_provider(
    'admin',        -- _created_by
    1,              -- _user_id
    null,           -- _correlation_id
    'azure_ad',     -- _provider_code
    'Azure Active Directory',  -- _provider_name
    _allows_group_mapping := true,
    _allows_group_sync := true
);

-- idempotent: create if missing, return existing if found
select * from auth.ensure_provider(
    'admin', 1, null,
    'google', 'Google Authentication',
    _allows_group_mapping := true
);
-- returns: __provider_id, __is_new (true on first call, false on subsequent)

-- enable or disable a provider
select * from auth.enable_provider('admin', 1, null, 'azure_ad');
select * from auth.disable_provider('admin', 1, null, 'azure_ad');

Disabling a provider

When a provider is disabled (is_active = false), any attempt to authenticate through that provider will fail with error 33010 ("Provider is not in active state"). Existing user identities linked to the provider are not deleted, but they cannot be used for login until the provider is re-enabled.

User Identity

The auth.user_identity table links users to their external provider accounts. A single user in auth.user_info can have multiple identities -- one per provider they have authenticated with. This means the same person can log in through AzureAD at work, Google from a personal device, or email/password from a registration form, and all of these map to a single user record.

The auth.user_identity Table

Column Type Default Description
user_identity_id bigint auto-generated Primary key
provider_code text -- FK to auth.provider; identifies which provider issued this identity
user_id bigint -- FK to auth.user_info; the user this identity belongs to
uid text -- Provider-specific user identifier (e.g., email address, internal ID)
provider_oid text -- Provider object ID (e.g., AzureAD object GUID); globally unique
provider_groups text[] -- Array of group identifiers from the provider (e.g., AzureAD group GUIDs)
provider_roles text[] -- Array of role names from the provider (e.g., global_admin, reader)
user_data jsonb -- Arbitrary provider-specific data (e.g., profile fields, claims)
password_hash text -- Hashed password (used only by the email provider for local authentication)
password_salt text -- Password salt (used only by the email provider)
is_active boolean true Whether this identity can be used for authentication

Unique Constraints

The table has two important uniqueness rules:

  • uq_user_identity -- Unique on (provider_code, uid), ensuring that a given provider-specific user ID maps to exactly one identity record. The coalesce(uid, '-1') in the index allows null UIDs without violating uniqueness.

  • uq_user_identity_provider_oid -- Unique on provider_oid, ensuring that each provider object ID is globally unique across all providers.

Multiple Identities per User

A user can accumulate identities over time as they authenticate through different providers:

auth.user_info (user_id: 1001, username: "jane.doe")
    |
    +-- auth.user_identity (provider: "azure_ad", uid: "jane@corp.com",
    |       provider_oid: "aad-guid-123", provider_groups: ["IT", "Admins"])
    |
    +-- auth.user_identity (provider: "google", uid: "jane.doe@gmail.com",
    |       provider_oid: "google-id-456", provider_groups: [])
    |
    +-- auth.user_identity (provider: "email", uid: "jane@example.com",
            password_hash: "...", password_salt: "...")

Each identity is independent. You can enable or disable individual identities without affecting the others:

-- disable a specific provider identity for a user
select * from auth.disable_user_identity('admin', 1, null, 1001, 'google');

-- re-enable it later
select * from auth.enable_user_identity('admin', 1, null, 1001, 'google');

Provider Groups and Provider Roles

The provider_groups and provider_roles arrays are the bridge between external identity providers and the internal group system. These arrays are populated by your application when the user authenticates, typically from the identity provider's token claims or API response.

  • provider_groups -- Typically contains group identifiers such as AzureAD group GUIDs, LDAP group DNs, or Google Workspace group IDs. Matched against auth.user_group_mapping.mapped_object_id.

  • provider_roles -- Typically contains role names such as global_admin, user, or billing_manager. Matched against auth.user_group_mapping.mapped_role.

The system uses these arrays during permission resolution to determine which external and hybrid groups the user belongs to, without requiring explicit membership rows in auth.user_group_member.

Last Used Provider

The auth.user_info table has a last_used_provider_code column that tracks which provider the user most recently authenticated through. This is a critical piece of the permission resolution system.

Why It Matters

When a user has multiple identities, each identity may carry different provider_groups and provider_roles. The system needs a deterministic way to decide which identity's group/role data to use for permission calculations. The answer is simple: the last provider the user logged in with wins.

When a user authenticates:

  1. The system updates auth.user_info.last_used_provider_code to the current provider
  2. Permission resolution reads the provider_groups and provider_roles from the identity matching this provider
  3. Those arrays are matched against auth.user_group_mapping to determine external group membership
  4. The resulting group memberships (plus any direct/internal memberships) feed into the full permission calculation

This means that if a user logs in via AzureAD, they get permissions based on their AzureAD groups. If the same user later logs in via Google, their permissions shift to reflect their Google groups instead.

Practical impact

In most deployments, users authenticate through a single provider consistently (e.g., always AzureAD for corporate users). The last-used-provider mechanism primarily matters in environments where the same user genuinely switches between providers, which is uncommon but fully supported.

How It Is Updated

The last_used_provider_code is set automatically by the unsecure.update_last_used_provider() function, which is called internally by both auth.ensure_user_from_provider() and auth.register_user(). You do not need to update it manually.

-- this is called internally; shown here for understanding only
update auth.user_info
set last_used_provider_code = _provider_code
where user_id = _target_user_id;

Supported Provider Types

The system does not have a hardcoded list of supported providers. Any authentication system can be integrated by registering it as a provider and supplying the appropriate identifiers during user login. Common examples include:

Provider Typical code Identity Data
Azure Active Directory azure_ad Object GUID as provider_oid, group GUIDs in provider_groups, role names in provider_roles
Google Workspace / OAuth google Google user ID as provider_oid, workspace group IDs in provider_groups
Facebook facebook Facebook user ID as provider_oid
KeyCloak keycloak KeyCloak user UUID as provider_oid, realm roles in provider_roles
LDAP / Active Directory ldap Distinguished name or GUID as provider_oid, group DNs in provider_groups
Windows Authentication windows Windows SID or GUID as provider_oid, AD group SIDs in provider_groups
Email / Password email Email address as both uid and provider_oid, password_hash and password_salt for local auth
SAML saml NameID as provider_oid, SAML attributes mapped to provider_groups / provider_roles

Provider-agnostic design

The permissions model has no knowledge of any specific provider's API or protocol. Your application layer is responsible for authenticating the user with the provider and then passing the resulting identifiers, groups, and roles to the database functions. This keeps the database layer clean and independent of any particular authentication technology.

Login Flow

The primary function for provider-based authentication is auth.ensure_user_from_provider(). This function handles both first-time user provisioning (creating a new user) and returning user authentication (updating existing records) in a single call. Your application calls this function after successfully authenticating a user with an external provider.

How ensure_user_from_provider Works

The function follows this logic:

  1. Reject email provider -- The email provider uses auth.register_user() instead; calling ensure_user_from_provider with email raises an error.

  2. Validate provider -- Check that the provider is active (is_active = true). If not, raise error 33010.

  3. Look up existing identity -- Search auth.user_identity for a matching provider_code and either uid or provider_oid.

  4. If no identity found (new user):

    • Check the user blacklist for the provider identity
    • Create a new auth.user_info record
    • Create a new auth.user_identity record linking the user to the provider
    • Log a user_registered event

  5. If identity found (returning user):

    • Update the identity's uid and provider_oid if they have changed
    • Update basic user data (username, display name, email) if changed
    • Validate the user can log in (can_login = true), is active (is_active = true), and the identity is active (is_active = true)

  6. Clean up duplicate UIDs -- Remove any other identities for the same user and provider that have a different uid (handles provider-side ID changes).

  7. Update last used provider -- Set auth.user_info.last_used_provider_code to the current provider.

  8. Log login event -- Create a user_logged_in event in auth.user_event.

  9. Return user data -- Return the user's user_id, code, uuid, username, email, and display_name.

Sequence Diagram

sequenceDiagram
    participant App as Application
    participant DB as auth.ensure_user_from_provider()
    participant Provider as auth.provider
    participant Identity as auth.user_identity
    participant User as auth.user_info
    participant Event as auth.user_event

    App->>DB: call with provider_code, uid, oid, username, etc.
    DB->>Provider: validate provider is active
    Provider-->>DB: OK

    DB->>Identity: look up by provider_code + uid/oid

    alt New user (no identity found)
        DB->>DB: check user blacklist
        DB->>User: create user_info record
        DB->>Identity: create user_identity record
        DB->>Event: log user_registered
    else Returning user (identity found)
        DB->>Identity: update uid/oid if changed
        DB->>User: update username/display_name/email if changed
        DB->>DB: validate can_login, is_active, identity is_active
    end

    DB->>Identity: clean up duplicate UIDs for same provider
    DB->>User: set last_used_provider_code
    DB->>Event: log user_logged_in
    DB-->>App: return user_id, code, uuid, username, email, display_name

Example Usage

-- user logs in via AzureAD; application has already verified the token
select * from auth.ensure_user_from_provider(
    'app',                      -- _created_by
    3,                          -- _user_id (svc_authenticator service account)
    'corr-abc-123',             -- _correlation_id
    'azure_ad',                 -- _provider_code
    'jane@corp.com',            -- _provider_uid
    'aad-guid-12345',           -- _provider_oid
    'jane.doe',                 -- _username
    'Jane Doe',                 -- _display_name
    'jane@corp.com',            -- _email
    '{"department": "IT"}'::jsonb  -- _user_data (optional)
);

After this call, your application should update the user's provider_groups and provider_roles on the auth.user_identity record with the data from the provider's token, so that group mapping resolution has up-to-date information.

Email Registration Flow

For email/password-based registration, use auth.register_user() instead. This function is specifically designed for the built-in email provider and handles password hashing storage:

select * from auth.register_user(
    'app',                  -- _created_by
    2,                      -- _user_id (svc_registrator service account)
    null,                   -- _correlation_id
    'jane@example.com',     -- _email (used as both username and uid)
    'hashed_password_here', -- _password_hash
    'Jane Doe'              -- _display_name
);

Provider separation

The email provider and external providers (AzureAD, Google, etc.) use different registration paths. Calling auth.ensure_user_from_provider() with provider_code = 'email' raises error 33006 ("User cannot be ensured for email provider"). This separation exists because email registration requires a password hash, while external providers handle authentication entirely outside the database.

Provider Events

All provider lifecycle operations are logged to the journal with event codes in the 16001--16005 range:

Event Code Event Description
16001 provider_created New provider was created
16002 provider_updated Provider was updated
16003 provider_deleted Provider was deleted
16004 provider_enabled Provider was enabled
16005 provider_disabled Provider was disabled

Identity-related events are logged under 10030--10034:

Event Code Event Description
10030 identity_created User identity was created
10031 identity_updated User identity was updated
10032 identity_deleted User identity was deleted
10033 identity_enabled User identity was enabled
10034 identity_disabled User identity was disabled

See Also