User System Overview

The PostgreSQL Permissions Model implements a sophisticated user management system designed around three core tables that provide flexibility, extensibility, and multi-provider authentication support. This system separates user identity, authentication providers, and custom data to support complex enterprise scenarios.

Three-Table User Architecture

The user system is built on three main tables that work together to provide comprehensive user management:

graph LR
    subgraph Core["Core User Management"]
        UI["auth.user_info<br/>- user_id<br/>- username<br/>- email<br/>- display_name<br/>- user_type"]
    end

    subgraph Identity["Identity Provider Integration"]
        UID["auth.user_identity<br/>- user_identity_id<br/>- user_id<br/>- provider_id<br/>- provider_user_id<br/>- provider_groups<br/>- is_last_used"]

        P["auth.provider<br/>- provider_id<br/>- code<br/>- name<br/>- provider_type"]
    end

    subgraph Data["Extensible User Data"]
        UD["auth.user_data<br/>- user_id<br/>- employee_number<br/>- custom fields..."]
    end

    UI --> UID
    UI --> UD
    UID --> P

Core User Management (auth.user_info)

The auth.user_info table stores essential user information that remains consistent across all authentication providers.

Table Structure

CREATE TABLE auth.user_info (
    user_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    username text UNIQUE NOT NULL,
    email text,
    display_name text NOT NULL,
    user_type text DEFAULT 'human' CHECK (user_type IN ('human', 'api')),
    is_active boolean DEFAULT true,
    is_locked boolean DEFAULT false,
    password_hash text,  -- For database authentication
    created_at timestamptz DEFAULT now(),
    updated_at timestamptz DEFAULT now()
);

Key Functions

-- User registration and lifecycle
SELECT auth.register_user(
    _tenant_id := NULL,  -- Users are global
    _username := 'john.doe',
    _email := 'john@company.com',
    _display_name := 'John Doe',
    _password := 'secure_password'  -- Optional for external auth
);

-- User status management
SELECT auth.enable_user(_user_id);
SELECT auth.disable_user(_user_id);
SELECT auth.lock_user(_user_id);
SELECT auth.unlock_user(_user_id);

-- Password management
SELECT auth.update_user_password(_user_id, _new_password);

User Types

Human Users (user_type = 'human'): - Regular users who authenticate via login forms - Can have multiple identity providers - Support interactive authentication flows

API Users (user_type = 'api'): - Created automatically when API keys are generated - Used for service-to-service authentication - Non-interactive authentication only

Identity Provider Management (auth.user_identity)

The auth.user_identity table manages multiple authentication providers per user, enabling seamless integration with various identity systems.

Multi-Provider Support

A single user can authenticate through multiple providers:

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]

Last-Used Identity Logic

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

-- Update identity as last used (happens during login)
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(),
    provider_groups = ARRAY['Engineering', 'Team Leads'],
    provider_roles = ARRAY['Manager']
WHERE user_id = 'user-uuid'
  AND provider_id = 'azure-ad-provider-uuid';

Key Functions

-- Create new identity for existing user
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['TeamLead'],
    _provider_data := '{"department": "Engineering"}'::jsonb
);

-- User provisioning from external provider
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'],
    _provider_roles := ARRAY['Employee']
);

Extensible User Data (auth.user_data)

The auth.user_data table provides flexible storage for application-specific user attributes.

Two Extension Approaches

Approach 1: Modify user_data table

-- Add custom columns to user_data table
ALTER TABLE auth.user_data
ADD COLUMN employee_number text,
ADD COLUMN department text,
ADD COLUMN hire_date date,
ADD COLUMN has_children boolean DEFAULT false,
ADD COLUMN is_casual_driver boolean DEFAULT false;

-- Update user data
SELECT auth.update_user_data(
    _user_id := 'user-uuid',
    _data := '{
        "employee_number": "EMP001",
        "department": "Engineering",
        "hire_date": "2024-01-15",
        "has_children": true
    }'::jsonb
);

Approach 2: Create separate tables

-- Create application-specific user table
CREATE TABLE app.employee_details (
    user_id uuid PRIMARY KEY REFERENCES auth.user_info(user_id),
    employee_number text UNIQUE,
    department text,
    hire_date date,
    manager_user_id uuid REFERENCES auth.user_info(user_id),
    created_at timestamptz DEFAULT now()
);

-- Link to user system
INSERT INTO app.employee_details (user_id, employee_number, department, hire_date)
VALUES ('user-uuid', 'EMP001', 'Engineering', '2024-01-15');

User System Integration Patterns

Permission Resolution with Identity Providers

flowchart TD
    A[User Requests Action] --> B[Get User's Last Used Identity]
    B --> C[Get Provider Groups/Roles]
    C --> D[Map to Internal Groups]
    D --> E[Get Group Permissions]
    E --> F[Combine with Direct Permissions]
    F --> G[Check Required Permission]
    G -->|Has Permission| H[Allow Action]
    G -->|Lacks Permission| I[Deny Action]

User Lifecycle Management

-- Complete user setup example
DO $$
DECLARE
    v_user_id uuid;
    v_tenant_id uuid := 'company-tenant-uuid';
BEGIN
    -- 1. Register user
    v_user_id := auth.register_user(
        NULL,  -- Users are global
        'jane.smith',
        'jane.smith@company.com',
        'Jane Smith',
        NULL  -- No password for external auth
    );

    -- 2. Add to tenant
    INSERT INTO auth.tenant_user (tenant_id, user_id)
    VALUES (v_tenant_id, v_user_id);

    -- 3. Create Azure AD identity
    PERFORM auth.create_user_identity(
        v_user_id,
        'azure-ad-provider-uuid',
        'azure-object-id-for-jane',
        ARRAY['Domain Users', 'Engineering'],
        ARRAY['Developer'],
        '{"department": "Engineering", "title": "Software Developer"}'::jsonb
    );

    -- 4. Add to default groups
    PERFORM auth.add_user_to_default_groups(v_tenant_id, v_user_id);

    -- 5. Set custom user data
    PERFORM auth.update_user_data(
        v_user_id,
        '{"employee_number": "EMP002", "start_date": "2024-02-01"}'::jsonb
    );

    RAISE NOTICE 'User setup complete for: %', v_user_id;
END $$;

Common User Management Scenarios

Single Sign-On Integration

-- User logs in via Azure AD
-- System updates their identity and groups
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'],
    _provider_roles := ARRAY['TechLead'],
    _provider_data := '{"title": "Senior Software Engineer"}'::jsonb
);

Multi-Provider User

-- Check all identities for a user
SELECT
    ui.provider_user_id,
    p.name as provider_name,
    ui.provider_groups,
    ui.is_last_used,
    ui.last_login_at
FROM auth.user_identity ui
JOIN auth.provider p ON ui.provider_id = p.provider_id
WHERE ui.user_id = 'user-uuid'
ORDER BY ui.last_login_at DESC;

User Deactivation

-- Proper user deactivation
DO $$
DECLARE
    v_user_id uuid := 'user-to-deactivate';
BEGIN
    -- Disable user
    PERFORM auth.disable_user(v_user_id);

    -- Disable all identities
    UPDATE auth.user_identity
    SET is_active = false
    WHERE user_id = v_user_id;

    -- Clear permission cache
    PERFORM unsecure.clear_permission_cache(v_user_id);

    -- Log deactivation event
    PERFORM auth.create_auth_event(
        NULL, -- No specific tenant
        v_user_id,
        '50008', -- User deactivated
        'User account deactivated',
        '{"reason": "employee terminated"}'::jsonb
    );
END $$;

Performance Considerations

Indexing Strategy

-- Essential indexes for user system
CREATE INDEX idx_user_identity_last_used ON auth.user_identity(user_id) WHERE is_last_used = true;
CREATE INDEX idx_user_identity_provider_lookup ON auth.user_identity(provider_id, provider_user_id);
CREATE INDEX idx_user_data_lookup ON auth.user_data(user_id);
CREATE INDEX idx_tenant_user_active ON auth.tenant_user(tenant_id, is_active);

Query Optimization

-- Efficient user lookup with current identity
SELECT
    u.user_id,
    u.username,
    u.display_name,
    ui.provider_groups,
    ui.provider_roles,
    p.name as current_provider
FROM auth.user_info u
LEFT JOIN auth.user_identity ui ON u.user_id = ui.user_id AND ui.is_last_used = true
LEFT JOIN auth.provider p ON ui.provider_id = p.provider_id
WHERE u.username = 'john.doe';

Security Considerations

User Data Protection

1. Sensitive Data: Store sensitive user data in encrypted columns
2. Access Control: Limit access to user_data table
3. Audit Logging: Log all user data modifications
4. Data Retention: Implement data retention policies

Identity Security

1. Provider Validation: Validate all identity provider data
2. Group Mapping: Secure group mapping configurations
3. Token Handling: Properly handle and validate provider tokens
4. Session Management: Implement proper session timeout

What's Next

• Explore User Identity provider integration
• Learn about User Data Extension patterns
• Review User Functions reference
• Understand Groups integration