Tenant Management¶

The PostgreSQL Permissions Model provides comprehensive multi-tenancy support, allowing complete isolation of permissions, users, and data between different tenants. This enables SaaS applications and enterprise systems to securely manage multiple organizations within a single database.

Overview¶

Multi-tenancy in this system provides: - Complete Isolation: Each tenant has its own users, groups, permissions, and data - Flexible Permission Sets: Each tenant can define custom permission sets - Tenant-Specific Configurations: Separate settings and preferences per tenant - Ownership Management: Track tenant owners and administrators

Core Tables¶

auth.tenant¶

The main tenant table storing tenant information:

CREATE TABLE auth.tenant (
    tenant_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    name text NOT NULL,
    code text UNIQUE,
    is_active boolean DEFAULT true,
    created_at timestamptz DEFAULT now(),
    updated_at timestamptz DEFAULT now()
);

auth.tenant_user¶

Links users to tenants they have access to:

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)
);

auth.owner¶

Tracks ownership of tenants and groups:

CREATE TABLE auth.owner (
    owner_id uuid PRIMARY KEY,
    tenant_id uuid REFERENCES auth.tenant,
    user_group_id uuid REFERENCES auth.user_group,
    user_id uuid REFERENCES auth.user_info
);

Creating and Managing Tenants¶

Create a New Tenant¶

-- Create a new tenant
SELECT auth.create_tenant(
    _name := 'Acme Corporation',
    _code := 'ACME',
    _owner_user_id := 'user-uuid-here'
);

-- Returns: tenant_id

Update Tenant Information¶

-- Update tenant details
SELECT auth.update_tenant(
    _tenant_id := 'tenant-uuid',
    _name := 'Acme Corp International',
    _code := 'ACME-INTL'
);

Activate/Deactivate Tenants¶

-- Deactivate a tenant
UPDATE auth.tenant
SET is_active = false,
    updated_at = now()
WHERE tenant_id = 'tenant-uuid';

-- Reactivate a tenant
UPDATE auth.tenant
SET is_active = true,
    updated_at = now()
WHERE tenant_id = 'tenant-uuid';

Managing Tenant Users¶

Add User to Tenant¶

-- Add existing user to tenant
INSERT INTO auth.tenant_user (tenant_id, user_id)
VALUES ('tenant-uuid', 'user-uuid');

-- Or use helper function
SELECT auth.add_user_to_tenant(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid'
);

Remove User from Tenant¶

-- Soft delete (deactivate)
UPDATE auth.tenant_user
SET is_active = false
WHERE tenant_id = 'tenant-uuid'
  AND user_id = 'user-uuid';

-- Hard delete
DELETE FROM auth.tenant_user
WHERE tenant_id = 'tenant-uuid'
  AND user_id = 'user-uuid';

List Tenant Users¶

-- Get all active users for a tenant
SELECT
    tu.user_id,
    ui.username,
    ui.email,
    ui.display_name,
    tu.joined_at
FROM auth.tenant_user tu
JOIN auth.user_info ui ON tu.user_id = ui.user_id
WHERE tu.tenant_id = 'tenant-uuid'
  AND tu.is_active = true
  AND ui.is_active = true
ORDER BY ui.display_name;

Tenant Ownership¶

Assign Tenant Owner¶

-- Assign owner to tenant
SELECT auth.assign_tenant_owner(
    _tenant_id := 'tenant-uuid',
    _user_id := 'owner-user-uuid'
);

Transfer Ownership¶

-- Transfer tenant ownership
UPDATE auth.owner
SET user_id = 'new-owner-uuid'
WHERE tenant_id = 'tenant-uuid';

Query Tenant Owners¶

-- Get tenant owner information
SELECT
    t.name as tenant_name,
    u.display_name as owner_name,
    u.email as owner_email
FROM auth.tenant t
JOIN auth.owner o ON t.tenant_id = o.tenant_id
JOIN auth.user_info u ON o.user_id = u.user_id
WHERE t.tenant_id = 'tenant-uuid';

Tenant-Specific Permission Sets¶

Each tenant can have unique permission sets tailored to their needs:

-- Create tenant-specific permission set
SELECT auth.create_perm_set(
    _tenant_id := 'tenant-uuid',
    _code := 'TENANT_ADMIN',
    _name := 'Tenant Administrator',
    _description := 'Full administrative access within tenant'
);

-- Add permissions to the set
SELECT auth.add_perm_set_permissions(
    _tenant_id := 'tenant-uuid',
    _perm_set_code := 'TENANT_ADMIN',
    _perm_codes := ARRAY['users', 'groups', 'permissions']
);

User Tenant Preferences¶

Store user-specific preferences per tenant:

-- Set user preference for tenant
INSERT INTO auth.user_tenant_preference
    (user_id, tenant_id, preference_key, preference_value)
VALUES
    ('user-uuid', 'tenant-uuid', 'theme', 'dark')
ON CONFLICT (user_id, tenant_id, preference_key)
DO UPDATE SET preference_value = EXCLUDED.preference_value;

-- Get user preferences for tenant
SELECT preference_key, preference_value
FROM auth.user_tenant_preference
WHERE user_id = 'user-uuid'
  AND tenant_id = 'tenant-uuid';

Tenant Isolation Patterns¶

Permission Checks with Tenant Context¶

All permission checks require tenant context:

-- Check permission within tenant context
SELECT auth.has_permission(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid',
    _perm_code := 'users.create_user'
);

Tenant-Scoped Queries¶

Always include tenant context in queries:

-- Get tenant-specific groups
SELECT * FROM auth.user_group
WHERE tenant_id = 'tenant-uuid';

-- Get tenant-specific permission assignments
SELECT * FROM auth.permission_assignment
WHERE tenant_id = 'tenant-uuid';

Common Tenant Operations¶

Create Tenant with Initial Setup¶

-- Complete tenant setup
DO $$
DECLARE
    v_tenant_id uuid;
    v_admin_role_id uuid;
BEGIN
    -- Create tenant
    v_tenant_id := auth.create_tenant(
        'New Company',
        'NEWCO',
        'admin-user-uuid'
    );

    -- Create default groups
    v_admin_role_id := auth.create_user_group(
        v_tenant_id,
        'ADMINS',
        'Administrators'
    );

    -- Create default permission sets
    PERFORM auth.create_perm_set(
        v_tenant_id,
        'FULL_ACCESS',
        'Full Access',
        'Complete system access'
    );

    -- Assign permissions to admin group
    PERFORM auth.assign_permission(
        v_tenant_id,
        NULL,
        v_admin_role_id,
        'FULL_ACCESS',
        NULL
    );
END $$;

Clone Tenant Configuration¶

-- Clone permission sets from one tenant to another
SELECT auth.duplicate_perm_set(
    _from_tenant_id := 'source-tenant-uuid',
    _to_tenant_id := 'target-tenant-uuid',
    _perm_set_code := 'STANDARD_USER'
);

Tenant Statistics¶

-- Get tenant statistics
SELECT
    t.name as tenant_name,
    COUNT(DISTINCT tu.user_id) as user_count,
    COUNT(DISTINCT ug.user_group_id) as group_count,
    COUNT(DISTINCT ps.perm_set_id) as permission_set_count
FROM auth.tenant t
LEFT JOIN auth.tenant_user tu ON t.tenant_id = tu.tenant_id AND tu.is_active = true
LEFT JOIN auth.user_group ug ON t.tenant_id = ug.tenant_id
LEFT JOIN auth.perm_set ps ON t.tenant_id = ps.tenant_id
WHERE t.tenant_id = 'tenant-uuid'
GROUP BY t.tenant_id, t.name;

Security Considerations¶

Tenant Isolation Enforcement¶

Always Include Tenant Context: Every query should filter by tenant_id
Validate Tenant Access: Check user belongs to tenant before operations
Audit Tenant Operations: Log all tenant-related activities
Separate Tenant Data: Consider schema-per-tenant for sensitive data

Cross-Tenant Security¶

-- Prevent cross-tenant access
CREATE OR REPLACE FUNCTION check_tenant_access(
    _tenant_id uuid,
    _user_id uuid
) RETURNS boolean AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM auth.tenant_user
        WHERE tenant_id = _tenant_id
          AND user_id = _user_id
          AND is_active = true
    );
END;
$$ LANGUAGE plpgsql;

Best Practices¶

Tenant Naming: Use clear, unique identifiers for tenant codes
Default Setup: Create template permission sets for new tenants
Regular Audits: Review tenant access and permissions regularly
Tenant Limits: Implement resource limits per tenant if needed
Backup Strategy: Consider tenant-specific backup requirements
Performance: Index tenant_id columns for query performance

What's Next¶

Learn about Tenant Users management
Explore Tenant Isolation strategies
Understand Permission Sets for tenants