Documentation

pgcomply is a pure PL/pgSQL compliance toolkit for PostgreSQL. Install it as a single SQL file — no agents, no sidecars, no external dependencies.

Requirements

✓PostgreSQL 14+ — works on 14, 15, 16, 17
✓pgcrypto — required for SHA-256 audit chain (pre-installed on most platforms)
~Superuser — needed for DDL event triggers; gracefully skipped without it

ℹpgcrypto ships with PostgreSQL by default on most managed platforms (Supabase, Neon, RDS, etc.). If you hit a missing extension error, run CREATE EXTENSION IF NOT EXISTS pgcrypto; first.

Installation

Download pgcomply.sql from GitHub and load it into your database. That's the entire installation.

Option A — psql (recommended)

bash

# Download
curl -O https://raw.githubusercontent.com/pgcomply/pgcomply/main/pgcomply.sql

# Install into your database
psql -h localhost -U postgres -d mydb -f pgcomply.sql

Option B — inside a psql session

psql

\i /path/to/pgcomply.sql

Option C — pipe from curl

bash

curl -s https://raw.githubusercontent.com/pgcomply/pgcomply/main/pgcomply.sql \
  | psql -h localhost -U postgres -d mydb

✓Installation takes about 2 seconds. pgcomply creates a pgcomply schema with all functions and tables. Your existing data and schema are never modified.

Getting Started

Run quick_setup() once after installation. It scans all your tables, classifies PII automatically, and enables auditing.

psql — complete first-run sequence

-- Step 1: Install
\i pgcomply.sql

-- Step 2: Auto-setup — scans tables, classifies PII, enables auditing
SELECT pgcomply.quick_setup();

-- Step 3: See what was found
SELECT * FROM pgcomply.pii_registry LIMIT 20;

-- Step 4: Run security health check
SELECT * FROM pgcomply.health_check();

-- Step 5: Check your compliance score
SELECT pgcomply.compliance_score();

What quick_setup() does

→Scans all tables for columns matching 12+ PII patterns (email, phone, SSN, IBAN, …)
→Registers found columns in the PII registry
→Classifies data sensitivity levels (public / internal / confidential / restricted)
→Enables DML auditing on registered tables
→Runs a first health check and stores the baseline

Common Scenarios

1 — GDPR: Delete a user's data (Right to Erasure)

Article 17 requires deleting personal data on request. forget() removes PII from every registered table and creates an immutable audit record.

psql

-- Delete all PII for a user across every registered table
SELECT pgcomply.forget('user-4821');

-- Verify nothing was missed (checks all registered columns)
SELECT * FROM pgcomply.verify_forget('user-4821');

-- Inspect the audit record — proof for the DPA if requested
SELECT * FROM pgcomply.audit_log
WHERE operation = 'forget' AND subject_id = 'user-4821'
ORDER BY created_at DESC;

✓verify_forget() returns a row for every registered column that still contains the subject ID. An empty result confirms the erasure is complete.

2 — GDPR: Subject Access Request (Right of Access)

Article 15 gives users the right to know what data you hold. inspect() collects all PII across registered tables and returns it as JSON.

psql

-- Return all data held for a subject (as JSON)
SELECT pgcomply.inspect('user-4821');

-- Export to a portable JSON file
COPY (SELECT pgcomply.inspect('user-4821')) TO '/tmp/user-4821-sar.json';

3 — GDPR: Consent management

Article 6/7 requires a documented legal basis for every processing purpose. Define purposes once, then record consent events per user.

psql

-- Register a processing purpose (once per purpose)
SELECT pgcomply.register_purpose(
  'newsletter',        -- purpose_id
  'Email marketing',   -- label
  'consent',           -- legal_basis: consent / legitimate_interest / contract / legal_obligation
  '2 years'            -- retention period
);

-- Record consent when a user opts in
SELECT pgcomply.grant_consent('user-4821', 'newsletter');

-- Record withdrawal when a user opts out
SELECT pgcomply.withdraw_consent('user-4821', 'newsletter');

-- Check consent history for a user
SELECT * FROM pgcomply.consent_log
WHERE subject_id = 'user-4821'
ORDER BY created_at DESC;

4 — Security: CIS Benchmark health check

16 automated checks covering SSL enforcement, encryption settings, superuser count, row-level security, audit chain integrity, and more.

psql

-- Run all 16 CIS Benchmark checks
SELECT * FROM pgcomply.health_check();

-- Show only failing checks
SELECT check_name, status, detail
FROM pgcomply.health_check()
WHERE status = 'fail';

-- Persist a snapshot for trending over time
SELECT pgcomply.save_health_snapshot();

5 — Security: Mask PII for non-privileged roles

Dynamic masking creates automatic views so analysts see al***@e***.com instead of real addresses — no application changes needed.

psql

-- Partial masking: al***@e***.com
SELECT pgcomply.mask('users', 'email', 'partial');

-- Full masking: replaces with a fixed placeholder
SELECT pgcomply.mask('users', 'phone', 'full');

-- Hash masking: deterministic (same input → same hash)
SELECT pgcomply.mask('users', 'national_id', 'hash');

-- Remove masking from a column
SELECT pgcomply.unmask('users', 'email');

-- List all active masking rules
SELECT * FROM pgcomply.masking_rules;

6 — Monitoring: Schema drift detection

Catches new columns that may contain PII before an auditor does. Run regularly or add to a cron job.

psql

-- Scan for new columns not yet in the PII registry
SELECT * FROM pgcomply.schema_drift();

-- Manually register a newly discovered PII column
SELECT pgcomply.register_pii(
  'orders',           -- table_name
  'customer_email',   -- column_name
  'email',            -- pii_type
  'customer_id'       -- subject_key_column
);

7 — GDPR: Report a data breach (Article 33)

Article 33 requires notifying the supervisory authority within 72 hours of discovering a breach. pgcomply starts the countdown automatically.

psql

-- Report a breach — starts the 72h DPA notification countdown
SELECT pgcomply.report_breach(
  'Unauthorized DB access',
  'Production replica exposed via misconfigured firewall',
  'high',    -- severity: low / medium / high / critical
  500        -- estimated affected subjects
);

-- Monitor countdown and status
SELECT id, title, severity, created_at,
  ROUND(EXTRACT(EPOCH FROM (created_at + INTERVAL '72 hours' - now())) / 3600, 1)
    AS hours_remaining
FROM pgcomply.breaches
ORDER BY created_at DESC;

-- Update investigation status
UPDATE pgcomply.breaches
SET status = 'investigating',
    remediation_notes = 'Firewall rule corrected at 03:45 UTC'
WHERE id = 1;

Platform-specific Setup

Supabase

ℹSupabase supports pgcrypto by default. Use the SQL Editor in the Supabase dashboard or connect via psql.

Supabase SQL Editor

-- Enable pgcrypto if not already active
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Paste the full contents of pgcomply.sql and run
-- Then initialise:
SELECT pgcomply.quick_setup();

bash — psql with Supabase connection string

# Connection string: Supabase Dashboard → Settings → Database
psql "postgresql://postgres.[ref]:[password]@aws-0-eu-central-1.pooler.supabase.com:6543/postgres" \
  -f pgcomply.sql

Neon

bash

# Connection string: Neon Console → Connection Details
psql "postgresql://[user]:[password]@[endpoint].neon.tech/[dbname]?sslmode=require" \
  -f pgcomply.sql

ℹNeon branches are separate PostgreSQL instances — install pgcomply on each branch you want to monitor independently.

AWS RDS / Aurora PostgreSQL

bash

# Enable pgcrypto (requires rds_superuser)
psql -h mydb.cluster-xxxx.eu-west-1.rds.amazonaws.com -U postgres -d mydb \
  -c "CREATE EXTENSION IF NOT EXISTS pgcrypto;"

# Install pgcomply
psql -h mydb.cluster-xxxx.eu-west-1.rds.amazonaws.com -U postgres -d mydb \
  -f pgcomply.sql

ℹDDL event triggers require the rds_superuser role. Without it, schema drift detection still works via column-level scanning — only event-trigger-based DDL capture is skipped.

Azure Database for PostgreSQL

bash

psql "host=myserver.postgres.database.azure.com port=5432 dbname=mydb \
  user=myadmin sslmode=require" \
  -f pgcomply.sql

Google Cloud SQL

bash

# Via Cloud SQL Auth Proxy (recommended)
./cloud-sql-proxy myproject:europe-west1:myinstance &
psql -h 127.0.0.1 -U postgres -d mydb -f pgcomply.sql

Docker / self-hosted

bash

# Pipe directly into the container
docker exec -i my-postgres psql -U postgres -d mydb < pgcomply.sql

# Or copy the file first
docker cp pgcomply.sql my-postgres:/pgcomply.sql
docker exec my-postgres psql -U postgres -d mydb -f /pgcomply.sql

Hetzner / OVH / DigitalOcean (managed PostgreSQL)

bash

# These platforms provide a standard connection string
# Download it from your provider's dashboard, then:
psql "[your-connection-string]" -f pgcomply.sql

# Example for DigitalOcean:
psql "postgresql://doadmin:[password]@mydb.db.ondigitalocean.com:25060/defaultdb?sslmode=require" \
  -f pgcomply.sql

Function Reference

Core functions grouped by category. List everything installed on your instance:

psql

SELECT routine_name
FROM information_schema.routines
WHERE routine_schema = 'pgcomply'
ORDER BY routine_name;

Function	Description	Category
pgcomply.quick_setup()	Scan all tables, classify PII, enable auditing	Setup
pgcomply.register_pii('table','col','type','key')	Manually register a PII column	Setup
pgcomply.forget('subject-id')	Delete all PII for a subject across registered tables	GDPR
pgcomply.verify_forget('subject-id')	Confirm no PII remains for a given subject	GDPR
pgcomply.inspect('subject-id')	Return all held data for a subject as JSON	GDPR
pgcomply.register_purpose('id','label','basis','retention')	Define a data processing purpose	Consent
pgcomply.grant_consent('subject','purpose')	Record a user consent grant	Consent
pgcomply.withdraw_consent('subject','purpose')	Record a consent withdrawal	Consent
pgcomply.health_check()	16 CIS Benchmark security checks	Security
pgcomply.save_health_snapshot()	Persist health check results for trending	Security
pgcomply.mask('table','col','mode')	Apply PII masking — partial / full / hash	Masking
pgcomply.unmask('table','col')	Remove masking from a column	Masking
pgcomply.schema_drift()	Detect columns not yet in the PII registry	Monitoring
pgcomply.report_breach('title','desc','severity',n)	Log a breach and start 72h countdown	GDPR
pgcomply.compliance_score()	Return a 0–100 compliance score	Reporting
pgcomply.compliance_report()	Generate a full compliance snapshot✦ Plus	Reporting

Full API reference

Detailed parameter docs, return types, and examples for all 67 Community functions are available on GitHub.

View on GitHub →