5-Minute PostgreSQL Compliance: From Zero to Audit-Ready with quick_setup()

Install and Setup

Step 1: Install pgcomply (30 seconds)

\i pgcomply.sql

This creates the pgcomply schema with all functions and tables.

Step 2: Run quick_setup() (60 seconds)

SELECT pgcomply.quick_setup();

Output:

NOTICE: pgcomply.quick_setup: Scanning 14 tables across 1 schema...
NOTICE: pgcomply.quick_setup: Detected 24 PII columns in 7 tables
NOTICE: pgcomply.quick_setup: Registered 24 PII columns in registry
NOTICE: pgcomply.quick_setup: Classified 14 tables (2 restricted, 3 confidential, 5 internal, 4 public)
NOTICE: pgcomply.quick_setup: Created immutable audit trail
NOTICE: pgcomply.quick_setup: Running initial health check...
NOTICE: pgcomply.health_check: 12 PASS, 3 WARN, 1 FAIL
NOTICE: pgcomply.quick_setup: Baseline established. Run SELECT * FROM pgcomply.health_check() for details.

Step 3: Review Results (3 minutes)

-- What PII was found?
SELECT table_name, column_name, pii_type, confidence
FROM pgcomply.pii_registry ORDER BY table_name;

-- How are tables classified?
SELECT * FROM pgcomply.classification_map();

-- What security issues exist?
SELECT rule, status, detail FROM pgcomply.health_check()
WHERE status IN ('FAIL', 'WARN');

-- Any PII that was missed?
SELECT * FROM pgcomply.schema_drift();

What quick_setup() Does

1. Scans all tables in public schema (and other schemas if configured)
2. Pattern matches column names against 12+ PII types
3. Registers PII in the pgcomply.pii_registry with confidence levels
4. Auto-classifies tables based on PII sensitivity (ISO 27001 levels)
5. Creates audit trail with SHA-256 hash chain
6. Runs health_check() for 14 CIS-based security checks
7. Logs everything in the immutable audit trail

What to Do Next

Based on health_check() results:

| Finding | Priority | Action | |---------|----------|--------| | SSL FAIL | Critical | Enable ssl = on in postgresql.conf | | Superuser WARN | High | Remove extra superusers | | RLS missing on PII | Medium | pgcomply.enable_rls() per table | | No masking rules | Medium | pgcomply.mask() for PII columns | | No retention policy | Low | pgcomply.retain() per table |

What Happens After quick_setup()

quick_setup() establishes the baseline. The next steps depend on your situation:

If health_check() shows FAIL items

Fix critical issues first:

-- Most common: SSL not enabled
ALTER SYSTEM SET ssl = on;
SELECT pg_reload_conf();

-- Second most common: MD5 instead of SCRAM
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();

If schema_drift() finds unregistered PII

Register the detected columns:

-- For each drift finding, register the PII
SELECT pgcomply.register_pii('orders', 'customer_phone', 'phone', 'user_id');
SELECT pgcomply.register_pii('contacts', 'email_address', 'email', 'contact_id');

-- Re-run to verify
SELECT * FROM pgcomply.schema_drift();
-- Should now return empty (no drift)

Setting up automation

Schedule ongoing compliance checks:

-- Install pg_cron jobs for automated monitoring
SELECT pgcomply.schedule_jobs(install := true);

-- This creates:
-- Daily: enforce_retention() at 03:00
-- Weekly: health_check() Monday 06:00
-- Weekly: schema_drift() Monday 06:15
-- Every 15 min: log_sessions()

Integrating with Your Application

After the baseline is established, wire the key functions into your application:

// User deletion endpoint
app.delete('/api/users/:id', async (req, res) => {
  await db.query('SELECT pgcomply.forget($1)', [req.params.id]);
  const verify = await db.query('SELECT * FROM pgcomply.verify_forget($1)', [req.params.id]);
  res.json({ deleted: true, verified: verify.rows.length === 0 });
});

// Data export endpoint (GDPR Article 15/20)
app.get('/api/users/:id/export', async (req, res) => {
  const data = await db.query("SELECT pgcomply.export_user_data($1, 'json')", [req.params.id]);
  res.json(data.rows[0]);
});

Summary

Five minutes. One command. You now know where your PII lives, how your security is configured, and what needs fixing. This is the starting point for every pgcomply implementation. From here, add masking, consent tracking, and automated monitoring as your compliance needs grow.