Schema Organization¶

The PostgreSQL Permissions Model organizes its functionality across multiple PostgreSQL schemas to provide clear separation of concerns, security boundaries, and maintainability. Understanding this schema structure is crucial for proper integration and usage.

Schema Overview¶

The system uses 8 primary schemas plus the standard public schema:

graph TB
    subgraph "Permission-Protected Schemas"
        auth["🔐 auth<br/>Authentication/authorization functions<br/>WITH permission validation<br/>(moved from public for clean separation)"]
        public["📂 public<br/>Application business functions<br/>WITH permission validation<br/>(kept clean for app-specific functions)"]
    end

    subgraph "Trusted Context Schemas"
        internal["🔧 internal<br/>Business logic functions<br/>NO permission checks<br/>(trusted context or wrapped by protected functions)"]
        const["📋 const<br/>Constants and configuration tables<br/>Read-only reference data"]
        helpers["🛠 helpers<br/>Utility functions<br/>Pure functions, no sensitive data access"]
    end

    subgraph "System Administration Schemas"
        unsecure["⚠️ unsecure<br/>Security-related system functions<br/>NO permission checks<br/>(admin/system operations only)"]
        error["❌ error<br/>Error handling and exception functions<br/>Structured error management"]
    end

    subgraph "Supporting Schemas"
        ext["🔌 ext<br/>PostgreSQL extensions<br/>ltree, uuid-ossp, unaccent, pg_trgm"]
        stage["📦 stage<br/>Staging tables for data imports<br/>External group members, bulk operations"]
    end

    auth --> const
    auth --> helpers
    auth --> unsecure
    auth --> error
    public --> const
    public --> helpers
    public --> internal
    helpers --> internal
    stage --> auth

Detailed Schema Descriptions¶

🔐 auth Schema - Permission-Protected API¶

Purpose: Authentication/authorization functions with full permission checking (equivalent to public schema pattern)

Key Components: - User management functions (register_user, update_user, etc.) - Permission checking functions (has_permission, has_permissions) - Group management (create_user_group, assign_permission) - Tenant operations (create_tenant, assign_tenant_owner) - API key management (create_api_key, update_api_key) - Audit logging (create_auth_event)

Security Model: All functions include permission validation and audit logging (except utility functions like get_app_version)

Design Note: Auth functions were moved from public schema to keep public clean for application-specific business functions

Example Functions:

-- User registration with full validation
SELECT auth.register_user(_tenant_id, _username, _email, _display_name, _password);

-- Permission check with audit trail
SELECT auth.has_permission(_tenant_id, _user_id, _perm_code, _throw_err);

-- Group creation with tenant isolation
SELECT auth.create_user_group(_tenant_id, _code, _name, _description);

📋 const Schema - System Constants¶

Purpose: Centralized configuration and constant definitions

Key Tables: - User types (human, technical) - Event types and error codes - System parameters and configuration - Default values and enumerations

Usage Pattern: Read-only reference data for the entire system

Example Content:

-- User types
SELECT * FROM const.user_type;
-- Returns: 'human', 'technical'

-- Event codes and descriptions
SELECT * FROM const.event_type WHERE code = '52002';
-- Returns: PERMISSION_DENIED error details

🛠 helpers Schema - Utility Functions¶

Purpose: General-purpose utility functions without security context

Key Functions: - Random string generation - Code generation utilities - ltree path operations - Text processing functions - Date/time utilities

Security Model: No permission checks - pure utility functions

Example Functions:

-- Generate secure random string
SELECT helpers.generate_random_string(32);

-- ltree path manipulation
SELECT helpers.build_permission_path('users', 'create', 'admin');

-- Code generation
SELECT helpers.generate_user_code('john.doe@company.com');

Purpose: Security and system administration functions without permission validation

⚠️ Security Note: These are security-related functions only - not general business functions without permissions

Key Functions: - Permission cache operations (clear_permission_cache) - User provisioning for system/migration purposes - Security system maintenance - Authentication system operations - Internal security utilities

Access Control: Should only be accessible to system/admin users

Important: This is NOT for unprotected business functions - those belong in internal schema

Example Functions:

-- Clear permission cache (security operation)
SELECT unsecure.clear_permission_cache(_user_id);

-- System user provisioning (security/admin operation)
SELECT unsecure.provision_system_user(_tenant_id, _user_data);

📂 public Schema - Application Business Functions¶

Purpose: Application-specific business functions with full permission checking

Key Functions: - Application business logic - Domain-specific operations - User-facing application functions - Custom business workflows

Security Model: Full permission validation - same pattern as auth schema but for application functions

Design Note: Kept clean and separate from authentication/authorization functions which were moved to auth schema

🔧 internal Schema - Trusted Context Functions¶

Purpose: Business logic functions without permission checks - trusted to be called from secure contexts

Key Functions: - Data validation helpers - Complex calculation functions - Internal workflow support - Business logic operations - Application-specific internal functions

Security Model: No permission checks - assumes caller has already validated permissions or is calling from secure context

Usage: Either wrapped by auth/public functions OR called directly by applications that handle security at application level

❌ error Schema - Error Management¶

Purpose: Centralized error handling and exception management

Key Functions: - Structured error throwing - Error code management - Exception formatting - Error logging utilities

Example Functions:

-- Throw structured permission error
SELECT error.throw_permission_denied(_tenant_id, _user_id, _perm_code);

-- Format error message
SELECT error.format_error_message('USER_NOT_FOUND', _user_id);

Schema Access Patterns¶

Application Integration¶

graph LR
    subgraph "Your Application"
        APP[Application Code]
    end

    subgraph "PostgreSQL Permissions Model"
        subgraph "Application Access"
            auth["auth.*<br/>Permission-protected"]
            public["public.*<br/>Permission-protected"]
            const["const.*<br/>Read-only reference"]
            helpers["helpers.*<br/>Pure utilities"]
        end

        subgraph "Trusted Context Access"
            internal["internal.*<br/>App can call directly<br/>if handling security"]
        end

        subgraph "Database Admin Only"
            unsecure["unsecure.*<br/>🚫 NEVER from app code<br/>DBA/System only"]
            error["error.*<br/>Via auth/public only"]
        end
    end

    APP --> auth
    APP --> public
    APP --> const
    APP --> helpers
    APP -.->|If app handles security| internal

    auth --> unsecure
    auth --> internal
    auth --> error
    public --> internal

Recommended Access Grants¶

For application database users:

-- Standard application user permissions
GRANT USAGE ON SCHEMA auth TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT USAGE ON SCHEMA const TO app_user;
GRANT USAGE ON SCHEMA helpers TO app_user;

-- Function execution permissions
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA auth TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA helpers TO app_user;

-- Read access to constants
GRANT SELECT ON ALL TABLES IN SCHEMA const TO app_user;

-- Optional: If app handles its own security for some operations
GRANT USAGE ON SCHEMA internal TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA internal TO app_user;

-- 🚫 NEVER grant application users access to unsecure schema
-- unsecure schema is for database administrators and system operations ONLY
-- GRANT USAGE ON SCHEMA unsecure TO app_user;  -- ❌ DO NOT DO THIS

-- Database admin user only (separate from application users)
GRANT USAGE ON SCHEMA unsecure TO dba_user;  -- Only for DBAs
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA unsecure TO dba_user;

Migration and Schema Evolution¶

Schema Creation Order¶

The schemas are created in dependency order during migration:

-- Migration sequence
1. ext       -- Extensions first
2. error     -- Error handling foundation
3. const     -- System constants
4. internal  -- Internal helpers
5. helpers   -- Utility functions
6. unsecure  -- Internal system functions
7. auth      -- Main API (depends on all others)
8. stage     -- Data staging (optional)
9. public    -- General tables (journal, templates)

Version Tracking¶

The system uses a simple public.__version table to track migration execution:

-- Check current version status
SELECT * FROM public.__version
ORDER BY execution_started DESC;

-- Check if specific version was applied
SELECT public.check_version('1.16', 'main');

-- View version with component support
SELECT
    component,
    version,
    title,
    description,
    execution_started,
    execution_finished
FROM public.__version
WHERE component = 'main'
ORDER BY version;

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

Schema Security Model¶

Permission Boundaries¶

graph TD
    subgraph "Security Layers"
        subgraph "Public API Layer"
            auth_public["auth schema functions<br/>✅ Permission checks<br/>✅ Audit logging<br/>✅ Error handling"]
        end

        subgraph "Utility Layer"
            helpers_util["helpers schema<br/>✅ No sensitive data<br/>✅ Pure functions<br/>⚠️ No permission checks"]
            const_ref["const schema<br/>✅ Read-only data<br/>✅ System constants<br/>✅ Safe for all users"]
        end

        subgraph "System Layer"
            unsecure_sys["unsecure schema<br/>⚠️ No permission checks<br/>⚠️ Security functions only<br/>🔒 DBA/System access ONLY"]
            internal_sys["internal schema<br/>✅ Business logic functions<br/>✅ Called by auth/public OR<br/>✅ App with security context"]
        end
    end

    auth_public --> helpers_util
    auth_public --> const_ref
    auth_public --> unsecure_sys
    auth_public --> internal_sys

Data Access Control¶

auth schema: All functions validate tenant access and user permissions
const schema: Read-only reference data, safe for all authenticated users
helpers schema: Utility functions with no data access, safe for applications
unsecure schema: Direct data manipulation, requires elevated privileges
internal schema: System-only functions, not accessible to applications
error schema: Error handling, accessed through auth functions

Performance Considerations¶

Schema-Based Optimization¶

-- Schema-specific indexes
CREATE INDEX idx_auth_tenant_isolation ON auth.user_info(tenant_id);
CREATE INDEX idx_auth_permission_cache ON auth.user_permission_cache(user_id, tenant_id);

-- Cross-schema query optimization
-- Avoid cross-schema JOINs when possible
-- Use schema-specific connection pooling for high-traffic functions

Function Distribution¶

auth schema: Complex business logic with full security (slower but secure)
helpers schema: Simple utilities optimized for speed
unsecure schema: Direct operations for performance-critical system tasks

Integration Examples¶

Standard Application Pattern¶

-- Application initialization
SET search_path = auth, const, helpers, public;

-- Standard permission check
IF auth.has_permission(v_tenant_id, v_user_id, 'users.create') THEN
    -- Proceed with operation
    PERFORM your_application.create_user_record();
END IF;

-- Use helper functions
v_random_code := helpers.generate_random_string(8);

Administrative Operations¶

-- Admin operations requiring unsecure access
SET search_path = auth, unsecure, const, helpers, public;

-- Bulk user import (bypasses normal validation)
SELECT unsecure.bulk_import_users_from_stage();

-- Clear all permission caches
SELECT unsecure.clear_all_permission_caches();

Best Practices¶

Schema Usage Guidelines¶

Primary Access: Use auth schema functions for all user-facing operations
Utilities: Use helpers for general-purpose functions
Constants: Reference const tables for system configuration
Avoid Direct Access: Never directly query tables outside your application's scope
Admin Functions: Restrict unsecure schema access to admin operations only

Error Handling¶

-- Let auth functions handle errors
BEGIN
    PERFORM auth.has_permission(v_tenant_id, v_user_id, v_permission);
    -- Continue with operation
EXCEPTION
    WHEN SQLSTATE '52002' THEN
        -- Permission denied - handled by error schema
        RAISE NOTICE 'Access denied for operation';
END;

What's Next¶

Review the Installation process
Learn about Database Setup
Explore Migration Scripts in detail

Schema Organization¶

Schema Overview¶

Detailed Schema Descriptions¶

🔐 auth Schema - Permission-Protected API¶

📋 const Schema - System Constants¶

🛠 helpers Schema - Utility Functions¶

⚠️ unsecure Schema - Security-Related System Functions¶

📂 public Schema - Application Business Functions¶

🔧 internal Schema - Trusted Context Functions¶

❌ error Schema - Error Management¶

Schema Access Patterns¶

Application Integration¶

Recommended Access Grants¶

Migration and Schema Evolution¶

Schema Creation Order¶

Version Tracking¶

Schema Security Model¶

Permission Boundaries¶

Data Access Control¶

Performance Considerations¶

Schema-Based Optimization¶

Function Distribution¶

Integration Examples¶

Standard Application Pattern¶

Administrative Operations¶

Best Practices¶

Schema Usage Guidelines¶

Error Handling¶

What's Next¶