Tenant Isolation¶

The PostgreSQL Permissions Model implements complete tenant isolation where everything except users and permissions is tenant-specific. This design ensures secure separation between tenants while allowing efficient management of global resources.

Core Isolation Principle¶

What is Tenant-Specific¶

Everything is isolated by tenant except: - Users (auth.user_info) - Global across all tenants - Permissions (auth.permission) - Global permission hierarchy

Everything else is tenant-specific: - Permission sets and their contents - Groups and group memberships - Permission assignments - Tenant preferences and settings - API keys and technical users - Audit events and logs

graph TB
    subgraph "Global Resources"
        U[Users<br/>auth.user_info<br/>🌐 Shared across tenants]
        P[Permissions<br/>auth.permission<br/>🌐 Global hierarchy]
    end

    subgraph "Tenant A Resources"
        PSA[Permission Sets<br/>🏢 Tenant A specific]
        GA[Groups<br/>🏢 Tenant A specific]
        PAA[Permission Assignments<br/>🏢 Tenant A specific]
        AKA[API Keys<br/>🏢 Tenant A specific]
    end

    subgraph "Tenant B Resources"
        PSB[Permission Sets<br/>🏢 Tenant B specific]
        GB[Groups<br/>🏢 Tenant B specific]
        PAB[Permission Assignments<br/>🏢 Tenant B specific]
        AKB[API Keys<br/>🏢 Tenant B specific]
    end

    U --> PAA
    U --> PAB
    P --> PSA
    P --> PSB
    PSA --> PAA
    PSB --> PAB

Why This Design?¶

Global Users¶

Single Identity: A user can access multiple tenants with one account
Cross-Tenant Access: Users can switch between tenants they have access to
Simplified Management: One user record, multiple tenant memberships

Global Permissions¶

Consistent Structure: Same permission hierarchy across all tenants
Standardization: Applications use the same permission codes everywhere
Maintenance: Single place to define and update permission structure

Tenant-Specific Everything Else¶

Security Isolation: Complete separation of tenant data and access
Customization: Each tenant can have different permission sets and groups
Billing/Compliance: Clear tenant boundaries for auditing and billing

Isolation Implementation¶

Database Schema Isolation¶

Every tenant-specific table includes tenant_id:

-- Tenant-specific tables
CREATE TABLE auth.perm_set (
    perm_set_id uuid PRIMARY KEY,
    tenant_id uuid REFERENCES auth.tenant,  -- 🏢 Tenant isolation
    code text NOT NULL,
    name text NOT NULL,
    UNIQUE (tenant_id, code)  -- Unique within tenant
);

CREATE TABLE auth.user_group (
    user_group_id uuid PRIMARY KEY,
    tenant_id uuid REFERENCES auth.tenant,  -- 🏢 Tenant isolation
    code text NOT NULL,
    name text NOT NULL,
    UNIQUE (tenant_id, code)  -- Same group name can exist in different tenants
);

CREATE TABLE auth.permission_assignment (
    permission_assignment_id uuid PRIMARY KEY,
    tenant_id uuid REFERENCES auth.tenant,  -- 🏢 Tenant isolation
    user_id uuid REFERENCES auth.user_info,
    user_group_id uuid REFERENCES auth.user_group,
    -- Assignments are always tenant-specific
);

Global Tables (No tenant_id)¶

-- Global users table
CREATE TABLE auth.user_info (
    user_id uuid PRIMARY KEY,
    -- No tenant_id - users are global
    username text UNIQUE NOT NULL,
    email text,
    display_name text NOT NULL
);

-- Global permissions table
CREATE TABLE auth.permission (
    permission_id uuid PRIMARY KEY,
    -- No tenant_id - permissions are global
    code text UNIQUE NOT NULL,
    name text NOT NULL,
    path ltree  -- Hierarchical structure
);

Tenant Access Control¶

User-Tenant Membership¶

Users gain access to tenants through explicit membership:

-- User-tenant relationship
CREATE TABLE auth.tenant_user (
    tenant_id uuid REFERENCES auth.tenant,
    user_id uuid REFERENCES auth.user_info,
    is_active boolean DEFAULT true,
    joined_at timestamptz DEFAULT now(),
    PRIMARY KEY (tenant_id, user_id)
);

-- Check user's tenant access
SELECT t.name, t.code
FROM auth.tenant t
JOIN auth.tenant_user tu ON t.tenant_id = tu.tenant_id
WHERE tu.user_id = 'user-uuid'
  AND tu.is_active = true;

Cross-Tenant Security¶

All permission checks require tenant context:

-- Permission check always includes tenant
SELECT auth.has_permission(
    _tenant_id := 'tenant-uuid',  -- Required tenant context
    _user_id := 'user-uuid',
    _perm_code := 'users.create'
);

-- User might have permission in Tenant A but not Tenant B
-- Even with same permission sets, assignments are tenant-specific

Practical Examples¶

Same User, Different Tenants¶

-- User John exists globally
INSERT INTO auth.user_info (user_id, username, email, display_name)
VALUES ('john-uuid', 'john.doe', 'john@company.com', 'John Doe');

-- John has access to multiple tenants
INSERT INTO auth.tenant_user (tenant_id, user_id) VALUES
    ('tenant-a-uuid', 'john-uuid'),  -- Member of Tenant A
    ('tenant-b-uuid', 'john-uuid');  -- Member of Tenant B

-- John has different permissions in each tenant
-- Tenant A: John is an admin
SELECT auth.assign_permission(
    'tenant-a-uuid', 'john-uuid', NULL, 'ADMIN', NULL
);

-- Tenant B: John is read-only
SELECT auth.assign_permission(
    'tenant-b-uuid', 'john-uuid', NULL, 'VIEWER', NULL
);

Same Permission, Different Permission Sets¶

-- Global permission exists once
INSERT INTO auth.permission (permission_id, code, name, path)
VALUES ('perm-uuid', 'users.create', 'Create Users', 'users.create');

-- Tenant A includes it in Admin set
INSERT INTO auth.perm_set_perm (perm_set_id, permission_id)
VALUES (
    (SELECT perm_set_id FROM auth.perm_set
     WHERE tenant_id = 'tenant-a-uuid' AND code = 'ADMIN'),
    'perm-uuid'
);

-- Tenant B includes it in Manager set (different grouping)
INSERT INTO auth.perm_set_perm (perm_set_id, permission_id)
VALUES (
    (SELECT perm_set_id FROM auth.perm_set
     WHERE tenant_id = 'tenant-b-uuid' AND code = 'MANAGER'),
    'perm-uuid'
);

Tenant-Specific Groups¶

-- Same group name, different tenants
INSERT INTO auth.user_group (user_group_id, tenant_id, code, name) VALUES
    ('group-a-uuid', 'tenant-a-uuid', 'ADMINS', 'Administrators'),
    ('group-b-uuid', 'tenant-b-uuid', 'ADMINS', 'Administrators');

-- Groups can have different members and permissions per tenant
-- Even though they have the same name, they are completely separate

Benefits of This Isolation Model¶

Security Benefits¶

Complete Separation: No risk of cross-tenant data access
Permission Isolation: Tenant A's permissions don't affect Tenant B
Audit Trails: Clear tenant boundaries for all operations

Management Benefits¶

Single User Account: Users don't need separate accounts per tenant
Consistent Permissions: Same permission structure across all tenants
Flexible Assignment: Different permission combinations per tenant

Performance Benefits¶

Efficient Queries: All tenant-specific data filtered by tenant_id
Index Optimization: Composite indexes on (tenant_id, other_columns)
Scalable Design: Clear partitioning boundaries

Isolation Validation¶

Check Tenant Isolation¶

-- Verify no cross-tenant data leakage
-- Groups should be completely separate
SELECT
    'Groups per tenant' as check_type,
    t.name as tenant_name,
    COUNT(ug.user_group_id) as group_count
FROM auth.tenant t
LEFT JOIN auth.user_group ug ON t.tenant_id = ug.tenant_id
GROUP BY t.tenant_id, t.name;

-- Permission assignments should be tenant-specific
SELECT
    'Assignments per tenant' as check_type,
    t.name as tenant_name,
    COUNT(pa.permission_assignment_id) as assignment_count
FROM auth.tenant t
LEFT JOIN auth.permission_assignment pa ON t.tenant_id = pa.tenant_id
GROUP BY t.tenant_id, t.name;

Cross-Tenant Access Prevention¶

-- This query should return no results (good isolation)
-- Users should not have assignments in tenants they're not members of
SELECT
    u.username,
    t.name as tenant_name,
    'User has permissions but no tenant membership' as issue
FROM auth.permission_assignment pa
JOIN auth.user_info u ON pa.user_id = u.user_id
JOIN auth.tenant t ON pa.tenant_id = t.tenant_id
LEFT JOIN auth.tenant_user tu ON (pa.tenant_id = tu.tenant_id AND pa.user_id = tu.user_id)
WHERE tu.tenant_id IS NULL;  -- Should be empty

Best Practices¶

Application Design¶

Always Pass Tenant Context: Every permission check must include tenant_id
Validate Tenant Membership: Verify user belongs to tenant before operations
Use Tenant Filters: All queries should filter by tenant_id
Audit with Tenant: Include tenant information in all audit logs

Database Queries¶

-- ✅ Good - includes tenant filter
SELECT * FROM auth.user_group
WHERE tenant_id = 'tenant-uuid';

-- ❌ Bad - no tenant filter (security risk)
SELECT * FROM auth.user_group
WHERE code = 'ADMINS';  -- Could return multiple tenants' admin groups

Permission Checks¶

-- ✅ Good - tenant-scoped permission check
IF auth.has_permission('tenant-uuid', 'user-uuid', 'users.create') THEN
    -- User has permission in this specific tenant
    PERFORM create_user_in_tenant();
END IF;

-- ❌ Bad - no tenant context
-- This pattern is not supported by the system

What's Next¶

Learn about Tenant Management operations
Explore Tenant Users membership
Understand Multi-Tenancy architecture