Permission Assignment

Permission assignment in the PostgreSQL Permissions Model provides flexible, secure methods for granting access rights to users and groups. The system supports both direct permission assignment and permission set assignment, with comprehensive audit trails and conflict resolution mechanisms.

Assignment Architecture

The permission assignment system operates through the auth.user_permission table, which serves as the central junction for all permission grants:

graph TB
    subgraph "Assignment Targets"
        USER["Users<br/>auth.user_info"]
        GROUP["Groups<br/>auth.user_group<br/>- Internal<br/>- External<br/>- Hybrid"]
    end

    subgraph "Permission Sources"
        PERM["Individual Permissions<br/>auth.permission<br/>e.g., users.create"]
        PERMSET["Permission Sets<br/>auth.permission_set<br/>e.g., ADMIN_PERMISSIONS"]
    end

    subgraph "Assignment Junction"
        ASSIGN["Permission Assignments<br/>auth.user_permission<br/>- user_id OR user_group_id<br/>- perm_code OR perm_set_code<br/>- granted: true/false<br/>- expires_at<br/>- tenant_id"]
    end

    subgraph "Resolution"
        RESOLVE["Permission Resolution<br/>auth.has_permission()<br/>- Direct assignments<br/>- Group memberships<br/>- Hierarchical inheritance<br/>- Caching"]
    end

    USER --> ASSIGN
    GROUP --> ASSIGN
    PERM --> ASSIGN
    PERMSET --> ASSIGN
    ASSIGN --> RESOLVE

Key Assignment Concepts:

1. Target Flexibility: Assign to users or groups (mutually exclusive)
2. Source Flexibility: Assign individual permissions or permission sets
3. Grant/Deny Support: Explicit grants and denials with precedence rules
4. Tenant Isolation: All assignments are tenant-specific
5. Expiration Support: Time-based permission expiration
6. Audit Trail: Complete tracking of who assigned what when

Core Assignment Functions

Basic Permission Assignment

-- Assign individual permission to user
CREATE OR REPLACE FUNCTION auth.assign_permission(
    _tenant_id uuid,
    _user_id uuid DEFAULT NULL,
    _user_group_id uuid DEFAULT NULL,
    _perm_set_code text DEFAULT NULL,
    _perm_code text DEFAULT NULL,
    _granted boolean DEFAULT true,
    _expires_at timestamptz DEFAULT NULL
) RETURNS uuid AS $$
DECLARE
    v_assignment_id uuid;
    v_assigned_by uuid;
BEGIN
    -- Validate parameters
    IF (_user_id IS NULL AND _user_group_id IS NULL) OR
       (_user_id IS NOT NULL AND _user_group_id IS NOT NULL) THEN
        RAISE EXCEPTION 'Must specify exactly one of user_id or user_group_id';
    END IF;

    IF (_perm_code IS NULL AND _perm_set_code IS NULL) OR
       (_perm_code IS NOT NULL AND _perm_set_code IS NOT NULL) THEN
        RAISE EXCEPTION 'Must specify exactly one of perm_code or perm_set_code';
    END IF;

    -- Validate tenant access for user assignments
    IF _user_id IS NOT NULL THEN
        IF NOT EXISTS (
            SELECT 1 FROM auth.tenant_user
            WHERE tenant_id = _tenant_id AND user_id = _user_id AND is_active = true
        ) THEN
            RAISE EXCEPTION 'User % does not have access to tenant %', _user_id, _tenant_id;
        END IF;
    END IF;

    -- Validate group belongs to tenant
    IF _user_group_id IS NOT NULL THEN
        IF NOT EXISTS (
            SELECT 1 FROM auth.user_group
            WHERE user_group_id = _user_group_id AND tenant_id = _tenant_id AND is_active = true
        ) THEN
            RAISE EXCEPTION 'Group % does not belong to tenant %', _user_group_id, _tenant_id;
        END IF;
    END IF;

    -- Get current user for audit trail
    v_assigned_by := COALESCE(_user_id, current_setting('auth.current_user_id', true)::uuid);

    -- Create assignment
    INSERT INTO auth.user_permission (
        tenant_id,
        user_id,
        user_group_id,
        perm_code,
        perm_set_code,
        granted,
        expires_at,
        assigned_by
    )
    VALUES (
        _tenant_id,
        _user_id,
        _user_group_id,
        _perm_code,
        _perm_set_code,
        _granted,
        _expires_at,
        v_assigned_by
    )
    ON CONFLICT (
        tenant_id,
        COALESCE(user_id, '00000000-0000-0000-0000-000000000000'::uuid),
        COALESCE(user_group_id, '00000000-0000-0000-0000-000000000000'::uuid),
        COALESCE(perm_code, ''),
        COALESCE(perm_set_code, '')
    )
    DO UPDATE SET
        granted = _granted,
        expires_at = _expires_at,
        assigned_at = now(),
        assigned_by = v_assigned_by
    RETURNING user_permission_id INTO v_assignment_id;

    -- Clear permission cache for affected users
    IF _user_id IS NOT NULL THEN
        PERFORM auth.clear_permission_cache(_user_id);
    ELSE
        -- Clear cache for all group members
        PERFORM auth.clear_group_members_cache(_user_group_id);
    END IF;

    -- Log assignment
    PERFORM auth.create_auth_event(
        _tenant_id,
        COALESCE(_user_id, v_assigned_by),
        '50030', -- PERMISSION_ASSIGNED
        'Permission assignment: ' || COALESCE(_perm_code, _perm_set_code),
        jsonb_build_object(
            'assignment_id', v_assignment_id,
            'target_type', CASE WHEN _user_id IS NOT NULL THEN 'user' ELSE 'group' END,
            'target_id', COALESCE(_user_id, _user_group_id),
            'permission', COALESCE(_perm_code, _perm_set_code),
            'granted', _granted,
            'expires_at', _expires_at
        )
    );

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

Bulk Assignment Operations

-- Assign multiple permissions at once
CREATE OR REPLACE FUNCTION auth.assign_permissions_bulk(
    _tenant_id uuid,
    _user_id uuid DEFAULT NULL,
    _user_group_id uuid DEFAULT NULL,
    _assignments jsonb -- Array of {perm_code?, perm_set_code?, granted?, expires_at?}
) RETURNS integer AS $$
DECLARE
    v_assignment jsonb;
    v_count integer := 0;
    v_assignment_id uuid;
BEGIN
    -- Validate target
    IF (_user_id IS NULL AND _user_group_id IS NULL) OR
       (_user_id IS NOT NULL AND _user_group_id IS NOT NULL) THEN
        RAISE EXCEPTION 'Must specify exactly one of user_id or user_group_id';
    END IF;

    -- Process each assignment
    FOR v_assignment IN SELECT jsonb_array_elements(_assignments)
    LOOP
        v_assignment_id := auth.assign_permission(
            _tenant_id,
            _user_id,
            _user_group_id,
            v_assignment->>'perm_set_code',
            v_assignment->>'perm_code',
            COALESCE((v_assignment->>'granted')::boolean, true),
            CASE WHEN v_assignment ? 'expires_at'
                 THEN (v_assignment->>'expires_at')::timestamptz
                 ELSE NULL
            END
        );

        IF v_assignment_id IS NOT NULL THEN
            v_count := v_count + 1;
        END IF;
    END LOOP;

    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

-- Usage example
SELECT auth.assign_permissions_bulk(
    'tenant-uuid',
    'user-uuid',
    NULL,
    '[
        {"perm_code": "users.view", "granted": true},
        {"perm_code": "reports.generate", "granted": true, "expires_at": "2024-12-31T23:59:59"},
        {"perm_set_code": "BASIC_USER", "granted": true}
    ]'::jsonb
);

User Permission Assignment

Direct User Assignments

-- Common user permission assignment patterns
DO $$
DECLARE
    v_user_id uuid := 'example-user-uuid';
    v_tenant_id uuid := 'example-tenant-uuid';
BEGIN
    -- Basic user permissions
    PERFORM auth.assign_permission(v_tenant_id, v_user_id, NULL, 'BASIC_USER', NULL);

    -- Additional specific permissions
    PERFORM auth.assign_permission(v_tenant_id, v_user_id, NULL, NULL, 'reports.financial');
    PERFORM auth.assign_permission(v_tenant_id, v_user_id, NULL, NULL, 'admin.users.view');

    -- Temporary elevated permissions (expires in 30 days)
    PERFORM auth.assign_permission(
        v_tenant_id, v_user_id, NULL, NULL, 'admin.system.maintenance',
        true, now() + interval '30 days'
    );

    -- Explicit denial (overrides group permissions)
    PERFORM auth.assign_permission(
        v_tenant_id, v_user_id, NULL, NULL, 'data.delete',
        false  -- Explicit denial
    );
END $$;

User Permission Profiles

-- Create permission profiles for different user types
CREATE OR REPLACE FUNCTION auth.assign_user_profile(
    _tenant_id uuid,
    _user_id uuid,
    _profile_type text
) RETURNS integer AS $$
DECLARE
    v_assignments jsonb;
    v_count integer;
BEGIN
    -- Define profile-based assignments
    v_assignments := CASE _profile_type
        WHEN 'new_employee' THEN '[
            {"perm_set_code": "BASIC_USER", "granted": true},
            {"perm_code": "profile.update.own", "granted": true},
            {"perm_code": "training.access", "granted": true, "expires_at": "2024-06-01T00:00:00"}
        ]'::jsonb

        WHEN 'manager' THEN '[
            {"perm_set_code": "STANDARD_USER", "granted": true},
            {"perm_set_code": "MANAGER_PERMISSIONS", "granted": true},
            {"perm_code": "reports.team", "granted": true},
            {"perm_code": "users.view.team", "granted": true}
        ]'::jsonb

        WHEN 'contractor' THEN '[
            {"perm_set_code": "BASIC_USER", "granted": true},
            {"perm_code": "projects.assigned", "granted": true},
            {"perm_code": "admin.users.create", "granted": false},
            {"perm_code": "data.export", "granted": false}
        ]'::jsonb

        WHEN 'admin' THEN '[
            {"perm_set_code": "ADMIN_PERMISSIONS", "granted": true},
            {"perm_code": "admin.system", "granted": true}
        ]'::jsonb

        ELSE '[]'::jsonb
    END;

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

    -- Log profile assignment
    PERFORM auth.create_auth_event(
        _tenant_id,
        _user_id,
        '50031', -- USER_PROFILE_ASSIGNED
        'User profile assigned: ' || _profile_type,
        jsonb_build_object(
            'profile_type', _profile_type,
            'permissions_assigned', v_count
        )
    );

    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT auth.assign_user_profile('tenant-uuid', 'user-uuid', 'manager');

Group Permission Assignment

Group-Based Assignments

-- Assign permissions to groups for inherited access
DO $$
DECLARE
    v_tenant_id uuid := 'example-tenant-uuid';
    v_engineering_group_id uuid;
    v_managers_group_id uuid;
BEGIN
    -- Get group IDs
    SELECT user_group_id INTO v_engineering_group_id
    FROM auth.user_group
    WHERE tenant_id = v_tenant_id AND code = 'ENGINEERING';

    SELECT user_group_id INTO v_managers_group_id
    FROM auth.user_group
    WHERE tenant_id = v_tenant_id AND code = 'MANAGERS';

    -- Engineering group permissions
    PERFORM auth.assign_permission(v_tenant_id, NULL, v_engineering_group_id, 'DEVELOPER_PERMISSIONS', NULL);
    PERFORM auth.assign_permission(v_tenant_id, NULL, v_engineering_group_id, NULL, 'api.development');
    PERFORM auth.assign_permission(v_tenant_id, NULL, v_engineering_group_id, NULL, 'data.read');

    -- Managers group permissions
    PERFORM auth.assign_permission(v_tenant_id, NULL, v_managers_group_id, 'MANAGER_PERMISSIONS', NULL);
    PERFORM auth.assign_permission(v_tenant_id, NULL, v_managers_group_id, NULL, 'reports.team');
    PERFORM auth.assign_permission(v_tenant_id, NULL, v_managers_group_id, NULL, 'users.view.team');
    PERFORM auth.assign_permission(v_tenant_id, NULL, v_managers_group_id, NULL, 'admin.groups.view');
END $$;

Role-Based Group Assignments

-- Create role-based permission assignments
CREATE OR REPLACE FUNCTION auth.assign_role_permissions(
    _tenant_id uuid,
    _role_name text
) RETURNS integer AS $$
DECLARE
    v_group_id uuid;
    v_assignments jsonb;
    v_count integer := 0;
BEGIN
    -- Get or create role group
    INSERT INTO auth.user_group (tenant_id, code, name, group_type)
    VALUES (_tenant_id, 'ROLE_' || upper(_role_name), _role_name || ' Role', 'internal')
    ON CONFLICT (tenant_id, code)
    DO UPDATE SET name = _role_name || ' Role'
    RETURNING user_group_id INTO v_group_id;

    IF v_group_id IS NULL THEN
        SELECT user_group_id INTO v_group_id
        FROM auth.user_group
        WHERE tenant_id = _tenant_id AND code = 'ROLE_' || upper(_role_name);
    END IF;

    -- Define role-based permissions
    v_assignments := CASE lower(_role_name)
        WHEN 'developer' THEN '[
            {"perm_set_code": "DEVELOPER_PERMISSIONS", "granted": true},
            {"perm_code": "api.development", "granted": true},
            {"perm_code": "data.read", "granted": true},
            {"perm_code": "reports.technical", "granted": true}
        ]'::jsonb

        WHEN 'qa_tester' THEN '[
            {"perm_set_code": "BASIC_USER", "granted": true},
            {"perm_code": "testing.execute", "granted": true},
            {"perm_code": "bugs.report", "granted": true},
            {"perm_code": "data.read", "granted": true}
        ]'::jsonb

        WHEN 'team_lead' THEN '[
            {"perm_set_code": "DEVELOPER_PERMISSIONS", "granted": true},
            {"perm_code": "team.manage", "granted": true},
            {"perm_code": "reports.team", "granted": true},
            {"perm_code": "users.view.team", "granted": true}
        ]'::jsonb

        WHEN 'project_manager' THEN '[
            {"perm_set_code": "MANAGER_PERMISSIONS", "granted": true},
            {"perm_code": "projects.manage", "granted": true},
            {"perm_code": "reports.project", "granted": true},
            {"perm_code": "budgets.view", "granted": true}
        ]'::jsonb

        ELSE '[]'::jsonb
    END;

    -- Apply role permissions to group
    SELECT auth.assign_permissions_bulk(_tenant_id, NULL, v_group_id, v_assignments)
    INTO v_count;

    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

-- Create multiple roles
SELECT auth.assign_role_permissions('tenant-uuid', 'developer');
SELECT auth.assign_role_permissions('tenant-uuid', 'team_lead');
SELECT auth.assign_role_permissions('tenant-uuid', 'project_manager');

Advanced Assignment Patterns

Conditional Assignments

-- Assign permissions based on conditions
CREATE OR REPLACE FUNCTION auth.assign_conditional_permissions(
    _tenant_id uuid,
    _user_id uuid,
    _conditions jsonb
) RETURNS integer AS $$
DECLARE
    v_count integer := 0;
    v_user_data jsonb;
    v_assignments jsonb := '[]'::jsonb;
BEGIN
    -- Get user data for condition evaluation
    SELECT data INTO v_user_data
    FROM auth.user_data
    WHERE user_id = _user_id;

    -- Department-based permissions
    IF v_user_data->>'department' = 'Finance' THEN
        v_assignments := v_assignments || '[
            {"perm_code": "finance.reports", "granted": true},
            {"perm_code": "budgets.view", "granted": true}
        ]'::jsonb;
    END IF;

    -- Seniority-based permissions
    IF (v_user_data->>'job_level')::integer >= 8 THEN
        v_assignments := v_assignments || '[
            {"perm_code": "admin.users.view", "granted": true},
            {"perm_code": "reports.confidential", "granted": true}
        ]'::jsonb;
    END IF;

    -- Location-based permissions
    IF v_user_data->>'location' = 'headquarters' THEN
        v_assignments := v_assignments || '[
            {"perm_code": "facilities.access", "granted": true},
            {"perm_code": "meetings.book.executive", "granted": true}
        ]'::jsonb;
    END IF;

    -- Apply conditional assignments
    IF jsonb_array_length(v_assignments) > 0 THEN
        SELECT auth.assign_permissions_bulk(_tenant_id, _user_id, NULL, v_assignments)
        INTO v_count;
    END IF;

    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

Time-Based Assignments

-- Create time-based permission assignments
CREATE OR REPLACE FUNCTION auth.assign_temporary_permissions(
    _tenant_id uuid,
    _user_id uuid,
    _permissions text[],
    _duration interval
) RETURNS integer AS $$
DECLARE
    v_perm_code text;
    v_count integer := 0;
    v_expires_at timestamptz := now() + _duration;
BEGIN
    FOREACH v_perm_code IN ARRAY _permissions
    LOOP
        PERFORM auth.assign_permission(
            _tenant_id,
            _user_id,
            NULL,
            NULL,
            v_perm_code,
            true,
            v_expires_at
        );
        v_count := v_count + 1;
    END LOOP;

    -- Log temporary assignment
    PERFORM auth.create_auth_event(
        _tenant_id,
        _user_id,
        '50032', -- TEMPORARY_PERMISSIONS_ASSIGNED
        'Temporary permissions assigned for ' || _duration::text,
        jsonb_build_object(
            'permissions', _permissions,
            'expires_at', v_expires_at,
            'duration', _duration::text
        )
    );

    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

-- Usage: Grant admin access for 2 hours
SELECT auth.assign_temporary_permissions(
    'tenant-uuid',
    'user-uuid',
    ARRAY['admin.system.maintenance', 'admin.users.unlock'],
    interval '2 hours'
);

Delegation Patterns

-- Allow users to delegate specific permissions to others
CREATE OR REPLACE FUNCTION auth.delegate_permission(
    _tenant_id uuid,
    _delegator_user_id uuid,
    _delegatee_user_id uuid,
    _perm_code text,
    _duration interval DEFAULT interval '24 hours'
) RETURNS uuid AS $$
DECLARE
    v_assignment_id uuid;
    v_can_delegate boolean := false;
BEGIN
    -- Check if delegator has the permission and delegation rights
    SELECT auth.has_permission(_tenant_id, _delegator_user_id, _perm_code, false)
    INTO v_can_delegate;

    IF NOT v_can_delegate THEN
        RAISE EXCEPTION 'User % does not have permission % to delegate', _delegator_user_id, _perm_code;
    END IF;

    -- Check if delegator has delegation rights for this permission
    IF NOT auth.has_permission(_tenant_id, _delegator_user_id, 'admin.permissions.delegate', false) THEN
        RAISE EXCEPTION 'User % does not have delegation rights', _delegator_user_id;
    END IF;

    -- Create delegated assignment
    INSERT INTO auth.user_permission (
        tenant_id,
        user_id,
        perm_code,
        granted,
        expires_at,
        assigned_by,
        assignment_metadata
    )
    VALUES (
        _tenant_id,
        _delegatee_user_id,
        _perm_code,
        true,
        now() + _duration,
        _delegator_user_id,
        jsonb_build_object(
            'delegation', true,
            'delegator', _delegator_user_id,
            'delegated_at', now()
        )
    )
    RETURNING user_permission_id INTO v_assignment_id;

    -- Log delegation
    PERFORM auth.create_auth_event(
        _tenant_id,
        _delegator_user_id,
        '50033', -- PERMISSION_DELEGATED
        'Permission delegated: ' || _perm_code,
        jsonb_build_object(
            'delegatee', _delegatee_user_id,
            'permission', _perm_code,
            'duration', _duration::text,
            'assignment_id', v_assignment_id
        )
    );

    RETURN v_assignment_id;
END;
$$ LANGUAGE plpgsql;

Permission Conflict Resolution

Priority and Precedence Rules

-- Resolve permission conflicts with clear precedence rules
CREATE OR REPLACE FUNCTION auth.resolve_permission_conflicts(
    _tenant_id uuid,
    _user_id uuid,
    _perm_code text
) RETURNS TABLE (
    source_type text,
    source_id uuid,
    source_name text,
    granted boolean,
    priority integer,
    effective boolean
) AS $$
BEGIN
    RETURN QUERY
    WITH permission_sources AS (
        -- Direct user permissions (highest priority)
        SELECT
            'user_direct'::text as source_type,
            up.user_id as source_id,
            'Direct Assignment' as source_name,
            up.granted,
            1 as priority,
            up.user_permission_id as assignment_id
        FROM auth.user_permission up
        WHERE up.tenant_id = _tenant_id
          AND up.user_id = _user_id
          AND up.perm_code = _perm_code
          AND (up.expires_at IS NULL OR up.expires_at > now())

        UNION ALL

        -- Group permissions (lower priority)
        SELECT
            'group'::text,
            ug.user_group_id,
            ug.name,
            up.granted,
            10 + CASE ug.group_type
                WHEN 'internal' THEN 0
                WHEN 'hybrid' THEN 1
                WHEN 'external' THEN 2
                ELSE 5
            END as priority,
            up.user_permission_id
        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 ug.is_active = true
          AND up.tenant_id = _tenant_id
          AND up.perm_code = _perm_code
          AND (up.expires_at IS NULL OR up.expires_at > now())

        UNION ALL

        -- Permission set permissions
        SELECT
            CASE WHEN up.user_id IS NOT NULL THEN 'user_permset' ELSE 'group_permset' END,
            COALESCE(up.user_id, up.user_group_id),
            'Permission Set: ' || up.perm_set_code,
            psi.granted,
            CASE WHEN up.user_id IS NOT NULL THEN 2 ELSE 12 END as priority,
            up.user_permission_id
        FROM auth.user_permission up
        JOIN auth.permission_set_item psi ON up.perm_set_code = psi.perm_set_code
            AND up.tenant_id = psi.tenant_id
        WHERE up.tenant_id = _tenant_id
          AND psi.perm_code = _perm_code
          AND psi.granted = true
          AND (up.expires_at IS NULL OR up.expires_at > now())
          AND (
              up.user_id = _user_id
              OR
              (up.user_group_id IS NOT NULL
               AND EXISTS (
                   SELECT 1 FROM auth.user_group_member ugm2
                   WHERE ugm2.user_group_id = up.user_group_id
                     AND ugm2.user_id = _user_id
                     AND ugm2.is_active = true
               ))
          )
    ),
    prioritized_sources AS (
        SELECT
            ps.*,
            ROW_NUMBER() OVER (ORDER BY ps.priority, ps.granted DESC) as precedence
        FROM permission_sources ps
    )
    SELECT
        ps.source_type,
        ps.source_id,
        ps.source_name,
        ps.granted,
        ps.priority,
        ps.precedence = 1 as effective
    FROM prioritized_sources ps
    ORDER BY ps.priority, ps.granted DESC;
END;
$$ LANGUAGE plpgsql;

Conflict Detection and Resolution

-- Detect and resolve assignment conflicts
CREATE OR REPLACE FUNCTION auth.detect_permission_conflicts(
    _tenant_id uuid,
    _user_id uuid DEFAULT NULL
) RETURNS TABLE (
    user_id uuid,
    username text,
    perm_code text,
    conflict_type text,
    sources text,
    recommendation text
) AS $$
BEGIN
    RETURN QUERY
    WITH user_permissions AS (
        SELECT
            COALESCE(_user_id, up.user_id) as target_user_id,
            up.perm_code,
            up.granted,
            CASE
                WHEN up.user_id IS NOT NULL THEN 'direct'
                ELSE 'group'
            END as assignment_type,
            COALESCE(up.user_id, up.user_group_id) as source_id
        FROM auth.user_permission up
        LEFT JOIN auth.user_group_member ugm ON up.user_group_id = ugm.user_group_id
        WHERE up.tenant_id = _tenant_id
          AND (_user_id IS NULL OR up.user_id = _user_id OR ugm.user_id = _user_id)
          AND up.is_active = true
          AND (up.expires_at IS NULL OR up.expires_at > now())
    ),
    conflicts AS (
        SELECT
            up1.target_user_id,
            up1.perm_code,
            array_agg(DISTINCT up1.assignment_type || ':' || up1.granted::text) as conflict_sources,
            CASE
                WHEN bool_and(up1.granted) THEN 'redundant_grants'
                WHEN bool_and(NOT up1.granted) THEN 'redundant_denials'
                ELSE 'grant_deny_conflict'
            END as conflict_type
        FROM user_permissions up1
        GROUP BY up1.target_user_id, up1.perm_code
        HAVING COUNT(DISTINCT up1.granted) > 1 OR COUNT(*) > 1
    )
    SELECT
        c.target_user_id,
        u.username,
        c.perm_code,
        c.conflict_type,
        array_to_string(c.conflict_sources, ', ') as sources,
        CASE c.conflict_type
            WHEN 'grant_deny_conflict' THEN 'Review explicit denials vs grants'
            WHEN 'redundant_grants' THEN 'Consider consolidating into permission set'
            WHEN 'redundant_denials' THEN 'Remove duplicate denials'
            ELSE 'Manual review required'
        END as recommendation
    FROM conflicts c
    JOIN auth.user_info u ON c.target_user_id = u.user_id
    ORDER BY u.username, c.perm_code;
END;
$$ LANGUAGE plpgsql;

Assignment Management and Maintenance

Permission Assignment Queries

-- Get comprehensive assignment information
CREATE OR REPLACE FUNCTION auth.get_user_assignments(
    _tenant_id uuid,
    _user_id uuid,
    _include_inherited boolean DEFAULT true
) RETURNS TABLE (
    assignment_type text,
    source_name text,
    permission_or_set text,
    granted boolean,
    assigned_at timestamptz,
    expires_at timestamptz,
    assigned_by_name text
) AS $$
BEGIN
    RETURN QUERY
    -- Direct user assignments
    SELECT
        CASE
            WHEN up.perm_code IS NOT NULL THEN 'Direct Permission'
            ELSE 'Direct Permission Set'
        END::text as assignment_type,
        'User Assignment'::text as source_name,
        COALESCE(up.perm_code, up.perm_set_code) as permission_or_set,
        up.granted,
        up.assigned_at,
        up.expires_at,
        COALESCE(assigner.display_name, 'System') as assigned_by_name
    FROM auth.user_permission up
    LEFT JOIN auth.user_info assigner ON up.assigned_by = assigner.user_id
    WHERE up.tenant_id = _tenant_id
      AND up.user_id = _user_id
      AND (up.expires_at IS NULL OR up.expires_at > now())

    UNION ALL

    -- Group assignments (if requested)
    SELECT
        CASE
            WHEN up.perm_code IS NOT NULL THEN 'Group Permission'
            ELSE 'Group Permission Set'
        END::text,
        'Group: ' || ug.name,
        COALESCE(up.perm_code, up.perm_set_code),
        up.granted,
        up.assigned_at,
        up.expires_at,
        COALESCE(assigner.display_name, 'System')
    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
    LEFT JOIN auth.user_info assigner ON up.assigned_by = assigner.user_id
    WHERE _include_inherited
      AND ugm.user_id = _user_id
      AND ugm.is_active = true
      AND ug.tenant_id = _tenant_id
      AND ug.is_active = true
      AND up.tenant_id = _tenant_id
      AND (up.expires_at IS NULL OR up.expires_at > now())

    ORDER BY assignment_type, permission_or_set;
END;
$$ LANGUAGE plpgsql;

Cleanup and Maintenance

-- Clean up expired assignments
CREATE OR REPLACE FUNCTION auth.cleanup_expired_assignments(
    _tenant_id uuid DEFAULT NULL
) RETURNS integer AS $$
DECLARE
    v_cleanup_count integer;
BEGIN
    DELETE FROM auth.user_permission
    WHERE expires_at < now()
      AND (_tenant_id IS NULL OR tenant_id = _tenant_id);

    GET DIAGNOSTICS v_cleanup_count = ROW_COUNT;

    -- Log cleanup
    PERFORM auth.create_auth_event(
        _tenant_id,
        NULL,
        '50034', -- EXPIRED_PERMISSIONS_CLEANED
        'Cleaned up expired permission assignments',
        jsonb_build_object('cleaned_count', v_cleanup_count)
    );

    RETURN v_cleanup_count;
END;
$$ LANGUAGE plpgsql;

-- Revoke all permissions for user
CREATE OR REPLACE FUNCTION auth.revoke_all_user_permissions(
    _tenant_id uuid,
    _user_id uuid,
    _reason text DEFAULT 'Administrative action'
) RETURNS integer AS $$
DECLARE
    v_revoked_count integer;
BEGIN
    -- Soft delete by setting granted = false
    UPDATE auth.user_permission
    SET
        granted = false,
        revoked_at = now(),
        revoked_reason = _reason
    WHERE tenant_id = _tenant_id
      AND user_id = _user_id
      AND granted = true;

    GET DIAGNOSTICS v_revoked_count = ROW_COUNT;

    -- Clear cache
    PERFORM auth.clear_permission_cache(_user_id);

    -- Log revocation
    PERFORM auth.create_auth_event(
        _tenant_id,
        _user_id,
        '50035', -- ALL_PERMISSIONS_REVOKED
        'All permissions revoked: ' || _reason,
        jsonb_build_object('revoked_count', v_revoked_count)
    );

    RETURN v_revoked_count;
END;
$$ LANGUAGE plpgsql;

Best Practices for Permission Assignment

Design Guidelines

1. Prefer Groups: Use group assignments for role-based permissions
2. Limit Direct Assignments: Use direct user assignments sparingly for exceptions
3. Use Permission Sets: Group related permissions into sets
4. Document Exceptions: Always document why direct assignments were made
5. Regular Audits: Periodically review and clean up assignments

Common Assignment Patterns

-- Template for standard user onboarding
CREATE OR REPLACE FUNCTION auth.onboard_new_user(
    _tenant_id uuid,
    _user_id uuid,
    _department text,
    _role text,
    _manager_user_id uuid DEFAULT NULL
) RETURNS void AS $$
BEGIN
    -- 1. Basic user permissions
    PERFORM auth.assign_permission(_tenant_id, _user_id, NULL, 'BASIC_USER', NULL);

    -- 2. Department-based permissions
    PERFORM auth.assign_permission(
        _tenant_id, _user_id, NULL, upper(_department) || '_PERMISSIONS', NULL
    );

    -- 3. Role-based permissions
    IF _role = 'manager' THEN
        PERFORM auth.assign_permission(_tenant_id, _user_id, NULL, 'MANAGER_PERMISSIONS', NULL);
    ELSIF _role = 'senior' THEN
        PERFORM auth.assign_permission(_tenant_id, _user_id, NULL, 'SENIOR_PERMISSIONS', NULL);
    END IF;

    -- 4. Temporary training permissions (30 days)
    PERFORM auth.assign_temporary_permissions(
        _tenant_id, _user_id,
        ARRAY['training.access', 'onboarding.complete'],
        interval '30 days'
    );

    -- 5. Add to appropriate groups
    PERFORM auth.add_user_to_default_groups(_tenant_id, _user_id);
END;
$$ LANGUAGE plpgsql;

What's Next

• Learn about Permission Caching for optimizing assignment resolution
• Explore Permission Sets for organizing related permissions
• Review Hierarchical Permissions and assignment inheritance
• Understand Groups integration with permission assignment