Skip to content

Phase 07: Security, Roles & Compliance Auditing

Chapter 29: Role & Privilege Architecture

29.1 The Unified Role Concept

In PostgreSQL, "Users" and "Groups" are the exact same database object: a ROLE. * A ROLE WITH LOGIN is what other databases call a "User". * A ROLE WITHOUT LOGIN is what other databases call a "Group".

-- Production Best Practice: Create Group Roles, then assign to Login Roles
CREATE ROLE app_read_only NOINHERIT;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read_only;

CREATE ROLE app_read_write;
GRANT app_read_only TO app_read_write;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_read_write;

-- Create actual human or service login accounts:
CREATE ROLE svc_web_backend WITH LOGIN PASSWORD 'strong_password';
GRANT app_read_write TO svc_web_backend;

29.2 Default Privileges (Preventing Permission Drifts)

  • The Annoying Gotcha: When a migration creates a new table in a schema, existing roles cannot read it, even if you previously ran GRANT SELECT ON ALL TABLES.
  • The Permanent Fix: 
    ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO app_read_only;

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT INSERT, UPDATE, DELETE ON TABLES TO app_read_write;

29.3 pg_hba.conf (Host-Based Authentication)

Controls who is permitted to connect to what database from where: 

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
hostssl all             all             10.0.0.0/16             scram-sha-256
host    all             all             0.0.0.0/0               reject
* Always use scram-sha-256: Obsoletes md5 (vulnerable to rainbow tables). Prevents password sniffing over the wire even if TLS is compromised. * Require hostssl: Reject unencrypted plaintext TCP connections.

Chapter 30: Row-Level Security (RLS) & Multi-Tenancy

30.1 Building Multi-Tenant SaaS with RLS

Row-Level Security allows you to enforce multi-tenancy at the database engine level, ensuring tenant \(A\) can never view or modify tenant \(B\)'s data—even if an application bug omits WHERE tenant_id = ....

-- 1. Enable RLS on the table
ALTER TABLE customer_invoices ENABLE ROW LEVEL SECURITY;

-- 2. Force RLS even for the table owner (Critical!)
ALTER TABLE customer_invoices FORCE ROW LEVEL SECURITY;

-- 3. Define the tenant isolation policy
CREATE POLICY tenant_isolation_policy ON customer_invoices
    AS PERMISSIVE
    FOR ALL
    TO app_user
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid)
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

Application Session Context Setup:

Before running queries on a pooled connection, the application sets the session variable: 

SET LOCAL app.current_tenant_id = 'a54f89d2-3b2e-4a6f-9e2c-123456789abc';
-- All subsequent queries are automatically filtered:
SELECT * FROM customer_invoices; 
-- PostgreSQL transparently appends: WHERE tenant_id = 'a54f89d2...'

30.2 RLS Performance Traps & Optimization

  • Index Requirement: Every table with an RLS policy must have a high-performance index on the policy key (tenant_id), or every query degrades into a sequential scan.
  • Subquery Traps: If an RLS policy includes an expensive subquery (e.g., checking user role memberships in another table), Postgres must execute that subquery for every evaluated tuple.
  • Fix: Mark policy helper functions as STABLE or LEAKPROOF so the query planner can optimize and cache evaluation across the scan.

Chapter 31: Compliance Auditing with pgaudit

31.1 Why Standard Postgres Logging is Insufficient

Standard logging (log_statement = 'all') logs raw SQL text strings. It produces gigabytes of unformatted text, captures passwords in plain text, and lacks structured compliance categorization required by SOC2, HIPAA, and PCI-DSS.

31.2 pgaudit Implementation

The pgaudit extension provides structured, tamper-evident audit logs directly to syslog: 

-- postgresql.conf settings:
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'read, write, ddl, role'
pgaudit.log_relation = on
pgaudit.log_catalog = off
pgaudit.log_level = 'log'
* Generates audit records showing: * Timestamp, User, Database, Process ID. * Statement Class (READ, WRITE, DDL). * Target relation names. * Exact parameters and executed statements.