Skip to content

Introduction to PostgreSQL Permissions Model

This is a standalone PostgreSQL database model that provides complete tenant/user/group/permissions management for any PostgreSQL project. It's a self-contained SQL-based framework that gives applications comprehensive handling of multi-tenancy, user management, hierarchical permissions, permission sets, and role-based access control.

What This System Provides

Complete Multi-Tenancy Support

  • Tenant isolation: Complete separation of users, groups, and permissions between tenants
  • Tenant-specific permission sets: Each tenant can define custom permission collections
  • Tenant ownership: Track and manage tenant administrators
  • Cross-tenant security: Built-in protection against cross-tenant access

Flexible User Management

  • Multi-identity support: Users can authenticate via multiple providers (Windows, Azure AD, Google, etc.)
  • API users: API keys automatically create API users for consistent permission handling
  • Extensible user data: Custom fields via user_data table or separate tables
  • User lifecycle management: Registration, activation, deactivation, password management

Three-Tier Group System

  • Internal Groups: Traditional database-managed membership
  • External Groups: Membership determined by identity provider mappings
  • Hybrid Groups: Combination of both internal and external membership

Hierarchical Permissions with Permission Sets

  • Global permissions: Hierarchical structure using PostgreSQL ltree (e.g., users.create_user.with_admin_rights)
  • Tenant-specific permission sets: Collections of permissions grouped by role/function
  • Flexible assignment: Permissions assigned to users directly or via groups
  • Permission caching: Built-in caching for performance optimization

Core Architecture Principles

Pure PostgreSQL Solution

  • No external dependencies: Uses only PostgreSQL extensions (ltree, uuid-ossp, unaccent, pg_trgm, pgcrypto)
  • Self-contained: All logic implemented in SQL functions and procedures
  • Database-driven security: All permission checks happen at the database level

Identity Provider Agnostic

  • Windows Authentication: Domain group integration
  • Azure Active Directory: OAuth/SAML token processing
  • OAuth Providers: Google, Facebook, GitHub integration
  • SAML Providers: Okta, Auth0, KeyCloak support
  • Custom Providers: Extensible provider system

Audit-First Design

  • Comprehensive logging: All security events recorded in auth.user_event
  • Structured error codes: Specific codes for different event types (50001-52999)
  • Permission check auditing: Optional auditing of all permission checks
  • Event correlation: Link events to users, tenants, and operations

System Overview Diagram

graph TB
    subgraph "Multi-Tenant Architecture"
        T1[Tenant A]
        T2[Tenant B]
        T3[Tenant N...]
    end

    subgraph "User Management"
        U[Users] --> UI[User Identities]
        UI --> P1[Provider: Windows AD]
        UI --> P2[Provider: Azure AD]
        UI --> P3[Provider: OAuth...]
        U --> UD[User Data]
        U --> TU[Technical Users]
        TU --> AK[API Keys]
    end

    subgraph "Group System"
        IG[Internal Groups] --> GM[Group Members]
        EG[External Groups] --> GMP[Group Mappings]
        HG[Hybrid Groups] --> GM
        HG --> GMP
        GMP --> UI
    end

    subgraph "Permission System"
        GP[Global Permissions] --> PS[Permission Sets]
        PS --> PA[Permission Assignments]
        PA --> U
        PA --> IG
        PA --> EG
        PA --> HG
    end

    T1 --> U
    T2 --> U
    T3 --> U
    T1 --> PS
    T2 --> PS
    T3 --> PS

Key Features That Set This Apart

API User Pattern

  • API Keys = Users: Each API key creates an API user for consistent permission handling
  • Uniform authorization: Same functions work for human users and API services
  • Audit consistency: All actions traced to a user (human or API)

Last-Used Identity Logic

  • Multi-provider users: Same user can have Windows, Azure AD, Google identities
  • Dynamic permissions: Permissions calculated from most recently used identity
  • Provider group inheritance: Users inherit groups from their active identity provider

Version-Managed Migrations

  • Migration tracking: __version table tracks all schema changes
  • Incremental updates: Numbered migration scripts (001, 002, etc.)
  • Environment support: Different configurations for dev/staging/prod

Integration Points

Application Integration

  • Function-based API: Rich set of SQL functions for all operations
  • Permission checking: auth.has_permission() and auth.has_permissions()
  • Event logging: auth.create_user_event() for audit trails
  • Error handling: Structured error codes with throw/no-throw options

External System Integration

  • Identity providers: Seamless integration with enterprise authentication
  • Group synchronization: Map external groups to internal permissions
  • Token processing: Handle JWT, SAML tokens from various providers

Important SQL Convention

⚠️ Always use fully qualified schema names in all SQL commands (e.g., auth.has_permission, public.__version) to avoid "cannot find table/function" errors due to search_path issues.

-- ✅ Good - fully qualified
SELECT auth.has_permission(_target_user_id, 'users.create', _tenant_id);
SELECT * FROM public.__version;

-- ❌ Bad - relies on search_path
SELECT has_permission(_target_user_id, 'users.create', _tenant_id);
SELECT * FROM __version;

What's Next