Chapter 06: Database Strategy

PostgreSQL 16 on Shared Infrastructure

6.1 Overview

This chapter defines the database strategy for the POS Platform, using PostgreSQL 16 on shared infrastructure. The strategy balances performance, isolation, and operational simplicity for a multi-tenant SaaS application.

Key Decisions

6.2 Infrastructure Architecture

Physical Deployment

┌─────────────────────────────────────────────────────────────────────────────┐
│                         SYNOLOGY NAS (192.168.1.26)                         │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│   ┌─────────────────────────────────────────────────────────────────────┐   │
│   │                      PostgreSQL 16 Container                         │   │
│   │                        (postgres16)                                  │   │
│   │                                                                      │   │
│   │   Port: 5433 (external) → 5432 (internal)                           │   │
│   │   Data: /volume1/docker/postgres/data                               │   │
│   │   Network: postgres_default                                          │   │
│   │                                                                      │   │
│   │   ┌───────────────────────────────────────────────────────────────┐ │   │
│   │   │                   pos_platform Database                        │ │   │
│   │   │                                                                │ │   │
│   │   │   ┌──────────────┐   ┌─────────────────────────────────────┐  │ │   │
│   │   │   │   shared     │   │          public schema               │  │ │   │
│   │   │   │   schema     │   │   (all tenant tables with RLS)       │  │ │   │
│   │   │   │              │   │                                      │  │ │   │
│   │   │   │ tenants      │   │ products     (tenant_id + RLS)       │  │ │   │
│   │   │   │ plans        │   │ orders       (tenant_id + RLS)       │  │ │   │
│   │   │   │ features     │   │ customers    (tenant_id + RLS)       │  │ │   │
│   │   │   │              │   │ inventory    (tenant_id + RLS)       │  │ │   │
│   │   │   │              │   │ ... all other tenant tables          │  │ │   │
│   │   │   └──────────────┘   └─────────────────────────────────────┘  │ │   │
│   │   │                                                                │ │   │
│   │   └───────────────────────────────────────────────────────────────┘ │   │
│   │                                                                      │   │
│   │   Other Databases: salessight_db, stanly_db, shopsyncflow_db, ...  │   │
│   │                                                                      │   │
│   └─────────────────────────────────────────────────────────────────────┘   │
│                                                                             │
│   ┌─────────────────────┐         ┌──────────────────────────────────────┐ │
│   │   PgBouncer         │◄───────►│       Application Containers         │ │
│   │   Port: 6432        │         │       (pos-api, pos-admin, etc.)     │ │
│   └─────────────────────┘         └──────────────────────────────────────┘ │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

Database Creation

-- Create the main POS platform database
CREATE DATABASE pos_platform
    WITH OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TEMPLATE = template0;

-- Connect to the new database
\c pos_platform

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";       -- UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";        -- Cryptographic functions
CREATE EXTENSION IF NOT EXISTS "btree_gist";      -- GiST index support
CREATE EXTENSION IF NOT EXISTS "pg_trgm";         -- Trigram text search

-- Create the shared schema for platform-wide tables
CREATE SCHEMA IF NOT EXISTS shared;

-- Grant usage to application role
GRANT USAGE ON SCHEMA shared TO pos_app;
GRANT USAGE ON SCHEMA public TO pos_app;

6.3 Row-Level Security (RLS) Architecture

Why Row-Level Security?

Row-Level Security was selected because the BRD v19.0 data models already include tenant_id UUID on every tenant-scoped table (135+ occurrences across all modules). RLS enforces isolation at the database level as defense-in-depth, preventing accidental cross-tenant data access even if application code has bugs.

How RLS Works

All tenants share the same tables in the public schema. Every tenant-scoped table includes a tenant_id UUID NOT NULL column. PostgreSQL RLS policies automatically filter rows so each tenant can only see and modify their own data.

┌─────────────────────────────────────────────────────────────────┐
│                  RLS Data Flow                                    │
├─────────────────────────────────────────────────────────────────┤
│                                                                   │
│  1. Request arrives for tenant "nexus"                           │
│     ┌─────────────────────────────────┐                          │
│     │  POST /api/products             │                          │
│     │  Authorization: Bearer <jwt>    │                          │
│     └──────────────┬──────────────────┘                          │
│                    │                                              │
│  2. Middleware extracts tenant_id from JWT                        │
│                    │                                              │
│  3. Application sets PostgreSQL session variable                 │
│     ┌─────────────────────────────────┐                          │
│     │  SET app.current_tenant =       │                          │
│     │    'a1b2c3d4-...-tenant-uuid'   │                          │
│     └──────────────┬──────────────────┘                          │
│                    │                                              │
│  4. Query executes — RLS policy filters automatically            │
│     ┌─────────────────────────────────┐                          │
│     │  SELECT * FROM products;        │                          │
│     │  -- RLS adds: WHERE tenant_id   │                          │
│     │  -- = 'a1b2c3d4-...'            │                          │
│     └─────────────────────────────────┘                          │
│                                                                   │
│  Result: Only nexus's products returned. Other tenants           │
│  invisible even without WHERE clause in application code.        │
│                                                                   │
└─────────────────────────────────────────────────────────────────┘

RLS Policy Implementation

-- Step 1: Add tenant_id to every tenant-scoped table
-- (Already present in schema design — see Chapter 07)

-- Step 2: Enable RLS on each tenant-scoped table
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE products FORCE ROW LEVEL SECURITY;

-- Step 3: Create isolation policy
CREATE POLICY tenant_isolation ON products
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- The USING clause applies to SELECT, UPDATE, DELETE
-- For INSERT, add a WITH CHECK clause to prevent inserting for wrong tenant
CREATE POLICY tenant_insert ON products
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

Application Tenant Context (C# Middleware)

// TenantMiddleware.cs — Sets PostgreSQL session variable for RLS
public class TenantMiddleware
{
    private readonly RequestDelegate _next;
    private readonly ILogger<TenantMiddleware> _logger;

    public TenantMiddleware(RequestDelegate next, ILogger<TenantMiddleware> logger)
    {
        _next = next;
        _logger = logger;
    }

    public async Task InvokeAsync(HttpContext context, PosDbContext dbContext)
    {
        var tenantId = ResolveTenantFromJwt(context);

        if (tenantId == null)
        {
            context.Response.StatusCode = 401;
            await context.Response.WriteAsJsonAsync(new { error = "Tenant not resolved" });
            return;
        }

        // Set PostgreSQL session variable — RLS policies read this
        await dbContext.Database.ExecuteSqlRawAsync(
            "SET app.current_tenant = {0}", tenantId.ToString());

        // Store in HttpContext for application-layer use
        context.Items["TenantId"] = tenantId;

        _logger.LogDebug("RLS context set for tenant: {TenantId}", tenantId);

        await _next(context);
    }

    private Guid? ResolveTenantFromJwt(HttpContext context)
    {
        var claim = context.User?.FindFirst("tenant_id");
        return claim != null ? Guid.Parse(claim.Value) : null;
    }
}

Benefits of RLS

• Simpler operations: Single schema, no per-tenant schema migrations
• No schema sprawl: 100 tenants = same number of tables (not 100x)
• Simpler connection pooling: Shared pool for all tenants (no search_path switching)
• Database-enforced isolation: Even buggy application code cannot leak data
• Easier migrations: ALTER TABLE once, applies to all tenants
• Matches BRD data models: 135+ tenant_id FK occurrences already in BRD v19.0

Trade-offs

• Less physical isolation than schema-per-tenant (mitigated by RLS enforcement)
• All tenants share the same table structure (schema flexibility limited)
• RLS policies must be applied to every tenant-scoped table (automated via migration scripts)
• Per-tenant backup requires WHERE tenant_id = X exports instead of pg_dump -n schema

Reference: See Chapter 04, Section L.10A.4 for the full multi-tenancy decision analysis, comparison matrix, and C# middleware implementation details.

6.4 Connection Pooling Strategy

PgBouncer Configuration

; /etc/pgbouncer/pgbouncer.ini

[databases]
; Route all connections through pooler
pos_platform = host=postgres16 port=5432 dbname=pos_platform

[pgbouncer]
; Pool mode: transaction (best for multi-tenant with RLS)
pool_mode = transaction

; Pooler ports
listen_addr = 0.0.0.0
listen_port = 6432

; Authentication
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Pool sizing
; With RLS, all tenants share the same pool (no per-schema pools needed)
default_pool_size = 20
max_client_conn = 1000
min_pool_size = 5

; Reserve connections for admin
reserve_pool_size = 5
reserve_pool_timeout = 3

; Connection limits
max_db_connections = 100
max_user_connections = 100

; Timeouts
server_connect_timeout = 5
server_idle_timeout = 60
server_lifetime = 3600
query_timeout = 30

; Logging
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1
stats_period = 60

Pool Mode Comparison

Recommendation: Use transaction mode for the POS API to maximize connection reuse across tenants. With RLS, the tenant context is set via SET app.current_tenant at the start of each transaction, so connection reuse is safe.

Docker Compose Integration

# docker-compose.yml
services:
  pgbouncer:
    image: bitnami/pgbouncer:latest
    container_name: pos-pgbouncer
    environment:
      - PGBOUNCER_DATABASE=pos_platform
      - PGBOUNCER_PORT=6432
      - PGBOUNCER_POOL_MODE=transaction
      - PGBOUNCER_MAX_CLIENT_CONN=1000
      - PGBOUNCER_DEFAULT_POOL_SIZE=20
      - POSTGRESQL_HOST=postgres16
      - POSTGRESQL_PORT=5432
      - POSTGRESQL_USERNAME=pos_app
      - POSTGRESQL_PASSWORD=${DB_PASSWORD}
    ports:
      - "6432:6432"
    networks:
      - postgres_default
    depends_on:
      - postgres16
    healthcheck:
      test: ["CMD", "pg_isready", "-h", "localhost", "-p", "6432"]
      interval: 10s
      timeout: 5s
      retries: 5

networks:
  postgres_default:
    external: true

6.5 Backup and Restore

Backup Strategy Overview

With RLS architecture, all tenant data resides in the same database and schema. Full database backups capture everything. Tenant-specific exports use WHERE tenant_id = X to extract individual tenant data.

Full Database Backup

#!/bin/bash
# /volume1/docker/scripts/backup-pos-full.sh

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/volume1/backup/pos_platform"
CONTAINER="postgres16"
DB_NAME="pos_platform"

# Create backup directory
mkdir -p "$BACKUP_DIR/full"

# Full database dump with compression
docker exec $CONTAINER pg_dump \
    -U postgres \
    -d $DB_NAME \
    -Fc \
    -Z 9 \
    -f /tmp/pos_platform_${DATE}.dump

# Copy to backup location
docker cp $CONTAINER:/tmp/pos_platform_${DATE}.dump \
    "$BACKUP_DIR/full/"

# Cleanup container temp file
docker exec $CONTAINER rm /tmp/pos_platform_${DATE}.dump

# Remove backups older than 30 days
find "$BACKUP_DIR/full" -name "*.dump" -mtime +30 -delete

echo "Full backup completed: pos_platform_${DATE}.dump"

Tenant-Specific Data Export

With RLS, per-tenant backup is a data export using SQL queries filtered by tenant_id:

#!/bin/bash
# /volume1/docker/scripts/export-tenant.sh
# Usage: ./export-tenant.sh <tenant-uuid>

TENANT_ID=$1
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/volume1/backup/pos_platform/tenants"
CONTAINER="postgres16"
DB_NAME="pos_platform"

if [ -z "$TENANT_ID" ]; then
    echo "Usage: $0 <tenant-uuid>"
    exit 1
fi

# Create backup directory
mkdir -p "$BACKUP_DIR/$TENANT_ID"

# Export tenant data using COPY with WHERE clause
# This exports each tenant-scoped table filtered by tenant_id
docker exec $CONTAINER psql -U postgres -d $DB_NAME -c "
    -- Export products for this tenant
    COPY (SELECT * FROM products WHERE tenant_id = '$TENANT_ID')
        TO '/tmp/tenant_products.csv' WITH CSV HEADER;
    -- Export customers for this tenant
    COPY (SELECT * FROM customers WHERE tenant_id = '$TENANT_ID')
        TO '/tmp/tenant_customers.csv' WITH CSV HEADER;
    -- Export orders for this tenant
    COPY (SELECT * FROM orders WHERE tenant_id = '$TENANT_ID')
        TO '/tmp/tenant_orders.csv' WITH CSV HEADER;
    -- ... repeat for all tenant-scoped tables
"

# Package into tar archive
docker exec $CONTAINER tar czf /tmp/tenant_${TENANT_ID}_${DATE}.tar.gz \
    /tmp/tenant_*.csv

# Copy to backup location
docker cp $CONTAINER:/tmp/tenant_${TENANT_ID}_${DATE}.tar.gz \
    "$BACKUP_DIR/$TENANT_ID/"

# Cleanup
docker exec $CONTAINER rm /tmp/tenant_*.csv /tmp/tenant_${TENANT_ID}_${DATE}.tar.gz

echo "Tenant export completed: tenant_${TENANT_ID}_${DATE}.tar.gz"

Tenant Data Restore

-- Restore tenant data from export
-- Step 1: Delete existing tenant data (if re-importing)
BEGIN;

DELETE FROM order_items WHERE order_id IN (
    SELECT id FROM orders WHERE tenant_id = 'target-tenant-uuid'
);
DELETE FROM orders WHERE tenant_id = 'target-tenant-uuid';
DELETE FROM inventory_levels WHERE tenant_id = 'target-tenant-uuid';
DELETE FROM variants WHERE product_id IN (
    SELECT id FROM products WHERE tenant_id = 'target-tenant-uuid'
);
DELETE FROM products WHERE tenant_id = 'target-tenant-uuid';
DELETE FROM customers WHERE tenant_id = 'target-tenant-uuid';
-- ... repeat for all tenant-scoped tables in dependency order

-- Step 2: Import from CSV
COPY products FROM '/tmp/tenant_products.csv' WITH CSV HEADER;
COPY customers FROM '/tmp/tenant_customers.csv' WITH CSV HEADER;
COPY orders FROM '/tmp/tenant_orders.csv' WITH CSV HEADER;
-- ... repeat for all tables

COMMIT;

Tenant Migration (Between Databases)

-- Export tenant to SQL for migration to a different server
-- Uses pg_dump with row-level filter via a view

-- Step 1: Create temporary view filtered by tenant
CREATE TEMP VIEW export_products AS
    SELECT * FROM products WHERE tenant_id = 'source-tenant-uuid';

-- Step 2: Use COPY to export
COPY (SELECT * FROM export_products) TO '/tmp/migration_products.csv' WITH CSV HEADER;

-- Step 3: On target server, COPY FROM with updated tenant_id if needed
-- Step 4: Update shared.tenants registry on target

6.6 Performance Considerations

RLS Performance

RLS policy overhead is minimal. PostgreSQL evaluates the USING clause as part of the query plan, effectively adding a WHERE tenant_id = X filter. With proper indexing, this has negligible impact:

-- Composite indexes with tenant_id as leading column
-- These are critical for RLS performance
CREATE INDEX idx_products_tenant ON products(tenant_id);
CREATE INDEX idx_products_tenant_sku ON products(tenant_id, sku);
CREATE INDEX idx_orders_tenant_created ON orders(tenant_id, created_at);
CREATE INDEX idx_inventory_tenant_location ON inventory_levels(tenant_id, location_id);
CREATE INDEX idx_customers_tenant_email ON customers(tenant_id, email);

-- The query planner uses these indexes to efficiently filter by tenant
-- before applying any additional WHERE clauses

RLS Performance Benchmarks (expected): | Scenario | Without RLS | With RLS | Overhead | |–––––|———––|–––––|–––––| | Simple SELECT | 0.5ms | 0.6ms | ~20% | | JOIN 3 tables | 2.1ms | 2.3ms | ~10% | | Aggregate query | 5.0ms | 5.2ms | ~4% |

The overhead decreases proportionally as query complexity increases, since the tenant_id filter is a simple equality check on an indexed column.

Table Partitioning Strategy

For high-volume time-series tables, use declarative partitioning:

-- Partition inventory_transactions by month
CREATE TABLE inventory_transactions (
    id BIGSERIAL,
    tenant_id UUID NOT NULL,
    variant_id INT NOT NULL,
    location_id INT NOT NULL,
    transaction_type VARCHAR(20) NOT NULL,
    quantity_change INT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    -- ... other columns
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE inventory_transactions_2025_01
    PARTITION OF inventory_transactions
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE inventory_transactions_2025_02
    PARTITION OF inventory_transactions
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- RLS policies apply to the parent table and are inherited by partitions
ALTER TABLE inventory_transactions ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory_transactions FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON inventory_transactions
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- Automate partition creation
CREATE OR REPLACE FUNCTION create_monthly_partitions()
RETURNS VOID AS $$
DECLARE
    next_month DATE;
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    -- Create partitions for next 3 months
    FOR i IN 0..2 LOOP
        next_month := date_trunc('month', CURRENT_DATE + (i || ' months')::interval);
        start_date := next_month;
        end_date := next_month + '1 month'::interval;
        partition_name := 'inventory_transactions_' || to_char(next_month, 'YYYY_MM');

        -- Check if partition exists
        IF NOT EXISTS (
            SELECT 1 FROM pg_class c
            JOIN pg_namespace n ON c.relnamespace = n.oid
            WHERE n.nspname = 'public'
            AND c.relname = partition_name
        ) THEN
            EXECUTE format(
                'CREATE TABLE %I PARTITION OF inventory_transactions
                 FOR VALUES FROM (%L) TO (%L)',
                partition_name, start_date, end_date
            );
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Connection Pooling Metrics

Monitor these key metrics in PgBouncer:

-- Connect to PgBouncer admin console
-- psql -h localhost -p 6432 -U postgres pgbouncer

-- Show pool status
SHOW POOLS;

-- Key metrics to monitor:
-- cl_active: Active client connections
-- cl_waiting: Clients waiting for connection
-- sv_active: Active server connections
-- sv_idle: Idle server connections
-- sv_used: Server connections in use

-- Show statistics
SHOW STATS;

-- Alert thresholds:
-- cl_waiting > 10: Pool exhaustion risk
-- sv_active / default_pool_size > 0.8: Near capacity

Query Timeout Configuration

-- Set statement timeout to prevent long-running queries
SET statement_timeout = '30s';

-- For specific operations, extend timeout
SET LOCAL statement_timeout = '5m';

-- Connection-level setting in PgBouncer
; query_timeout = 30

-- PostgreSQL server-level (postgresql.conf)
statement_timeout = 30000  -- 30 seconds
lock_timeout = 10000       -- 10 seconds
idle_in_transaction_session_timeout = 60000  -- 1 minute

Memory Configuration

# postgresql.conf optimizations for multi-tenant RLS

# Shared memory (25% of RAM)
shared_buffers = 4GB

# Work memory per query (be conservative with many concurrent tenants)
work_mem = 64MB

# Maintenance operations
maintenance_work_mem = 512MB

# Effective cache size (75% of RAM)
effective_cache_size = 12GB

# Connection limits
max_connections = 200  # Higher limit, pooler handles distribution

# WAL settings
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 512MB

# Query planning
random_page_cost = 1.1  # SSD storage
effective_io_concurrency = 200  # SSD storage

6.7 High Availability Considerations

Replication Setup (Future)

# docker-compose-ha.yml (for future HA deployment)
services:
  postgres-primary:
    image: postgres:16-alpine
    environment:
      - POSTGRES_REPLICATION_MODE=master
      - POSTGRES_REPLICATION_USER=replicator
      - POSTGRES_REPLICATION_PASSWORD=${REPL_PASSWORD}
    volumes:
      - postgres-primary-data:/var/lib/postgresql/data

  postgres-replica:
    image: postgres:16-alpine
    environment:
      - POSTGRES_REPLICATION_MODE=slave
      - POSTGRES_MASTER_HOST=postgres-primary
      - POSTGRES_REPLICATION_USER=replicator
      - POSTGRES_REPLICATION_PASSWORD=${REPL_PASSWORD}
    volumes:
      - postgres-replica-data:/var/lib/postgresql/data
    depends_on:
      - postgres-primary

Read Replica Routing

# PgBouncer configuration for read replicas
[databases]
pos_platform = host=postgres-primary port=5432 dbname=pos_platform
pos_platform_ro = host=postgres-replica port=5432 dbname=pos_platform

Application routing:

// Database connection factory
public class DatabaseConnectionFactory
{
    private readonly string _primaryConnectionString;
    private readonly string _readonlyConnectionString;

    public DatabaseConnectionFactory(IConfiguration config)
    {
        _primaryConnectionString = config.GetConnectionString("Primary")!;
        _readonlyConnectionString = config.GetConnectionString("Readonly")!;
    }

    // Route based on operation type
    // RLS works identically on both primary and replica —
    // SET app.current_tenant must be called on each connection
    public string GetConnectionString(bool readOnly = false)
    {
        return readOnly ? _readonlyConnectionString : _primaryConnectionString;
    }
}

6.8 Monitoring and Alerting

Key Database Metrics

-- Database size per tenant (RLS approach)
SELECT
    tenant_id,
    t.name AS tenant_name,
    pg_size_pretty(SUM(pg_column_size(p.*))) AS products_size
FROM products p
JOIN shared.tenants t ON t.id = p.tenant_id
GROUP BY tenant_id, t.name
ORDER BY SUM(pg_column_size(p.*)) DESC;

-- Approximate tenant data size across all tables
SELECT
    t.name AS tenant_name,
    COUNT(DISTINCT p.id) AS product_count,
    COUNT(DISTINCT o.id) AS order_count,
    COUNT(DISTINCT c.id) AS customer_count
FROM shared.tenants t
LEFT JOIN products p ON p.tenant_id = t.id
LEFT JOIN orders o ON o.tenant_id = t.id
LEFT JOIN customers c ON c.tenant_id = t.id
GROUP BY t.name
ORDER BY COUNT(DISTINCT o.id) DESC;

-- Active connections (all tenants share the same pool)
SELECT
    COUNT(*) AS total_connections,
    COUNT(*) FILTER (WHERE state = 'active') AS active,
    COUNT(*) FILTER (WHERE state = 'idle') AS idle,
    COUNT(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity
WHERE datname = 'pos_platform';

-- Table bloat check
SELECT
    schemaname || '.' || relname AS table_name,
    pg_size_pretty(pg_relation_size(schemaname || '.' || relname)) AS size,
    n_dead_tup AS dead_tuples,
    last_autovacuum
FROM pg_stat_user_tables
WHERE schemaname = 'public'
  AND n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Slow queries (requires pg_stat_statements)
SELECT
    query,
    calls,
    mean_exec_time::numeric(10,2) AS avg_ms,
    total_exec_time::numeric(10,2) AS total_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Alerting Thresholds

6.9 Security Configuration

Role-Based Access

-- Create application role (minimal privileges)
CREATE ROLE pos_app WITH LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE pos_platform TO pos_app;
GRANT USAGE ON SCHEMA shared TO pos_app;
GRANT USAGE ON SCHEMA public TO pos_app;

-- Grant table access in public schema (RLS enforces tenant isolation)
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO pos_app;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO pos_app;

-- Default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO pos_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT ALL ON SEQUENCES TO pos_app;

-- RLS enforces that pos_app can only see rows for the current tenant
-- This is defense-in-depth: even with full table access, RLS filters rows

-- Create admin role (elevated privileges, bypasses RLS)
CREATE ROLE pos_admin WITH LOGIN PASSWORD 'admin_password' BYPASSRLS;
GRANT ALL PRIVILEGES ON DATABASE pos_platform TO pos_admin;

-- Create read-only role (for reporting, respects RLS)
CREATE ROLE pos_readonly WITH LOGIN PASSWORD 'readonly_password';
GRANT CONNECT ON DATABASE pos_platform TO pos_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA shared TO pos_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO pos_readonly;
-- RLS policies still apply — readonly user must SET app.current_tenant

RLS Security Notes

-- FORCE ROW LEVEL SECURITY ensures RLS applies even to table owners
-- Without FORCE, the table owner bypasses RLS
ALTER TABLE products FORCE ROW LEVEL SECURITY;

-- The pos_admin role has BYPASSRLS for administrative operations
-- (tenant migration, cross-tenant reporting, data cleanup)
-- This role should ONLY be used for administrative tasks, never by the API

-- Verify RLS is enabled on all tenant-scoped tables
SELECT
    schemaname,
    tablename,
    rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;

SSL/TLS Configuration

# postgresql.conf
ssl = on
ssl_cert_file = '/var/lib/postgresql/server.crt'
ssl_key_file = '/var/lib/postgresql/server.key'
ssl_ca_file = '/var/lib/postgresql/root.crt'
ssl_min_protocol_version = 'TLSv1.2'

# Require SSL for external connections
# pg_hba.conf
hostssl pos_platform pos_app 0.0.0.0/0 scram-sha-256

6.10 Quick Reference

Connection Strings

# Direct PostgreSQL connection
postgres://pos_app:password@192.168.1.26:5433/pos_platform

# Through PgBouncer (recommended)
postgres://pos_app:password@192.168.1.26:6432/pos_platform

# Application environment variables
DATABASE_URL=postgres://pos_app:password@pgbouncer:6432/pos_platform
DATABASE_URL_READONLY=postgres://pos_readonly:password@pgbouncer:6432/pos_platform_ro

Common Operations

# Connect to database
docker exec -it postgres16 psql -U postgres -d pos_platform

# List all tenants
docker exec postgres16 psql -U postgres -d pos_platform -c \
    "SELECT id, name, slug, status FROM shared.tenants;"

# Check row counts per tenant for a table
docker exec postgres16 psql -U postgres -d pos_platform -c \
    "SELECT tenant_id, COUNT(*) FROM products GROUP BY tenant_id;"

# Verify RLS is enabled
docker exec postgres16 psql -U postgres -d pos_platform -c \
    "SELECT tablename, rowsecurity FROM pg_tables WHERE schemaname = 'public';"

# Export specific tenant data
./export-tenant.sh <tenant-uuid>

# Vacuum full (maintenance window only)
docker exec postgres16 psql -U postgres -d pos_platform -c "VACUUM FULL ANALYZE products;"

RLS Quick Setup for New Tables

-- Template: Enable RLS on a new tenant-scoped table
ALTER TABLE <table_name> ENABLE ROW LEVEL SECURITY;
ALTER TABLE <table_name> FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON <table_name>
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

CREATE POLICY tenant_insert ON <table_name>
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

CREATE INDEX idx_<table_name>_tenant ON <table_name>(tenant_id);

Next Chapter: Chapter 07: Schema Design - Detailed schema structure with 51 tables across 13 domains.

Document Information

This chapter is part of the POS Blueprint Book. All content is self-contained.