Permission Sets¶

Permission Sets are tenant-specific collections of permissions that group related access rights together. They provide a reusable way to assign multiple permissions at once, making permission management more maintainable and consistent.

Key Concepts¶

Global Permissions vs Tenant Permission Sets¶

Global Permissions (auth.permission): - Defined once for the entire system - Hierarchical structure using PostgreSQL ltree - Same permission tree for all tenants - Examples: users.create, reports.view, admin.system

Permission Sets (auth.perm_set): - Tenant-specific groupings of global permissions - Each tenant can create unique sets - Reusable across users and groups within a tenant - Examples: "Administrator", "Read-Only User", "Department Manager"

Database Structure¶

Permission Set Tables¶

-- Permission sets per tenant
CREATE TABLE auth.perm_set (
    perm_set_id uuid PRIMARY KEY,
    tenant_id uuid REFERENCES auth.tenant,
    code text NOT NULL,
    name text NOT NULL,
    description text,
    is_active boolean DEFAULT true,
    created_at timestamptz DEFAULT now(),
    updated_at timestamptz DEFAULT now(),
    UNIQUE (tenant_id, code)
);

-- Links permissions to permission sets
CREATE TABLE auth.perm_set_perm (
    perm_set_id uuid REFERENCES auth.perm_set,
    permission_id uuid REFERENCES auth.permission,
    added_at timestamptz DEFAULT now(),
    PRIMARY KEY (perm_set_id, permission_id)
);

Creating Permission Sets¶

Basic Permission Set¶

-- Create a permission set
SELECT auth.create_perm_set(
    _tenant_id := 'tenant-uuid',
    _code := 'VIEWER',
    _name := 'Viewer Role',
    _description := 'Read-only access to most resources'
);

Add Permissions to Set¶

-- Add individual permissions to a set
SELECT auth.add_perm_set_permissions(
    _tenant_id := 'tenant-uuid',
    _perm_set_code := 'VIEWER',
    _perm_codes := ARRAY[
        'users.read',
        'reports.view',
        'dashboard.access'
    ]
);

Create Complete Permission Set¶

-- Create and populate a permission set in one transaction
DO $$
DECLARE
    v_perm_set_id uuid;
BEGIN
    -- Create the permission set
    v_perm_set_id := auth.create_perm_set(
        'tenant-uuid',
        'MANAGER',
        'Manager Role',
        'Management access with limited admin rights'
    );

    -- Add permissions
    INSERT INTO auth.perm_set_perm (perm_set_id, permission_id)
    SELECT
        v_perm_set_id,
        p.permission_id
    FROM auth.permission p
    WHERE p.code IN (
        'users.read',
        'users.update',
        'reports',
        'dashboard',
        'teams.manage'
    );
END $$;

Common Permission Set Patterns¶

Administrator Set¶

-- Full administrative access
SELECT auth.create_perm_set(
    _tenant_id := 'tenant-uuid',
    _code := 'ADMIN',
    _name := 'Administrator',
    _description := 'Full system access'
);

-- Add all permissions (or specific admin permissions)
INSERT INTO auth.perm_set_perm (perm_set_id, permission_id)
SELECT
    ps.perm_set_id,
    p.permission_id
FROM auth.perm_set ps
CROSS JOIN auth.permission p
WHERE ps.code = 'ADMIN'
  AND ps.tenant_id = 'tenant-uuid';

Read-Only Set¶

-- Read-only access
SELECT auth.create_perm_set(
    _tenant_id := 'tenant-uuid',
    _code := 'READONLY',
    _name := 'Read Only User',
    _description := 'View access without modification rights'
);

-- Add only read/view permissions
INSERT INTO auth.perm_set_perm (perm_set_id, permission_id)
SELECT
    ps.perm_set_id,
    p.permission_id
FROM auth.perm_set ps
CROSS JOIN auth.permission p
WHERE ps.code = 'READONLY'
  AND ps.tenant_id = 'tenant-uuid'
  AND (p.code LIKE '%.read%' OR p.code LIKE '%.view%');

Department-Specific Sets¶

-- HR Department permissions
SELECT auth.create_perm_set(
    _tenant_id := 'tenant-uuid',
    _code := 'HR_STAFF',
    _name := 'HR Staff',
    _description := 'Human Resources department permissions'
);

SELECT auth.add_perm_set_permissions(
    _tenant_id := 'tenant-uuid',
    _perm_set_code := 'HR_STAFF',
    _perm_codes := ARRAY[
        'employees',
        'payroll.view',
        'benefits.manage',
        'recruitment'
    ]
);

Managing Permission Sets¶

Update Permission Set¶

-- Update permission set details
UPDATE auth.perm_set
SET
    name = 'Updated Name',
    description = 'Updated description',
    updated_at = now()
WHERE tenant_id = 'tenant-uuid'
  AND code = 'VIEWER';

Add Permissions¶

-- Add new permissions to existing set
INSERT INTO auth.perm_set_perm (perm_set_id, permission_id)
SELECT
    ps.perm_set_id,
    p.permission_id
FROM auth.perm_set ps
CROSS JOIN auth.permission p
WHERE ps.tenant_id = 'tenant-uuid'
  AND ps.code = 'MANAGER'
  AND p.code = 'new.permission'
ON CONFLICT DO NOTHING;

Remove Permissions¶

-- Remove specific permissions from set
DELETE FROM auth.perm_set_perm
WHERE perm_set_id = (
    SELECT perm_set_id FROM auth.perm_set
    WHERE tenant_id = 'tenant-uuid' AND code = 'MANAGER'
)
AND permission_id IN (
    SELECT permission_id FROM auth.permission
    WHERE code IN ('permission.to.remove', 'another.permission')
);

Deactivate Permission Set¶

-- Soft delete permission set
UPDATE auth.perm_set
SET is_active = false, updated_at = now()
WHERE tenant_id = 'tenant-uuid' AND code = 'DEPRECATED_SET';

Copying Permission Sets¶

Within Same Tenant¶

-- Copy a permission set within tenant
SELECT auth.copy_perm_set(
    _tenant_id := 'tenant-uuid',
    _from_perm_set_code := 'VIEWER',
    _to_perm_set_code := 'VIEWER_PLUS',
    _to_name := 'Viewer Plus',
    _to_description := 'Extended viewer permissions'
);

Across Tenants¶

-- Duplicate permission set to another tenant
SELECT auth.duplicate_perm_set(
    _from_tenant_id := 'source-tenant-uuid',
    _to_tenant_id := 'target-tenant-uuid',
    _perm_set_code := 'STANDARD_USER',
    _new_code := 'STANDARD_USER',
    _new_name := 'Standard User'
);

Assigning Permission Sets¶

Assign to User¶

-- Assign permission set to user
SELECT auth.assign_permission(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid',
    _user_group_id := NULL,
    _perm_set_code := 'MANAGER',
    _perm_code := NULL
);

Assign to Group¶

-- Assign permission set to group
SELECT auth.assign_permission(
    _tenant_id := 'tenant-uuid',
    _user_id := NULL,
    _user_group_id := 'group-uuid',
    _perm_set_code := 'VIEWER',
    _perm_code := NULL
);

Multiple Assignments¶

-- Assign multiple permission sets
DO $$
BEGIN
    -- User gets both viewer and editor sets
    PERFORM auth.assign_permission('tenant-uuid', 'user-uuid', NULL, 'VIEWER', NULL);
    PERFORM auth.assign_permission('tenant-uuid', 'user-uuid', NULL, 'EDITOR', NULL);
END $$;

Querying Permission Sets¶

List Tenant Permission Sets¶

-- Get all permission sets for a tenant
SELECT
    ps.code,
    ps.name,
    ps.description,
    COUNT(psp.permission_id) as permission_count,
    ps.is_active
FROM auth.perm_set ps
LEFT JOIN auth.perm_set_perm psp ON ps.perm_set_id = psp.perm_set_id
WHERE ps.tenant_id = 'tenant-uuid'
GROUP BY ps.perm_set_id
ORDER BY ps.name;

View Permission Set Contents¶

-- List all permissions in a set
SELECT
    p.code,
    p.name,
    p.description,
    p.path::text as hierarchy
FROM auth.perm_set ps
JOIN auth.perm_set_perm psp ON ps.perm_set_id = psp.perm_set_id
JOIN auth.permission p ON psp.permission_id = p.permission_id
WHERE ps.tenant_id = 'tenant-uuid'
  AND ps.code = 'MANAGER'
ORDER BY p.path;

Find Users with Permission Set¶

-- Find all users assigned a specific permission set
SELECT DISTINCT
    u.user_id,
    u.username,
    u.display_name,
    CASE
        WHEN pa.user_id IS NOT NULL THEN 'Direct'
        ELSE 'Via Group'
    END as assignment_type
FROM auth.permission_assignment pa
JOIN auth.perm_set ps ON pa.perm_set_id = ps.perm_set_id
LEFT JOIN auth.user_info u ON pa.user_id = u.user_id
LEFT JOIN auth.user_group ug ON pa.user_group_id = ug.user_group_id
LEFT JOIN auth.user_group_member ugm ON ug.user_group_id = ugm.user_group_id
LEFT JOIN auth.user_info u2 ON ugm.user_id = u2.user_id
WHERE ps.tenant_id = 'tenant-uuid'
  AND ps.code = 'ADMIN'
  AND (u.user_id IS NOT NULL OR u2.user_id IS NOT NULL);

Permission Set Inheritance¶

Hierarchical Sets¶

-- Create base and extended permission sets
DO $$
DECLARE
    v_base_set_id uuid;
    v_extended_set_id uuid;
BEGIN
    -- Base set
    v_base_set_id := auth.create_perm_set(
        'tenant-uuid', 'BASE_USER', 'Base User', 'Minimum permissions'
    );

    -- Extended set (includes base + more)
    v_extended_set_id := auth.create_perm_set(
        'tenant-uuid', 'POWER_USER', 'Power User', 'Extended permissions'
    );

    -- Copy base permissions to extended
    INSERT INTO auth.perm_set_perm (perm_set_id, permission_id)
    SELECT v_extended_set_id, permission_id
    FROM auth.perm_set_perm
    WHERE perm_set_id = v_base_set_id;

    -- Add additional permissions to extended
    INSERT INTO auth.perm_set_perm
    SELECT v_extended_set_id, permission_id
    FROM auth.permission
    WHERE code IN ('advanced.feature1', 'advanced.feature2');
END $$;

Best Practices¶

Naming Conventions¶

Use Clear Codes: ADMIN, MANAGER, VIEWER, EDITOR
Descriptive Names: "Financial Administrator" not just "Admin"
Document Purpose: Clear descriptions for each set
Version in Code: VIEWER_V2 when updating significantly

Permission Set Design¶

Start Minimal: Begin with least privilege
Group Logically: Related permissions together
Avoid Overlap: Minimize duplicate permissions across sets
Regular Review: Audit and update sets periodically

Performance Optimization¶

-- Create index for permission lookups
CREATE INDEX idx_perm_set_perm_lookup
ON auth.perm_set_perm (perm_set_id, permission_id);

-- Materialized view for permission set analysis
CREATE MATERIALIZED VIEW permission_set_summary AS
SELECT
    ps.tenant_id,
    ps.code,
    ps.name,
    array_agg(p.code ORDER BY p.code) as permissions
FROM auth.perm_set ps
JOIN auth.perm_set_perm psp ON ps.perm_set_id = psp.perm_set_id
JOIN auth.permission p ON psp.permission_id = p.permission_id
WHERE ps.is_active = true
GROUP BY ps.tenant_id, ps.perm_set_id;

Common Issues and Solutions¶

Duplicate Permissions¶

-- Find users with redundant permission assignments
SELECT
    u.username,
    array_agg(DISTINCT ps.code) as permission_sets,
    COUNT(DISTINCT p.permission_id) as unique_permissions,
    COUNT(p.permission_id) as total_assignments
FROM auth.permission_assignment pa
JOIN auth.user_info u ON pa.user_id = u.user_id
JOIN auth.perm_set ps ON pa.perm_set_id = ps.perm_set_id
JOIN auth.perm_set_perm psp ON ps.perm_set_id = psp.perm_set_id
JOIN auth.permission p ON psp.permission_id = p.permission_id
GROUP BY u.user_id
HAVING COUNT(p.permission_id) > COUNT(DISTINCT p.permission_id);

Orphaned Assignments¶

-- Find assignments to inactive permission sets
SELECT
    pa.*,
    ps.code as inactive_set
FROM auth.permission_assignment pa
JOIN auth.perm_set ps ON pa.perm_set_id = ps.perm_set_id
WHERE ps.is_active = false;

What's Next¶

Understand the Permission Model
Learn about Permission Assignment
Explore Hierarchical Permissions
Review Permission Caching strategies