Skip to content

PostgreSQL Permissions Model Architecture

The PostgreSQL Permissions Model implements a sophisticated multi-tenant authorization system built entirely within PostgreSQL. This section details how the system is structured, how components interact, and how permissions flow through the architecture.

Core Architecture Components

Multi-Schema Organization

The system organizes functionality across multiple PostgreSQL schemas for security and maintainability:

graph LR
    subgraph "PostgreSQL Database"
        subgraph "Public Schemas"
            auth["🔐 auth<br/>Main API Functions"]
            const["📋 const<br/>System Constants"]
            helpers["🛠 helpers<br/>Utility Functions"]
        end

        subgraph "Internal Schemas"
            unsecure["⚠️ unsecure<br/>Internal Functions"]
            internal["🔧 internal<br/>Helper Functions"]
            error["❌ error<br/>Error Handling"]
        end

        subgraph "Supporting Schemas"
            ext["🔌 ext<br/>Extensions"]
            stage["📦 stage<br/>Staging Tables"]
            public["📂 public<br/>Templates & Journal"]
        end
    end

Entity Relationship Architecture

erDiagram
    TENANT ||--o{ TENANT_USER : contains
    TENANT ||--o{ USER_GROUP : contains
    TENANT ||--o{ PERM_SET : contains

    USER_INFO ||--o{ TENANT_USER : belongs_to
    USER_INFO ||--o{ USER_IDENTITY : has
    USER_INFO ||--o{ API_KEY : technical_user
    USER_INFO ||--o{ USER_DATA : extends

    USER_GROUP ||--o{ USER_GROUP_MEMBER : internal_members
    USER_GROUP ||--o{ USER_GROUP_MAPPING : external_mapping
    USER_GROUP ||--o{ PERMISSION_ASSIGNMENT : assigned_to

    PROVIDER ||--o{ USER_IDENTITY : authenticates
    PROVIDER ||--o{ USER_GROUP_MAPPING : maps_from

    PERMISSION ||--o{ PERM_SET_PERM : global_permissions
    PERM_SET ||--o{ PERM_SET_PERM : tenant_sets
    PERM_SET ||--o{ PERMISSION_ASSIGNMENT : assigned_as_set

    PERMISSION_ASSIGNMENT }|--|| USER_INFO : direct_assignment
    PERMISSION_ASSIGNMENT }|--|| USER_GROUP : group_assignment

Permission Resolution Flow

Multi-Tenant Permission Check

flowchart TD
    A[User Requests Action] --> B{Tenant Access?}
    B -->|No| C[Access Denied]
    B -->|Yes| D[Get Last Used Identity]

    D --> E{Direct Permissions?}
    E -->|Yes| F[Collect Direct Permissions]
    E -->|No| G[Check Group Memberships]

    G --> H{Internal Groups?}
    H -->|Yes| I[Get Internal Group Permissions]

    G --> J{External Mappings?}
    J -->|Yes| K[Get Provider Groups/Roles]
    K --> L[Map to Internal Groups]
    L --> M[Get Mapped Group Permissions]

    F --> N[Combine All Permissions]
    I --> N
    M --> N

    N --> O{Has Required Permission?}
    O -->|Yes| P[Access Granted]
    O -->|No| Q[Create Audit Event]
    Q --> C

    P --> R[Create Success Event]

Identity Provider Integration Architecture

Multi-Provider User Model

graph TB
    subgraph "User Identity System"
        U[User Info<br/>Core User Data]

        subgraph "Multiple Identities"
            I1[Identity 1<br/>Windows AD]
            I2[Identity 2<br/>Azure AD]
            I3[Identity 3<br/>Google OAuth]
            I4[Identity N<br/>Custom SAML]
        end

        LU[Last Used Identity<br/>Determines Current Permissions]
    end

    subgraph "External Providers"
        P1[Windows AD<br/>Domain Groups]
        P2[Azure AD<br/>Groups + Roles]
        P3[Google Workspace<br/>Groups]
        P4[Custom SAML<br/>Attributes]
    end

    subgraph "Group Mapping System"
        GM[Group Mappings<br/>External → Internal]
        IG[Internal Groups<br/>Permission Containers]
    end

    U --> I1
    U --> I2
    U --> I3
    U --> I4

    I1 -.->|Last Used| LU
    I2 --> LU
    I3 --> LU
    I4 --> LU

    P1 --> I1
    P2 --> I2
    P3 --> I3
    P4 --> I4

    I1 --> GM
    I2 --> GM
    I3 --> GM
    I4 --> GM

    GM --> IG

Permission Hierarchy and Inheritance

Hierarchical Permission Structure

graph TD
    subgraph "Global Permission Tree (ltree)"
        root[Root Permissions]

        users[users] --> users_create[users.create_user]
        users --> users_read[users.read_users]
        users_read --> users_gdpr[users.read_users.read_gdpr_protected_data]

        reports[reports] --> reports_view[reports.view]
        reports --> reports_generate[reports.generate]
        reports_generate --> reports_financial[reports.generate.financial]

        admin[admin] --> admin_system[admin.system_settings]
        admin --> admin_tenant[admin.tenant_management]
    end

    subgraph "Tenant-Specific Permission Sets"
        ps1[Permission Set: ADMIN<br/>Tenant A]
        ps2[Permission Set: VIEWER<br/>Tenant A]
        ps3[Permission Set: ADMIN<br/>Tenant B]
    end

    subgraph "Assignment to Users/Groups"
        u1[User 1]
        u2[User 2]
        g1[Group: Managers]
        g2[Group: Viewers]
    end

    users --> ps1
    reports --> ps1
    admin --> ps1

    users_read --> ps2
    reports_view --> ps2

    users --> ps3
    reports_generate --> ps3

    ps1 --> u1
    ps1 --> g1
    ps2 --> u2
    ps2 --> g2
    ps3 --> u1

API Key and Technical User Architecture

Technical User Pattern

graph LR
    subgraph "API Key System"
        AK[API Key Creation Request]

        AK --> TU[Create Technical User<br/>in user_info]
        TU --> AKR[Create API Key Record<br/>linked to Technical User]

        AKR --> PS[Assign Permission Sets<br/>to Technical User]
        PS --> GM[Add to Groups<br/>if needed]
    end

    subgraph "Permission Resolution"
        API[API Request with Key] --> AUTH[Validate API Key]
        AUTH --> GETUSER[Get Technical User ID]
        GETUSER --> CHECKPERM[Check Permissions<br/>Same as Human Users]
        CHECKPERM --> AUDIT[Create Audit Event]
    end

    subgraph "Consistency Benefits"
        UNIFORM[Uniform Permission Model<br/>Humans + APIs]
        AUDITTRAIL[Complete Audit Trail<br/>All Actions → Users]
        SIMPLE[Single Permission Logic<br/>No Special Cases]
    end

Database Schema Layout

Core Data Model

graph TB
    subgraph "Multi-Tenancy Layer"
        T[tenant] --> TU[tenant_user]
        T --> O[owner]
        TU --> UTP[user_tenant_preference]
    end

    subgraph "User Management Layer"
        UI[user_info] --> UID[user_identity]
        UI --> UD[user_data]
        UI --> UPC[user_permission_cache]
        UID --> P[provider]
    end

    subgraph "Group Management Layer"
        UG[user_group] --> UGM[user_group_member]
        UG --> UGMA[user_group_mapping]
        UGMA --> P
    end

    subgraph "Permission Layer"
        PERM[permission] --> PSP[perm_set_perm]
        PS[perm_set] --> PSP
        PS --> PA[permission_assignment]
        PERM --> PA
    end

    subgraph "Authentication Layer"
        AK[api_key] --> UI
        TOK[token] --> UI
        UE[user_event] --> UI
    end

    subgraph "Supporting Tables"
        J[journal] --> CT[const tables]
        ST[stage tables] --> UGMA
    end

Performance and Caching Architecture

Permission Cache Strategy

graph LR
    subgraph "Permission Resolution"
        REQ[Permission Request] --> CACHE{Check Cache}
        CACHE -->|Hit| CACHED[Return Cached Result]
        CACHE -->|Miss| CALC[Calculate Permissions]

        CALC --> DIRECT[Direct User Permissions]
        CALC --> GROUP[Group Permissions]
        CALC --> INHERIT[Inherited from Groups]

        DIRECT --> COMBINE[Combine All Permissions]
        GROUP --> COMBINE
        INHERIT --> COMBINE

        COMBINE --> STORE[Store in Cache]
        STORE --> RETURN[Return Result]
    end

    subgraph "Cache Invalidation"
        USERUPDATE[User Updated] --> CLEAR[Clear User Cache]
        GROUPUPDATE[Group Membership Changed] --> CLEAR
        PERMUPDATE[Permissions Changed] --> CLEAR
        IDENTITYCHANGE[Identity Provider Sync] --> CLEAR
    end

Version Management and Migration Architecture

Database Version Control

graph TD
    subgraph "Migration System"
        V[public.__version Table<br/>component | version | title | description<br/>execution_started | execution_finished]

        V --> M1[Migration 001<br/>Basic Structure]
        V --> M2[Migration 002<br/>Version Management]
        V --> M3[Migration 003<br/>Helpers]
        V --> M4[Migration 004<br/>Main Permissions<br/>~217KB]
        V --> MN[Migration 007-024<br/>Incremental Updates]

        M1 --> VS[public.start_version_update<br/>Creates record with timestamp]
        M2 --> VS
        M3 --> VS
        M4 --> VS
        MN --> VS

        VS --> EXEC[Execute Migration SQL]
        EXEC --> VE[public.stop_version_update<br/>Sets finish timestamp]
    end

    subgraph "Version Functions"
        CHECK[public.check_version<br/>Returns if version applied]
        START[public.start_version_update<br/>Begin version tracking]
        STOP[public.stop_version_update<br/>Complete version tracking]
    end

    subgraph "Environment Support"
        ENV[debee.env] --> LOCAL[.debee.env<br/>Local Overrides]
        ENV --> PROD[debee.prod.env]
        ENV --> STAGE[debee.staging.env]
    end

    subgraph "Migration Tools"
        PS1[debee.ps1<br/>PowerShell Script]
        BAT[run.bat<br/>Windows Batch]

        PS1 --> OPS[Operations:<br/>recreateDatabase<br/>restoreDatabase<br/>updateDatabase<br/>preUpdateScripts<br/>postUpdateScripts]
    end

What's Next