Authorization

The PostgreSQL Permissions Model implements a sophisticated authorization system based on hierarchical permissions, permission sets, and multi-level permission checking. All authorization decisions are made at the database level using pure PostgreSQL functions, ensuring consistent security across all application layers.

Authorization Architecture

The authorization system operates on several key principles:

graph TB
    subgraph "Permission Resolution Process"
        A[Authorization Request] --> B[User Identity Resolution]
        B --> C[Group Membership Calculation]
        C --> D[Permission Collection]
        D --> E[Hierarchical Permission Check]
        E --> F[Cache Update]
        F --> G[Authorization Decision]
    end

    subgraph "Permission Sources"
        H[Direct User Permissions]
        I[Group Permissions]
        J[Provider Group Mappings]
        K[Permission Set Assignments]
    end

    D --> H
    D --> I
    D --> J
    D --> K

    subgraph "Decision Types"
        G --> L[Grant Access]
        G --> M[Deny Access]
        G --> N[Conditional Access]
    end

Core Authorization Functions

Primary Permission Checking

auth.has_permission() - Single Permission Check

-- Check if user has specific permission (throws exception by default)
SELECT auth.has_permission(
    _target_user_id := 123,  -- bigint user ID
    _perm_code := 'users.create_user',
    _tenant_id := 1,  -- int tenant ID, defaults to 1
    _throw_err := true  -- boolean, defaults to true
) as has_permission;

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

The function performs: 1. Identity Resolution: Determines user's current identity (last-used provider) 2. Group Resolution: Calculates group membership from provider and direct assignments 3. Permission Collection: Gathers all permissions from direct assignments and groups 4. Hierarchical Check: Checks permission hierarchy (e.g., 'admin' grants 'admin.users') 5. Cache Management: Updates permission cache for performance 6. Audit Logging: Logs permission checks if auditing is enabled

auth.has_permissions() - Multiple Permission Check

-- Check multiple permissions at once
SELECT auth.has_permissions(
    _target_user_id := 123,  -- bigint user ID
    _perm_codes := ARRAY['users.read', 'users.update', 'users.delete'],
    _tenant_id := 1,  -- int tenant ID, defaults to 1
    _throw_err := true  -- boolean, defaults to true
) as has_all_permissions;
-- Returns: true only if ALL permissions are granted

Permission Assignment System

Direct Permission Assignment

-- Assign permission set to user
SELECT * FROM auth.assign_permission(
    _created_by := 'admin_username',
    _user_id := 1,  -- requesting user ID (for permission check)
    _user_group_id := NULL,  -- NULL for user assignment
    _target_user_id := 123,  -- user receiving the permission
    _perm_set_code := 'USER_MANAGER',
    _perm_code := NULL,  -- NULL when assigning permission set
    _tenant_id := 1
);

-- Assign individual permission to user
SELECT * FROM auth.assign_permission(
    _created_by := 'admin_username',
    _user_id := 1,  -- requesting user ID
    _user_group_id := NULL,
    _target_user_id := 123,
    _perm_set_code := NULL,  -- NULL when assigning individual permission
    _perm_code := 'users.create_user',
    _tenant_id := 1
);

Group Permission Assignment

-- Assign permissions to group (all members inherit)
SELECT * FROM auth.assign_permission(
    _created_by := 'admin_username',
    _user_id := 1,  -- requesting user ID
    _user_group_id := 5,  -- group receiving the permission
    _target_user_id := NULL,  -- NULL for group assignment
    _perm_set_code := 'DEVELOPER_ACCESS',
    _perm_code := NULL,
    _tenant_id := 1
);

Permission Revocation

Unassign Permissions

-- Remove specific permission assignment
SELECT * FROM auth.unassign_permission(
    _deleted_by := 'admin_username',
    _user_id := 1,  -- requesting user ID (for permission check)
    _assignment_id := 456,  -- specific assignment to remove
    _tenant_id := 1
);

Advanced Authorization Patterns

Conditional Authorization

-- Authorization based on data ownership
CREATE OR REPLACE FUNCTION check_data_access_permission(
    _tenant_id uuid,
    _user_id uuid,
    _resource_id uuid,
    _action text
) RETURNS boolean AS $$
DECLARE
    v_resource_owner_id uuid;
    v_has_admin boolean;
BEGIN
    -- Check if user is admin (can access any resource)
    v_has_admin := auth.has_permission(_user_id, 'admin', _tenant_id, false);

    IF v_has_admin THEN
        RETURN true;
    END IF;

    -- Check if user owns the resource
    SELECT owner_user_id INTO v_resource_owner_id
    FROM app.resources
    WHERE resource_id = _resource_id AND tenant_id = _tenant_id;

    IF v_resource_owner_id = _user_id THEN
        -- Owner can perform any action
        RETURN true;
    END IF;

    -- Check specific permission for non-owned resources
    RETURN auth.has_permission(_user_id, 'resources.' || _action || '_others', _tenant_id, false);
END;
$$ LANGUAGE plpgsql;

Time-Based Authorization

-- Authorization with time restrictions
CREATE OR REPLACE FUNCTION check_time_restricted_permission(
    _tenant_id uuid,
    _user_id uuid,
    _perm_code text,
    _allowed_start time DEFAULT '08:00:00',
    _allowed_end time DEFAULT '18:00:00'
) RETURNS boolean AS $$
DECLARE
    v_current_time time;
    v_has_base_permission boolean;
BEGIN
    -- Check base permission first
    v_has_base_permission := auth.has_permission(_user_id, _perm_code, _tenant_id, false);

    IF NOT v_has_base_permission THEN
        RETURN false;
    END IF;

    -- Check if user has override permission (for 24/7 access)
    IF auth.has_permission(_user_id, _perm_code || '.no_time_restriction', _tenant_id, false) THEN
        RETURN true;
    END IF;

    -- Check time restriction
    v_current_time := now()::time;
    RETURN v_current_time BETWEEN _allowed_start AND _allowed_end;
END;
$$ LANGUAGE plpgsql;

Context-Aware Authorization

-- Authorization based on request context
CREATE OR REPLACE FUNCTION authorize_api_request(
    _tenant_id uuid,
    _user_id uuid,
    _endpoint text,
    _method text,
    _ip_address inet,
    _user_agent text
) RETURNS jsonb AS $$
DECLARE
    v_base_permission text;
    v_has_permission boolean;
    v_risk_score int := 0;
    v_result jsonb;
BEGIN
    -- Map endpoint and method to permission
    v_base_permission := CASE
        WHEN _endpoint LIKE '/api/users%' AND _method = 'GET' THEN 'users.read'
        WHEN _endpoint LIKE '/api/users%' AND _method = 'POST' THEN 'users.create'
        WHEN _endpoint LIKE '/api/users%' AND _method = 'PUT' THEN 'users.update'
        WHEN _endpoint LIKE '/api/users%' AND _method = 'DELETE' THEN 'users.delete'
        ELSE 'api.access'
    END;

    -- Check base permission
    v_has_permission := auth.has_permission(_user_id, v_base_permission, _tenant_id, false);

    IF NOT v_has_permission THEN
        RETURN jsonb_build_object(
            'authorized', false,
            'reason', 'insufficient_permissions',
            'required_permission', v_base_permission
        );
    END IF;

    -- Calculate risk score based on context
    IF NOT (_ip_address <<= '10.0.0.0/8' OR _ip_address <<= '192.168.0.0/16') THEN
        v_risk_score := v_risk_score + 30;  -- External IP
    END IF;

    IF _user_agent NOT LIKE '%known-app%' THEN
        v_risk_score := v_risk_score + 20;  -- Unknown user agent
    END IF;

    -- High risk operations require additional verification
    IF v_risk_score > 40 AND _method IN ('POST', 'PUT', 'DELETE') THEN
        IF NOT auth.has_permission(_user_id, v_base_permission || '.high_risk', _tenant_id, false) THEN
            RETURN jsonb_build_object(
                'authorized', false,
                'reason', 'high_risk_context',
                'risk_score', v_risk_score,
                'required_permission', v_base_permission || '.high_risk'
            );
        END IF;
    END IF;

    RETURN jsonb_build_object(
        'authorized', true,
        'permission_used', v_base_permission,
        'risk_score', v_risk_score
    );
END;
$$ LANGUAGE plpgsql;

Permission Analysis

The system provides permission resolution through multiple sources as shown in the architecture diagrams above. To understand how permissions are resolved for a specific user, you can query the underlying tables directly.

Multi-Provider Permission Resolution

The system resolves permissions from multiple sources:

graph TB
    subgraph "Permission Sources"
        A[Direct User Permissions]
        B[Internal Group Memberships]
        C[Provider Group Mappings]
        D[Hybrid Group Memberships]
    end

    subgraph "Resolution Process"
        E[Collect All Sources]
        F[Apply Permission Sets]
        G[Resolve Individual Permissions]
        H[Check Hierarchical Grants]
        I[Final Permission Decision]
    end

    A --> E
    B --> E
    C --> E
    D --> E

    E --> F
    F --> G
    G --> H
    H --> I

Provider Group Resolution

-- See how provider groups resolve to permissions
WITH user_identity AS (
    SELECT
        ui.provider_groups,
        ui.provider_roles,
        p.name as provider_name
    FROM auth.user_identity ui
    JOIN auth.provider p ON ui.provider_id = p.provider_id
    WHERE ui.user_id = 123  -- bigint user ID
      AND ui.is_last_used = true
),
group_mappings AS (
    SELECT
        ugm.provider_group_name,
        ugm.provider_role_name,
        ug.name as internal_group_name
    FROM auth.user_group_mapping ugm
    JOIN auth.user_group ug ON ugm.user_group_id = ug.user_group_id
    WHERE ugm.tenant_id = 1  -- int tenant ID
)
SELECT
    ui.provider_name,
    unnest(ui.provider_groups) as provider_group,
    gm.internal_group_name,
    'group_mapping' as source_type
FROM user_identity ui
LEFT JOIN group_mappings gm ON gm.provider_group_name = ANY(ui.provider_groups)

UNION ALL

SELECT
    ui.provider_name,
    unnest(ui.provider_roles) as provider_role,
    gm.internal_group_name,
    'role_mapping' as source_type
FROM user_identity ui
LEFT JOIN group_mappings gm ON gm.provider_role_name = ANY(ui.provider_roles);

Authorization Patterns for Applications

Function-Level Authorization

-- Example business function with authorization
CREATE OR REPLACE FUNCTION app.create_user_report(
    _tenant_id uuid,
    _requesting_user_id uuid,
    _report_type text,
    _target_user_id uuid DEFAULT NULL
) RETURNS jsonb AS $$
DECLARE
    v_result jsonb;
BEGIN
    -- Base permission check
    PERFORM auth.has_permission(_requesting_user_id, 'reports.generate', _tenant_id);

    -- Additional checks based on report type
    CASE _report_type
        WHEN 'salary_report' THEN
            PERFORM auth.has_permission(_requesting_user_id, 'reports.salary_data', _tenant_id);
        WHEN 'performance_review' THEN
            PERFORM auth.has_permission(_requesting_user_id, 'reports.performance_data', _tenant_id);
    END CASE;

    -- If targeting specific user, check additional permission
    IF _target_user_id IS NOT NULL AND _target_user_id != _requesting_user_id THEN
        PERFORM auth.has_permission(_requesting_user_id, 'reports.other_users', _tenant_id);
    END IF;

    -- Generate report (business logic)
    v_result := jsonb_build_object(
        'report_type', _report_type,
        'generated_by', _requesting_user_id,
        'generated_at', now()
    );

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

    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

Row-Level Authorization

-- Authorization filter for data access
CREATE OR REPLACE FUNCTION app.get_filtered_data(
    _tenant_id uuid,
    _user_id uuid,
    _table_name text
) RETURNS TABLE(data jsonb) AS $$
DECLARE
    v_has_admin boolean;
    v_query text;
BEGIN
    -- Check if user has admin access (sees all data)
    v_has_admin := auth.has_permission(_user_id, 'admin.view_all_data', _tenant_id, false);

    IF v_has_admin THEN
        v_query := format('SELECT row_to_json(t) FROM %I t WHERE tenant_id = %L',
                         _table_name, _tenant_id);
    ELSE
        -- Regular users see only their own data
        v_query := format('SELECT row_to_json(t) FROM %I t WHERE tenant_id = %L AND created_by = %L',
                         _table_name, _tenant_id, _user_id);
    END IF;

    RETURN QUERY EXECUTE v_query;
END;
$$ LANGUAGE plpgsql;

Security Audit and Monitoring

Authorization Event Monitoring

-- Monitor authorization failures
SELECT
    DATE_TRUNC('hour', ue.event_at) as hour_bucket,
    COUNT(*) as denial_count,
    COUNT(DISTINCT ue.user_id) as unique_users,
    jsonb_agg(DISTINCT ue.event_data->>'required_permission') as denied_permissions
FROM auth.user_event ue
WHERE ue.event_code = '52002'  -- PERMISSION_DENIED
  AND ue.event_at > now() - interval '24 hours'
GROUP BY hour_bucket
ORDER BY hour_bucket DESC;

Permission Assignment Audit

-- Audit recent permission changes
SELECT
    pa.assigned_at,
    pa.assigned_by,
    u.username,
    COALESCE(ps.code, p.full_code::text) as permission,
    pa.granted,
    pa.expires_at,
    CASE
        WHEN pa.target_user_id IS NOT NULL THEN 'Direct'
        ELSE 'Via Group: ' || ug.name
    END as assignment_type
FROM auth.permission_assignment pa
LEFT JOIN auth.user_info u ON pa.target_user_id = u.user_id
LEFT JOIN auth.user_group ug ON pa.user_group_id = ug.user_group_id
LEFT JOIN auth.perm_set ps ON pa.perm_set_id = ps.perm_set_id
LEFT JOIN auth.permission p ON pa.permission_id = p.permission_id
WHERE pa.assigned_at > now() - interval '7 days'
ORDER BY pa.assigned_at DESC;

Best Practices

Permission Design

1. Hierarchical Structure: Use hierarchical permissions (e.g., 'admin.users.create')
2. Granular Permissions: Create specific permissions for different operations
3. Permission Sets: Group related permissions into reusable sets
4. Least Privilege: Grant minimum necessary permissions

Performance Optimization

1. Batch Checks: Use has_permissions() for multiple permission checks
2. Index Strategy: Maintain proper indexes on permission tables
3. Minimize Function Calls: Cache permission results in application logic when appropriate
4. Use Silent Checks: Use _throw_err := false for conditional logic without exceptions

Security Practices

1. Regular Audits: Monitor permission assignments and access patterns
2. Time-Based Permissions: Implement expiration dates for temporary access
3. Context Awareness: Consider request context in authorization decisions
4. Principle of Least Privilege: Regularly review and minimize permissions

Error Handling

1. Graceful Degradation: Handle authorization failures gracefully
2. User Feedback: Provide meaningful error messages
3. Audit Trail: Log all authorization attempts and failures
4. Exception Handling: Use _throw_err parameter appropriately

What's Next

• Learn about Audit Logging for tracking authorization events
• Explore Best Practices for security implementation
• Review Error Handling for authorization failures
• Understand API Keys for service authorization