Permission Functions Reference

This reference provides comprehensive documentation for all permission-related functions in the PostgreSQL Permissions Model. Functions are organized by schema and security level, with detailed examples and usage patterns.

Function Categories by Schema

Core Permission Functions (auth schema)

These functions include full permission validation and audit logging.

Permission Checking Functions

auth.has_permission() - Single permission check with caching

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

-- Silent permission check (no exception)
SELECT auth.has_permission(
    'tenant-uuid', 'user-uuid', 'admin.settings', false
) as can_access_settings;

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
) -> boolean; -- Returns true if ALL permissions granted

-- Alternative: Check with partial matching
SELECT auth.has_any_permissions(
    'tenant-uuid', 'user-uuid',
    ARRAY['admin.users', 'admin.groups', 'admin.system']
) -> boolean; -- Returns true if ANY permission granted

auth.has_permission_level() - Level-based permission check

-- Check if user has permissions at specific level or higher
SELECT auth.has_permission_level(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid',
    _required_level := 'admin'::auth.permission_level
) -> boolean;

-- Usage in conditional logic
IF auth.has_permission_level('tenant-uuid', 'user-uuid', 'system') THEN
    -- User has system-level permissions
    PERFORM execute_system_function();
END IF;

Permission Assignment Functions

auth.assign_permission() - Core assignment function

-- Assign individual permission to user
SELECT auth.assign_permission(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid',
    _user_group_id := NULL,
    _perm_set_code := NULL,
    _perm_code := 'reports.financial',
    _granted := true,
    _expires_at := now() + interval '30 days'
) -> uuid; -- Returns assignment_id

-- Assign permission set to group
SELECT auth.assign_permission(
    'tenant-uuid', NULL, 'managers-group-uuid', 'MANAGER_PERMISSIONS', NULL
) -> uuid;

-- Explicit denial (overrides group permissions)
SELECT auth.assign_permission(
    'tenant-uuid', 'user-uuid', NULL, NULL, 'data.delete', false
) -> uuid;

auth.assign_permissions_bulk() - Bulk assignment

-- Assign multiple permissions at once
SELECT auth.assign_permissions_bulk(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid',
    _user_group_id := NULL,
    _assignments := '[
        {"perm_code": "users.view", "granted": true},
        {"perm_code": "reports.generate", "expires_at": "2024-12-31T23:59:59"},
        {"perm_set_code": "BASIC_USER", "granted": true}
    ]'::jsonb
) -> integer; -- Returns count of assignments created

auth.revoke_permission() - Permission revocation

-- Revoke specific permission
SELECT auth.revoke_permission(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid',
    _user_group_id := NULL,
    _perm_code := 'admin.users.delete',
    _reason := 'Security policy change'
) -> boolean;

-- Revoke all permissions for user
SELECT auth.revoke_all_user_permissions(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid',
    _reason := 'User deactivation'
) -> integer; -- Returns count of revoked permissions

Permission Set Management

auth.create_permission_set() - Create new permission set

-- Create tenant-specific permission set
SELECT auth.create_permission_set(
    _tenant_id := 'tenant-uuid',
    _perm_set_code := 'CUSTOM_ROLE',
    _name := 'Custom Role Permissions',
    _description := 'Permissions for custom business role',
    _is_system_set := false
) -> boolean;

auth.add_permission_to_set() - Add permission to set

-- Add individual permission to set
SELECT auth.add_permission_to_set(
    _tenant_id := 'tenant-uuid',
    _perm_set_code := 'CUSTOM_ROLE',
    _perm_code := 'reports.financial',
    _granted := true
) -> boolean;

-- Add another permission set to this set (inheritance)
SELECT auth.add_permission_to_set(
    'tenant-uuid', 'MANAGER_ROLE', 'BASIC_USER', true
) -> boolean;

auth.remove_permission_from_set() - Remove permission from set

-- Remove specific permission
SELECT auth.remove_permission_from_set(
    _tenant_id := 'tenant-uuid',
    _perm_set_code := 'CUSTOM_ROLE',
    _perm_code := 'admin.users.delete'
) -> boolean;

Permission Analysis Functions

auth.get_user_permissions() - Get all user permissions

-- Get comprehensive permission list for user
SELECT * FROM auth.get_user_permissions(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid',
    _include_inherited := true,
    _include_denied := false
) -> TABLE (
    perm_code text,
    permission_name text,
    source_type text,
    source_name text,
    granted boolean,
    assigned_at timestamptz,
    expires_at timestamptz
);

auth.get_effective_permissions() - Get resolved permissions

-- Get final effective permissions after resolution
SELECT * FROM auth.get_effective_permissions(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid',
    _permission_filter := 'admin%'  -- Optional filter pattern
) -> TABLE (
    perm_code text,
    has_permission boolean,
    source_priority integer,
    resolution_reason text
);

auth.analyze_permission_conflicts() - Detect conflicts

-- Find permission assignment conflicts
SELECT * FROM auth.analyze_permission_conflicts(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid'  -- NULL for all users
) -> TABLE (
    conflict_type text,
    perm_code text,
    conflicting_sources text[],
    recommendation text
);

Hierarchical Permission Functions

auth.get_permission_ancestors() - Find parent permissions

-- Get all permissions that grant this permission
SELECT * FROM auth.get_permission_ancestors('admin.users.create')
-> TABLE (perm_code text, name text, level integer);

-- Example result:
-- perm_code      | name                | level
-- admin.users    | User Administration | 1
-- admin          | Administration      | 2

auth.get_permission_descendants() - Find child permissions

-- Get all permissions granted by this permission
SELECT * FROM auth.get_permission_descendants('admin.users')
-> TABLE (perm_code text, name text, level integer);

-- Example result:
-- perm_code           | name         | level
-- admin.users.create  | Create Users | 1
-- admin.users.update  | Update Users | 1
-- admin.users.delete  | Delete Users | 1

auth.has_hierarchical_permission() - Hierarchical check

-- Check permission with hierarchy consideration
SELECT auth.has_hierarchical_permission(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid',
    _perm_code := 'admin.users.create',
    _check_ancestors := true  -- Check if user has 'admin' or 'admin.users'
) -> boolean;

Helper Functions (helpers schema)

Utility functions without permission checks or sensitive data access.

helpers.validate_permission_code() - Validate permission format

-- Validate permission code format
SELECT helpers.validate_permission_code('admin.users.create') -> boolean; -- true
SELECT helpers.validate_permission_code('invalid..code') -> boolean; -- false

helpers.generate_permission_tree() - Build permission hierarchy tree

-- Generate hierarchical permission structure
SELECT helpers.generate_permission_tree(
    _root_permission := 'admin',
    _max_depth := 3
) -> jsonb; -- Returns nested JSON tree structure

helpers.permission_code_depth() - Get permission hierarchy depth

-- Get depth of permission in hierarchy
SELECT helpers.permission_code_depth('admin.users.create.bulk') -> integer; -- 4

Internal Functions (internal schema)

Business logic functions without permission checks - use with caution.

internal.resolve_user_permissions_raw() - Raw permission resolution

-- Get raw permission data without security checks
SELECT * FROM internal.resolve_user_permissions_raw(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid'
) -> TABLE (
    perm_code text,
    source_type text,
    source_id uuid,
    granted boolean,
    priority integer
);
-- WARNING: Use only in trusted application contexts

internal.bulk_assign_permissions_unsafe() - Bulk assignment without validation

-- High-performance bulk assignment (bypasses validation)
SELECT internal.bulk_assign_permissions_unsafe(
    _assignments := '[
        {"tenant_id": "...", "user_id": "...", "perm_code": "users.view"},
        {"tenant_id": "...", "user_id": "...", "perm_code": "reports.view"}
    ]'::jsonb
) -> integer;
-- WARNING: Only use in migration or trusted batch operations

Unsecure Functions (unsecure schema)

Security and system administration 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',
    _reason := 'Permission assignment changed'
) -> integer; -- Returns count of cleared cache entries

unsecure.rebuild_permission_cache() - Rebuild permission cache

-- Rebuild permission cache for all users
SELECT unsecure.rebuild_permission_cache(
    _tenant_id := 'tenant-uuid',  -- NULL for all tenants
    _force_refresh := true
) -> integer; -- Returns count of cache entries created

unsecure.audit_permission_assignments() - Security audit

-- Comprehensive permission assignment audit
SELECT * FROM unsecure.audit_permission_assignments(
    _tenant_id := 'tenant-uuid',
    _include_system_permissions := false,
    _days_back := 90
) -> TABLE (
    audit_type text,
    user_id uuid,
    username text,
    perm_code text,
    issue_description text,
    risk_level text,
    recommendation text
);

unsecure.emergency_revoke_permissions() - Emergency security action

-- Emergency revocation of permissions (security incident)
SELECT unsecure.emergency_revoke_permissions(
    _permission_pattern := 'admin.system%',
    _reason := 'Security incident response',
    _except_users := ARRAY['emergency-admin-uuid']
) -> integer; -- Returns count of revoked permissions

Common Usage Patterns

Application Permission Checks

-- Standard application permission check pattern
CREATE OR REPLACE FUNCTION app.secure_business_function(
    _tenant_id uuid,
    _user_id uuid,
    _operation_data jsonb
) RETURNS jsonb AS $$
DECLARE
    v_result jsonb;
BEGIN
    -- 1. Check basic permission
    PERFORM auth.has_permission(_tenant_id, _user_id, 'business.function.execute');

    -- 2. Check additional permissions based on data
    IF _operation_data ? 'financial_data' THEN
        PERFORM auth.has_permission(_tenant_id, _user_id, 'finance.data.access');
    END IF;

    -- 3. Execute business logic
    v_result := jsonb_build_object(
        'status', 'success',
        'data', 'business function result'
    );

    -- 4. Log the operation
    PERFORM auth.create_auth_event(
        _tenant_id, _user_id, '50100', 'Business function executed',
        jsonb_build_object('operation', 'secure_business_function')
    );

    RETURN v_result;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Conditional Permission Assignment

-- Smart permission assignment based on user attributes
CREATE OR REPLACE FUNCTION auth.assign_role_based_permissions(
    _tenant_id uuid,
    _user_id uuid,
    _role_data jsonb
) RETURNS integer AS $$
DECLARE
    v_assignments jsonb := '[]'::jsonb;
    v_count integer;
BEGIN
    -- Build role-based assignments
    CASE _role_data->>'department'
        WHEN 'Engineering' THEN
            v_assignments := v_assignments || '[
                {"perm_set_code": "DEVELOPER_PERMISSIONS"},
                {"perm_code": "api.development"},
                {"perm_code": "data.read"}
            ]'::jsonb;

        WHEN 'Finance' THEN
            v_assignments := v_assignments || '[
                {"perm_set_code": "FINANCE_PERMISSIONS"},
                {"perm_code": "reports.financial"},
                {"perm_code": "budgets.manage"}
            ]'::jsonb;
    END CASE;

    -- Add level-based permissions
    IF (_role_data->>'level')::integer >= 8 THEN
        v_assignments := v_assignments || '[
            {"perm_code": "admin.users.view"},
            {"perm_code": "reports.confidential"}
        ]'::jsonb;
    END IF;

    -- Apply assignments
    SELECT auth.assign_permissions_bulk(_tenant_id, _user_id, NULL, v_assignments)
    INTO v_count;

    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

Permission Validation Middleware

-- Middleware pattern for API permission validation
CREATE OR REPLACE FUNCTION auth.validate_api_permission(
    _tenant_id uuid,
    _user_id uuid,
    _http_method text,
    _endpoint_path text
) RETURNS boolean AS $$
DECLARE
    v_required_permission text;
    v_has_permission boolean := false;
BEGIN
    -- Map HTTP method and path to permission
    v_required_permission := CASE
        WHEN _endpoint_path ~ '^/api/v1/users' AND _http_method = 'GET' THEN 'api.users.read'
        WHEN _endpoint_path ~ '^/api/v1/users' AND _http_method = 'POST' THEN 'api.users.create'
        WHEN _endpoint_path ~ '^/api/v1/users' AND _http_method = 'PUT' THEN 'api.users.update'
        WHEN _endpoint_path ~ '^/api/v1/users' AND _http_method = 'DELETE' THEN 'api.users.delete'
        WHEN _endpoint_path ~ '^/api/v1/reports' THEN 'api.reports.access'
        WHEN _endpoint_path ~ '^/api/v1/admin' THEN 'api.admin.access'
        ELSE 'api.basic.access'
    END;

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

    -- Log API access attempt
    PERFORM auth.create_auth_event(
        _tenant_id, _user_id,
        CASE WHEN v_has_permission THEN '50200' ELSE '50201' END, -- API_ACCESS_GRANTED/DENIED
        'API access: ' || _http_method || ' ' || _endpoint_path,
        jsonb_build_object(
            'method', _http_method,
            'path', _endpoint_path,
            'required_permission', v_required_permission,
            'granted', v_has_permission
        )
    );

    RETURN v_has_permission;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Bulk Permission Operations

-- Efficient bulk permission checks for UI components
CREATE OR REPLACE FUNCTION auth.check_ui_permissions(
    _tenant_id uuid,
    _user_id uuid,
    _ui_components text[]
) RETURNS TABLE (
    component text,
    can_view boolean,
    can_edit boolean,
    can_delete boolean
) AS $$
DECLARE
    v_component text;
    v_permissions_to_check text[] := ARRAY[]::text[];
    v_permission_results jsonb;
BEGIN
    -- Build list of all permissions to check
    FOREACH v_component IN ARRAY _ui_components
    LOOP
        v_permissions_to_check := v_permissions_to_check || ARRAY[
            'ui.' || v_component || '.view',
            'ui.' || v_component || '.edit',
            'ui.' || v_component || '.delete'
        ];
    END LOOP;

    -- Bulk check all permissions
    SELECT jsonb_object_agg(perm_code, has_permission)
    INTO v_permission_results
    FROM (
        SELECT
            perm,
            auth.has_permission(_tenant_id, _user_id, perm, false) as has_permission
        FROM unnest(v_permissions_to_check) AS perm
    ) AS perm_checks(perm_code, has_permission);

    -- Return organized results
    FOREACH v_component IN ARRAY _ui_components
    LOOP
        RETURN QUERY
        SELECT
            v_component,
            COALESCE((v_permission_results->('ui.' || v_component || '.view'))::boolean, false),
            COALESCE((v_permission_results->('ui.' || v_component || '.edit'))::boolean, false),
            COALESCE((v_permission_results->('ui.' || v_component || '.delete'))::boolean, false);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Error Handling and Debugging

Permission Error Functions

-- Custom permission error handling
CREATE OR REPLACE FUNCTION auth.throw_permission_denied(
    _tenant_id uuid,
    _user_id uuid,
    _perm_code text,
    _context text DEFAULT NULL
) RETURNS void AS $$
DECLARE
    v_error_code text := '42501'; -- Insufficient privilege
    v_error_message text;
    v_user_info record;
BEGIN
    -- Get user context for error message
    SELECT username, display_name INTO v_user_info
    FROM auth.user_info
    WHERE user_id = _user_id;

    v_error_message := format(
        'User %s (%s) denied access to permission "%s" in tenant %s',
        COALESCE(v_user_info.display_name, 'Unknown'),
        COALESCE(v_user_info.username, _user_id::text),
        _perm_code,
        _tenant_id
    );

    IF _context IS NOT NULL THEN
        v_error_message := v_error_message || '. Context: ' || _context;
    END IF;

    -- Log security event
    PERFORM auth.create_auth_event(
        _tenant_id, _user_id, '52001', -- PERMISSION_DENIED
        'Permission denied: ' || _perm_code,
        jsonb_build_object(
            'perm_code', _perm_code,
            'context', _context,
            'error_code', v_error_code
        )
    );

    RAISE EXCEPTION USING
        ERRCODE = v_error_code,
        MESSAGE = v_error_message,
        DETAIL = 'Required permission: ' || _perm_code;
END;
$$ LANGUAGE plpgsql;

Debug Functions

-- Debug permission resolution
CREATE OR REPLACE FUNCTION auth.debug_permission_resolution(
    _tenant_id uuid,
    _user_id uuid,
    _perm_code text
) RETURNS jsonb AS $$
DECLARE
    v_debug_info jsonb := '{}';
    v_user_perms jsonb;
    v_group_perms jsonb;
    v_hierarchy_check jsonb;
    v_final_result boolean;
BEGIN
    -- Get user direct permissions
    SELECT jsonb_agg(
        jsonb_build_object(
            'perm_code', up.perm_code,
            'perm_set_code', up.perm_set_code,
            'granted', up.granted,
            'expires_at', up.expires_at,
            'source', 'direct_assignment'
        )
    )
    INTO v_user_perms
    FROM auth.user_permission up
    WHERE up.tenant_id = _tenant_id
      AND up.user_id = _user_id;

    -- Get group permissions
    SELECT jsonb_agg(
        jsonb_build_object(
            'group_name', ug.name,
            'perm_code', up.perm_code,
            'perm_set_code', up.perm_set_code,
            'granted', up.granted,
            'source', 'group_assignment'
        )
    )
    INTO v_group_perms
    FROM auth.user_group_member ugm
    JOIN auth.user_group ug ON ugm.user_group_id = ug.user_group_id
    JOIN auth.user_permission up ON ug.user_group_id = up.user_group_id
    WHERE ugm.user_id = _user_id
      AND ugm.is_active = true
      AND ug.tenant_id = _tenant_id
      AND up.tenant_id = _tenant_id;

    -- Check hierarchy
    SELECT jsonb_build_object(
        'requested_permission', _perm_code,
        'hierarchy_matches', jsonb_agg(
            jsonb_build_object(
                'parent_perm', p.perm_code,
                'covers_requested', (_perm_code::ltree <@ p.perm_path)
            )
        )
    )
    INTO v_hierarchy_check
    FROM auth.permission p
    WHERE _perm_code::ltree <@ p.perm_path
      AND p.is_active = true;

    -- Get final result
    v_final_result := auth.has_permission(_tenant_id, _user_id, _perm_code, false);

    RETURN jsonb_build_object(
        'user_id', _user_id,
        'tenant_id', _tenant_id,
        'requested_permission', _perm_code,
        'final_result', v_final_result,
        'user_direct_permissions', COALESCE(v_user_perms, '[]'),
        'group_permissions', COALESCE(v_group_perms, '[]'),
        'hierarchy_analysis', COALESCE(v_hierarchy_check, '{}'),
        'debug_timestamp', now()
    );
END;
$$ LANGUAGE plpgsql;

Function Security Notes

Usage Guidelines by Schema

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

Performance Considerations

1. Use Caching: Prefer auth.has_permission_cached() for repeated checks
2. Bulk Operations: Use bulk functions for multiple permissions
3. Materialized Views: Use pre-computed views for read-heavy workloads
4. Index Usage: Ensure proper indexes on permission tables

Security Best Practices

1. Always Validate Context: Check tenant access before permission checks
2. Use SECURITY DEFINER: For functions that need elevated privileges
3. Log Security Events: Use auth.create_auth_event() for audit trails
4. Handle Errors Gracefully: Use appropriate error codes and messages

What's Next

• Review Permission Model for architectural overview
• Explore Permission Assignment patterns
• Learn about Permission Caching optimization
• See Permission Check Examples for practical usage