Permission Model¶

The PostgreSQL Permissions Model implements a hierarchical permission system with clear separation between global permissions, tenant-specific permission sets, and flexible assignment to users and groups. Permissions are checked via a cache-backed function that transparently recalculates when stale, providing both correctness and performance.

Permissions (Global)¶

Permissions are the atomic units of authorization. They form a global tree structure shared across all tenants, stored in the auth.permission table. The hierarchy is implemented using PostgreSQL's ltree extension, which enables efficient ancestor/descendant queries on dot-separated paths.

Hierarchical Structure¶

Each permission has a full_code expressed as an ltree path. Child permissions inherit their parent's path prefix:

users                                    -- parent (non-assignable)
users.create_user                        -- child (assignable)
users.read_users                         -- child (non-assignable)
users.read_users.read_gdpr_protected_data  -- grandchild (assignable)
orders                                   -- parent (non-assignable)
orders.view                              -- child (assignable)
orders.cancel_order                      -- child (assignable)

When a parent permission is assigned (e.g. users), the system resolves all its descendant permissions at query time via ltree's <@ operator. This means assigning users effectively grants users.create_user, users.read_users, users.read_users.read_gdpr_protected_data, and any other permission under the users subtree.

Assignability

Permissions with is_assignable = false serve as organizational containers (e.g. users, orders). They cannot be checked individually -- only their assignable descendants are resolved during permission evaluation.

Permission Table¶

The auth.permission table stores the global permission tree:

Column	Type	Default	Description
`permission_id`	integer	auto-generated	Primary key
`code`	text	--	Short name for this node (e.g. `create_user`)
`full_code`	ltree	--	Full hierarchical path (e.g. `users.create_user`); unique
`node_path`	ltree	--	Path used for ancestor/descendant queries
`has_children`	boolean	`false`	Whether this permission has child permissions
`is_assignable`	boolean	`true`	Whether this permission can be assigned to users/groups
`short_code`	text	--	Optional short alias for the permission; unique when set
`source`	text	--	Identifies which module created this permission (used by ensure functions)
`nrm_search_data`	text	--	Normalized data for trigram search

Display text

title, full_title, and description are stored in public.translation (with data_group = 'permission' and context = 'title'/'full_title'), not as columns on the table. The full_title breadcrumb is precomputed into translations when a permission is created or its title changes.

Key indexes:

uq_permission_full_code -- unique index on full_code for fast lookup by code
ix_permission_node_path -- GiST index on node_path for efficient ltree ancestor/descendant queries
uq_permission_short_code -- unique partial index on short_code (where not null)

Short Codes¶

Permissions can have an optional short_code -- a compact alias that is easier to transmit in JWTs or API responses. For example, a permission with full_code = 'users.read_users.read_gdpr_protected_data' might have short_code = 'GDPR'. Short codes are stored alongside full codes in the permission cache, so applications can use either form.

Permission Sets (Tenant-Specific)¶

Permission sets are tenant-specific collections of global permissions, representing roles like "Admin", "Editor", or "Viewer". Each tenant can define different permission sets with different combinations of permissions, even though the underlying permission tree is global.

Permission Set Table¶

The auth.perm_set table stores permission sets:

Column	Type	Default	Description
`perm_set_id`	integer	auto-generated	Primary key
`tenant_id`	integer	--	Tenant this set belongs to; references `auth.tenant`
`code`	text	--	Short code; unique per tenant
`is_system`	boolean	`false`	Whether this is a system-managed set (protected from deletion)
`is_assignable`	boolean	`true`	Whether this set can be assigned to users/groups
`source`	text	--	Identifies the module that created this set (used by ensure functions)
`nrm_search_data`	text	--	Normalized data for trigram search

Display text (title) is stored in public.translation with data_group = 'perm_set'.

Uniqueness: The combination of code and tenant_id is unique -- different tenants can have permission sets with the same code but different permission contents.

Permission Set Permissions (Join Table)¶

The auth.perm_set_perm table links individual permissions into permission sets:

Column	Type	Default	Description
`psp_id`	integer	auto-generated	Primary key
`perm_set_id`	integer	--	References `auth.perm_set`; cascades on delete
`permission_id`	integer	--	References `auth.permission`; cascades on delete

Uniqueness: Each permission can only appear once in a given permission set (perm_set_id, permission_id).

Per-Tenant Customization¶

Because permission sets are scoped to tenants, each tenant can define its own role structure:

-- Tenant 1: "Editor" has read + write
select auth.create_perm_set('admin', 1, null, 'Editor', 1);
select auth.create_perm_set_permissions('admin', 1, null, 'editor', 1,
    array['documents.read_documents', 'documents.write_documents']);

-- Tenant 2: "Editor" has only read (more restricted)
select auth.create_perm_set('admin', 1, null, 'Editor', 2);
select auth.create_perm_set_permissions('admin', 1, null, 'editor', 2,
    array['documents.read_documents']);

Permission sets can also be copied between tenants using auth.copy_perm_set() and duplicated within a tenant using auth.duplicate_perm_set().

Effective Permissions View¶

The auth.effective_permissions view resolves which actual (leaf) permissions each permission set grants, accounting for the ltree hierarchy. It joins perm_set -> perm_set_perm -> permission and expands parent permissions to include all their descendants:

select perm_set_code, permission_code, permission_short_code
from auth.effective_permissions
where perm_set_code = 'document_editor';

Permission Assignment¶

Permissions are granted to users and groups through the auth.permission_assignment table. Each assignment links a target (user or group) to a grant (permission set or individual permission) within a tenant.

Assignment Table¶

The auth.permission_assignment table:

Column	Type	Default	Description
`assignment_id`	bigint	auto-generated	Primary key
`tenant_id`	integer	--	Tenant context; references `auth.tenant`
`user_group_id`	integer	--	Target group (null if assigning to a user)
`user_id`	bigint	--	Target user (null if assigning to a group)
`perm_set_id`	integer	--	Permission set being assigned (null if assigning individual permission)
`permission_id`	integer	--	Individual permission being assigned (null if assigning a set)

Constraints:

pa_either_object -- at least one of user_group_id or user_id must be non-null
pa_either_perm -- at least one of perm_set_id or permission_id must be non-null

This design supports four assignment combinations:

Target	Grant	Example
User + Permission Set	Assign a role to a user	User 42 gets "Document Editor" set
User + Permission	Assign a single permission to a user	User 42 gets `documents.write_documents`
Group + Permission Set	Assign a role to a group	"Editors" group gets "Document Editor" set
Group + Permission	Assign a single permission to a group	"Editors" group gets `documents.write_documents`

Inheritance Model¶

A user's effective permissions are the union of:

Direct user assignments -- permissions or permission sets assigned directly to the user
Group assignments -- permissions or permission sets assigned to any group the user belongs to

graph TB
    U["User"]

    subgraph "Direct Assignments"
        DA_PS["Permission Set A"]
        DA_P["Permission X"]
    end

    subgraph "Group Membership"
        G1["Group: Editors"]
        G2["Group: Reviewers"]
    end

    subgraph "Group Assignments"
        GA_PS1["Permission Set B"]
        GA_PS2["Permission Set C"]
        GA_P1["Permission Y"]
    end

    subgraph "Resolved Permissions"
        RP["Effective Permissions<br/>(union of all)"]
    end

    U --> DA_PS
    U --> DA_P
    U --> G1
    U --> G2
    G1 --> GA_PS1
    G1 --> GA_P1
    G2 --> GA_PS2

    DA_PS --> RP
    DA_P --> RP
    GA_PS1 --> RP
    GA_PS2 --> RP
    GA_P1 --> RP

Group types and permissions

Group membership can come from multiple sources: direct membership (internal groups), identity provider mappings (external groups), or both (hybrid groups). Regardless of how a user became a member, the permission inheritance works the same way. See Group Types for details on how membership is determined.

Permission Checking¶

The auth.has_permission() and auth.has_permissions() functions are the primary authorization mechanism. Every secured function in the auth and public schemas calls one of these before proceeding.

How `has_permissions` Works¶

The auth.has_permissions function implements the following logic:

flowchart TD
    A["has_permissions(user_id, perm_codes, tenant_id)"] --> B{user_id = 1?}
    B -- Yes --> R1["Return TRUE<br/>(system user bypass)"]
    B -- No --> C{Is tenant owner?}
    C -- Yes --> R1
    C -- No --> D{Cache exists<br/>and not expired?}
    D -- Yes --> F["Read permissions<br/>from cache"]
    D -- No --> E1["Recalculate groups<br/>(unsecure.recalculate_user_groups)"]
    E1 --> E2["Recalculate permissions<br/>(unsecure.recalculate_user_permissions)"]
    E2 --> F
    F --> G{Any requested<br/>permission in<br/>cached list?}
    G -- Yes --> R2["Return TRUE"]
    G -- No --> H{_throw_err?}
    H -- Yes --> R3["Log to journal +<br/>raise error 32001"]
    H -- No --> R4["Return FALSE"]

Key Behaviors¶

System user bypass: User ID 1 (the system user) always passes all permission checks. This is the migration/seed user and should never be used at runtime. Use service accounts instead.

Tenant owner bypass: Users who are recorded as owners of the tenant (in auth.owner) bypass all permission checks for that tenant.

Cache-first evaluation: The function first checks the auth.user_permission_cache table. If a valid (non-expired) cache entry exists, it reads the pre-computed permissions text array and checks whether any requested code is in the list. No joins are needed at check time.

Transparent recalculation: If the cache is missing or expired, the function calls unsecure.recalculate_user_groups (to resolve group membership from identity provider mappings) and then unsecure.recalculate_user_permissions (to compute the full permission list from all sources). The result is written back to the cache.

Silent mode: When _throw_err := false, the function returns false instead of raising an exception. This is useful for conditional logic where the absence of a permission is not an error.

Usage Patterns¶

-- Throw exception if denied (default, used in most functions)
perform auth.has_permission(_user_id, _correlation_id, 'orders.cancel_order', _tenant_id);

-- Silent check for conditional logic
if auth.has_permission(_user_id, _correlation_id, 'orders.view', _tenant_id, _throw_err := false) then
    -- user has permission, include sensitive data
end if;

-- Check multiple permissions (passes if user has ANY of them)
perform auth.has_permissions(_user_id, _correlation_id,
    array['orders.view', 'orders.manage'],
    _tenant_id);

For complete function signatures, see Permissions & Authorization Functions.

Permission Cache¶

The permission cache eliminates expensive multi-join queries on every permission check. The system precomputes each user's full list of effective permissions per tenant and stores it in auth.user_permission_cache.

Cache Table¶

The auth.user_permission_cache table:

Column	Type	Default	Description
`upc_id`	bigint	auto-generated	Primary key
`user_id`	bigint	--	The user this cache belongs to
`tenant_id`	integer	--	The tenant context
`tenant_uuid`	uuid	--	Tenant UUID (denormalized for convenience)
`groups`	text[]	`'{}'`	Array of group codes the user belongs to
`permissions`	text[]	`'{}'`	Array of full permission codes the user holds
`short_code_permissions`	text[]	`'{}'`	Array of short codes for the same permissions
`expiration_date`	timestamptz	--	When this cache entry expires

Uniqueness: One cache entry per (user_id, tenant_id) combination.

Cache Lifecycle¶

Creation and refresh: When auth.has_permissions is called and no valid cache exists, unsecure.recalculate_user_permissions computes the full permission list by:

Collecting the user's group memberships (from auth.user_group_members view)
Gathering group-level assignments (permission sets and individual permissions via auth.permission_assignment)
Gathering user-level assignments (permission sets and individual permissions directly assigned to the user)
Expanding parent permissions to all assignable descendants using ltree <@ queries
Resolving short codes alongside full codes
Writing the result to auth.user_permission_cache with an expiration timestamp

The cache TTL is configured via the auth.perm_cache_timeout_in_s system parameter in const.sys_param (defaults to 300 seconds if not set).

Cache Invalidation¶

The system uses two invalidation strategies:

Soft invalidation (preferred): Updates expiration_date to now(), marking the entry as stale. The next has_permissions call will recalculate. This is faster than deletion because it avoids index rebalancing.

Hard invalidation: Deletes the cache row entirely. Used when a user is locked or disabled, since they should not have any cached permissions.

Invalidation is triggered automatically by the relevant mutation functions:

Event	Invalidation Function	Strategy
Permission assigned/unassigned to a user	`unsecure.invalidate_users_permission_cache`	Soft
Permission assigned/unassigned to a group	`unsecure.invalidate_group_members_permission_cache`	Soft
Permissions added/removed from a permission set	`unsecure.invalidate_perm_set_users_permission_cache`	Soft
Permission assignability changed	`unsecure.invalidate_permission_users_cache`	Soft
Group member added/removed	`unsecure.invalidate_group_members_permission_cache`	Soft
Group deleted or disabled	`unsecure.invalidate_users_permission_cache`	Soft
User locked or disabled	`unsecure.clear_permission_cache`	Hard (delete)
Manual cache clear	`unsecure.clear_permission_cache`	Hard (delete)

Real-time notifications

In addition to cache invalidation, the system sends pg_notify messages on the permission_changes channel when permission-relevant mutations occur. Backend applications can listen on this channel to push refresh signals to connected clients. See Audit & Notifications for details.

Recalculation Flow¶

When the cache is expired or missing, the recalculation proceeds through two stages:

Stage 1 -- Group recalculation (unsecure.recalculate_user_groups):

Reads the user's last_used_provider_code from auth.user_info
Loads provider_groups and provider_roles from the corresponding auth.user_identity record
Adds the user to any default groups (is_default = true)
If the provider allows group mapping, resolves external group mappings:
- Removes memberships for groups the user is no longer mapped to
- Creates new memberships for newly-mapped groups
- Clears the permission cache for affected tenants

Stage 2 -- Permission recalculation (unsecure.recalculate_user_permissions):

Validates the user is active and not locked
If a valid cache already exists, returns it immediately
Otherwise, computes the full permission list by collecting group-level and user-level assignments, expanding ltree hierarchies, and aggregating group codes, permission codes, and short codes per tenant
Writes the result to auth.user_permission_cache with the configured TTL
Returns the computed permissions

Permission Resolution Diagram¶

The following diagram shows the complete flow from tables to resolved permissions:

graph LR
    subgraph "Assignment Sources"
        PA["permission_assignment"]
    end

    subgraph "Targets"
        UI["user_info"]
        UG["user_group"]
    end

    subgraph "Grants"
        PS["perm_set"]
        P["permission"]
    end

    subgraph "Resolution"
        PSP["perm_set_perm"]
        LTREE["ltree expansion<br/>(node_path <@ parent)"]
    end

    subgraph "Output"
        UPC["user_permission_cache<br/>permissions text[]<br/>short_code_permissions text[]<br/>groups text[]"]
    end

    PA --> UI
    PA --> UG
    PA --> PS
    PA --> P
    PS --> PSP
    PSP --> P
    P --> LTREE
    LTREE --> UPC
    UG -->|"group memberships"| UI

Next Steps¶

Group Types -- how internal, external, and hybrid groups affect permission inheritance
Multi-Tenancy -- tenant-scoped permission sets and isolation
Permissions & Authorization Functions -- complete function reference
Audit & Notifications -- real-time permission change notifications