Resource-Level ACL

The PostgreSQL Permissions Model includes a resource-level Access Control List (ACL) system that layers on top of RBAC. While RBAC controls what actions a user can perform globally (e.g. "can create folders"), the ACL system controls which specific resources they can act on (e.g. "can read folder #42").

---

RBAC vs Resource ACL

RBAC and resource-level ACL solve different problems and are designed to work together:

Aspect	RBAC (Permissions)	Resource ACL
Question answered	"Can this user perform this action?"	"Can this user act on this specific resource?"
Scope	Global across all resources	Per-resource, per-flag
Storage	auth.permission_assignment + cache	auth.resource_access (partitioned)
Deny support	No (grant-only model)	Yes (user-level deny overrides grants)
Group support	Yes (via group assignments)	Yes (via group grants on resources)
Check function	auth.has_permission()	auth.has_resource_access()

When to use each

Use RBAC permissions when access applies uniformly to an entire category of actions -- "can this user create folders at all?" or "can this user manage users?" These are broad, role-based questions.

Use resource ACL when access varies per individual resource -- "can this user read this specific folder?" or "can this user write to this specific document?" These are fine-grained, per-object questions.

Use both together when a function needs to check both the action capability and the specific resource access. This is the recommended integration pattern:

create or replace function public.get_folder(
    _created_by text, _user_id bigint, _correlation_id text,
    _tenant_id integer, _folder_id jsonb
) returns table(...) as $$
begin
    -- 1. RBAC: Can the user perform this action at all?
    perform auth.has_permission(_user_id, _correlation_id, 'documents.read_folders', _tenant_id);

    -- 2. ACL: Can the user access THIS specific resource?
    perform auth.has_resource_access(_user_id, _correlation_id, 'folder', _folder_id, 'read', _tenant_id);

    -- 3. Do the work
    return query select ... from public.folder where folder_id = (_folder_id->>'id')::bigint;
end;
$$ language plpgsql;

Dual-check pattern

The RBAC check acts as a coarse filter ("do you even have the right role?") while the ACL check acts as a fine filter ("do you have access to this particular item?"). Skipping RBAC would mean every user with any resource grant could call the function; skipping ACL would mean every user with the right role could access every resource.

---

Core Concepts

Resource Types

Resource types define what kinds of objects can have ACL entries. They are registered in the const.resource_type table and are global (not tenant-specific). Each application registers the resource types it needs.

Resource types support hierarchical structure using PostgreSQL's ltree extension. A root type like project can have children like project.documents and project.invoices. A grant on a parent type cascades to all child types at check time.

project                    -- root type (gets its own partition)
project.documents          -- child type (parent_code = 'project')
project.invoices           -- child type (parent_code = 'project')

Column	Type	Description
code	text PK	Type identifier (e.g. folder, project.documents)
title	text	Display name
full_title	text	Breadcrumb title (e.g. Project > Project Documents)
description	text	Optional description
is_active	boolean	Whether the type is active
source	text	Origin tracker (e.g. documents_app)
parent_code	text FK	Parent resource type code (null for root types)
path	ltree	Hierarchical path for ancestor/descendant queries
key_schema	jsonb	Defines expected resource_id structure (see below)

Registering resource types

Root types automatically get their own database partition (see Partitioning below). Child types share their root type's partition. Use auth.create_resource_type() or auth.ensure_resource_types() to register types -- see the Resource Access reference for details.

Composite Resource Keys (jsonb)

Resource IDs are stored as jsonb objects, supporting composite keys that identify resources with multiple fields. Each resource type defines a key_schema that specifies the required key fields.

Example key schemas:

Resource Type	Key Schema	Example resource_id
document	{"id": "bigint"}	{"id": 42}
project	{"project_id": "bigint"}	{"project_id": 123}
project.documents	{"project_id": "bigint", "folder_id": "bigint"}	{"project_id": 123, "folder_id": 1000}

The key_schema serves two purposes:

1. Validation at write time -- assign_resource_access and deny_resource_access call unsecure.validate_resource_id() to ensure all required keys are present.
2. Ancestor key extraction at read time -- during hierarchy walk-up, has_resource_access extracts only the keys defined in the ancestor's key_schema from the full resource_id. For example, checking project.documents with {"project_id": 123, "folder_id": 42} walks up to project and looks for grants on {"project_id": 123}.

GIN index for containment

The resource_id column uses a GIN index for efficient @> containment queries. This enables operations like revoke_all_resource_access on {"project_id": 123} to cascade to all sub-resource grants containing that key (e.g. {"project_id": 123, "folder_id": 42}).

Unique constraints via md5

Since jsonb cannot be used directly in btree unique indexes, unique constraints use md5(resource_id::text) to ensure one grant/deny per user+resource+flag combination.

Access Flags

Access flags define what kind of access is being granted, denied, or checked. The const.resource_access_flag table is the global registry of all available flags -- it defines which flags exist system-wide. Custom flags can be added by inserting rows.

Built-in flags:

Flag	Meaning
read	View/read the resource
write	Create/modify the resource
delete	Delete the resource
share	Grant access to others
approve	Approve/sign off on the resource
export	Export/download the resource

Per-Type Flag Mapping

Not every flag makes sense for every resource type. The const.resource_type_flag mapping table defines which flags from the global registry are valid for each resource type. For example, an invoice type might allow read, approve, and export but not write or delete.

• When a resource type has entries in resource_type_flag, only those flags can be used in grant/deny operations for that type. Attempting to grant or deny an unmapped flag raises error 35006.
• When a resource type has no entries in resource_type_flag, all globally registered flags are allowed. This preserves backward compatibility with types created before per-type mapping was introduced.
• The access matrix (auth.get_resource_access_matrix) only returns flags valid for each type, including for system user and tenant owner auto-grants.

Custom flags can be added for application-specific needs (e.g. comment, subscribe). All flags work uniformly in grant/deny/check operations.

Hierarchical Resource Paths

When resource types form a hierarchy, the access check algorithm walks up the type tree from the most specific type to the root. A grant on a parent type applies to all its descendants unless overridden by an explicit entry on a child type.

For example, if a user has read on resource type project for resource ID {"project_id": 42}, they also have read on project.documents for {"project_id": 42, "folder_id": 100} -- because the walk-up extracts {"project_id": 42} from the full key and finds the parent grant. An explicit deny on the child type overrides the inherited grant.

---

Access Check Algorithm

When auth.has_resource_access() is called, checks happen in a strict priority order. The first matching rule wins:

1. System user (user ID 1) -- always allowed
2. Tenant owner -- always allowed
3. User-level deny (is_deny = true) -- blocked, overrides everything
4. User-level grant (direct flag in resource_access) -- allowed
5. User role grant (role assigned to user in resource_role_assignment containing the flag) -- allowed
6. Group-level grant (direct flag via group membership) -- allowed
7. Group role grant (role assigned to any of user's groups containing the flag) -- allowed
8. No matching rule -- denied

flowchart TD
    A["has_resource_access(user_id, resource_type, resource_id, flag)"] --> B{user_id = 1?}
    B -- Yes --> R1["GRANTED<br/>(system user)"]
    B -- No --> C{Is tenant owner?}
    C -- Yes --> R2["GRANTED<br/>(tenant owner)"]
    C -- No --> D["Get cached group IDs<br/>Walk up type hierarchy"]
    D --> E{User-level<br/>DENY found?}
    E -- Yes --> R3["DENIED<br/>(explicit deny)"]
    E -- No --> F{User-level<br/>GRANT found?}
    F -- Yes --> R4["GRANTED<br/>(direct)"]
    F -- No --> G{Group-level<br/>GRANT found?}
    G -- Yes --> R5["GRANTED<br/>(via group)"]
    G -- No --> H{_throw_err?}
    H -- Yes --> R6["Raise error 35001"]
    H -- No --> R7["Return FALSE"]

Key rule: user-level deny beats all group grants

Even if Bob is in the "Editors" group with write access on a folder, a user-level deny on Bob for that folder blocks him specifically. This is how you create exceptions for individual users without removing them from groups.

Deny Model

The deny model is deliberately constrained to keep access rules predictable:

• User-level only -- you cannot deny a group. Group denies would create unpredictable cascading effects across all members.
• Per-flag -- denying read does not affect write. Each flag is independent.
• Explicit -- denies must be set with auth.deny_resource_access(). There is no implicit deny based on the absence of a grant.
• Removable -- to lift a deny, use auth.revoke_resource_access() which deletes the deny row.

A deny is not a separate flag — it is a negation of any existing access flag. The same flags used for grants (read, write, delete, etc.) are used for denies. The is_deny column on the auth.resource_access row determines whether it is a grant or a deny.

Example: deny invoices for a specific user

The "Editors" group has read + write on project 123 (at the project level, cascading to all sub-types). Bob is a member of Editors but should not be able to see or modify invoices:

-- Deny read + write on project.invoices for Bob specifically
perform auth.deny_resource_access(
    'app', _admin_user_id, _correlation_id,
    'project.invoices',
    '{"project_id": 123}'::jsonb,
    _target_user_id := _bob_id,
    _access_flags := array['read', 'write']
);

Bob can still read and write documents and contacts (inherited from the group grant on project), but invoices are blocked. The deny only affects the flags listed — if Bob had delete via the group, that would still work on invoices.

To lift the deny later (restoring Bob's inherited group access):

-- Remove the deny rows — Bob regains access via the group grant
perform auth.revoke_resource_access(
    'app', _admin_user_id, _correlation_id,
    'project.invoices',
    '{"project_id": 123}'::jsonb,
    _target_user_id := _bob_id,
    _access_flags := array['read', 'write']
);

Revoke removes the row, it does not create a grant

revoke_resource_access deletes the deny row from auth.resource_access. It does not create a grant. After revoking the deny, Bob's access falls back to whatever grants exist (in this case, the group grant on the parent project type).

Group Access

Groups can receive resource grants just like individual users:

1. Call auth.assign_resource_access() with _user_group_id to grant flags to a group
2. During access checks, the system resolves group membership through auth.user_group_id_cache (or recalculates on cache miss)
3. auth.get_resource_access_flags() returns group-sourced grants with the group's title as the source
4. All group types (internal, external, hybrid) work equally with resource access

---

Tables

const.resource_type

Registry of valid resource types. Global (not tenant-specific). Supports hierarchical parent/child relationships via parent_code and path (ltree). The key_schema column defines the expected jsonb structure for resource_id values.

A GiST index on path enables efficient ancestor/descendant queries for the type hierarchy walk-up during access checks.

const.resource_access_flag

Global registry of all valid access flags. Ships with six built-in flags (read, write, delete, share, approve, export) and is extensible by inserting additional rows. This table defines which flags exist system-wide; use const.resource_type_flag to control which flags are valid per resource type.

const.resource_type_flag

Mapping table that defines which access flags are valid for each resource type. Each row links a resource type code to an access flag code. If a resource type has no entries in this table, all flags from const.resource_access_flag are allowed (backward compatible). Used by unsecure.validate_access_flags_for_type() during grant/deny operations and by auth.get_resource_access_matrix to filter output flags.

auth.resource_access

Core ACL table. One row represents one flag for one user or group on one resource. The resource_id column is jsonb with a check constraint enforcing it must be an object (jsonb_typeof(resource_id) = 'object'). The table enforces mutual exclusivity: each row targets either a user_id or a user_group_id, never both and never neither.

Key constraints:

• ra_either_user_or_group -- at least one of user_id or user_group_id must be set
• ra_not_both_user_and_group -- they cannot both be set
• ra_resource_id_is_object -- resource_id must be a jsonb object
• Unique indexes (via md5(resource_id::text)) ensure one grant/deny per user+resource+flag and per group+resource+flag
• GIN index on resource_id for containment queries (@>)

auth.user_group_id_cache

Cached group membership IDs used by resource access functions to avoid recalculating group membership on every access check. Follows the same cache pattern as auth.user_permission_cache: populated on demand, expired via TTL, soft-invalidated on membership changes, and hard-invalidated on user disable/lock/delete.

const.resource_role / const.resource_role_flag

Named bundles of access flags scoped to a resource_type. A resource role (e.g., "folder_editor") groups multiple flags (read, write, delete, export) into a single assignable unit. The resource_role_flag junction table defines which flags belong to each role.

Roles are global (not tenant-scoped) — registered at application startup via ensure_resource_roles. They are analogous to auth.perm_set in the RBAC layer: perm_set : permission :: resource_role : access_flag.

Key design decisions:

• Grant-only — roles cannot be used for denies. Denies remain flag-level in auth.resource_access, keeping precedence rules unambiguous
• Expand at check time — has_resource_access joins resource_role_flag to determine which flags a role provides. Redefining a role's flags takes effect instantly for all assigned users — no cascade, no data migration
• Per-type scoping — each role is bound to one resource_type via a composite FK. Hierarchy cascade happens via the ltree walk-up, not via lax FKs
• Coexists with direct flags — a user can have both a role assignment and direct flag grants on the same resource. They're independent rows in independent tables

auth.resource_role_assignment

Tenant-scoped role grant table, list-partitioned by root_type (same strategy as auth.resource_access). One row per (tenant, resource, user|group, role_code). Same constraints as resource_access: exactly one of user_id or user_group_id, jsonb resource_id, md5-based uniqueness.

Partitioning

Both auth.resource_access and auth.resource_role_assignment are list-partitioned by root_type (the first segment of the resource type code). Each root resource type gets its own partition pair, while child types share the root's partitions:

auth.resource_access (partitioned by root_type)
  |-- auth.resource_access_project   (root_type = 'project')
  |     handles: project, project.documents, project.invoices
  |-- auth.resource_access_folder    (root_type = 'folder')
  |-- auth.resource_access_default   (catches unregistered root types)

auth.resource_role_assignment (partitioned by root_type)
  |-- auth.resource_role_assignment_project
  |-- auth.resource_role_assignment_folder
  |-- auth.resource_role_assignment_default

Partitions are auto-created by unsecure.ensure_resource_access_partition() when resource types are registered — both tables get their partition in a single call. PostgreSQL prunes partitions automatically during queries.

---

Integration Example

The documents app example in the source repository demonstrates the full RBAC + ACL system working together:

Resource types: folder, document

RBAC permissions: documents.create_folder, documents.read_folders, documents.delete_folder, documents.create_document, documents.read_documents, documents.delete_document

Permission set: "Document user" bundles all document and resource permissions.

Users and access:

User	RBAC Role	ACL Grants	Effective Access
Alice	admin + document_user	Full access on all folders	Can do everything
Bob	document_user, Editors group	Editors group = read+write on Projects; deny read on Private	Can read/write Projects, blocked from Private
Charlie	document_user	read on Shared	Can only read Shared folder
Dave	no permissions	none	Cannot call any document functions (blocked at RBAC layer)

Notice how Dave is blocked before ACL is even checked -- the RBAC permission check in the function rejects him. Bob demonstrates the deny override: even though his "Editors" group has read+write on Projects, his personal deny on the Private folder blocks him from that specific resource.

Bulk Filtering

For functions that return lists of resources, use auth.filter_accessible_resources() to efficiently filter down to only the resources the user can access:

create or replace function public.get_folders(
    _created_by text, _user_id bigint, _correlation_id text,
    _tenant_id integer, _parent_folder_id bigint default null
) returns table(...) as $$
declare
    _folder_ids jsonb[];
begin
    -- RBAC check
    perform auth.has_permission(_user_id, _correlation_id, 'documents.read_folders', _tenant_id);

    -- Collect resource IDs as jsonb array
    select array_agg(jsonb_build_object('id', folder_id))
    from public.folder where tenant_id = _tenant_id
    into _folder_ids;

    -- ACL bulk filter
    return query
    select f.*
    from public.folder f
    inner join auth.filter_accessible_resources(
        _user_id, _correlation_id, 'folder', _folder_ids, 'read', _tenant_id
    ) acl on acl.__resource_id = jsonb_build_object('id', f.folder_id);
end;
$$ language plpgsql;

---

Next Steps

• Resource Access Functions -- complete function reference for grant, deny, revoke, and query operations
• Permission Model -- how RBAC permissions and permission sets work
• Group Types -- how internal, external, and hybrid groups affect resource access inheritance
• Multi-Tenancy -- tenant-scoped resource access and isolation