Authentication¶

The PostgreSQL Permissions Model provides comprehensive authentication mechanisms that support multiple authentication methods, identity providers, and service accounts. The system is designed around pure PostgreSQL functions that handle user registration, login processing, identity management, and API key authentication.

Authentication Architecture¶

The authentication system supports three primary authentication patterns:

graph TB
    subgraph "Human User Authentication"
        A[User Login Request] --> B[Identity Provider Verification]
        B --> C[User Creation/Update]
        C --> D[Identity Linking]
        D --> E[Permission Resolution]
    end

    subgraph "API Key Authentication"
        F[API Request] --> G[API Key Validation]
        G --> H[API User Context]
        H --> I[Permission Check]
    end

    subgraph "Database Authentication"
        J[Direct Login] --> K[Password Verification]
        K --> L[User Context]
        L --> M[Permission Resolution]
    end

    E --> N[Authenticated Session]
    I --> N
    M --> N

Core Authentication Functions¶

User Registration and Lifecycle¶

User Registration¶

-- Create new user account
SELECT auth.register_user(
    _tenant_id := NULL,  -- Users are global, tenant assignment happens separately
    _username := 'john.doe',
    _email := 'john.doe@company.com',
    _display_name := 'John Doe',
    _password := 'secure_password123'  -- Optional for external auth users
) as new_user_id;

The auth.register_user() function: - Creates a new user in auth.user_info - Generates unique user ID - Optionally sets password hash for database authentication - Returns the new user's UUID - Performs permission validation before creation - Logs user creation event

User Status Management¶

-- Enable/disable user accounts
SELECT auth.enable_user(_user_id := 'user-uuid');
SELECT auth.disable_user(_user_id := 'user-uuid');

-- Lock/unlock for security (temporary suspension)
SELECT auth.lock_user(_user_id := 'user-uuid');
SELECT auth.unlock_user(_user_id := 'user-uuid');

Password Management¶

-- Update user password
SELECT auth.update_user_password(
    _user_id := 'user-uuid',
    _new_password := 'new_secure_password456'
);

Identity Provider Integration¶

The system supports multiple identity providers per user through the auth.user_identity table.

Create Provider Identity¶

-- Link user to external identity provider
SELECT auth.create_user_identity(
    _user_id := 'user-uuid',
    _provider_id := 'azure-ad-provider-uuid',
    _provider_user_id := 'azure-object-id',
    _provider_groups := ARRAY['Engineering', 'Managers'],
    _provider_roles := ARRAY['Developer', 'TeamLead'],
    _provider_data := '{"department": "Engineering", "title": "Senior Developer"}'::jsonb
) as identity_id;

Just-in-Time User Provisioning¶

-- Create user from external provider (if doesn't exist)
SELECT auth.ensure_user_from_provider(
    _provider_id := 'azure-ad-provider-uuid',
    _provider_user_id := 'azure-object-id',
    _email := 'newuser@company.com',
    _display_name := 'New User',
    _provider_groups := ARRAY['Domain Users', 'Engineering'],
    _provider_roles := ARRAY['Employee'],
    _provider_data := '{"department": "Engineering"}'::jsonb
) as user_id;

This function: - Checks if user already exists with this provider identity - Creates new user if needed - Links identity to existing or new user - Returns user_id (existing or newly created)

-- Update user identity during SSO login
SELECT auth.update_user_identity_on_login(
    _provider_id := 'azure-ad-provider-uuid',
    _provider_user_id := 'azure-object-id',
    _provider_groups := ARRAY['Engineering', 'Senior Staff'],  -- Updated groups
    _provider_roles := ARRAY['TechLead'],  -- New role
    _provider_data := '{"title": "Senior Developer", "department": "Engineering"}'::jsonb
);

This function: - Updates provider groups and roles with latest data - Marks this identity as the "last used" for permission calculation - Logs the login event - Updates the user's provider data

API Key Authentication¶

API keys create "API users" with user_type_code = 'api' for consistent permission handling.

API Key Creation¶

-- Create API key and associated API user
SELECT auth.create_api_key(
    _created_by := 'admin_user',
    _user_id := 1,  -- ID of human user creating the key
    _title := 'Service Integration API',
    _description := 'API key for service integration',
    _api_secret := 'secure-api-key-secret-123',
    _expire_at := now() + interval '90 days',
    _tenant_id := 1
) as api_key_result;

-- Returns: {api_key_id, api_key, api_secret}

API Key Validation¶

-- Complete API key validation
SELECT auth.validate_api_key(
    _api_key := 'SERVICE_API_KEY_123',
    _api_secret := 'provided-secret-from-request',
    _tenant_id := 1  -- Optional: validate against specific tenant
) as validation_result;

-- Returns record with:
-- - api_key_id: int
-- - user_id: uuid
-- - tenant_id: int
-- - is_valid: boolean
-- - error_message: text (if validation fails)

The validation process: 1. Finds API key record 2. Verifies secret hash using cryptographic comparison 3. Checks expiration date 4. Validates API user status (active/inactive) 5. Checks tenant access if specified 6. Logs validation attempt (success or failure)

Authentication Flows¶

-- Complete SSO authentication process
CREATE OR REPLACE FUNCTION process_sso_login(
    _provider_code text,
    _provider_user_id text,
    _user_claims jsonb
) RETURNS uuid AS $$
DECLARE
    v_user_id uuid;
    v_provider_id uuid;
BEGIN
    -- Get provider ID
    SELECT provider_id INTO v_provider_id
    FROM auth.provider
    WHERE code = _provider_code;

    -- Create/update user from provider
    v_user_id := auth.ensure_user_from_provider(
        v_provider_id,
        _provider_user_id,
        _user_claims->>'email',
        _user_claims->>'name',
        ARRAY(SELECT jsonb_array_elements_text(_user_claims->'groups')),
        ARRAY(SELECT jsonb_array_elements_text(_user_claims->'roles')),
        _user_claims
    );

    -- Update identity with latest login data
    PERFORM auth.update_user_identity_on_login(
        v_provider_id,
        _provider_user_id,
        ARRAY(SELECT jsonb_array_elements_text(_user_claims->'groups')),
        ARRAY(SELECT jsonb_array_elements_text(_user_claims->'roles')),
        _user_claims
    );

    -- Log successful login
    PERFORM auth.create_auth_event(
        NULL,  -- No specific tenant for login
        v_user_id,
        '50006',  -- LOGIN_SUCCESS
        'SSO login successful',
        jsonb_build_object('provider', _provider_code)
    );

    RETURN v_user_id;
END;
$$ LANGUAGE plpgsql;

API Request Authentication¶

-- Authenticate and authorize API request
CREATE OR REPLACE FUNCTION authenticate_api_request(
    _api_key text,
    _api_secret text,
    _tenant_id int,
    _required_permission text DEFAULT NULL
) RETURNS jsonb AS $$
DECLARE
    v_validation record;
BEGIN
    -- Step 1: Validate API key
    SELECT * FROM auth.validate_api_key(_api_key, _api_secret, _tenant_id)
    INTO v_validation;

    IF NOT v_validation.is_valid THEN
        RETURN jsonb_build_object(
            'authenticated', false,
            'error', v_validation.error_message
        );
    END IF;

    -- Step 2: Check specific permission if required
    IF _required_permission IS NOT NULL THEN
        IF NOT auth.has_permission(_tenant_id::uuid, v_validation.user_id, _required_permission, false) THEN
            RETURN jsonb_build_object(
                'authenticated', true,
                'authorized', false,
                'error', 'insufficient_permissions'
            );
        END IF;
    END IF;

    -- Step 3: Return success with user context
    RETURN jsonb_build_object(
        'authenticated', true,
        'authorized', true,
        'user_id', v_validation.user_id,
        'tenant_id', v_validation.tenant_id,
        'api_key_id', v_validation.api_key_id
    );
END;
$$ LANGUAGE plpgsql;

Multi-Provider Authentication¶

Users can authenticate through multiple providers simultaneously:

graph TB
    U[User: john.doe] --> I1[Identity 1<br/>Windows AD<br/>DOMAIN\\john.doe]
    U --> I2[Identity 2<br/>Azure AD<br/>john@company.com<br/>🟢 Last Used]
    U --> I3[Identity 3<br/>Google OAuth<br/>john.doe@gmail.com]

    I1 --> G1[Provider Groups:<br/>Domain Users<br/>Developers]
    I2 --> G2[Provider Groups:<br/>Engineering<br/>Team Leads<br/>Provider Roles:<br/>Manager]
    I3 --> G3[Provider Groups:<br/>None]

    G2 --> P[Current Permissions<br/>Based on Last Used Identity]

Last-Used Identity Logic¶

The system uses the most recently used identity for permission calculation:

-- Check user's current identity and permissions
SELECT
    ui.user_id,
    ui.username,
    ui.display_name,
    uid.provider_groups,
    uid.provider_roles,
    p.name as current_provider,
    uid.last_login_at
FROM auth.user_info ui
JOIN auth.user_identity uid ON ui.user_id = uid.user_id AND uid.is_last_used = true
JOIN auth.provider p ON uid.provider_id = p.provider_id
WHERE ui.username = 'john.doe';

Database Authentication¶

For users who authenticate directly against the database:

-- Verify database user credentials
CREATE OR REPLACE FUNCTION verify_database_login(
    _username text,
    _password text
) RETURNS uuid AS $$
DECLARE
    v_user_id uuid;
    v_password_hash text;
BEGIN
    -- Get user and password hash
    SELECT user_id, password_hash INTO v_user_id, v_password_hash
    FROM auth.user_info
    WHERE username = _username
      AND is_active = true
      AND is_locked = false;

    IF v_user_id IS NULL THEN
        PERFORM auth.create_auth_event(
            NULL, NULL, '52001', 'Login failed - user not found',
            jsonb_build_object('username', _username)
        );
        RETURN NULL;
    END IF;

    -- Verify password
    IF v_password_hash != crypt(_password, v_password_hash) THEN
        PERFORM auth.create_auth_event(
            NULL, v_user_id, '52002', 'Login failed - invalid password',
            jsonb_build_object('username', _username)
        );
        RETURN NULL;
    END IF;

    -- Log successful login
    PERFORM auth.create_auth_event(
        NULL, v_user_id, '50001', 'Database login successful',
        jsonb_build_object('username', _username)
    );

    RETURN v_user_id;
END;
$$ LANGUAGE plpgsql;

Authentication Security¶

Password Security¶

-- Generate secure password hash
SELECT crypt('user_password', gen_salt('bf', 12)) as password_hash;

-- Verify password
SELECT password_hash = crypt('provided_password', stored_password_hash) as valid;

API Key Security¶

-- Generate secure API secret
SELECT encode(gen_random_bytes(32), 'base64') as secure_secret;

-- Generate API key
SELECT 'API_' || encode(gen_random_bytes(16), 'hex') as api_key;

-- Hash API secret for storage
CREATE OR REPLACE FUNCTION auth.generate_api_secret_hash(_secret text)
RETURNS text AS $$
BEGIN
    RETURN crypt(_secret, gen_salt('bf', 12));
END;
$$ LANGUAGE plpgsql;

Rate Limiting¶

-- Track authentication attempts
CREATE TABLE IF NOT EXISTS auth.auth_attempts (
    attempt_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    identifier text NOT NULL,  -- username, API key, or IP
    attempt_type text NOT NULL,  -- 'login', 'api_key', 'password_reset'
    success boolean NOT NULL,
    attempt_time timestamptz DEFAULT now(),
    ip_address inet,
    user_agent text
);

-- Check rate limits
CREATE OR REPLACE FUNCTION check_auth_rate_limit(
    _identifier text,
    _attempt_type text,
    _time_window interval DEFAULT '15 minutes',
    _max_attempts int DEFAULT 5
) RETURNS boolean AS $$
DECLARE
    v_attempt_count int;
BEGIN
    SELECT COUNT(*) INTO v_attempt_count
    FROM auth.auth_attempts
    WHERE identifier = _identifier
      AND attempt_type = _attempt_type
      AND attempt_time > now() - _time_window
      AND success = false;

    RETURN v_attempt_count < _max_attempts;
END;
$$ LANGUAGE plpgsql;

Authentication Events and Audit¶

All authentication activities are logged in the auth.user_event table:

-- Common authentication event codes
-- 50001: LOGIN_SUCCESS
-- 50002: USER_CREATED
-- 50006: SSO_LOGIN_SUCCESS
-- 52001: AUTH_FAILED
-- 52002: INVALID_PASSWORD
-- 52301: API_KEY_INVALID

-- Query authentication events
SELECT
    ue.event_at,
    ue.event_code,
    ue.event_description,
    u.username,
    u.display_name,
    ue.event_data
FROM auth.user_event ue
LEFT JOIN auth.user_info u ON ue.user_id = u.user_id
WHERE ue.event_code IN ('50001', '50006', '52001', '52002')
ORDER BY ue.event_at DESC
LIMIT 100;

Best Practices¶

User Registration¶

External Auth Preferred: Use external identity providers when possible
Email Verification: Implement email verification for database users
Strong Passwords: Enforce password complexity for database authentication
Account Activation: Use activation workflow for new accounts

Identity Management¶

Provider Trust: Validate identity provider configurations
Group Mapping: Map external groups to internal permissions carefully
Identity Linking: Handle identity linking conflicts gracefully
Provider Failover: Plan for identity provider outages

API Authentication¶

Secret Rotation: Implement regular API key rotation
Scope Limitation: Assign minimal necessary permissions
Expiration: Set appropriate expiration dates
Audit Trail: Monitor API key usage patterns

Security Monitoring¶

Failed Attempts: Monitor and alert on authentication failures
Unusual Activity: Detect unusual login patterns
Rate Limiting: Implement rate limiting on authentication endpoints
Session Management: Implement proper session timeout and invalidation

What's Next¶

Learn about Authorization and permission checking
Explore Audit Logging for security events
Review Best Practices for security
Understand Error Handling for authentication failures

Authentication¶

Authentication Architecture¶

Core Authentication Functions¶

User Registration and Lifecycle¶

User Registration¶

User Status Management¶

Password Management¶

Identity Provider Integration¶

Create Provider Identity¶

Just-in-Time User Provisioning¶

Login Processing¶

API Key Authentication¶

API Key Creation¶

API Key Validation¶

Authentication Flows¶

SSO Login Flow¶

API Request Authentication¶

Multi-Provider Authentication¶

Last-Used Identity Logic¶

Database Authentication¶

Authentication Security¶

Password Security¶

API Key Security¶

Rate Limiting¶

Authentication Events and Audit¶

Best Practices¶

User Registration¶

Identity Management¶

API Authentication¶

Security Monitoring¶

What's Next¶