Installation¶

The PostgreSQL Permissions Model is a pure PostgreSQL solution that can be integrated into any PostgreSQL project. This guide covers the complete installation and setup process.

Prerequisites¶

PostgreSQL Requirements¶

PostgreSQL Version: 12.0 or higher (recommended: 14.0+)
Extensions: The following PostgreSQL extensions are required:
ltree - For hierarchical permissions
uuid-ossp - For UUID generation
unaccent - For text processing
pg_trgm - For text search capabilities
pgcrypto - For password hashing

System Requirements¶

Operating System: Any OS supported by PostgreSQL
Memory: Minimum 512MB available for PostgreSQL
Storage: At least 100MB for the permission model schema
Network: Access to PostgreSQL server

Tools Required¶

PowerShell (Windows) or Bash (Linux/macOS)
PostgreSQL Client (psql command line tool)
Git (for cloning the repository)

Installation Methods¶

You can install the PostgreSQL Permissions Model in two ways:

Method 1: Automated Installation with debee.ps1 (Recommended)¶

Use the PowerShell script for automated installation and management.

1. Clone the Repository¶

# Clone the postgresql-permissions-model repository
git clone https://github.com/your-org/postgresql-permissions-model.git
cd postgresql-permissions-model

2. Configure Environment¶

Create your environment configuration file:

# Copy the template
cp debee.env .debee.env

# Edit the configuration
nano .debee.env

Required environment variables:

# PostgreSQL Connection
PGHOST=localhost
PGPORT=5432
PGUSER=your_postgres_user
PGPASSWORD=your_postgres_password

# Target Database
DBDESTDB=your_database_name

# Migration Settings
DBUPDATESTARTNUMBER=001
DBUPDATEENDNUMBER=999

3. Run Automated Installation¶

# Full installation (Windows PowerShell)
./debee.ps1 -Operations fullService

# Or step by step
./debee.ps1 -Operations recreateDatabase
./debee.ps1 -Operations restoreDatabase
./debee.ps1 -Operations updateDatabase

# Linux/macOS equivalent
./run.sh

Method 2: Manual Script Execution¶

Run the migration scripts manually in the correct order on any clean or existing PostgreSQL database.

1. Execute Scripts in Order¶

# Connect to your PostgreSQL database and run scripts in sequence:
psql -h localhost -U postgres -d your_database -f 000_create_database.sql
psql -h localhost -U postgres -d your_database -f 001_create_basic_structure.sql
psql -h localhost -U postgres -d your_database -f 002_create_version_management.sql
psql -h localhost -U postgres -d your_database -f 003_create_helpers.sql
psql -h localhost -U postgres -d your_database -f 004_create_permissions.sql
# Continue with 007-024_update_permissions_v*.sql files in order
psql -h localhost -U postgres -d your_database -f 99_fix_permissions.sql

2. Migration Script Order¶

Core Setup Scripts: - 000_create_database.sql - Database creation (optional if database exists) - 001_create_basic_structure.sql - Schemas and extensions - 002_create_version_management.sql - Version tracking system - 003_create_helpers.sql - Utility functions - 004_create_permissions.sql - Main permissions system (~217KB)

Incremental Updates: - 007-024_update_permissions_v1-X.sql - Version updates in sequence - 99_fix_permissions.sql - Post-update fixes

Important: Run scripts in numerical order. Each script uses version management to prevent duplicate execution.

Verification¶

Check Installation Status¶

-- Verify schemas were created
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name IN ('auth', 'helpers', 'error', 'const', 'internal', 'unsecure');

-- Check version table
SELECT * FROM __version ORDER BY version_number DESC LIMIT 5;

Verify Extensions¶

-- List installed extensions
SELECT extname, extversion
FROM pg_extension
WHERE extname IN ('ltree', 'uuid-ossp', 'unaccent', 'pg_trgm', 'pgcrypto');

Test Basic Functionality¶

-- Test permission creation
SELECT auth.create_permission_by_code(
    'test.permission',
    'Test Permission',
    'Test permission for installation verification'
);

-- Test user creation
SELECT auth.register_user(
    NULL, -- No tenant for test
    'test_user',
    'test@example.com',
    'Test User',
    'test123'
);

Configuration Options¶

Environment Files¶

The system supports multiple environment configurations:

debee.env - Default configuration
.debee.env - Local overrides (gitignored)
debee.prod.env - Production environment
debee.staging.env - Staging environment

Key Configuration Variables¶

Variable	Description	Example
`PGHOST`	PostgreSQL host	`localhost`
`PGPORT`	PostgreSQL port	`5432`
`PGUSER`	Database user	`postgres`
`PGPASSWORD`	Database password	`your_password`
`DBDESTDB`	Target database	`your_app_db`
`DBUPDATESTARTNUMBER`	Start migration number	`001`
`DBUPDATEENDNUMBER`	End migration number	`999`
`DBBACKUPFILE`	Backup file path	`backup.sql`
`DBBACKUPTYPE`	Backup type	`schema`

Advanced Configuration¶

# Custom migration range
DBUPDATESTARTNUMBER=010
DBUPDATEENDNUMBER=020

# Backup configuration
DBBACKUPFILE=/path/to/backup.sql
DBBACKUPTYPE=full

# Connection pooling
PGMAXCONNECTIONS=100
PGCONNECTIONTIMEOUT=30

Integration with Existing Applications¶

Schema Integration¶

The permission model creates several schemas:

-- Main schemas created
CREATE SCHEMA IF NOT EXISTS auth;         -- Main auth functions
CREATE SCHEMA IF NOT EXISTS unsecure;     -- Internal functions
CREATE SCHEMA IF NOT EXISTS helpers;      -- Utility functions
CREATE SCHEMA IF NOT EXISTS error;        -- Error handling
CREATE SCHEMA IF NOT EXISTS const;        -- Constants
CREATE SCHEMA IF NOT EXISTS internal;     -- Internal helpers
CREATE SCHEMA IF NOT EXISTS ext;          -- Extensions
CREATE SCHEMA IF NOT EXISTS stage;        -- Staging tables

Application User Setup¶

Create a dedicated database user for your application:

-- Create application user
CREATE USER your_app_user WITH PASSWORD 'secure_password';

-- Grant necessary permissions
GRANT USAGE ON SCHEMA auth TO your_app_user;
GRANT USAGE ON SCHEMA const TO your_app_user;
GRANT USAGE ON SCHEMA helpers TO your_app_user;

-- Grant execute permissions on auth functions
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA auth TO your_app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA helpers TO your_app_user;

-- Grant select on const tables
GRANT SELECT ON ALL TABLES IN SCHEMA const TO your_app_user;

Connection String Example¶

# Application connection string
DATABASE_URL=postgresql://your_app_user:secure_password@localhost:5432/your_database_name

Post-Installation Setup¶

1. Create Initial Tenant¶

-- Create your first tenant
SELECT auth.create_tenant(
    _name := 'Your Organization',
    _code := 'YOUR_ORG',
    _owner_user_id := NULL -- Will be set later
);

2. Create Administrative User¶

-- Create admin user
DO $$
DECLARE
    v_tenant_id uuid;
    v_user_id uuid;
BEGIN
    -- Get tenant ID
    SELECT tenant_id INTO v_tenant_id
    FROM auth.tenant
    WHERE code = 'YOUR_ORG';

    -- Create admin user
    v_user_id := auth.register_user(
        v_tenant_id,
        'admin',
        'admin@yourorg.com',
        'System Administrator',
        'change_this_password'
    );

    -- Set as tenant owner
    PERFORM auth.assign_tenant_owner(v_tenant_id, v_user_id);
END $$;

3. Create Basic Permission Sets¶

-- Create administrator permission set
SELECT auth.create_perm_set(
    _tenant_id := (SELECT tenant_id FROM auth.tenant WHERE code = 'YOUR_ORG'),
    _code := 'ADMIN',
    _name := 'Administrator',
    _description := 'Full system access'
);

-- Add all permissions (or specific ones)
INSERT INTO auth.perm_set_perm (perm_set_id, permission_id)
SELECT
    ps.perm_set_id,
    p.permission_id
FROM auth.perm_set ps
CROSS JOIN auth.permission p
WHERE ps.code = 'ADMIN'
  AND ps.tenant_id = (SELECT tenant_id FROM auth.tenant WHERE code = 'YOUR_ORG');

Troubleshooting¶

Common Installation Issues¶

Extension Not Found¶

-- If extensions fail to install
CREATE EXTENSION IF NOT EXISTS ltree CASCADE;
-- Error: extension "ltree" is not available

-- Solution: Install PostgreSQL contrib package
-- Ubuntu/Debian: sudo apt-get install postgresql-contrib
-- RHEL/CentOS: sudo yum install postgresql-contrib
-- Windows: Included with PostgreSQL installer

Permission Denied¶

-- Error: permission denied to create extension
-- Solution: Connect as superuser or ensure user has CREATE privilege

ALTER USER your_postgres_user CREATEDB;
GRANT CREATE ON DATABASE your_database TO your_postgres_user;

Migration Script Errors¶

# If migration scripts fail
./debee.ps1 -Operations updateDatabase -UpdateStartNumber 001 -UpdateEndNumber 001

# Check specific migration
./debee.ps1 -Operations preUpdateScripts
# Review output for specific errors

Validation Scripts¶

-- Validate installation completeness
DO $$
DECLARE
    v_schema_count integer;
    v_function_count integer;
    v_table_count integer;
BEGIN
    -- Check schemas
    SELECT COUNT(*) INTO v_schema_count
    FROM information_schema.schemata
    WHERE schema_name IN ('auth', 'helpers', 'error', 'const');

    IF v_schema_count < 4 THEN
        RAISE EXCEPTION 'Missing schemas. Expected 4, found %', v_schema_count;
    END IF;

    -- Check functions
    SELECT COUNT(*) INTO v_function_count
    FROM information_schema.routines
    WHERE routine_schema = 'auth';

    IF v_function_count < 50 THEN
        RAISE EXCEPTION 'Missing auth functions. Expected 50+, found %', v_function_count;
    END IF;

    RAISE NOTICE 'Installation validation successful';
END $$;

What's Next¶

Review Migration Scripts in detail
Begin with User Management
Explore Multi-Tenancy features