Hierarchical Permissions

The PostgreSQL Permissions Model implements hierarchical permissions using PostgreSQL's ltree extension, enabling powerful inheritance patterns and efficient permission management. This system allows granting broad permissions that automatically include more specific ones, while maintaining fine-grained control when needed.

Overview

Hierarchical permissions use dot notation to create tree structures where parent permissions automatically grant access to child permissions:

graph TD
    subgraph "Permission Hierarchy"
        ROOT[admin<br/>Root Admin Access]

        L2_SYS[admin.system<br/>System Administration]
        L2_USERS[admin.users<br/>User Management]
        L2_REPORTS[admin.reports<br/>Report Administration]

        L3_CONFIG[admin.system.config<br/>System Configuration]
        L3_MAINT[admin.system.maintenance<br/>System Maintenance]

        L3_CREATE[admin.users.create<br/>Create Users]
        L3_UPDATE[admin.users.update<br/>Update Users]
        L3_DELETE[admin.users.delete<br/>Delete Users]

        L3_FIN[admin.reports.financial<br/>Financial Reports]
        L3_OPS[admin.reports.operational<br/>Operational Reports]

        L4_SALARY[admin.reports.financial.salary<br/>Salary Reports]
        L4_BUDGET[admin.reports.financial.budget<br/>Budget Reports]
    end

    ROOT --> L2_SYS
    ROOT --> L2_USERS
    ROOT --> L2_REPORTS

    L2_SYS --> L3_CONFIG
    L2_SYS --> L3_MAINT

    L2_USERS --> L3_CREATE
    L2_USERS --> L3_UPDATE
    L2_USERS --> L3_DELETE

    L2_REPORTS --> L3_FIN
    L2_REPORTS --> L3_OPS

    L3_FIN --> L4_SALARY
    L3_FIN --> L4_BUDGET

Key Benefits: - Inheritance: admin permission automatically grants admin.users.create - Granularity: Can grant specific permissions without broader access - Scalability: Easy to add new permissions to existing hierarchies - Performance: PostgreSQL ltree indexes provide fast hierarchy queries

Technical Implementation

PostgreSQL ltree Extension

-- Enable ltree extension for hierarchical data
CREATE EXTENSION IF NOT EXISTS ltree;

-- Add ltree column to permissions table
ALTER TABLE auth.permission ADD COLUMN IF NOT EXISTS perm_path ltree;

-- Create function to sync perm_code with perm_path
CREATE OR REPLACE FUNCTION auth.sync_permission_path() RETURNS trigger AS $$
BEGIN
    -- Automatically set perm_path from perm_code
    NEW.perm_path := NEW.perm_code::ltree;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger to maintain path synchronization
DROP TRIGGER IF EXISTS sync_permission_path_trigger ON auth.permission;
CREATE TRIGGER sync_permission_path_trigger
    BEFORE INSERT OR UPDATE OF perm_code ON auth.permission
    FOR EACH ROW EXECUTE FUNCTION auth.sync_permission_path();

-- Update existing permissions
UPDATE auth.permission
SET perm_path = perm_code::ltree
WHERE perm_path IS NULL;

Essential Indexes for Performance

-- GIST index for ancestor/descendant queries (most important)
CREATE INDEX CONCURRENTLY idx_permission_path_gist
    ON auth.permission USING GIST (perm_path)
    WHERE is_active = true;

-- BTREE index for exact matches and sorting
CREATE INDEX CONCURRENTLY idx_permission_path_btree
    ON auth.permission USING BTREE (perm_path)
    WHERE is_active = true;

-- Composite index for common lookup patterns
CREATE INDEX CONCURRENTLY idx_permission_path_active
    ON auth.permission (perm_path, is_active);

-- Index for permission level filtering
CREATE INDEX CONCURRENTLY idx_permission_level_path
    ON auth.permission (perm_level, perm_path)
    WHERE is_active = true;

Hierarchy Design Patterns

Application Domain Hierarchies

-- Complete application permission hierarchy
INSERT INTO auth.permission (perm_code, name, description, perm_level) VALUES
-- Root domains
('admin', 'Administration', 'Administrative access', 'admin'),
('api', 'API Access', 'API endpoint access', 'standard'),
('ui', 'User Interface', 'UI component access', 'basic'),
('data', 'Data Access', 'Data operations', 'standard'),

-- Admin sub-domains
('admin.system', 'System Administration', 'System-level operations', 'system'),
('admin.users', 'User Administration', 'User management operations', 'admin'),
('admin.groups', 'Group Administration', 'Group management operations', 'admin'),
('admin.permissions', 'Permission Administration', 'Permission management', 'admin'),
('admin.tenants', 'Tenant Administration', 'Tenant management', 'system'),
('admin.providers', 'Provider Administration', 'Identity provider management', 'admin'),

-- System admin operations
('admin.system.config', 'System Configuration', 'Modify system settings', 'system'),
('admin.system.maintenance', 'System Maintenance', 'Maintenance operations', 'system'),
('admin.system.monitoring', 'System Monitoring', 'Monitor system health', 'system'),
('admin.system.backup', 'System Backup', 'Backup and restore operations', 'system'),

-- User administration
('admin.users.create', 'Create Users', 'Create new user accounts', 'admin'),
('admin.users.read', 'Read Users', 'View user information', 'admin'),
('admin.users.update', 'Update Users', 'Modify user accounts', 'admin'),
('admin.users.delete', 'Delete Users', 'Remove user accounts', 'admin'),
('admin.users.impersonate', 'Impersonate Users', 'Login as another user', 'system'),

-- Group administration
('admin.groups.create', 'Create Groups', 'Create user groups', 'admin'),
('admin.groups.read', 'Read Groups', 'View group information', 'admin'),
('admin.groups.update', 'Update Groups', 'Modify groups', 'admin'),
('admin.groups.delete', 'Delete Groups', 'Remove groups', 'admin'),
('admin.groups.membership', 'Manage Membership', 'Add/remove group members', 'admin'),

-- API access patterns
('api.v1', 'API Version 1', 'Access to API v1 endpoints', 'standard'),
('api.v1.users', 'API Users', 'User-related API endpoints', 'standard'),
('api.v1.groups', 'API Groups', 'Group-related API endpoints', 'standard'),
('api.v1.reports', 'API Reports', 'Reporting API endpoints', 'standard'),

('api.v1.users.create', 'API Create Users', 'POST /api/v1/users', 'admin'),
('api.v1.users.read', 'API Read Users', 'GET /api/v1/users', 'standard'),
('api.v1.users.update', 'API Update Users', 'PUT /api/v1/users', 'admin'),
('api.v1.users.delete', 'API Delete Users', 'DELETE /api/v1/users', 'admin'),

-- Data access patterns
('data.read', 'Data Read', 'Read data operations', 'basic'),
('data.write', 'Data Write', 'Write data operations', 'standard'),
('data.delete', 'Data Delete', 'Delete data operations', 'admin'),

('data.users', 'User Data', 'Access to user data', 'standard'),
('data.users.personal', 'Personal User Data', 'Access to personal information', 'admin'),
('data.users.financial', 'Financial User Data', 'Access to financial information', 'system'),

-- UI component access
('ui.dashboard', 'Dashboard UI', 'Access to dashboard components', 'basic'),
('ui.admin', 'Admin UI', 'Access to admin interface', 'admin'),
('ui.reports', 'Reports UI', 'Access to reporting interface', 'standard'),

('ui.admin.users', 'Admin Users UI', 'User management interface', 'admin'),
('ui.admin.groups', 'Admin Groups UI', 'Group management interface', 'admin'),
('ui.admin.system', 'Admin System UI', 'System administration interface', 'system');

-- Update all paths
UPDATE auth.permission SET perm_path = perm_code::ltree WHERE perm_path IS NULL;

Business Domain Hierarchies

-- Business-specific permission hierarchies
INSERT INTO auth.permission (perm_code, name, description, perm_level) VALUES
-- Financial domain
('finance', 'Financial Access', 'Financial operations', 'standard'),
('finance.accounts', 'Account Management', 'Account operations', 'standard'),
('finance.accounts.receivable', 'Accounts Receivable', 'AR operations', 'standard'),
('finance.accounts.payable', 'Accounts Payable', 'AP operations', 'standard'),
('finance.accounts.payable.approve', 'Approve Payments', 'Approve AP payments', 'admin'),
('finance.accounts.payable.process', 'Process Payments', 'Process AP payments', 'standard'),

('finance.reports', 'Financial Reports', 'Financial reporting', 'standard'),
('finance.reports.budget', 'Budget Reports', 'Budget analysis', 'admin'),
('finance.reports.audit', 'Audit Reports', 'Audit and compliance reports', 'system'),

-- HR domain
('hr', 'Human Resources', 'HR operations', 'standard'),
('hr.employees', 'Employee Management', 'Employee operations', 'admin'),
('hr.employees.personal', 'Personal Information', 'Personal employee data', 'admin'),
('hr.employees.compensation', 'Compensation', 'Salary and benefits', 'system'),
('hr.employees.performance', 'Performance', 'Performance reviews', 'admin'),

('hr.benefits', 'Benefits Administration', 'Benefits management', 'admin'),
('hr.benefits.health', 'Health Benefits', 'Health insurance management', 'admin'),
('hr.benefits.retirement', 'Retirement Benefits', 'Retirement plan management', 'admin'),

-- Sales domain
('sales', 'Sales Operations', 'Sales activities', 'standard'),
('sales.leads', 'Lead Management', 'Manage sales leads', 'standard'),
('sales.leads.create', 'Create Leads', 'Create new leads', 'standard'),
('sales.leads.convert', 'Convert Leads', 'Convert leads to opportunities', 'standard'),

('sales.opportunities', 'Opportunity Management', 'Manage sales opportunities', 'standard'),
('sales.opportunities.close', 'Close Opportunities', 'Close won/lost opportunities', 'admin'),

('sales.reports', 'Sales Reports', 'Sales reporting', 'standard'),
('sales.reports.commission', 'Commission Reports', 'Sales commission calculations', 'admin');

-- Update paths
UPDATE auth.permission SET perm_path = perm_code::ltree WHERE perm_path IS NULL;

Hierarchy Query Operations

Finding Ancestors and Descendants

-- Get all ancestors of a permission (permissions that grant this one)
CREATE OR REPLACE FUNCTION auth.get_permission_ancestors(
    _perm_code text
) RETURNS TABLE (
    perm_code text,
    name text,
    level integer
) AS $$
BEGIN
    RETURN QUERY
    WITH RECURSIVE ancestors AS (
        -- Start with the given permission
        SELECT
            p.perm_code,
            p.name,
            p.perm_path,
            0 as level
        FROM auth.permission p
        WHERE p.perm_code = _perm_code
          AND p.is_active = true

        UNION ALL

        -- Find parents recursively
        SELECT
            parent.perm_code,
            parent.name,
            parent.perm_path,
            a.level + 1
        FROM ancestors a
        JOIN auth.permission parent ON a.perm_path <@ parent.perm_path
        WHERE parent.perm_code != a.perm_code
          AND parent.is_active = true
    )
    SELECT
        a.perm_code,
        a.name,
        a.level
    FROM ancestors a
    WHERE a.level > 0  -- Exclude the starting permission
    ORDER BY a.level DESC, a.perm_code;
END;
$$ LANGUAGE plpgsql;

-- Get all descendants of a permission (permissions granted by this one)
CREATE OR REPLACE FUNCTION auth.get_permission_descendants(
    _perm_code text
) RETURNS TABLE (
    perm_code text,
    name text,
    level integer
) AS $$
DECLARE
    v_parent_path ltree;
BEGIN
    -- Get the parent permission path
    SELECT perm_path INTO v_parent_path
    FROM auth.permission
    WHERE perm_code = _perm_code
      AND is_active = true;

    IF v_parent_path IS NULL THEN
        RETURN;
    END IF;

    RETURN QUERY
    SELECT
        p.perm_code,
        p.name,
        nlevel(p.perm_path) - nlevel(v_parent_path) as level
    FROM auth.permission p
    WHERE p.perm_path <@ v_parent_path
      AND p.perm_code != _perm_code
      AND p.is_active = true
    ORDER BY p.perm_path;
END;
$$ LANGUAGE plpgsql;

-- Usage examples
SELECT * FROM auth.get_permission_ancestors('admin.users.create');
SELECT * FROM auth.get_permission_descendants('admin.users');

Hierarchy Analysis Functions

-- Analyze permission hierarchy depth and breadth
CREATE OR REPLACE FUNCTION auth.analyze_permission_hierarchy()
RETURNS TABLE (
    domain text,
    max_depth integer,
    total_permissions integer,
    leaf_permissions integer
) AS $$
BEGIN
    RETURN QUERY
    WITH hierarchy_stats AS (
        SELECT
            split_part(perm_code, '.', 1) as domain,
            nlevel(perm_path) as depth,
            perm_code
        FROM auth.permission
        WHERE is_active = true
    ),
    domain_stats AS (
        SELECT
            hs.domain,
            MAX(hs.depth) as max_depth,
            COUNT(*) as total_permissions,
            COUNT(*) FILTER (
                WHERE NOT EXISTS (
                    SELECT 1 FROM auth.permission p2
                    WHERE p2.perm_path <@ (hs.domain || '.' ||
                        CASE WHEN position('.' in hs.perm_code) > 0
                             THEN substring(hs.perm_code from position('.' in hs.perm_code) + 1)
                             ELSE ''
                        END)::ltree
                      AND p2.perm_code != hs.perm_code
                      AND p2.is_active = true
                )
            ) as leaf_permissions
        FROM hierarchy_stats hs
        GROUP BY hs.domain
    )
    SELECT
        ds.domain,
        ds.max_depth,
        ds.total_permissions,
        ds.leaf_permissions
    FROM domain_stats ds
    ORDER BY ds.total_permissions DESC;
END;
$$ LANGUAGE plpgsql;

Permission Path Validation

-- Validate permission hierarchy consistency
CREATE OR REPLACE FUNCTION auth.validate_permission_hierarchy()
RETURNS TABLE (
    issue_type text,
    perm_code text,
    description text
) AS $$
BEGIN
    RETURN QUERY
    -- Find permissions with missing parents
    SELECT
        'MISSING_PARENT'::text,
        p.perm_code,
        'Permission has no parent but path suggests it should: ' ||
        subpath(p.perm_path, 0, nlevel(p.perm_path) - 1)::text as description
    FROM auth.permission p
    WHERE nlevel(p.perm_path) > 1
      AND p.is_active = true
      AND NOT EXISTS (
          SELECT 1 FROM auth.permission parent
          WHERE parent.perm_path = subpath(p.perm_path, 0, nlevel(p.perm_path) - 1)
            AND parent.is_active = true
      )

    UNION ALL

    -- Find permissions with inconsistent paths
    SELECT
        'INCONSISTENT_PATH'::text,
        p.perm_code,
        'Permission code and path do not match'
    FROM auth.permission p
    WHERE p.perm_code::ltree != p.perm_path
      AND p.is_active = true

    UNION ALL

    -- Find permissions with circular references (shouldn't be possible with ltree, but check anyway)
    SELECT
        'CIRCULAR_REFERENCE'::text,
        p1.perm_code,
        'Potential circular reference with: ' || p2.perm_code
    FROM auth.permission p1
    JOIN auth.permission p2 ON p1.perm_path <@ p2.perm_path
                              AND p2.perm_path <@ p1.perm_path
    WHERE p1.perm_code != p2.perm_code
      AND p1.is_active = true
      AND p2.is_active = true

    ORDER BY issue_type, perm_code;
END;
$$ LANGUAGE plpgsql;

Hierarchical Permission Assignment

Smart Assignment Functions

-- Assign permission with hierarchy awareness
CREATE OR REPLACE FUNCTION auth.assign_permission_hierarchical(
    _tenant_id uuid,
    _user_id uuid,
    _user_group_id uuid,
    _perm_code text,
    _include_descendants boolean DEFAULT false,
    _exclude_ancestors boolean DEFAULT false
) RETURNS integer AS $$
DECLARE
    v_assigned_count integer := 0;
    v_permission_record record;
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;

    -- Check if permission exists
    IF NOT EXISTS (
        SELECT 1 FROM auth.permission
        WHERE perm_code = _perm_code AND is_active = true
    ) THEN
        RAISE EXCEPTION 'Permission % does not exist', _perm_code;
    END IF;

    -- Remove redundant ancestor permissions if requested
    IF _exclude_ancestors THEN
        DELETE FROM auth.user_permission up
        WHERE up.tenant_id = _tenant_id
          AND (
              (_user_id IS NOT NULL AND up.user_id = _user_id) OR
              (_user_group_id IS NOT NULL AND up.user_group_id = _user_group_id)
          )
          AND EXISTS (
              SELECT 1 FROM auth.permission p1, auth.permission p2
              WHERE p1.perm_code = _perm_code
                AND p2.perm_code = up.perm_code
                AND p1.perm_path <@ p2.perm_path
                AND p1.perm_code != p2.perm_code
          );
    END IF;

    -- Assign the main permission
    INSERT INTO auth.user_permission (
        tenant_id, user_id, user_group_id, perm_code, assigned_by
    )
    VALUES (
        _tenant_id, _user_id, _user_group_id, _perm_code,
        COALESCE(_user_id, (SELECT created_by FROM auth.user_group WHERE user_group_id = _user_group_id))
    )
    ON CONFLICT (tenant_id, COALESCE(user_id, '00000000-0000-0000-0000-000000000000'::uuid),
                 COALESCE(user_group_id, '00000000-0000-0000-0000-000000000000'::uuid), perm_code)
    DO UPDATE SET granted = true, assigned_at = now();

    v_assigned_count := v_assigned_count + 1;

    -- Assign descendant permissions if requested
    IF _include_descendants THEN
        FOR v_permission_record IN
            SELECT perm_code FROM auth.get_permission_descendants(_perm_code)
        LOOP
            INSERT INTO auth.user_permission (
                tenant_id, user_id, user_group_id, perm_code, assigned_by
            )
            VALUES (
                _tenant_id, _user_id, _user_group_id, v_permission_record.perm_code,
                COALESCE(_user_id, (SELECT created_by FROM auth.user_group WHERE user_group_id = _user_group_id))
            )
            ON CONFLICT (tenant_id, COALESCE(user_id, '00000000-0000-0000-0000-000000000000'::uuid),
                         COALESCE(user_group_id, '00000000-0000-0000-0000-000000000000'::uuid), perm_code)
            DO UPDATE SET granted = true, assigned_at = now();

            v_assigned_count := v_assigned_count + 1;
        END LOOP;
    END IF;

    -- Remove redundant descendant permissions (they're inherited anyway)
    DELETE FROM auth.user_permission up
    WHERE up.tenant_id = _tenant_id
      AND (
          (_user_id IS NOT NULL AND up.user_id = _user_id) OR
          (_user_group_id IS NOT NULL AND up.user_group_id = _user_group_id)
      )
      AND up.perm_code != _perm_code
      AND EXISTS (
          SELECT 1 FROM auth.permission p1, auth.permission p2
          WHERE p1.perm_code = _perm_code
            AND p2.perm_code = up.perm_code
            AND p2.perm_path <@ p1.perm_path
      );

    RETURN v_assigned_count;
END;
$$ LANGUAGE plpgsql;

Optimal Permission Set Creation

-- Create optimized permission set using hierarchy
CREATE OR REPLACE FUNCTION auth.create_optimized_permission_set(
    _tenant_id uuid,
    _perm_set_code text,
    _name text,
    _permission_list text[]
) RETURNS integer AS $$
DECLARE
    v_perm_code text;
    v_optimized_list text[] := ARRAY[]::text[];
    v_added_count integer := 0;
BEGIN
    -- Create the permission set
    INSERT INTO auth.permission_set (perm_set_code, tenant_id, name)
    VALUES (_perm_set_code, _tenant_id, _name)
    ON CONFLICT (perm_set_code, tenant_id)
    DO UPDATE SET name = _name;

    -- Optimize the permission list by removing redundant permissions
    FOREACH v_perm_code IN ARRAY _permission_list
    LOOP
        -- Only add if not already covered by a parent permission
        IF NOT EXISTS (
            SELECT 1 FROM unnest(v_optimized_list) AS opt(perm)
            JOIN auth.permission p1 ON opt.perm = p1.perm_code
            JOIN auth.permission p2 ON v_perm_code = p2.perm_code
            WHERE p2.perm_path <@ p1.perm_path
              AND p1.perm_code != p2.perm_code
        ) THEN
            -- Remove any descendants that are now redundant
            SELECT ARRAY(
                SELECT opt.perm
                FROM unnest(v_optimized_list) AS opt(perm)
                JOIN auth.permission p1 ON opt.perm = p1.perm_code
                JOIN auth.permission p2 ON v_perm_code = p2.perm_code
                WHERE NOT (p1.perm_path <@ p2.perm_path AND p1.perm_code != p2.perm_code)
            ) INTO v_optimized_list;

            -- Add the new permission
            v_optimized_list := v_optimized_list || v_perm_code;
        END IF;
    END LOOP;

    -- Add optimized permissions to the set
    FOREACH v_perm_code IN ARRAY v_optimized_list
    LOOP
        INSERT INTO auth.permission_set_item (perm_set_code, tenant_id, perm_code)
        VALUES (_perm_set_code, _tenant_id, v_perm_code)
        ON CONFLICT (perm_set_code, tenant_id, perm_code)
        DO UPDATE SET granted = true;

        v_added_count := v_added_count + 1;
    END LOOP;

    RETURN v_added_count;
END;
$$ LANGUAGE plpgsql;

Advanced Hierarchy Patterns

Dynamic Permission Trees

-- Build dynamic permission tree for UI display
CREATE OR REPLACE FUNCTION auth.build_permission_tree(
    _user_id uuid DEFAULT NULL,
    _tenant_id uuid DEFAULT NULL,
    _include_granted boolean DEFAULT true
) RETURNS jsonb AS $$
DECLARE
    v_result jsonb := '{}';
    v_permission record;
    v_path_parts text[];
    v_current_obj jsonb;
    v_part text;
    v_has_permission boolean;
BEGIN
    FOR v_permission IN
        SELECT
            p.perm_code,
            p.name,
            p.description,
            p.perm_level,
            p.perm_path
        FROM auth.permission p
        WHERE p.is_active = true
        ORDER BY p.perm_path
    LOOP
        -- Check if user has this permission (if user specified)
        v_has_permission := false;
        IF _user_id IS NOT NULL AND _tenant_id IS NOT NULL THEN
            v_has_permission := auth.has_permission(_tenant_id, _user_id, v_permission.perm_code, false);
        END IF;

        -- Skip if not including granted permissions and user doesn't have it
        IF NOT _include_granted AND _user_id IS NOT NULL AND NOT v_has_permission THEN
            CONTINUE;
        END IF;

        -- Split permission path
        v_path_parts := string_to_array(v_permission.perm_code, '.');
        v_current_obj := v_result;

        -- Build nested structure
        FOR i IN 1..array_length(v_path_parts, 1)
        LOOP
            v_part := v_path_parts[i];

            IF i = array_length(v_path_parts, 1) THEN
                -- Leaf node - add permission details
                v_current_obj := jsonb_set(
                    v_current_obj,
                    ARRAY[v_part],
                    jsonb_build_object(
                        'name', v_permission.name,
                        'description', v_permission.description,
                        'level', v_permission.perm_level,
                        'granted', v_has_permission,
                        'full_code', v_permission.perm_code
                    )
                );
            ELSE
                -- Intermediate node
                IF NOT (v_current_obj ? v_part) THEN
                    v_current_obj := jsonb_set(
                        v_current_obj,
                        ARRAY[v_part],
                        jsonb_build_object('children', '{}')
                    );
                END IF;
                v_current_obj := v_current_obj -> v_part -> 'children';
            END IF;
        END LOOP;

        v_result := v_current_obj;
    END LOOP;

    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

Permission Conflict Resolution

-- Resolve permission conflicts in hierarchy
CREATE OR REPLACE FUNCTION auth.resolve_permission_conflicts(
    _tenant_id uuid,
    _user_id uuid
) RETURNS TABLE (
    conflict_type text,
    parent_permission text,
    child_permission text,
    recommendation text
) AS $$
BEGIN
    RETURN QUERY
    -- Find explicit denials that conflict with parent grants
    WITH user_permissions AS (
        SELECT up.perm_code, up.granted, p.perm_path
        FROM auth.user_permission up
        JOIN auth.permission p ON up.perm_code = p.perm_code
        WHERE up.tenant_id = _tenant_id
          AND up.user_id = _user_id
          AND (up.expires_at IS NULL OR up.expires_at > now())
    ),
    conflicts AS (
        SELECT
            up1.perm_code as parent_perm,
            up2.perm_code as child_perm,
            up1.granted as parent_granted,
            up2.granted as child_granted
        FROM user_permissions up1
        JOIN user_permissions up2 ON up2.perm_path <@ up1.perm_path
        WHERE up1.perm_code != up2.perm_code
          AND up1.granted != up2.granted
    )
    SELECT
        CASE
            WHEN c.parent_granted AND NOT c.child_granted THEN 'EXPLICIT_DENY'
            WHEN NOT c.parent_granted AND c.child_granted THEN 'EXPLICIT_GRANT'
            ELSE 'UNKNOWN'
        END::text as conflict_type,
        c.parent_perm,
        c.child_perm,
        CASE
            WHEN c.parent_granted AND NOT c.child_granted THEN
                'Remove explicit deny or remove parent grant'
            WHEN NOT c.parent_granted AND c.child_granted THEN
                'Remove explicit grant or remove parent deny'
            ELSE 'Review permission assignment'
        END as recommendation
    FROM conflicts c
    ORDER BY c.parent_perm, c.child_perm;
END;
$$ LANGUAGE plpgsql;

Performance Optimization for Hierarchies

Materialized Path Queries

-- Pre-compute common hierarchy queries for performance
CREATE MATERIALIZED VIEW auth.permission_hierarchy_cache AS
WITH RECURSIVE hierarchy AS (
    -- Root permissions (no parent)
    SELECT
        perm_code,
        name,
        perm_path,
        perm_level,
        0 as depth,
        ARRAY[perm_code] as ancestors,
        perm_code as root_permission
    FROM auth.permission
    WHERE nlevel(perm_path) = 1
      AND is_active = true

    UNION ALL

    -- Child permissions
    SELECT
        p.perm_code,
        p.name,
        p.perm_path,
        p.perm_level,
        h.depth + 1,
        h.ancestors || p.perm_code,
        h.root_permission
    FROM auth.permission p
    JOIN hierarchy h ON subpath(p.perm_path, 0, nlevel(p.perm_path) - 1) = h.perm_path
    WHERE p.is_active = true
)
SELECT
    perm_code,
    name,
    perm_path,
    perm_level,
    depth,
    ancestors,
    root_permission,
    array_length(ancestors, 1) as total_ancestors
FROM hierarchy;

-- Index the materialized view
CREATE UNIQUE INDEX idx_perm_hierarchy_cache_perm_code
    ON auth.permission_hierarchy_cache (perm_code);
CREATE INDEX idx_perm_hierarchy_cache_root
    ON auth.permission_hierarchy_cache (root_permission);
CREATE INDEX idx_perm_hierarchy_cache_ancestors
    ON auth.permission_hierarchy_cache USING GIN (ancestors);

-- Refresh function
CREATE OR REPLACE FUNCTION auth.refresh_permission_hierarchy_cache()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY auth.permission_hierarchy_cache;
END;
$$ LANGUAGE plpgsql;

Optimized Hierarchy Checks

-- Fast hierarchy permission check using materialized view
CREATE OR REPLACE FUNCTION auth.has_hierarchical_permission_fast(
    _tenant_id uuid,
    _user_id uuid,
    _perm_code text
) RETURNS boolean AS $$
DECLARE
    v_result boolean := false;
BEGIN
    -- Check using pre-computed hierarchy
    SELECT true INTO v_result
    FROM auth.user_permission up
    JOIN auth.permission_hierarchy_cache phc ON up.perm_code = ANY(phc.ancestors)
    WHERE up.tenant_id = _tenant_id
      AND up.user_id = _user_id
      AND phc.perm_code = _perm_code
      AND up.granted = true
      AND (up.expires_at IS NULL OR up.expires_at > now())
    LIMIT 1;

    RETURN COALESCE(v_result, false);
END;
$$ LANGUAGE plpgsql;

Best Practices for Hierarchical Permissions

Design Guidelines

1. Logical Grouping: Group related permissions under common ancestors
2. Consistent Depth: Keep similar operations at the same hierarchy level
3. Meaningful Names: Use clear, descriptive permission codes
4. Avoid Deep Nesting: Generally limit to 4-5 levels maximum

Common Anti-Patterns to Avoid

-- DON'T: Create unnecessarily deep hierarchies
-- BAD: 'app.module.submodule.feature.action.specific.detail'

-- DO: Keep reasonable depth
-- GOOD: 'app.users.create' or 'finance.reports.view'

-- DON'T: Mix business and technical hierarchies
-- BAD: 'api.v1.finance.reports.generate'

-- DO: Separate concerns
-- GOOD: 'finance.reports.generate' and 'api.reports.access'

-- DON'T: Create single-child hierarchies unnecessarily
-- BAD: 'admin' -> 'admin.only_child' -> 'admin.only_child.action'

-- DO: Flatten when appropriate
-- GOOD: 'admin.action' (skip unnecessary intermediate levels)

Maintenance and Migration

-- Function to safely restructure hierarchy
CREATE OR REPLACE FUNCTION auth.restructure_permission_hierarchy(
    _old_prefix text,
    _new_prefix text
) RETURNS integer AS $$
DECLARE
    v_affected_count integer := 0;
    v_permission record;
BEGIN
    -- Update permission codes and paths
    FOR v_permission IN
        SELECT perm_code, perm_path
        FROM auth.permission
        WHERE perm_code LIKE _old_prefix || '%'
          AND is_active = true
    LOOP
        UPDATE auth.permission
        SET
            perm_code = _new_prefix || substring(perm_code from length(_old_prefix) + 1),
            perm_path = (_new_prefix || substring(perm_code from length(_old_prefix) + 1))::ltree
        WHERE perm_code = v_permission.perm_code;

        v_affected_count := v_affected_count + 1;
    END LOOP;

    -- Update assignments
    UPDATE auth.user_permission
    SET perm_code = _new_prefix || substring(perm_code from length(_old_prefix) + 1)
    WHERE perm_code LIKE _old_prefix || '%';

    -- Update permission sets
    UPDATE auth.permission_set_item
    SET perm_code = _new_prefix || substring(perm_code from length(_old_prefix) + 1)
    WHERE perm_code LIKE _old_prefix || '%';

    -- Refresh materialized view
    PERFORM auth.refresh_permission_hierarchy_cache();

    RETURN v_affected_count;
END;
$$ LANGUAGE plpgsql;

What's Next

• Explore Permission Assignment patterns for users and groups
• Learn about Permission Caching for performance optimization
• Review Permission Sets for organizing hierarchical permissions
• Understand integration with Groups and hierarchical inheritance