Identity Provider Configuration

The PostgreSQL Permissions Model supports integration with multiple external identity providers through a flexible provider system. Users can have multiple identities (Windows Auth, Azure AD, Google, etc.), with permissions calculated from their most recently used provider.

Core Concepts

Multi-Identity Support

• Users can have multiple provider identities
• Each identity stores provider-specific information
• The "last used" identity determines current permissions
• Seamless switching between authentication methods

Provider Integration

• Windows Authentication (domain/GUID)
• Azure Active Directory (OAuth/SAML)
• Google, Facebook, GitHub (OAuth)
• LDAP/Active Directory
• KeyCloak, Auth0, Okta (SAML/OIDC)
• Custom providers via API

Database Structure

Provider Configuration

CREATE TABLE auth.provider (
    provider_id uuid PRIMARY KEY,
    code text UNIQUE NOT NULL,
    name text NOT NULL,
    provider_type text NOT NULL, -- 'windows', 'azuread', 'google', etc.
    configuration jsonb, -- Provider-specific settings
    is_active boolean DEFAULT true,
    created_at timestamptz DEFAULT now(),
    updated_at timestamptz DEFAULT now()
);

User Identities

CREATE TABLE auth.user_identity (
    user_identity_id uuid PRIMARY KEY,
    user_id uuid REFERENCES auth.user_info,
    provider_id uuid REFERENCES auth.provider,
    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(),
    UNIQUE (provider_id, provider_user_id)
);

Creating Identity Providers

Windows Authentication Provider

-- Configure Windows Auth provider
INSERT INTO auth.provider (
    provider_id,
    code,
    name,
    provider_type,
    configuration
) VALUES (
    gen_random_uuid(),
    'WINDOWS_AUTH',
    'Windows Authentication',
    'windows',
    '{
        "domain": "COMPANY.LOCAL",
        "trusted_domains": ["PARTNER.COM"],
        "group_prefix": "DOMAIN\\",
        "sync_groups": true
    }'::jsonb
);

Azure Active Directory Provider

-- Configure Azure AD provider
INSERT INTO auth.provider (
    provider_id,
    code,
    name,
    provider_type,
    configuration
) VALUES (
    gen_random_uuid(),
    'AZURE_AD',
    'Azure Active Directory',
    'azuread',
    '{
        "tenant_id": "12345678-1234-1234-1234-123456789012",
        "client_id": "app-registration-id",
        "authority": "https://login.microsoftonline.com/tenant-id",
        "group_claims": ["groups", "roles"],
        "sync_groups": true,
        "sync_roles": true
    }'::jsonb
);

Google OAuth Provider

-- Configure Google OAuth provider
INSERT INTO auth.provider (
    provider_id,
    code,
    name,
    provider_type,
    configuration
) VALUES (
    gen_random_uuid(),
    'GOOGLE_OAUTH',
    'Google OAuth',
    'google',
    '{
        "client_id": "google-app-client-id.apps.googleusercontent.com",
        "hosted_domain": "company.com",
        "sync_groups": false
    }'::jsonb
);

Generic SAML Provider

-- Configure SAML provider (Okta, Auth0, etc.)
INSERT INTO auth.provider (
    provider_id,
    code,
    name,
    provider_type,
    configuration
) VALUES (
    gen_random_uuid(),
    'OKTA_SAML',
    'Okta SAML',
    'saml',
    '{
        "sso_url": "https://company.okta.com/app/saml/xyz/sso/saml",
        "issuer": "http://www.okta.com/xyz",
        "certificate": "-----BEGIN CERTIFICATE-----...",
        "group_attribute": "groups",
        "role_attribute": "roles",
        "user_id_attribute": "uid"
    }'::jsonb
);

Managing User Identities

Create User Identity

-- Link user to external provider
SELECT auth.create_user_identity(
    _user_id := 'user-uuid',
    _provider_id := 'azure-ad-provider-uuid',
    _provider_user_id := 'azure-ad-object-id',
    _provider_groups := ARRAY['Domain Users', 'Developers', 'Managers'],
    _provider_roles := ARRAY['ApplicationDeveloper', 'TeamLead'],
    _provider_data := '{
        "email": "user@company.com",
        "department": "Engineering",
        "manager": "manager@company.com"
    }'::jsonb
);

Update Identity on Login

-- Update user identity after authentication
SELECT auth.update_user_identity_on_login(
    _provider_id := 'azure-ad-provider-uuid',
    _provider_user_id := 'azure-ad-object-id',
    _provider_groups := ARRAY['Domain Users', 'Developers', 'Senior Developers'],
    _provider_roles := ARRAY['ApplicationDeveloper', 'TeamLead', 'Architect'],
    _provider_data := '{
        "email": "user@company.com",
        "department": "Engineering",
        "title": "Senior Developer"
    }'::jsonb
);

Switch Active Identity

-- Mark identity as last used (affects permission calculation)
UPDATE auth.user_identity
SET is_last_used = false
WHERE user_id = 'user-uuid';

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

User Provisioning Patterns

Automatic User Creation

-- Ensure user exists from provider (creates if needed)
SELECT auth.ensure_user_from_provider(
    _provider_id := 'azure-ad-provider-uuid',
    _provider_user_id := 'azure-ad-object-id',
    _email := 'newuser@company.com',
    _display_name := 'New User',
    _provider_groups := ARRAY['Domain Users'],
    _provider_roles := ARRAY['Employee'],
    _provider_data := '{
        "department": "Sales",
        "hire_date": "2024-01-15"
    }'::jsonb
);

Just-In-Time (JIT) Provisioning

-- JIT provisioning function
CREATE OR REPLACE FUNCTION auth.jit_provision_user(
    _tenant_id uuid,
    _provider_id uuid,
    _provider_user_id text,
    _user_data jsonb
) RETURNS uuid AS $$
DECLARE
    v_user_id uuid;
    v_username text;
    v_email text;
    v_display_name text;
BEGIN
    -- Extract user info from provider data
    v_email := _user_data->>'email';
    v_display_name := _user_data->>'name';
    v_username := COALESCE(_user_data->>'preferred_username', v_email);

    -- Try to find existing user by identity
    SELECT ui.user_id INTO v_user_id
    FROM auth.user_identity uident
    JOIN auth.user_info ui ON uident.user_id = ui.user_id
    WHERE uident.provider_id = _provider_id
      AND uident.provider_user_id = _provider_user_id;

    -- Create user if doesn't exist
    IF v_user_id IS NULL THEN
        v_user_id := auth.register_user(
            _tenant_id,
            v_username,
            v_email,
            v_display_name,
            NULL -- No password for external users
        );

        -- Create identity
        PERFORM auth.create_user_identity(
            v_user_id,
            _provider_id,
            _provider_user_id,
            COALESCE((_user_data->>'groups')::text[], ARRAY[]::text[]),
            COALESCE((_user_data->>'roles')::text[], ARRAY[]::text[]),
            _user_data
        );

        -- Add to default groups
        PERFORM auth.add_user_to_default_groups(_tenant_id, v_user_id);
    END IF;

    RETURN v_user_id;
END;
$$ LANGUAGE plpgsql;

Group Mapping Integration

Map Provider Groups to Internal Groups

-- Map Azure AD groups to internal permissions
INSERT INTO auth.user_group_mapping (
    mapping_id,
    user_group_id,
    provider_id,
    external_group_name,
    is_active
) VALUES
    (gen_random_uuid(), 'admin-group-uuid', 'azure-ad-provider-uuid', 'Domain Admins', true),
    (gen_random_uuid(), 'dev-group-uuid', 'azure-ad-provider-uuid', 'Developers', true),
    (gen_random_uuid(), 'user-group-uuid', 'azure-ad-provider-uuid', 'Domain Users', true);

Role-Based Mapping

-- Map provider roles to internal groups
INSERT INTO auth.user_group_mapping (
    mapping_id,
    user_group_id,
    provider_id,
    external_role_name,
    is_active
) VALUES
    (gen_random_uuid(), 'manager-group-uuid', 'azure-ad-provider-uuid', 'Manager', true),
    (gen_random_uuid(), 'lead-group-uuid', 'azure-ad-provider-uuid', 'TeamLead', true);

Permission Resolution with Providers

Current User's Effective Groups

-- Get user's groups based on last used provider
WITH last_used_identity AS (
    SELECT
        ui.user_id,
        ui.provider_id,
        ui.provider_groups,
        ui.provider_roles
    FROM auth.user_identity ui
    WHERE ui.user_id = 'user-uuid'
      AND ui.is_last_used = true
      AND ui.is_active = true
),
provider_groups AS (
    -- Groups from external mappings
    SELECT DISTINCT
        ug.user_group_id,
        ug.code,
        'external' as source
    FROM last_used_identity lui
    JOIN auth.user_group_mapping ugm ON lui.provider_id = ugm.provider_id
    JOIN auth.user_group ug ON ugm.user_group_id = ug.user_group_id
    WHERE ugm.is_active = true
      AND (
          ugm.external_group_name = ANY(lui.provider_groups) OR
          ugm.external_role_name = ANY(lui.provider_roles)
      )

    UNION

    -- Direct group memberships
    SELECT
        ug.user_group_id,
        ug.code,
        'direct' as source
    FROM auth.user_group_member ugmem
    JOIN auth.user_group ug ON ugmem.user_group_id = ug.user_group_id
    WHERE ugmem.user_id = 'user-uuid'
)
SELECT * FROM provider_groups;

Check Permission with Provider Context

-- Permission check considers last used identity
CREATE OR REPLACE FUNCTION auth.has_permission_with_provider_context(
    _tenant_id uuid,
    _user_id uuid,
    _perm_code text
) RETURNS boolean AS $$
DECLARE
    v_has_permission boolean := false;
    v_provider_groups text[];
    v_provider_roles text[];
    v_provider_id uuid;
BEGIN
    -- Get user's last used identity
    SELECT
        provider_id,
        provider_groups,
        provider_roles
    INTO
        v_provider_id,
        v_provider_groups,
        v_provider_roles
    FROM auth.user_identity
    WHERE user_id = _user_id
      AND is_last_used = true
      AND is_active = true;

    -- Check permission using standard function
    -- (which internally uses the provider group mappings)
    RETURN auth.has_permission(_tenant_id, _user_id, _perm_code);
END;
$$ LANGUAGE plpgsql;

Provider-Specific Features

Windows Authentication

-- Handle Windows domain groups
CREATE OR REPLACE FUNCTION auth.process_windows_groups(
    _user_id uuid,
    _domain_groups text[]
) RETURNS void AS $$
DECLARE
    v_group text;
    v_clean_group text;
BEGIN
    FOREACH v_group IN ARRAY _domain_groups
    LOOP
        -- Remove domain prefix (DOMAIN\GroupName -> GroupName)
        v_clean_group := regexp_replace(v_group, '^[^\\]+\\', '');

        -- Update user identity with cleaned groups
        UPDATE auth.user_identity
        SET provider_groups = array_append(
            COALESCE(provider_groups, ARRAY[]::text[]),
            v_clean_group
        )
        WHERE user_id = _user_id
          AND provider_id = (
              SELECT provider_id FROM auth.provider
              WHERE code = 'WINDOWS_AUTH'
          );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Azure AD Token Processing

-- Process Azure AD JWT token claims
CREATE OR REPLACE FUNCTION auth.process_azuread_token(
    _user_id uuid,
    _token_claims jsonb
) RETURNS void AS $$
DECLARE
    v_provider_id uuid;
    v_groups text[];
    v_roles text[];
BEGIN
    -- Get Azure AD provider
    SELECT provider_id INTO v_provider_id
    FROM auth.provider
    WHERE code = 'AZURE_AD';

    -- Extract groups and roles from token
    v_groups := ARRAY(SELECT jsonb_array_elements_text(_token_claims->'groups'));
    v_roles := ARRAY(SELECT jsonb_array_elements_text(_token_claims->'roles'));

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

    -- Clear last_used flag from other identities
    UPDATE auth.user_identity
    SET is_last_used = false
    WHERE user_id = _user_id
      AND provider_id != v_provider_id;
END;
$$ LANGUAGE plpgsql;

Monitoring and Administration

Provider Usage Statistics

-- Provider usage report
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
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;

Identity Synchronization Status

-- Check for users with outdated identities
SELECT
    u.username,
    u.display_name,
    p.name as provider,
    ui.last_login_at,
    now() - ui.last_login_at as time_since_login
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 ui.is_last_used = true
  AND ui.last_login_at < now() - interval '90 days'
ORDER BY ui.last_login_at;

Best Practices

Security Considerations

1. Validate Tokens: Always verify provider tokens/assertions
2. Secure Configuration: Store provider secrets securely
3. Audit Changes: Log provider configuration changes
4. Regular Review: Audit provider mappings regularly

Performance Optimization

1. Cache Provider Data: Cache frequently accessed provider info
2. Index Identity Lookups: Index provider_user_id columns
3. Batch Updates: Group identity updates when possible
4. Monitor Performance: Track provider authentication times

User Experience

1. Seamless Switching: Allow users to switch between providers
2. Clear Feedback: Show current authentication method
3. Consistent Permissions: Ensure permission consistency across providers
4. Error Handling: Graceful handling of provider outages

What's Next

• Learn about Windows Authentication specifics
• Explore Azure AD integration details
• Understand External Providers configuration
• Review Group Mappings in detail