Permissions & Authorization Functions¶
Functions for permission checking, permission CRUD, permission set management, assignment, searching, and batch synchronization. These are the core authorization functions that enforce access control throughout the system.
Source: 022_functions_auth_permission.sql
See also: Permission Model concepts for an overview of hierarchical permissions, permission sets, and the assignment model.
Permission Checking¶
These functions are the primary authorization mechanism. Every secured function in the auth and public schemas calls one of these before proceeding.
auth.has_permission¶
Checks whether a user holds a single permission. Delegates to auth.has_permissions internally.
| Parameter | Type | Default | Description |
|---|---|---|---|
_target_user_id |
bigint |
-- | The user whose permissions are being checked |
_correlation_id |
text |
-- | Correlation ID for tracing and audit logging |
_perm_code |
text |
-- | Permission code to check (e.g. 'users.create_user') |
_tenant_id |
integer |
1 |
Tenant context for the permission check |
_throw_err |
boolean |
true |
When true, throws error 52109 on failure; when false, returns false silently |
Returns: boolean -- true if the user has the permission
Permission required: None (this is the permission check)
Source: 022_functions_auth_permission.sql:144
auth.has_permissions¶
Checks whether a user holds at least one of the specified permissions. Automatically recalculates the permission cache if it has expired. User ID 1 (system user) and tenant owners bypass all checks.
| Parameter | Type | Default | Description |
|---|---|---|---|
_target_user_id |
bigint |
-- | The user whose permissions are being checked |
_correlation_id |
text |
-- | Correlation ID for tracing and audit logging |
_perm_codes |
text[] |
-- | Array of permission codes; passes if the user holds any of them |
_tenant_id |
integer |
1 |
Tenant context for the permission check |
_throw_err |
boolean |
true |
When true, throws error 52109 on failure; when false, returns false silently |
Returns: boolean -- true if the user has at least one of the permissions
Permission required: None (this is the permission check)
Source: 022_functions_auth_permission.sql:63
Cache behavior
If the user's permission cache has expired (or does not exist), has_permissions transparently calls unsecure.recalculate_user_groups and unsecure.recalculate_user_permissions to rebuild it before evaluating.
Error Helpers¶
Internal helpers that raise standardized permission-denied errors. Not called directly by application code -- they are invoked by has_permissions and other authorization functions when a check fails.
auth.throw_no_access¶
Raises error 52108 indicating the user has no access to the tenant.
| Parameter | Type | Default | Description |
|---|---|---|---|
_username |
text |
-- | Username or identifier for the error message |
_tenant_id |
integer |
1 |
Tenant ID for the error context |
Returns: void
Permission required: None
Source: 022_functions_auth_permission.sql:13
internal.throw_no_permission¶
Raises error 52109 indicating the user lacks the required permission(s). Has four overloaded signatures for convenience.
Overload 1 -- array of codes with tenant:
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint |
-- | User who lacks the permission |
_perm_codes |
text[] |
-- | Permission codes that were required |
_tenant_id |
integer |
1 |
Tenant context |
Source: 022_functions_auth_permission.sql:23
Overload 2 -- array of codes, default tenant:
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint |
-- | User who lacks the permission |
_perm_codes |
text[] |
-- | Permission codes that were required |
Source: 022_functions_auth_permission.sql:33
Overload 3 -- single code with tenant:
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint |
-- | User who lacks the permission |
_perm_code |
text |
-- | Permission code that was required |
_tenant_id |
integer |
1 |
Tenant context |
Source: 022_functions_auth_permission.sql:43
Overload 4 -- single code, default tenant:
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint |
-- | User who lacks the permission |
_perm_code |
text |
-- | Permission code that was required |
Source: 022_functions_auth_permission.sql:53
Returns: void (always raises an exception)
Permission required: None (internal helper)
Permission CRUD¶
Functions to create and modify individual permission entries in the global permission tree.
auth.create_permission¶
Creates a new permission node in the hierarchical permission tree. The permission code is automatically generated from the title.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User performing the action (checked for authorization) |
_correlation_id |
text |
-- | Correlation ID for tracing |
_title |
text |
-- | Human-readable title (e.g. 'Create user') |
_parent_full_code |
text |
null |
Full code of the parent permission (e.g. 'users'); null for root-level |
_is_assignable |
boolean |
true |
Whether this permission can be directly assigned to users/groups |
_short_code |
text |
null |
Optional short code alias for compact permission representations |
_source |
text |
null |
Source tag for tracking which module registered the permission |
Returns: setof auth.permission (single row with the created permission)
Permission required: permissions.add_permission
Source: 022_functions_auth_permission.sql:227
auth.set_permission_as_assignable¶
Updates whether a permission can be assigned to users and groups. Useful for marking branch nodes (categories) as non-assignable.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
-- | Correlation ID for tracing |
_permission_id |
integer |
null |
Permission ID to update (use either this or _permission_full_code) |
_permission_full_code |
text |
null |
Full code of the permission to update |
_is_assignable |
boolean |
true |
New assignable status |
Returns: setof auth.permission_assignment
Permission required: permissions.update_permission
Source: 022_functions_auth_permission.sql:179
Permission Query Functions¶
Functions to retrieve permissions and their assignments for users and groups.
auth.get_all_permissions¶
Returns all permissions in the global permission tree.
| Parameter | Type | Default | Description |
|---|---|---|---|
_requested_by |
text |
-- | Username or identifier of the requester |
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
-- | Correlation ID for tracing |
_tenant_id |
integer |
1 |
Tenant context |
Returns: table(__permission_id integer, __is_assignable boolean, __title text, __code text, __full_code text, __has_children boolean, __short_code text, __source text)
Permission required: permissions.get_perm_sets
Source: 022_functions_auth_permission.sql:247
auth.get_user_permissions¶
Returns a detailed breakdown of all permissions assigned to a user, including the source of each permission (direct assignment, permission set, or group membership). Users can query their own permissions without additional authorization.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
-- | Correlation ID for tracing |
_target_user_id |
bigint |
-- | User whose permissions to retrieve |
_tenant_id |
integer |
1 |
Tenant context |
_target_tenant_id |
integer |
null |
Cross-tenant: query a specific tenant's data (admin only) |
Returns: table(__assignment_id bigint, __perm_set_code text, __perm_set_title text, __user_group_member_id bigint, __user_group_title text, __permission_inheritance_type text, __permission_code text, __permission_title text, __tenant_id integer, __tenant_code text, __tenant_title text)
The __permission_inheritance_type column indicates how the permission was acquired:
'assignment'-- directly assigned to the user'perm_set'-- inherited through a permission set'user_group'-- inherited through group membership
Permission required: users.get_permissions / users.get_all_permissions (cross-tenant). Self-query (_user_id = _target_user_id) requires no permission.
Source: 022_functions_auth_permission.sql:351
auth.get_user_assigned_permissions¶
Returns the user's permission assignments grouped by permission set, including all individual permissions within each set.
| Parameter | Type | Default | Description |
|---|---|---|---|
_requested_by |
text |
-- | Username or identifier of the requester |
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
-- | Correlation ID for tracing |
_target_user_id |
bigint |
-- | User whose assignments to retrieve |
_tenant_id |
integer |
1 |
Tenant context |
Returns: table(__permissions jsonb, __perm_set_title text, __perm_set_id integer, __perm_set_code text, __assignment_id bigint, __user_group_id integer)
Permission required: users.get_permissions
Source: 022_functions_auth_permission.sql:704
auth.get_effective_group_permissions¶
Returns the effective (flattened) permissions for a group, resolving all permission sets into individual permission entries.
| Parameter | Type | Default | Description |
|---|---|---|---|
_requested_by |
text |
-- | Username or identifier of the requester |
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
-- | Correlation ID for tracing |
_group_id |
integer |
-- | Group whose effective permissions to retrieve |
_tenant_id |
integer |
1 |
Tenant context |
Returns: table(__full_code text, __permission_title text, __perm_set_title text, __perm_set_code text, __perm_set_id integer, __assignment_id bigint)
Permission required: groups.get_permissions
Source: 022_functions_auth_permission.sql:154
auth.get_assigned_group_permissions¶
Returns the raw permission assignments for a group (before flattening permission sets).
| Parameter | Type | Default | Description |
|---|---|---|---|
_requested_by |
text |
-- | Username or identifier of the requester |
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
-- | Correlation ID for tracing |
_user_group_id |
integer |
-- | Group whose assignments to retrieve |
_tenant_id |
integer |
1 |
Tenant context |
Returns: table(__permissions jsonb, __perm_set_title text, __perm_set_id integer, __perm_set_code text, __assignment_id bigint)
Permission required: groups.get_permissions
Source: 022_functions_auth_permission.sql:166
public.get_permissions_map¶
Returns a flat list of all assignable permissions. This is a public utility function with no permission check -- useful for building permission selection UIs.
| Parameter | Type | Default | Description |
|---|---|---|---|
| (none) |
Returns: table(__permission_id integer, __full_code text, __short_code text, __title text, __source text)
Permission required: None
Source: 022_functions_auth_permission.sql:859
Permission Assignment¶
Functions to assign and unassign permissions (or permission sets) to users and groups.
auth.assign_permission¶
Assigns a permission set or individual permission to a user or group within a tenant. Specify either _user_group_id or _target_user_id (not both), and either _perm_set_code or _perm_code (not both).
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
-- | Correlation ID for tracing |
_user_group_id |
integer |
-- | Target group (pass null for user assignment) |
_target_user_id |
bigint |
-- | Target user (pass null for group assignment) |
_perm_set_code |
text |
-- | Permission set code to assign (pass null for individual permission) |
_perm_code |
text |
-- | Individual permission code to assign (pass null for permission set) |
_tenant_id |
integer |
1 |
Tenant context |
Returns: setof auth.permission_assignment
Permission required: permissions.assign_permission
Source: 022_functions_auth_permission.sql:194
auth.unassign_permission¶
Removes a permission assignment by its assignment ID.
| Parameter | Type | Default | Description |
|---|---|---|---|
_deleted_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
-- | Correlation ID for tracing |
_assignment_id |
bigint |
-- | ID of the assignment record to remove |
_tenant_id |
integer |
1 |
Tenant context |
Returns: setof auth.permission_assignment
Permission required: permissions.unassign_permission
Source: 022_functions_auth_permission.sql:213
Permission Sets¶
Functions to manage permission sets -- tenant-specific collections of permissions that can be assigned as a unit. See Permission Model concepts for the relationship between global permissions and tenant-specific permission sets.
auth.get_perm_sets¶
Returns all permission sets for a tenant, including their contained permissions as a JSONB array.
| Parameter | Type | Default | Description |
|---|---|---|---|
_requested_by |
text |
-- | Username or identifier of the requester |
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
-- | Correlation ID for tracing |
_tenant_id |
integer |
1 |
Tenant context |
Returns: table(__perm_set_id integer, __title text, __code text, __is_system boolean, __is_assignable boolean, __permissions jsonb, __source text)
Permission required: permissions.get_perm_sets
Source: 022_functions_auth_permission.sql:259
auth.create_perm_set¶
Creates a new permission set for a tenant, optionally pre-populated with permissions.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
-- | Correlation ID for tracing |
_title |
text |
-- | Human-readable title for the permission set |
_is_system |
boolean |
false |
Whether this is a system-level (protected) permission set |
_is_assignable |
boolean |
true |
Whether this set can be assigned to users/groups |
_permissions |
text[] |
null |
Array of permission full codes to include |
_tenant_id |
integer |
1 |
Tenant context |
_source |
text |
null |
Source tag for tracking which module registered the set |
Returns: setof auth.perm_set (single row with the created permission set)
Permission required: permissions.create_permission_set
Source: 022_functions_auth_permission.sql:271
auth.update_perm_set¶
Updates a permission set's title and assignable status. Raises error 52177 if the permission set does not exist in the specified tenant.
| Parameter | Type | Default | Description |
|---|---|---|---|
_updated_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
-- | Correlation ID for tracing |
_perm_set_id |
integer |
-- | ID of the permission set to update |
_title |
text |
-- | New title |
_is_assignable |
boolean |
true |
New assignable status |
_tenant_id |
integer |
1 |
Tenant context |
Returns: setof auth.perm_set (single row with the updated permission set)
Permission required: permissions.update_permission_set
Source: 022_functions_auth_permission.sql:288
auth.create_perm_set_permissions¶
Adds permissions to an existing permission set.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
-- | Correlation ID for tracing |
_perm_set_id |
integer |
-- | ID of the permission set |
_permissions |
text[] |
null |
Array of permission full codes to add |
_tenant_id |
integer |
1 |
Tenant context |
Returns: table(__perm_set_id integer, __perm_set_code text, __permission_id integer, __permission_code text)
Permission required: permissions.update_permission_set
Source: 022_functions_auth_permission.sql:311
auth.delete_perm_set_permissions¶
Removes permissions from an existing permission set.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
-- | Correlation ID for tracing |
_perm_set_id |
integer |
-- | ID of the permission set |
_permissions |
text[] |
null |
Array of permission full codes to remove |
_tenant_id |
integer |
1 |
Tenant context |
Returns: table(__perm_set_id integer, __perm_set_code text, __permission_id integer, __permission_code text)
Permission required: permissions.update_permission_set
Source: 022_functions_auth_permission.sql:329
Groups & Permissions Resolution¶
Functions that recalculate or retrieve the full set of a user's groups and permissions, typically used during login or token refresh.
auth.ensure_groups_and_permissions¶
Recalculates a user's group memberships and permissions based on their latest identity provider data. Call this during login or token refresh to update provider_groups and provider_roles from the identity provider, then rebuild the permission cache across all tenants.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
-- | Correlation ID for tracing |
_target_user_id |
bigint |
-- | User whose groups and permissions to recalculate |
_provider_code |
text |
-- | Identity provider code (e.g. 'aad', 'email') |
_provider_groups |
text[] |
null |
Groups reported by the identity provider |
_provider_roles |
text[] |
null |
Roles reported by the identity provider |
Returns: table(__tenant_id integer, __tenant_uuid uuid, __groups text[], __permissions text[], __short_code_permissions text[])
Returns one row per tenant the user belongs to, with their resolved groups, permissions (as full codes), and permissions (as short codes).
Permission required: authentication.ensure_permissions
Source: 022_functions_auth_permission.sql:644
auth.get_users_groups_and_permissions¶
Retrieves a user's current groups and permissions across all tenants without updating provider data. Unlike ensure_groups_and_permissions, this does not write provider_groups/provider_roles -- it just recalculates from what is already stored.
| Parameter | Type | Default | Description |
|---|---|---|---|
_requested_by |
text |
-- | Username or identifier of the requester |
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
-- | Correlation ID for tracing |
_target_user_id |
bigint |
-- | User whose data to retrieve |
Returns: table(__tenant_id integer, __tenant_uuid uuid, __groups text[], __permissions text[], __short_code_permissions text[])
Permission required: authentication.get_users_groups_and_permissions
Source: 022_functions_auth_permission.sql:682
Search Functions¶
Paginated search functions for listing permissions and permission sets with filtering.
auth.search_permissions¶
Searches the global permission tree with filtering and pagination. Supports text search (normalized), assignable filtering, parent scope filtering, and source filtering.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
null |
Correlation ID for tracing |
_search_criteria |
jsonb |
null |
Search filters (see criteria keys below) |
_page |
integer |
1 |
Page number (1-based) |
_page_size |
integer |
30 |
Results per page (capped at 100) |
_tenant_id |
integer |
1 |
Tenant context for permission check |
Criteria keys:
| Key | Type | Description |
|---|---|---|
search_text |
text | Free-text search (matched against normalized search data) |
is_assignable |
boolean | Filter by assignable status; omit for all |
parent_code |
text | Only return descendants of this permission code |
source |
text | Filter by source tag |
Returns: table(__permission_id integer, __title text, __code text, __full_code text, __short_code text, __is_assignable boolean, __has_children boolean, __source text, __total_items bigint)
The __total_items column contains the total count of matching records (before pagination), repeated on every row for convenience.
Permission required: permissions.read_permissions
Source: 022_functions_auth_permission.sql:748
auth.search_perm_sets¶
Searches permission sets for a tenant with filtering and pagination. Returns sets with their permission counts.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
null |
Correlation ID for tracing |
_search_criteria |
jsonb |
null |
Search filters (see criteria keys below) |
_page |
integer |
1 |
Page number (1-based) |
_page_size |
integer |
30 |
Results per page (capped at 100) |
_tenant_id |
integer |
1 |
Tenant context |
_target_tenant_id |
integer |
null |
Cross-tenant: query a specific tenant's data (admin only) |
Criteria keys:
| Key | Type | Description |
|---|---|---|
search_text |
text | Free-text search (matched against normalized search data) |
is_assignable |
boolean | Filter by assignable status; omit for all |
is_system |
boolean | Filter by system flag; omit for all |
source |
text | Filter by source tag |
Returns: table(__perm_set_id integer, __title text, __code text, __is_system boolean, __is_assignable boolean, __source text, __permission_count bigint, __total_items bigint)
Permission required: permissions.read_perm_sets
Source: 022_functions_auth_permission.sql:823
Batch Synchronization (Ensure Functions)¶
Idempotent functions for declaratively synchronizing permissions and permission sets from application code. These are designed for use during application startup or deployment to ensure the database matches the application's expected permission structure. See also App Bootstrapping (Ensure).
auth.ensure_permissions¶
Ensures a batch of permissions exists, creating any that are missing. When _is_final_state is true, also deletes permissions with the same _source that are not in the input set -- effectively making the input the canonical list.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
-- | Correlation ID for tracing |
_permissions |
jsonb |
-- | JSON array of permission objects (see format below) |
_source |
text |
null |
Default source tag applied to items without their own source; required when _is_final_state is true |
_is_final_state |
boolean |
false |
When true, removes permissions with the same source that are not in the input |
Returns: setof auth.permission -- all permissions that match the input full codes
Permission required: permissions.add_permission (and additionally permissions.delete_permission when _is_final_state is true)
Source: 022_functions_auth_permission.sql:874
Permission object format (each element in the _permissions JSON array):
{
"title": "Create user",
"parent_code": "users",
"is_assignable": true,
"short_code": "CU",
"source": "my_app"
}
| Field | Required | Default | Description |
|---|---|---|---|
title |
yes | -- | Human-readable title |
parent_code |
no | null |
Full code of the parent permission |
is_assignable |
no | true |
Whether the permission can be assigned |
short_code |
no | null |
Optional short code alias |
source |
no | function's _source |
Overrides the function-level source for this item |
Final state mode
When _is_final_state is true, any existing permissions with the matching _source that are not present in the input array will be deleted, along with their perm_set_perm and permission_assignment records. Use with care.
auth.ensure_perm_sets¶
Ensures a batch of permission sets exists for a tenant, creating any that are missing and adding any missing permissions to existing sets. When _is_final_state is true, also removes extra permissions from existing sets and deletes sets with the same _source that are not in the input.
| Parameter | Type | Default | Description |
|---|---|---|---|
_created_by |
text |
-- | Username or identifier of the actor |
_user_id |
bigint |
-- | User performing the action |
_correlation_id |
text |
-- | Correlation ID for tracing |
_perm_sets |
jsonb |
-- | JSON array of permission set objects (see format below) |
_source |
text |
null |
Default source tag; required when _is_final_state is true |
_tenant_id |
integer |
1 |
Tenant context |
_is_final_state |
boolean |
false |
When true, removes sets and extra permissions not in the input |
Returns: setof auth.perm_set -- all permission sets that match the input codes
Permission required: permissions.create_permission_set (and additionally permissions.delete_permission_set when _is_final_state is true)
Source: 022_functions_auth_permission.sql:1003
Permission set object format (each element in the _perm_sets JSON array):
{
"title": "Document editor",
"is_system": false,
"is_assignable": true,
"source": "my_app",
"permissions": ["documents.create", "documents.update", "documents.read"]
}
| Field | Required | Default | Description |
|---|---|---|---|
title |
yes | -- | Human-readable title (code is auto-generated) |
is_system |
no | false |
Whether this is a system-level protected set |
is_assignable |
no | true |
Whether this set can be assigned |
source |
no | function's _source |
Overrides the function-level source for this item |
permissions |
no | null |
Array of permission full codes to include |
Final state mode
When _is_final_state is true, permission sets with the matching _source and _tenant_id that are not present in the input array will be deleted, and their permission_assignment records will be removed. Additionally, existing sets will have any permissions not in the input removed. The permission cache for affected users is automatically invalidated.
Seed Data¶
auth.seed_permission_data¶
Initializes the complete default permission tree, system permission sets, default groups, and built-in providers. This function is called during initial database setup and should not be called again in production.
Creates:
- Permission tree -- all core permission categories (
authentication,journal,areas,tokens,token_configuration,permissions,users,tenants,providers,groups,api_keys,languages,translations,resources) - System permission sets --
System admin,Tenant creator,Tenant admin,Tenant owner,Tenant member,User manager,Group manager,Permission manager,Provider manager,Token manager,Api key manager,Auditor,Resource manager,Full admin - Service account permission sets --
Svc registrator permissions,Svc authenticator permissions,Svc token permissions,Svc api gateway permissions,Svc group syncer permissions,Svc data processor permissions - Default groups --
System admins,Tenant admins,Full admins - Providers --
emailandaad(Azure AD), both enabled
| Parameter | Type | Default | Description |
|---|---|---|---|
| (none) |
Returns: void
Permission required: None (intended for initial setup only)
Source: 022_functions_auth_permission.sql:385