User Functions Reference

The PostgreSQL Permissions Model provides a comprehensive set of functions for user management, organized across different schemas based on security and functionality. This reference covers all user-related functions with examples and usage patterns.

Function Categories

Core User Management Functions (auth schema)

These functions include full permission validation and audit logging.

User Registration and Lifecycle

auth.register_user() - Create new user account

-- Basic user registration
SELECT auth.register_user(
    _tenant_id := NULL,  -- Users are global, but can specify initial tenant
    _username := 'john.doe',
    _email := 'john.doe@company.com',
    _display_name := 'John Doe',
    _password := 'secure_password123'  -- Optional for external auth users
);
-- Returns: user_id (uuid)

-- Register user without password (for external auth)
SELECT auth.register_user(
    NULL,
    'jane.smith',
    'jane.smith@company.com',
    'Jane Smith',
    NULL
);

auth.enable_user() / auth.disable_user() - User status management

-- Enable user account
SELECT auth.enable_user(_user_id := 'user-uuid');

-- Disable user account (blocks login, keeps data)
SELECT auth.disable_user(_user_id := 'user-uuid');

auth.lock_user() / auth.unlock_user() - Account security

-- Lock user account (temporary security measure)
SELECT auth.lock_user(_user_id := 'user-uuid');

-- Unlock user account
SELECT auth.unlock_user(_user_id := 'user-uuid');

auth.update_user_password() - Password management

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

User Data Management

auth.update_user_data() - Update extensible user data

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

User Identity Management

auth.create_user_identity() - Add provider identity to user

-- Create Azure AD identity
SELECT auth.create_user_identity(
    _user_id := 'user-uuid',
    _provider_id := 'azure-ad-provider-uuid',
    _provider_user_id := 'azure-object-id',
    _provider_groups := ARRAY['Domain Users', 'Engineering'],
    _provider_roles := ARRAY['Developer'],
    _provider_data := '{"department": "Engineering"}'::jsonb
);
-- Returns: user_identity_id (uuid)

auth.ensure_user_from_provider() - JIT 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 := 'new-user-object-id',
    _email := 'newuser@company.com',
    _display_name := 'New User',
    _provider_groups := ARRAY['Domain Users'],
    _provider_roles := ARRAY['Employee'],
    _provider_data := '{"department": "Sales"}'::jsonb
);
-- Returns: user_id (uuid) - existing or newly created

auth.update_user_identity_on_login() - Update identity during SSO login

-- Update user identity with latest provider data
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"}'::jsonb
);

auth.enable_user_identity() / auth.disable_user_identity() - Identity status

-- Disable specific identity (e.g., user left Azure AD)
SELECT auth.disable_user_identity(_user_identity_id := 'identity-uuid');

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

Group Membership

auth.add_user_to_default_groups() - Add user to default tenant groups

-- Add user to default groups for tenant
SELECT auth.add_user_to_default_groups(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid'
);

Permission Checking Functions

auth.has_permission() - Single permission check

-- Check if user has specific permission
SELECT auth.has_permission(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid',
    _perm_code := 'users.create_user',
    _throw_err := true  -- Default: true (throws error if denied)
);
-- Returns: boolean

-- Silent permission check (no exception)
IF auth.has_permission('tenant-uuid', 'user-uuid', 'admin.settings', false) THEN
    -- User has permission
    PERFORM execute_admin_function();
ELSE
    -- User lacks permission, handle gracefully
    PERFORM show_access_denied_message();
END IF;

auth.has_permissions() - Multiple permission check

-- Check multiple permissions at once
SELECT auth.has_permissions(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid',
    _perm_codes := ARRAY['users.read', 'users.update', 'users.delete'],
    _throw_err := true
);
-- Returns: boolean (true if ALL permissions granted)

Audit and Event Functions

auth.create_auth_event() - Create audit log entry

-- Log custom authentication event
SELECT auth.create_auth_event(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid',
    _event_code := '50001',  -- Custom event code
    _event_description := 'User completed profile setup',
    _event_data := '{"profile_completion": 100}'::jsonb
);

Helper Functions (helpers schema)

Utility functions without permission checks or sensitive data access.

helpers.generate_random_string() - Generate random strings

-- Generate random password or codes
SELECT helpers.generate_random_string(32) as random_password;
SELECT helpers.generate_random_string(8) as user_code;

helpers.generate_user_code() - Generate user-specific codes

-- Generate code from email or username
SELECT helpers.generate_user_code('john.doe@company.com') as user_code;

Internal Functions (internal schema)

Business logic functions without permission checks - use with caution.

internal.create_user_record() - Direct user creation

-- Create user without permission validation (trusted context)
SELECT internal.create_user_record(
    _username := 'system.user',
    _email := 'system@company.com',
    _display_name := 'System User',
    _user_type := 'api'
);
-- Use only in migration scripts or trusted application contexts

Unsecure Functions (unsecure schema)

Security-related system functions - DBA/Admin only.

unsecure.clear_permission_cache() - Clear user permission cache

-- Clear cached permissions for user (after permission changes)
SELECT unsecure.clear_permission_cache(_user_id := 'user-uuid');

unsecure.bulk_import_users_from_stage() - Bulk user import

-- Import users from staging table (migration/bulk operations)
SELECT unsecure.bulk_import_users_from_stage();

Common Usage Patterns

User Registration Flow

-- Complete user setup process
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
        'new.employee',
        'new.employee@company.com',
        'New Employee',
        NULL  -- External auth, no password
    );

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

    -- 3. Create provider identity
    PERFORM auth.create_user_identity(
        v_user_id,
        'azure-ad-provider-uuid',
        'azure-object-id',
        ARRAY['Domain Users', 'New Hires'],
        ARRAY['Employee'],
        '{"department": "Onboarding"}'::jsonb
    );

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

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

    -- 6. Log registration event
    PERFORM auth.create_auth_event(
        v_tenant_id,
        v_user_id,
        '50002',  -- USER_CREATED
        'New user registration completed',
        '{"source": "hr_system"}'::jsonb
    );

    RAISE NOTICE 'User created: %', v_user_id;
END $$;

Login Processing Flow

-- Process SSO login
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;

    -- Update/create 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;

User Deactivation Flow

-- Comprehensive user deactivation
CREATE OR REPLACE FUNCTION deactivate_user_complete(
    _user_id uuid,
    _reason text DEFAULT 'user_requested'
) RETURNS void AS $$
BEGIN
    -- 1. Disable user account
    PERFORM auth.disable_user(_user_id);

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

    -- 3. Remove from all tenant memberships
    UPDATE auth.tenant_user
    SET is_active = false
    WHERE user_id = _user_id;

    -- 4. Clear permission cache
    PERFORM unsecure.clear_permission_cache(_user_id);

    -- 5. Log deactivation
    PERFORM auth.create_auth_event(
        NULL,
        _user_id,
        '52010',  -- USER_DEACTIVATED
        'User account deactivated',
        jsonb_build_object('reason', _reason, 'deactivated_at', now())
    );

    RAISE NOTICE 'User % deactivated successfully', _user_id;
END;
$$ LANGUAGE plpgsql;

Permission-Protected Business Logic

-- Example business function with permission checks
CREATE OR REPLACE FUNCTION create_user_report(
    _tenant_id uuid,
    _requesting_user_id uuid,
    _report_type text
) RETURNS text AS $$
DECLARE
    v_result text;
BEGIN
    -- Check permission first
    PERFORM auth.has_permission(_tenant_id, _requesting_user_id, 'reports.generate');

    -- Additional permission for sensitive reports
    IF _report_type = 'salary_report' THEN
        PERFORM auth.has_permission(_tenant_id, _requesting_user_id, 'reports.salary_data');
    END IF;

    -- Business logic here
    v_result := 'Report generated: ' || _report_type;

    -- Log the operation
    PERFORM auth.create_auth_event(
        _tenant_id,
        _requesting_user_id,
        '50100',  -- REPORT_GENERATED
        'User report generated',
        jsonb_build_object('report_type', _report_type)
    );

    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

Error Handling Patterns

Graceful Permission Checking

-- Handle permissions gracefully without exceptions
CREATE OR REPLACE FUNCTION get_user_dashboard_data(
    _tenant_id uuid,
    _user_id uuid
) RETURNS jsonb AS $$
DECLARE
    v_result jsonb := '{}'::jsonb;
BEGIN
    -- Always include basic data
    v_result := v_result || jsonb_build_object(
        'user_info', 'basic user dashboard data'
    );

    -- Conditionally add financial data
    IF auth.has_permission(_tenant_id, _user_id, 'dashboard.financial', false) THEN
        v_result := v_result || jsonb_build_object(
            'financial', 'financial dashboard data'
        );
    END IF;

    -- Conditionally add admin data
    IF auth.has_permission(_tenant_id, _user_id, 'dashboard.admin', false) THEN
        v_result := v_result || jsonb_build_object(
            'admin', 'admin dashboard data'
        );
    END IF;

    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

Function Security Notes

Schema-Based Security

• auth.* functions: Always use these for application logic
• helpers.* functions: Safe utility functions, no sensitive access
• internal.* functions: Use only in trusted contexts
• unsecure.* functions: DBA/system administration only

Permission Validation

-- All auth functions follow this pattern
CREATE OR REPLACE FUNCTION auth.example_function(
    _tenant_id uuid,
    _user_id uuid,
    _operation_data text
) RETURNS void AS $$
BEGIN
    -- 1. Validate tenant access
    IF NOT EXISTS (
        SELECT 1 FROM auth.tenant_user
        WHERE tenant_id = _tenant_id AND user_id = _user_id AND is_active = true
    ) THEN
        PERFORM auth.throw_no_tenant_access(_tenant_id, _user_id);
    END IF;

    -- 2. Check specific permission
    PERFORM auth.has_permission(_tenant_id, _user_id, 'required.permission');

    -- 3. Business logic
    -- ... operation implementation ...

    -- 4. Audit logging
    PERFORM auth.create_auth_event(_tenant_id, _user_id, '50001', 'Operation completed');
END;
$$ LANGUAGE plpgsql;

What's Next

• Learn about Groups and user membership
• Explore Permission Assignment to users
• Review API Keys and API users
• Understand Multi-Tenancy user management