Multi-Tenancy¶

The PostgreSQL Permissions Model provides built-in multi-tenancy support, allowing a single database installation to serve multiple isolated organizations, companies, or projects. Each tenant has its own users, groups, permission sets, and ownership structure while sharing the same global permission tree.

Tenant Model¶

A tenant represents an isolated organizational boundary -- typically a company, team, or workspace. Every tenant is stored as a row in the auth.tenant table:

Column	Type	Default	Description
`tenant_id`	integer	auto-generated	Primary key
`uuid`	uuid	`uuid_generate_v4()`	Externally-facing unique identifier
`title`	text	--	Display name for the tenant
`code`	text	--	Short code (unique); auto-generated from title if not provided
`access_type_code`	text	`'authenticated'`	Access model; references `const.tenant_access_type`
`is_removable`	boolean	`true`	Whether the tenant can be deleted
`is_assignable`	boolean	`true`	Whether users can be assigned to this tenant
`is_default`	boolean	`false`	Whether this is a default tenant
`nrm_search_data`	text	--	Normalized search data for trigram search

Access Types¶

The access_type_code column references const.tenant_access_type, which is seeded with three values:

Code	Description
`public`	Tenant content is publicly accessible
`authenticated`	Access requires authentication (default)
`private`	Access is restricted to explicitly assigned members

Automatic Bootstrapping¶

When you create a tenant via auth.create_tenant(), the system automatically:

Creates a "Tenant Admins" group scoped to the new tenant
Creates a "Tenant Members" group scoped to the new tenant
Copies the tenant_admin and tenant_member permission sets from the primary tenant (tenant ID 1)
Assigns each copied permission set to the corresponding group
Optionally assigns a tenant owner if _tenant_owner_id is provided

-- create a tenant with an owner
select *
from auth.create_tenant(
    'admin',            -- _created_by
    1,                  -- _user_id
    'corr-123',         -- _correlation_id
    'Acme Corporation', -- _title
    _tenant_owner_id := 1001
);

Tenant ID 1

The first tenant (tenant ID 1) serves as the primary tenant and is the source from which permission sets are copied when new tenants are created. It typically holds the master set of tenant_admin and tenant_member permission sets.

Tenant Users¶

Users are linked to tenants through group membership rather than through a direct assignment table. The auth.tenant_user table does exist for explicit tenant-user associations, but the primary mechanism for tenant access is group membership -- every group belongs to a specific tenant (via auth.user_group.tenant_id), so being a member of any group in a tenant implicitly grants access to that tenant.

The `auth.tenant_user` Table¶

Column	Type	Description
`tenant_user_id`	bigint	Auto-generated primary key
`tenant_id`	integer	FK to `auth.tenant` (cascading delete)
`user_id`	integer	FK to `auth.user_info` (cascading delete)
`created_at`	timestamptz	Creation timestamp
`created_by`	text	Identifier of the creator

A unique constraint on (tenant_id, user_id) prevents duplicate assignments.

Querying Tenant Membership¶

To retrieve the tenants a user belongs to (based on group membership):

-- get all tenants available to user 1001
select *
from auth.get_user_available_tenants(
    1,      -- _user_id (acting user)
    null,   -- _correlation_id
    1001    -- _target_user_id
);

This returns each tenant's tenant_id, uuid, code, title, and is_default flag. If the calling user queries their own tenants, no permission is required. Querying another user's tenants requires the users.get_available_tenants permission.

To retrieve users within a specific tenant:

-- get all users in tenant 1 with their group memberships
select *
from auth.get_tenant_users('admin', 1, null, 1);

Tenant Isolation¶

Multi-tenancy in this system is enforced through several layers of data scoping:

Permission Sets Are Tenant-Specific¶

While permissions (the global hierarchical tree) are shared across all tenants, permission sets are always scoped to a specific tenant. The auth.perm_set table includes a tenant_id column, and the unique constraint (code, tenant_id) ensures that each tenant can define its own permission sets independently.

-- the same code can exist in different tenants
-- tenant 1 might have a "viewer" perm set with permissions A, B
-- tenant 2 might have a "viewer" perm set with permissions A, C

Groups Are Tenant-Scoped¶

Every group (auth.user_group) belongs to a tenant via its tenant_id column. Group membership therefore inherently carries tenant context. When permission calculations happen, the system resolves permissions within the correct tenant scope.

Permission Assignments Carry Tenant Context¶

The auth.permission_assignment table includes a tenant_id column. Every assignment of a permission or permission set to a user or group is scoped to a specific tenant. This means the same user can have different permissions in different tenants.

Permission Cache Is Per-Tenant¶

The auth.user_permission_cache table stores cached permission calculations per user per tenant (with a unique constraint on (user_id, tenant_id)). Each cache entry contains:

groups -- array of group codes the user belongs to in that tenant
permissions -- array of resolved permission codes for that tenant
short_code_permissions -- array of permission short codes for that tenant
expiration_date -- when the cache entry expires

Cascading Tenant Deletion¶

When a tenant is deleted, PostgreSQL's foreign key cascading removes all associated data:

Tenant users (auth.tenant_user)
Groups scoped to the tenant (auth.user_group)
Permission sets for the tenant (auth.perm_set)
Permission assignments for the tenant (auth.permission_assignment)
Permission cache entries for the tenant (auth.user_permission_cache)
Ownership records for the tenant (auth.owner)

Deletion is permanent

Tenant deletion cascades to all related data. The is_removable flag on the tenant must be true for the system to allow deletion.

Tenant Preferences¶

Users can store arbitrary per-tenant preferences using the auth.user_tenant_preference table. This allows each user to have different settings, UI configurations, or application state in each tenant they belong to.

The `auth.user_tenant_preference` Table¶

Column	Type	Default	Description
`tenant_user_preference_id`	bigint	auto-generated	Primary key
`user_id`	bigint	--	FK to `auth.user_info` (cascading delete)
`tenant_id`	bigint	--	FK to `auth.tenant` (cascading delete)
`user_preferences`	jsonb	`'{}'::jsonb`	Arbitrary JSON preference data

A unique constraint on (user_id, tenant_id) ensures one preference record per user per tenant.

Creating and Updating Preferences¶

-- create preferences for a user in tenant 1
select *
from auth.create_user_tenant_preferences(
    'admin', 1, null, 1001,
    '{"theme": "dark", "language": "en"}'::text,
    _tenant_id := 1
);

-- merge new keys into existing preferences (default behavior)
select *
from auth.update_user_tenant_preferences(
    'admin', 1, null, 1001,
    '{"sidebar_collapsed": true}'::text,
    _tenant_id := 1
);

-- overwrite all preferences entirely
select *
from auth.update_user_tenant_preferences(
    'admin', 1, null, 1001,
    '{"theme": "light"}'::text,
    _should_overwrite_data := true,
    _tenant_id := 1
);

Users can manage their own preferences without any permission. Managing another user's preferences requires the users.create_user_tenant_preferences or users.update_user_tenant_preferences permission (tenant-scoped).

Last Selected Tenant¶

When a user works across multiple tenants, the system tracks which tenant was last active. This is stored directly on the auth.user_info table:

Column	Type	Description
`last_selected_tenant_id`	integer	FK to `auth.tenant` (set to `null` on tenant deletion)

This value enables applications to automatically restore the user's last working context on login. The system provides two functions to manage it:

auth.get_user_last_selected_tenant() -- retrieves the user's last selected tenant (returns tenant ID, UUID, code, and title). Returns an empty set if no tenant has been selected.
auth.update_user_last_selected_tenant() -- sets the user's last selected tenant. Validates that the user is actually a member of the specified tenant before updating.

-- get the last selected tenant for user 1001
select *
from auth.get_user_last_selected_tenant(1, null, 1001);

-- update the last selected tenant
select *
from auth.update_user_last_selected_tenant(
    'admin', 1, null, 1001, 'a1b2c3d4-...'
);

Note

The update_user_last_selected_tenant function verifies that the acting user is a member of the target tenant (via group membership). If the tenant UUID is not found or the user is not a member, an error is raised.

Tenant Ownership¶

Each tenant can have one or more owners -- users with elevated privileges for managing that tenant. Ownership is tracked in the auth.owner table and supports two levels:

Tenant-level ownership (user_group_id is null) -- the user owns the entire tenant and can manage other owners across all groups
Group-level ownership (user_group_id references a specific group) -- the user owns a specific group within the tenant

Tenant owners can be assigned during tenant creation (via the _tenant_owner_id parameter) or afterwards through the ownership management functions.

For full details on ownership checks, authorization rules, and management functions, see the Ownership reference.