Compliance & Regulatory Guide

CREATE ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;
GRANT analyst TO alice;          -- alice inherits analyst privileges

-- Column-level privilege
GRANT SELECT (id, name) ON customers TO reporting_role;
REVOKE SELECT (ssn) ON customers FROM reporting_role;

-- Example: restrict access to business hours only
CREATE POLICY business_hours ON orders
  USING (EXTRACT(HOUR FROM now()) BETWEEN 8 AND 18);

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::int);

-- Each tenant's queries automatically filter to their own rows

ALTER TABLE customers ALTER COLUMN email SET (mask = 'email');
-- Privileged role sees:  alice@example.com
-- Unprivileged role sees: a***@e***.com

-- Erase all data for a data subject across all tables
CALL absdb_gdpr_erase('users', 'email', 'alice@example.com');
-- Returns: { "tables_affected": 7, "rows_erased": 142, "audit_ref": "GDR-2026-04-001" }

-- Generate a complete SAR report in JSON
SELECT * FROM absdb_gdpr_report('alice@example.com');
-- Returns all data held across all tables for the subject,
-- formatted as a portable JSON document (Article 15 / Article 20)

-- Record consent
INSERT INTO _absdb_consent_ledger (subject_id, purpose, granted, ts)
VALUES ('alice@example.com', 'marketing_emails', true, NOW());

-- Withdraw consent
UPDATE _absdb_consent_ledger
SET granted = false, withdrawn_at = NOW()
WHERE subject_id = 'alice@example.com' AND purpose = 'marketing_emails';

-- Tag PHI columns
ALTER TABLE patients ALTER COLUMN ssn      SET (hipaa_phi = true);
ALTER TABLE patients ALTER COLUMN dob      SET (hipaa_phi = true);
ALTER TABLE patients ALTER COLUMN diagnosis SET (hipaa_phi = true);

-- PHI columns are automatically:
--   - Encrypted with AES-256-GCM
--   - Audited on every SELECT, INSERT, UPDATE
--   - Masked in query logs and error messages

-- Generate HIPAA access audit report for a date range
SELECT * FROM absdb_hipaa_access_report('2026-01-01', '2026-03-31');
-- user_id | action  | table     | column    | rows | timestamp
-- alice   | SELECT  | patients  | ssn       | 1    | 2026-01-15 09:22:11
-- bob     | UPDATE  | patients  | diagnosis | 3    | 2026-01-20 14:55:00

-- Tokenise a PAN (returns format-preserving token)
SELECT absdb_pci_tokenise('4111111111111111');
-- Returns: '4xxx-xxxx-xxxx-6374' (format-preserving, not the real PAN)

-- De-tokenise (role-restricted: requires pci_detokenise privilege)
SELECT absdb_pci_detokenise('4xxx-xxxx-xxxx-6374');
-- Returns: '4111111111111111'  (restricted role only)

-- Masked display (always safe for logs, UI, support)
SELECT absdb_pci_mask_pan('4111111111111111');
-- Returns: '****-****-****-1111'

-- Annual DEK rotation (PCI-DSS Req 3.7.2)
-- Re-encrypts all PCI-tagged columns with a new DEK.
-- Zero downtime — rotation happens in a background transaction.
CALL absdb_pci_rotate_dek('pci_tenant', absdb_kms_generate_dek('pci_tenant'));

-- Verify rotation
SELECT last_rotation, next_rotation_due FROM absdb_pci_dek_status;

-- Generate SOC 2 evidence package for audit period
SELECT * FROM absdb_soc2_evidence('2026-01-01', '2026-03-31');
-- Returns JSON with:
--   - DDL change log (_absdb_ddl_log)
--   - User access events
--   - Authentication successes/failures
--   - Encryption status per table
--   - Backup completion events
--   - High-availability events

-- DDL audit log (all schema changes, immutable)
SELECT * FROM _absdb_ddl_log
WHERE ts BETWEEN '2026-01-01' AND '2026-03-31'
ORDER BY ts;

-- View asset register (ISO 27001 Annex A.8 - Asset Management)
SELECT * FROM _absdb_assets;
-- asset_id | name         | owner       | classification | location
-- 1        | orders table | alice       | confidential   | primary DC
-- 2        | pki keys     | absdb-kms   | secret         | HSM

-- View implemented controls
SELECT * FROM absdb_iso27001_controls();
-- control_id | title                                    | status     | evidence
-- A.8.24     | Use of cryptography                      | implemented | adb_colencrypt, adb_tls_native
-- A.8.7      | Protection against malware                | implemented | input validation, WAF headers
-- A.8.15     | Logging                                  | implemented | _absdb_ddl_log, audit trail

pqc_enabled       = true         # enable post-quantum hybrid TLS
pqc_kem           = ml-kem-768   # FIPS 203 key encapsulation
pqc_hybrid_mode   = x25519       # classical + PQC combined secret
tls_alpn          = absdb/1,h2,http/1.1

-- Verify PQC status on current connection
SELECT absdb_tls_info();
-- { "version": "TLSv1.3", "cipher": "AES-256-GCM",
--   "kem": "X25519+ML-KEM-768", "sig": "ML-DSA-65",
--   "pqc": true, "fips203_kat": "PASS", "fips204_kat": "PASS" }

-- Generate a DEK for a tenant
SELECT absdb_kms_generate_dek('tenant_acme');

-- Encrypt a column at rest
ALTER TABLE users ALTER COLUMN ssn
  SET (encrypted = true, kms_key_id = 'tenant_acme_dek_v1');

-- Transparent decryption on SELECT (authorised roles only)
SELECT ssn FROM users WHERE id = 42;
-- '123-45-6789'   (decrypted transparently)

-- Unauthorised role sees ciphertext reference
-- ERROR: column ssn: insufficient privilege for decryption

-- Key rotation (re-encrypt with new DEK, background, zero downtime)
CALL absdb_kms_rotate_dek('tenant_acme_dek_v1', absdb_kms_generate_dek('tenant_acme'));

-- View recent audit events
SELECT ts, user_id, event_type, table_name, row_count, hash
FROM _absdb_audit_log
ORDER BY ts DESC LIMIT 20;

-- Verify chain integrity (detects tampering)
SELECT absdb_audit_verify_chain();
-- { "status": "OK", "records": 182441, "root_hash": "a3f7c..." }

-- Export audit log for external SIEM
COPY (SELECT * FROM _absdb_audit_log WHERE ts > NOW() - INTERVAL '1 day')
TO '/tmp/audit_export.jsonl' WITH (FORMAT jsonl);

-- Trace lineage of a column
SELECT * FROM absdb_lineage_of('orders', 'customer_id');
-- src_table   | src_col     | dst_table | dst_col     | query_id | ts
-- customers   | id          | orders    | customer_id | q-4821   | 2026-03-01
-- raw_events  | user_id     | customers | id          | q-1203   | 2026-02-15

-- GDPR impact analysis: which columns depend on a user's data?
SELECT * FROM absdb_impact_of('users', 'email');
-- Returns all downstream columns derived from users.email

-- Export OpenLineage v1 JSON for DataHub / Amundsen
SELECT absdb_lineage_export_openlineage('orders');

-- View raw lineage table
SELECT * FROM _absdb_lineage LIMIT 10;