User Identity Management

The PostgreSQL Permissions Model supports multi-provider authentication through the auth.user_identity table, allowing users to authenticate via multiple identity providers while maintaining a single user account. This system enables seamless integration with enterprise authentication systems.

Multi-Identity Architecture

Core Concept

A single user can have multiple authentication identities from different providers:

graph TB
    subgraph "User Account"
        U[User: john.doe<br/>auth.user_info]
    end

    subgraph "Multiple Identities"
        I1[Windows Identity<br/>DOMAIN\\john.doe]
        I2[Azure AD Identity<br/>john@company.com<br/>🟢 Last Used]
        I3[Google Identity<br/>john.doe@gmail.com]
    end

    subgraph "Provider Systems"
        P1[Windows Active Directory<br/>Groups: Domain Users, Developers]
        P2[Azure Active Directory<br/>Groups: Engineering, TeamLeads<br/>Roles: Manager]
        P3[Google Workspace<br/>Groups: None]
    end

    U --> I1
    U --> I2
    U --> I3

    I1 --> P1
    I2 --> P2
    I3 --> P3

Last-Used Identity Logic

The system uses the most recently used identity to determine current permissions:

• When a user logs in, their identity is marked as is_last_used = true
• All other identities for that user are marked as is_last_used = false
• Permission calculations use groups/roles from the last used identity
• This allows dynamic permission changes based on authentication method

Identity Table Structure

auth.user_identity Schema

CREATE TABLE auth.user_identity (
    user_identity_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id uuid NOT NULL REFERENCES auth.user_info(user_id),
    provider_id uuid NOT NULL REFERENCES auth.provider(provider_id),
    provider_user_id text NOT NULL,  -- External provider's user ID
    provider_groups text[],           -- Groups from provider
    provider_roles text[],            -- Roles from provider
    provider_data jsonb,              -- Additional provider data
    is_active boolean DEFAULT true,
    is_last_used boolean DEFAULT false,  -- Most recent login
    last_login_at timestamptz,
    created_at timestamptz DEFAULT now(),
    updated_at timestamptz DEFAULT now(),
    UNIQUE (provider_id, provider_user_id)
);

Key Fields Explained

provider_user_id: The user's unique identifier in the external system - Windows AD: DOMAIN\\username or SID - Azure AD: Object ID (GUID) - Google: Google user ID - SAML: Subject NameID

provider_groups: Array of group names from the provider - Windows AD: Security groups - Azure AD: Security groups and Office 365 groups - LDAP: Group DNs or names - Custom: Application-defined groups

provider_roles: Array of roles from the provider - Azure AD: Application roles and directory roles - SAML: Role attributes - Custom: Application-defined roles

provider_data: Additional JSON data from the provider - User attributes (department, title, manager) - Claims from JWT tokens - SAML assertion attributes

Creating User Identities

Add Identity to Existing User

-- Create Azure AD identity for existing user
SELECT auth.create_user_identity(
    _user_id := 'existing-user-uuid',
    _provider_id := 'azure-ad-provider-uuid',
    _provider_user_id := 'azure-ad-object-id',
    _provider_groups := ARRAY['Domain Users', 'Engineering', 'Senior Developers'],
    _provider_roles := ARRAY['Developer', 'TeamLead'],
    _provider_data := '{
        "email": "john.doe@company.com",
        "department": "Engineering",
        "title": "Senior Software Developer",
        "manager": "jane.manager@company.com"
    }'::jsonb
);

Just-In-Time User Provisioning

-- Create user and identity from external provider
SELECT auth.ensure_user_from_provider(
    _provider_id := 'azure-ad-provider-uuid',
    _provider_user_id := 'new-user-object-id',
    _email := 'newuser@company.com',
    _display_name := 'New User',
    _provider_groups := ARRAY['Domain Users', 'Marketing'],
    _provider_roles := ARRAY['Employee'],
    _provider_data := '{
        "department": "Marketing",
        "hire_date": "2024-01-15",
        "employee_id": "EMP123"
    }'::jsonb
);

Identity Provider Integration

Windows Authentication

-- Windows AD identity
SELECT auth.create_user_identity(
    _user_id := 'user-uuid',
    _provider_id := 'windows-ad-provider-uuid',
    _provider_user_id := 'S-1-5-21-1234567890-1234567890-1234567890-1001',  -- Windows SID
    _provider_groups := ARRAY['Domain Users', 'COMPANY\\Developers', 'COMPANY\\Engineering'],
    _provider_roles := ARRAY[],  -- Windows typically uses groups, not roles
    _provider_data := '{
        "domain": "COMPANY",
        "username": "john.doe",
        "computer": "WORKSTATION01"
    }'::jsonb
);

Azure Active Directory

-- Azure AD identity with rich claims
SELECT auth.create_user_identity(
    _user_id := 'user-uuid',
    _provider_id := 'azure-ad-provider-uuid',
    _provider_user_id := '12345678-1234-1234-1234-123456789012',  -- Azure object ID
    _provider_groups := ARRAY['All Company', 'Engineering', 'Senior Staff'],
    _provider_roles := ARRAY['Application Developer', 'Team Lead'],
    _provider_data := '{
        "email": "john.doe@company.com",
        "upn": "john.doe@company.com",
        "department": "Engineering",
        "title": "Senior Software Developer",
        "office_location": "Seattle",
        "manager": "jane.manager@company.com",
        "employee_id": "E123456"
    }'::jsonb
);

SAML Identity Provider

-- Generic SAML provider (Okta, Auth0, etc.)
SELECT auth.create_user_identity(
    _user_id := 'user-uuid',
    _provider_id := 'saml-okta-provider-uuid',
    _provider_user_id := 'john.doe@company.com',  -- SAML NameID
    _provider_groups := ARRAY['Everyone', 'Engineering', 'Admins'],
    _provider_roles := ARRAY['SystemAdmin', 'Developer'],
    _provider_data := '{
        "name_id": "john.doe@company.com",
        "session_index": "session123",
        "department": "IT",
        "cost_center": "CC001"
    }'::jsonb
);

Managing Identity Lifecycle

Login Processing

-- Update identity during login (typical SSO flow)
SELECT auth.update_user_identity_on_login(
    _provider_id := 'azure-ad-provider-uuid',
    _provider_user_id := 'azure-object-id',
    _provider_groups := ARRAY['Engineering', 'Senior Developers', 'Architects'],  -- Updated groups
    _provider_roles := ARRAY['TechLead', 'Architect'],  -- New role
    _provider_data := '{
        "title": "Principal Software Architect",
        "last_promotion": "2024-01-01"
    }'::jsonb
);

Switch Active Identity

-- User switches to different authentication method
DO $$
BEGIN
    -- Clear current last-used flag
    UPDATE auth.user_identity
    SET is_last_used = false
    WHERE user_id = 'user-uuid';

    -- Set new last-used identity
    UPDATE auth.user_identity
    SET
        is_last_used = true,
        last_login_at = now()
    WHERE user_id = 'user-uuid'
      AND provider_id = 'windows-ad-provider-uuid';

    -- Clear permission cache (permissions may change)
    PERFORM unsecure.clear_permission_cache('user-uuid');
END $$;

Identity Maintenance

-- Disable identity (e.g., user left Azure AD but still has Windows access)
SELECT auth.disable_user_identity(
    _user_identity_id := 'identity-uuid'
);

-- Re-enable identity
SELECT auth.enable_user_identity(
    _user_identity_id := 'identity-uuid'
);

Permission Resolution with Identities

Current Identity Lookup

-- Get user's current identity and groups
SELECT
    u.username,
    u.display_name,
    p.name as current_provider,
    ui.provider_groups,
    ui.provider_roles,
    ui.last_login_at
FROM auth.user_info u
JOIN auth.user_identity ui ON u.user_id = ui.user_id AND ui.is_last_used = true
JOIN auth.provider p ON ui.provider_id = p.provider_id
WHERE u.user_id = 'user-uuid';

Group Mapping Resolution

-- Find internal groups user belongs to via external mappings
WITH user_external_groups AS (
    SELECT
        ui.user_id,
        unnest(ui.provider_groups) as external_group,
        unnest(ui.provider_roles) as external_role,
        ui.provider_id
    FROM auth.user_identity ui
    WHERE ui.user_id = 'user-uuid'
      AND ui.is_last_used = true
      AND ui.is_active = true
)
SELECT DISTINCT
    ug.user_group_id,
    ug.code,
    ug.name,
    'external_mapping' as membership_source
FROM user_external_groups ueg
JOIN auth.user_group_mapping ugm ON (
    ugm.provider_id = ueg.provider_id AND (
        ugm.external_group_name = ueg.external_group OR
        ugm.external_role_name = ueg.external_role
    )
)
JOIN auth.user_group ug ON ugm.user_group_id = ug.user_group_id
WHERE ugm.is_active = true;

Identity Synchronization Patterns

Real-Time Synchronization

-- Process identity claims from JWT token or SAML assertion
CREATE OR REPLACE FUNCTION auth.process_identity_claims(
    _user_id uuid,
    _provider_id uuid,
    _claims jsonb
) RETURNS void AS $$
DECLARE
    v_groups text[];
    v_roles text[];
BEGIN
    -- Extract groups and roles from claims
    v_groups := ARRAY(SELECT jsonb_array_elements_text(_claims->'groups'));
    v_roles := ARRAY(SELECT jsonb_array_elements_text(_claims->'roles'));

    -- Update identity
    UPDATE auth.user_identity
    SET
        provider_groups = v_groups,
        provider_roles = v_roles,
        provider_data = _claims,
        is_last_used = true,
        last_login_at = now(),
        updated_at = now()
    WHERE user_id = _user_id
      AND provider_id = _provider_id;

    -- Clear last_used from other identities
    UPDATE auth.user_identity
    SET is_last_used = false
    WHERE user_id = _user_id
      AND provider_id != _provider_id;

    -- Clear permission cache
    PERFORM unsecure.clear_permission_cache(_user_id);
END;
$$ LANGUAGE plpgsql;

Batch Synchronization

-- Bulk update identities from external system
CREATE OR REPLACE FUNCTION auth.sync_provider_identities(
    _provider_id uuid,
    _identity_data jsonb[]
) RETURNS void AS $$
DECLARE
    v_data jsonb;
BEGIN
    FOREACH v_data IN ARRAY _identity_data
    LOOP
        UPDATE auth.user_identity
        SET
            provider_groups = ARRAY(SELECT jsonb_array_elements_text(v_data->'groups')),
            provider_roles = ARRAY(SELECT jsonb_array_elements_text(v_data->'roles')),
            provider_data = v_data->'data',
            updated_at = now()
        WHERE provider_id = _provider_id
          AND provider_user_id = v_data->>'provider_user_id';
    END LOOP;

    -- Clear all permission caches for this provider
    PERFORM unsecure.clear_permission_cache_by_provider(_provider_id);
END;
$$ LANGUAGE plpgsql;

Querying User Identities

All Identities for User

-- Complete identity overview
SELECT
    u.username,
    u.display_name,
    p.name as provider_name,
    p.provider_type,
    ui.provider_user_id,
    ui.provider_groups,
    ui.provider_roles,
    ui.is_active,
    ui.is_last_used,
    ui.last_login_at,
    ui.created_at
FROM auth.user_info u
JOIN auth.user_identity ui ON u.user_id = ui.user_id
JOIN auth.provider p ON ui.provider_id = p.provider_id
WHERE u.username = 'john.doe'
ORDER BY ui.last_login_at DESC NULLS LAST;

Identity Provider Statistics

-- Provider usage statistics
SELECT
    p.name as provider_name,
    COUNT(DISTINCT ui.user_id) as total_users,
    COUNT(DISTINCT ui.user_id) FILTER (WHERE ui.is_last_used = true) as active_users,
    COUNT(DISTINCT ui.user_id) FILTER (WHERE ui.last_login_at > now() - interval '30 days') as recent_logins,
    MAX(ui.last_login_at) as most_recent_login
FROM auth.provider p
LEFT JOIN auth.user_identity ui ON p.provider_id = ui.provider_id AND ui.is_active = true
WHERE p.is_active = true
GROUP BY p.provider_id
ORDER BY active_users DESC;

Security Considerations

Identity Validation

1. Provider Verification: Always validate tokens/assertions from providers
2. User Mapping: Ensure provider_user_id uniquely identifies users
3. Group Validation: Validate group/role claims against known values
4. Data Sanitization: Sanitize provider_data to prevent injection

Access Control

-- Validate user identity access
CREATE OR REPLACE FUNCTION auth.validate_identity_access(
    _user_id uuid,
    _identity_id uuid
) RETURNS boolean AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM auth.user_identity
        WHERE user_identity_id = _identity_id
          AND user_id = _user_id
    );
END;
$$ LANGUAGE plpgsql;

Troubleshooting Identity Issues

Common Problems

User has multiple last_used identities:

-- Fix multiple last_used flags
UPDATE auth.user_identity ui1
SET is_last_used = false
WHERE ui1.user_id = 'user-uuid'
  AND ui1.is_last_used = true
  AND EXISTS (
    SELECT 1 FROM auth.user_identity ui2
    WHERE ui2.user_id = ui1.user_id
      AND ui2.user_identity_id != ui1.user_identity_id
      AND ui2.is_last_used = true
  );

Identity without recent login:

-- Find stale identities
SELECT
    u.username,
    p.name as provider,
    ui.last_login_at,
    now() - ui.last_login_at as time_since_login
FROM auth.user_identity ui
JOIN auth.user_info u ON ui.user_id = u.user_id
JOIN auth.provider p ON ui.provider_id = p.provider_id
WHERE ui.last_login_at < now() - interval '90 days'
  OR ui.last_login_at IS NULL
ORDER BY ui.last_login_at NULLS FIRST;

What's Next

• Learn about User Data Extension patterns
• Review User Functions reference
• Explore Provider Configuration
• Understand Group Mappings