Permission Model

The PostgreSQL Permissions Model implements a sophisticated, hierarchical permission system that combines direct user permissions, group-based permissions, and multi-provider identity integration. This model provides fine-grained access control while maintaining performance through caching and efficient query patterns.

Core Concepts

The permission system is built around five fundamental concepts:

graph TB
    subgraph "Permission Core"
        PERM["Permissions<br/>auth.permission<br/>- perm_code<br/>- name<br/>- description"]

        PERMSET["Permission Sets<br/>auth.permission_set<br/>- perm_set_code<br/>- name<br/>- tenant_id"]

        ASSIGN["Permission Assignment<br/>auth.user_permission<br/>- user_id OR user_group_id<br/>- perm_code OR perm_set_code<br/>- tenant_id"]
    end

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

    subgraph "Hierarchy"
        HIER["Permission Hierarchy<br/>Using PostgreSQL ltree<br/>- Dot notation: admin.users.create<br/>- Inheritance: admin.* includes admin.users.*"]
    end

    PERMSET --> PERM
    ASSIGN --> USER
    ASSIGN --> GROUP
    ASSIGN --> PERM
    ASSIGN --> PERMSET
    PERM --> HIER

Key Components:

1. Permissions: Individual access rights (e.g., users.create, reports.financial)
2. Permission Sets: Collections of related permissions (e.g., ADMIN_PERMISSIONS, USER_BASE)
3. Assignments: Links between users/groups and permissions/permission sets
4. Hierarchy: Tree structure enabling inheritance (e.g., admin.* includes admin.users.create)
5. Multi-tenancy: Tenant-specific permission sets and assignments

Database Structure

Core Permission Tables

-- Global permissions catalog (system-wide)
CREATE TABLE auth.permission (
    perm_code text PRIMARY KEY,  -- Hierarchical: 'admin.users.create'
    name text NOT NULL,
    description text,
    perm_level text DEFAULT 'standard',  -- 'system', 'admin', 'standard', 'basic'
    is_active boolean DEFAULT true,
    created_at timestamptz DEFAULT now()
);

-- Tenant-specific permission sets
CREATE TABLE auth.permission_set (
    perm_set_code text NOT NULL,
    tenant_id uuid REFERENCES auth.tenant,
    name text NOT NULL,
    description text,
    is_system_set boolean DEFAULT false,  -- System vs custom sets
    is_active boolean DEFAULT true,
    created_at timestamptz DEFAULT now(),
    PRIMARY KEY (perm_set_code, tenant_id)
);

-- Permission set contents
CREATE TABLE auth.permission_set_item (
    perm_set_code text NOT NULL,
    tenant_id uuid REFERENCES auth.tenant,
    perm_code text REFERENCES auth.permission,
    granted boolean DEFAULT true,  -- true = grant, false = deny
    created_at timestamptz DEFAULT now(),
    PRIMARY KEY (perm_set_code, tenant_id, perm_code),
    FOREIGN KEY (perm_set_code, tenant_id) REFERENCES auth.permission_set
);

-- Permission assignments to users/groups
CREATE TABLE auth.user_permission (
    user_permission_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id uuid REFERENCES auth.tenant,

    -- Target (user OR group)
    user_id uuid REFERENCES auth.user_info,
    user_group_id uuid REFERENCES auth.user_group,

    -- Permission (direct OR set)
    perm_code text REFERENCES auth.permission,
    perm_set_code text,

    -- Assignment metadata
    granted boolean DEFAULT true,
    assigned_at timestamptz DEFAULT now(),
    assigned_by uuid REFERENCES auth.user_info,
    expires_at timestamptz,

    CHECK (
        (user_id IS NOT NULL AND user_group_id IS NULL) OR
        (user_id IS NULL AND user_group_id IS NOT NULL)
    ),
    CHECK (
        (perm_code IS NOT NULL AND perm_set_code IS NULL) OR
        (perm_code IS NULL AND perm_set_code IS NOT NULL)
    ),
    FOREIGN KEY (perm_set_code, tenant_id) REFERENCES auth.permission_set
);

Permission Hierarchy Implementation

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

-- Add ltree column for efficient hierarchy queries
ALTER TABLE auth.permission ADD COLUMN perm_path ltree;

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

-- Index for hierarchy queries
CREATE INDEX idx_permission_path_gist ON auth.permission USING GIST (perm_path);
CREATE INDEX idx_permission_path_btree ON auth.permission USING BTREE (perm_path);

Permission Hierarchy

Hierarchical Structure

The permission system uses dot notation to create hierarchical relationships:

-- Example permission hierarchy
INSERT INTO auth.permission (perm_code, name, description, perm_level) VALUES
-- Root level
('admin', 'Administrator Access', 'Root administrative permissions', 'system'),
('users', 'User Management', 'User-related operations', 'admin'),
('reports', 'Reporting', 'Report generation and viewing', 'standard'),
('api', 'API Access', 'API endpoint access', 'standard'),

-- Second level
('admin.system', 'System Administration', 'System-level admin functions', 'system'),
('admin.tenants', 'Tenant Management', 'Multi-tenant administration', 'system'),
('users.create', 'Create Users', 'Permission to create new users', 'admin'),
('users.update', 'Update Users', 'Permission to modify user data', 'admin'),
('users.delete', 'Delete Users', 'Permission to remove users', 'admin'),
('users.view', 'View Users', 'Permission to view user information', 'standard'),
('reports.financial', 'Financial Reports', 'Access to financial reporting', 'admin'),
('reports.operational', 'Operational Reports', 'Access to operational reports', 'standard'),

-- Third level
('admin.system.config', 'System Configuration', 'Modify system settings', 'system'),
('admin.system.maintenance', 'System Maintenance', 'Perform maintenance tasks', 'system'),
('users.view.basic', 'Basic User View', 'View basic user information', 'basic'),
('users.view.detailed', 'Detailed User View', 'View detailed user profiles', 'standard'),
('reports.financial.salary', 'Salary Reports', 'Access salary and compensation data', 'admin'),
('reports.financial.budget', 'Budget Reports', 'Access budget and spending data', 'admin');

-- Update hierarchy paths
UPDATE auth.permission SET perm_path = perm_code::ltree;

Inheritance Patterns

-- Hierarchy inheritance examples
CREATE OR REPLACE FUNCTION auth.demonstrate_hierarchy() RETURNS TABLE (
    permission text,
    inherited_by text
) AS $$
BEGIN
    RETURN QUERY
    WITH hierarchy_demo AS (
        SELECT
            p1.perm_code as permission,
            p2.perm_code as inherited_by
        FROM auth.permission p1
        JOIN auth.permission p2 ON p2.perm_path <@ p1.perm_path
        WHERE p1.perm_code != p2.perm_code
    )
    SELECT
        h.permission::text,
        h.inherited_by::text
    FROM hierarchy_demo h
    ORDER BY h.permission, h.inherited_by;
END;
$$ LANGUAGE plpgsql;

-- Example output:
-- permission        | inherited_by
-- admin            | admin.system
-- admin            | admin.system.config
-- admin            | admin.tenants
-- users            | users.create
-- users            | users.view
-- users.view       | users.view.basic

Permission Resolution

Resolution Algorithm

The system resolves permissions using a priority-based algorithm:

flowchart TD
    START[Permission Check Request] --> CACHE{Check Permission Cache}
    CACHE -->|Hit| RETURN_CACHED[Return Cached Result]
    CACHE -->|Miss| DIRECT[Check Direct User Permissions]

    DIRECT --> FOUND_DIRECT{Direct Permission Found?}
    FOUND_DIRECT -->|Yes| CACHE_STORE[Store in Cache]
    FOUND_DIRECT -->|No| GROUP[Check Group Permissions]

    GROUP --> RESOLVE_GROUPS[Resolve User's Current Groups]
    RESOLVE_GROUPS --> CHECK_GROUP_PERMS[Check Group Permissions]
    CHECK_GROUP_PERMS --> FOUND_GROUP{Group Permission Found?}

    FOUND_GROUP -->|Yes| CACHE_STORE
    FOUND_GROUP -->|No| HIERARCHY[Check Hierarchical Permissions]

    HIERARCHY --> CHECK_PARENTS[Check Parent Permissions]
    CHECK_PARENTS --> FOUND_PARENT{Parent Permission Found?}

    FOUND_PARENT -->|Yes| CACHE_STORE
    FOUND_PARENT -->|No| DENY[Deny Access]

    CACHE_STORE --> RETURN_RESULT[Return Result]
    DENY --> RETURN_RESULT
    RETURN_CACHED --> END[End]
    RETURN_RESULT --> END

Core Resolution Function

CREATE OR REPLACE FUNCTION auth.has_permission(
    _tenant_id uuid,
    _user_id uuid,
    _perm_code text,
    _throw_err boolean DEFAULT true
) RETURNS boolean AS $$
DECLARE
    v_has_permission boolean := false;
    v_cache_key text;
    v_cached_result boolean;
BEGIN
    -- 1. Check permission cache first
    v_cache_key := _tenant_id::text || ':' || _user_id::text || ':' || _perm_code;

    SELECT has_permission INTO v_cached_result
    FROM auth.permission_cache
    WHERE cache_key = v_cache_key
      AND expires_at > now()
    LIMIT 1;

    IF v_cached_result IS NOT NULL THEN
        RETURN v_cached_result;
    END IF;

    -- 2. Check direct user permissions
    SELECT true INTO v_has_permission
    FROM auth.user_permission up
    WHERE up.tenant_id = _tenant_id
      AND up.user_id = _user_id
      AND up.granted = true
      AND (up.expires_at IS NULL OR up.expires_at > now())
      AND (
          -- Direct permission match
          up.perm_code = _perm_code
          OR
          -- Permission set contains the permission
          (up.perm_set_code IS NOT NULL
           AND EXISTS (
               SELECT 1 FROM auth.permission_set_item psi
               WHERE psi.perm_set_code = up.perm_set_code
                 AND psi.tenant_id = _tenant_id
                 AND psi.perm_code = _perm_code
                 AND psi.granted = true
           ))
          OR
          -- Hierarchical permission match
          EXISTS (
              SELECT 1 FROM auth.permission p
              WHERE (p.perm_code = up.perm_code OR
                     (up.perm_set_code IS NOT NULL AND EXISTS (
                         SELECT 1 FROM auth.permission_set_item psi2
                         WHERE psi2.perm_set_code = up.perm_set_code
                           AND psi2.tenant_id = _tenant_id
                           AND psi2.perm_code = p.perm_code
                           AND psi2.granted = true
                     )))
                AND _perm_code::ltree <@ p.perm_path
          )
      )
    LIMIT 1;

    IF v_has_permission THEN
        -- Cache positive result
        INSERT INTO auth.permission_cache (cache_key, user_id, tenant_id, perm_code, has_permission)
        VALUES (v_cache_key, _user_id, _tenant_id, _perm_code, true)
        ON CONFLICT (cache_key) DO UPDATE SET
            has_permission = true,
            expires_at = now() + interval '15 minutes';
        RETURN true;
    END IF;

    -- 3. Check group permissions
    SELECT true INTO v_has_permission
    FROM auth.resolve_user_group_permissions(_tenant_id, _user_id) ugp
    WHERE ugp.perm_code = _perm_code
       OR _perm_code::ltree <@ ugp.perm_path
    LIMIT 1;

    -- Cache result (positive or negative)
    INSERT INTO auth.permission_cache (cache_key, user_id, tenant_id, perm_code, has_permission)
    VALUES (v_cache_key, _user_id, _tenant_id, _perm_code, COALESCE(v_has_permission, false))
    ON CONFLICT (cache_key) DO UPDATE SET
        has_permission = COALESCE(v_has_permission, false),
        expires_at = now() + interval '15 minutes';

    -- Handle denial
    IF NOT COALESCE(v_has_permission, false) AND _throw_err THEN
        PERFORM auth.throw_permission_denied(_tenant_id, _user_id, _perm_code);
    END IF;

    RETURN COALESCE(v_has_permission, false);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Group Permission Resolution

CREATE OR REPLACE FUNCTION auth.resolve_user_group_permissions(
    _tenant_id uuid,
    _user_id uuid
) RETURNS TABLE (
    perm_code text,
    perm_path ltree,
    source_group_id uuid,
    source_group_code text
) AS $$
BEGIN
    RETURN QUERY
    WITH user_groups AS (
        -- Get all groups user belongs to (internal, external, hybrid)
        SELECT DISTINCT ug.user_group_id, ug.code as group_code
        FROM auth.user_group ug
        WHERE ug.tenant_id = _tenant_id
          AND ug.is_active = true
          AND (
              -- Internal/hybrid groups: direct membership
              EXISTS (
                  SELECT 1 FROM auth.user_group_member ugm
                  WHERE ugm.user_group_id = ug.user_group_id
                    AND ugm.user_id = _user_id
                    AND ugm.is_active = true
              )
              OR
              -- External/hybrid groups: provider mappings
              (ug.group_type IN ('external', 'hybrid')
               AND EXISTS (
                   SELECT 1 FROM auth.user_group_mapping ugmp
                   JOIN auth.user_identity ui ON ugmp.provider_id = ui.provider_id
                   WHERE ugmp.user_group_id = ug.user_group_id
                     AND ui.user_id = _user_id
                     AND ugmp.is_active = true
                     AND ui.is_last_used = true
                     AND ui.is_active = true
                     AND (
                         (ugmp.provider_group_pattern IS NOT NULL
                          AND EXISTS (
                              SELECT 1 FROM unnest(ui.provider_groups) AS pg(name)
                              WHERE pg.name ~ ugmp.provider_group_pattern
                          ))
                         OR
                         (ugmp.provider_role_pattern IS NOT NULL
                          AND EXISTS (
                              SELECT 1 FROM unnest(ui.provider_roles) AS pr(name)
                              WHERE pr.name ~ ugmp.provider_role_pattern
                          ))
                     )
               ))
          )
    ),
    group_permissions AS (
        SELECT
            p.perm_code,
            p.perm_path,
            ug.user_group_id,
            ug.group_code
        FROM user_groups ug
        JOIN auth.user_permission up ON ug.user_group_id = up.user_group_id
        JOIN auth.permission p ON (
            p.perm_code = up.perm_code
            OR
            (up.perm_set_code IS NOT NULL
             AND EXISTS (
                 SELECT 1 FROM auth.permission_set_item psi
                 WHERE psi.perm_set_code = up.perm_set_code
                   AND psi.tenant_id = _tenant_id
                   AND psi.perm_code = p.perm_code
                   AND psi.granted = true
             ))
        )
        WHERE up.tenant_id = _tenant_id
          AND up.granted = true
          AND (up.expires_at IS NULL OR up.expires_at > now())
    )
    SELECT
        gp.perm_code,
        gp.perm_path,
        gp.user_group_id,
        gp.group_code
    FROM group_permissions gp;
END;
$$ LANGUAGE plpgsql;

Permission Levels and Security

Permission Levels

-- Permission levels define security boundaries
CREATE TYPE auth.permission_level AS ENUM (
    'system',    -- System administrators only
    'admin',     -- Tenant administrators
    'standard',  -- Regular privileged operations
    'basic'      -- Basic user operations
);

-- System-level permissions (highest security)
INSERT INTO auth.permission (perm_code, name, perm_level) VALUES
('admin.system.config', 'System Configuration', 'system'),
('admin.system.security', 'Security Administration', 'system'),
('admin.tenants.create', 'Create Tenants', 'system'),
('admin.tenants.delete', 'Delete Tenants', 'system');

-- Admin-level permissions
INSERT INTO auth.permission (perm_code, name, perm_level) VALUES
('admin.users.create', 'Create Users', 'admin'),
('admin.users.delete', 'Delete Users', 'admin'),
('admin.permissions.assign', 'Assign Permissions', 'admin'),
('admin.groups.manage', 'Manage Groups', 'admin');

-- Standard permissions
INSERT INTO auth.permission (perm_code, name, perm_level) VALUES
('users.view.detailed', 'View User Details', 'standard'),
('reports.generate', 'Generate Reports', 'standard'),
('data.export', 'Export Data', 'standard');

-- Basic permissions
INSERT INTO auth.permission (perm_code, name, perm_level) VALUES
('users.view.basic', 'View Basic User Info', 'basic'),
('profile.update.own', 'Update Own Profile', 'basic'),
('dashboard.view', 'View Dashboard', 'basic');

Security Enforcement

-- Security-aware permission checking
CREATE OR REPLACE FUNCTION auth.has_permission_secure(
    _tenant_id uuid,
    _user_id uuid,
    _perm_code text,
    _required_level auth.permission_level DEFAULT 'basic'
) RETURNS boolean AS $$
DECLARE
    v_has_permission boolean;
    v_permission_level auth.permission_level;
    v_user_max_level auth.permission_level;
BEGIN
    -- Get permission level
    SELECT perm_level INTO v_permission_level
    FROM auth.permission
    WHERE perm_code = _perm_code;

    -- Check if user's maximum permission level is sufficient
    SELECT MAX(p.perm_level::text)::auth.permission_level INTO v_user_max_level
    FROM auth.resolve_user_all_permissions(_tenant_id, _user_id) up
    JOIN auth.permission p ON up.perm_code = p.perm_code;

    -- Enforce level hierarchy: system > admin > standard > basic
    IF v_permission_level = 'system' AND v_user_max_level NOT IN ('system') THEN
        RETURN false;
    ELSIF v_permission_level = 'admin' AND v_user_max_level NOT IN ('system', 'admin') THEN
        RETURN false;
    ELSIF v_permission_level = 'standard' AND v_user_max_level = 'basic' THEN
        RETURN false;
    END IF;

    -- Standard permission check
    RETURN auth.has_permission(_tenant_id, _user_id, _perm_code, false);
END;
$$ LANGUAGE plpgsql;

Common Permission Patterns

Application Permission Sets

-- Create common permission sets for different user types
DO $$
DECLARE
    v_tenant_id uuid := 'example-tenant-uuid';
BEGIN
    -- Basic user permission set
    PERFORM auth.create_permission_set(v_tenant_id, 'BASIC_USER', 'Basic User Permissions');
    PERFORM auth.add_permission_to_set(v_tenant_id, 'BASIC_USER', 'dashboard.view');
    PERFORM auth.add_permission_to_set(v_tenant_id, 'BASIC_USER', 'profile.update.own');
    PERFORM auth.add_permission_to_set(v_tenant_id, 'BASIC_USER', 'users.view.basic');

    -- Standard user permission set
    PERFORM auth.create_permission_set(v_tenant_id, 'STANDARD_USER', 'Standard User Permissions');
    PERFORM auth.add_permission_to_set(v_tenant_id, 'STANDARD_USER', 'BASIC_USER'); -- Include basic
    PERFORM auth.add_permission_to_set(v_tenant_id, 'STANDARD_USER', 'users.view.detailed');
    PERFORM auth.add_permission_to_set(v_tenant_id, 'STANDARD_USER', 'reports.generate');
    PERFORM auth.add_permission_to_set(v_tenant_id, 'STANDARD_USER', 'data.export');

    -- Manager permission set
    PERFORM auth.create_permission_set(v_tenant_id, 'MANAGER', 'Manager Permissions');
    PERFORM auth.add_permission_to_set(v_tenant_id, 'MANAGER', 'STANDARD_USER'); -- Include standard
    PERFORM auth.add_permission_to_set(v_tenant_id, 'MANAGER', 'users.update');
    PERFORM auth.add_permission_to_set(v_tenant_id, 'MANAGER', 'reports.financial');
    PERFORM auth.add_permission_to_set(v_tenant_id, 'MANAGER', 'groups.manage.own');

    -- Administrator permission set
    PERFORM auth.create_permission_set(v_tenant_id, 'ADMIN', 'Administrator Permissions');
    PERFORM auth.add_permission_to_set(v_tenant_id, 'ADMIN', 'MANAGER'); -- Include manager
    PERFORM auth.add_permission_to_set(v_tenant_id, 'ADMIN', 'admin'); -- Root admin permissions
END $$;

Permission Inheritance Examples

-- Demonstrate hierarchical permissions
WITH permission_examples AS (
    SELECT
        'admin' as granted_permission,
        'admin.users.create' as requested_permission,
        auth.has_permission('tenant-uuid', 'user-uuid', 'admin.users.create', false) as has_access,
        'Parent permission includes child' as explanation

    UNION ALL

    SELECT
        'users.view',
        'users.view.detailed',
        auth.has_permission('tenant-uuid', 'user-uuid', 'users.view.detailed', false),
        'Specific permission inherits from general'

    UNION ALL

    SELECT
        'reports',
        'reports.financial.salary',
        auth.has_permission('tenant-uuid', 'user-uuid', 'reports.financial.salary', false),
        'Deep hierarchy inheritance'
)
SELECT * FROM permission_examples;

Performance Considerations

Essential Indexes

-- Critical indexes for permission resolution performance
CREATE INDEX CONCURRENTLY idx_user_permission_resolution
    ON auth.user_permission(tenant_id, user_id, granted)
    WHERE granted = true;

CREATE INDEX CONCURRENTLY idx_user_permission_group_resolution
    ON auth.user_permission(tenant_id, user_group_id, granted)
    WHERE granted = true;

CREATE INDEX CONCURRENTLY idx_permission_set_item_lookup
    ON auth.permission_set_item(perm_set_code, tenant_id, granted)
    WHERE granted = true;

CREATE INDEX CONCURRENTLY idx_user_group_member_active
    ON auth.user_group_member(user_group_id, user_id)
    WHERE is_active = true;

-- Hierarchy-specific indexes
CREATE INDEX CONCURRENTLY idx_permission_hierarchy_ancestor
    ON auth.permission USING GIST (perm_path)
    WHERE is_active = true;

CREATE INDEX CONCURRENTLY idx_permission_hierarchy_descendant
    ON auth.permission USING BTREE (perm_path)
    WHERE is_active = true;

Query Optimization

-- Optimized permission check for high-frequency operations
CREATE OR REPLACE FUNCTION auth.has_permission_fast(
    _tenant_id uuid,
    _user_id uuid,
    _perm_code text
) RETURNS boolean AS $$
DECLARE
    v_result boolean;
BEGIN
    -- Single query combining all checks
    SELECT true INTO v_result
    FROM (
        -- Direct user permissions
        SELECT 1
        FROM auth.user_permission up
        WHERE up.tenant_id = _tenant_id
          AND up.user_id = _user_id
          AND up.granted = true
          AND (up.expires_at IS NULL OR up.expires_at > now())
          AND (
              up.perm_code = _perm_code
              OR EXISTS (
                  SELECT 1 FROM auth.permission_set_item psi
                  WHERE psi.perm_set_code = up.perm_set_code
                    AND psi.tenant_id = _tenant_id
                    AND psi.perm_code = _perm_code
                    AND psi.granted = true
              )
              OR EXISTS (
                  SELECT 1 FROM auth.permission p
                  WHERE p.perm_code = up.perm_code
                    AND _perm_code::ltree <@ p.perm_path
              )
          )

        UNION ALL

        -- Group permissions (optimized subquery)
        SELECT 1
        FROM auth.user_group_member ugm
        JOIN auth.user_permission up ON ugm.user_group_id = up.user_group_id
        WHERE ugm.user_id = _user_id
          AND ugm.is_active = true
          AND up.tenant_id = _tenant_id
          AND up.granted = true
          AND (up.expires_at IS NULL OR up.expires_at > now())
          AND (
              up.perm_code = _perm_code
              OR EXISTS (
                  SELECT 1 FROM auth.permission_set_item psi
                  WHERE psi.perm_set_code = up.perm_set_code
                    AND psi.tenant_id = _tenant_id
                    AND psi.perm_code = _perm_code
                    AND psi.granted = true
              )
          )
    ) AS permission_check
    LIMIT 1;

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

What's Next

• Learn about Hierarchical Permissions in depth
• Explore Permission Sets for organizing permissions
• Understand Permission Assignment patterns
• Review Permission Caching for performance optimization