Postgres pgAudit Audit Logging Setup Prompt
Configure pgAudit for compliance-grade logging that captures the statements auditors need without drowning the disk or leaking secrets — scoped by role and object instead of logging every SELECT on the whole database.
- Target user
- DBAs and security engineers implementing database audit logging for compliance
- Difficulty
- Advanced
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior PostgreSQL security engineer who has stood up pgAudit for SOC 2 / PCI / HIPAA audits. You know the two failure modes — logging so much it fills the disk and hurts performance, or so little it fails the audit — and you scope logging deliberately with object auditing rather than blanket session auditing. I will describe: - The compliance regime and what it actually requires logged (DDL, privilege changes, reads of sensitive tables, all writes): [REQUIREMENT] - The sensitive objects/schemas that must be audited: [SENSITIVE OBJECTS] - Roles that touch the database (app role, admins, ETL, humans): [ROLES] - Current logging config and log shipping destination (file, syslog, SIEM): [LOG PIPELINE] - Postgres version and whether pgaudit is already installed: [VERSION / STATE] Work through this in order: 1. **Map requirements to pgAudit classes.** Explain the classes (READ, WRITE, FUNCTION, ROLE, DDL, MISC) and pick the minimum set that satisfies the requirement. Most regimes need ROLE + DDL + writes to sensitive tables, NOT every READ on the whole database. 2. **Choose session vs object auditing.** Session auditing (`pgaudit.log = 'ddl, role, write'`) is broad; object auditing (a dedicated audit role granted on specific tables + `pgaudit.role`) scopes READ/WRITE logging to just the sensitive objects. Recommend object auditing for high-traffic tables to avoid logging every ordinary SELECT. 3. **Give the exact config**: `shared_preload_libraries = 'pgaudit'`, the `pgaudit.*` GUCs, the audit-role grants for object auditing, and per-role overrides (`ALTER ROLE etl SET pgaudit.log = ...`) so a chatty service role doesn't flood logs. 4. **Protect the log pipeline**: log_line_prefix fields auditors need (user, db, session, timestamp, application_name), why logs must ship off-box to append-only storage a DBA can't edit, and the caveat that pgAudit can log statement TEXT — so parameterized secrets or PII in SQL literals can land in logs. Address redaction/handling. 5. **Estimate and control cost**: how to gauge log volume before rollout, what to watch (disk, log write latency), and how to roll out per-role to catch volume surprises early. Output: (a) requirement-to-class mapping table; (b) the exact postgresql.conf / role grants; (c) the log_line_prefix and shipping recommendation; (d) the secrets/PII caveat and mitigation; (e) a phased rollout with a volume check. Guardrails: audit logs must ship to append-only storage outside the DBA's mutation control, or they don't satisfy an auditor. Beware that statement text in audit logs can contain PII/secrets — never send raw audit logs to a low-trust destination. Roll out logging one role at a time and watch disk and write latency before enabling broadly.
Run this prompt with AI
Test it, get an AI-improved version, or compare models — live in the Prompt Workspace. No copy-paste.
Why this prompt works
Audit logging fails in two directions, and pgAudit makes both easy. Turn on session-level READ auditing on a busy database and you log every SELECT — the disk fills, write latency climbs, and the signal auditors need drowns in noise. Turn on too little and the audit fails. This prompt forces a deliberate mapping from the actual compliance requirement to the minimum pgAudit classes, and steers high-traffic reads toward object auditing scoped to the sensitive tables that matter.
The under-discussed hazard it surfaces is that pgAudit logs statement text, so PII and secrets in SQL literals can leak into logs that then get shipped somewhere less trusted than the database itself. By pairing the class mapping with append-only off-box shipping, a secrets caveat, and a phased per-role rollout with a volume check, the prompt produces a configuration that both passes the audit and survives production.
Related prompts
-
Postgres md5-to-SCRAM Password Migration Prompt
Migrate a Postgres instance from legacy md5 password auth to scram-sha-256 without locking out clients — sequencing password_encryption, per-user re-hashing, driver compatibility, and pg_hba.conf so no role is stranded.
-
Postgres pg_hba.conf Authentication & TLS Hardening Prompt
Review and harden Postgres client authentication — pg_hba.conf rules, auth methods (scram vs md5 vs trust), and TLS configuration — to close access gaps without locking out legitimate apps or replicas.
-
PostgreSQL Row-Level Security Policy Design Prompt
Produces a reviewed Row-Level Security design for multi-tenant isolation, with CREATE POLICY statements separating USING and WITH CHECK, a tenant-context strategy via session variables, performance analysis, and a test matrix proving tenants cannot see each other's rows.
-
Postgres auto_explain Plan Regression Detection Prompt
Set up auto_explain to catch the query whose plan flipped in production — capturing the bad plan with timing and buffers only when it matters — then diagnose why the planner chose it, without logging every fast query.
More Postgres prompts & error guides
Browse every Postgres prompt and troubleshooting guide in one place.
Reading prompts? Get all 500 in one free PDF
500 battle-tested, copy-paste AI prompts engineered by a senior systems engineer — every one with fill-in placeholders and safety/back-out notes. Drop your email and it's yours.
- 500 prompts: Linux · Kubernetes · Terraform · OpenStack · GitLab · Docker · Monitoring · Incident Response
- Instant PDF download — yours free, forever
- Plus one practical AI-workflow email a week (no spam)
Single opt-in · unsubscribe anytime · no spam.