When pgAudit audit logging becomes a requirement for PostgreSQL
pgAudit is a PostgreSQL extension that writes audit records of database activity. Its session audit logging records statements by statement class, such as reads, writes, DDL and role changes, for everything a session runs. Its object audit logging records statements that touch specific relations, typically selected by a role, so that access to sensitive tables can be tracked without logging all traffic. Teams usually add it when an auditor, an internal control owner or an incident process asks them to show who accessed or changed which data inside the database, and the server logs cannot answer that. The decision is whether that evidence requirement is firm, which statement classes and objects it covers, and who will review the output. Before you install anything, confirm the extension's behavior against the pgAudit project documentation for each PostgreSQL major version you run.
The default server logs record connections, errors and, if you enable it, statement text. Setting log_statement to all produces a large volume of text, but it does not classify statements, does not separate object access from other activity in a way a reviewer can filter, and records literal values that may themselves be sensitive. Those gaps are why teams that face an audit requirement end up with logs they cannot query for a specific question such as who read a given customer table last quarter. To test whether your current setup is enough, pick one sensitive table and one privileged role, run a few representative queries, and then try to answer, from the logs alone, who ran them, what they touched and when. If you cannot answer all three, the gap is concrete and you can scope the work around it.
Several choices shape the configuration more than the installation itself. First, map each requirement to the statement classes and relations it needs, so you do not switch on broad logging that nobody will read. Second, decide whether session logging, object logging or both are needed, and which roles or databases fall inside scope. Third, plan where the audit records will go, including how they will be forwarded to a SIEM, how long they will be retained and who will own the review process. Each of these choices drives the volume of records, so they should be made together with the security and compliance owners, not after the first logs arrive. Written answers to these questions also give an assessor a clear basis for the control design.
Version alignment is the point most often missed. PostgreSQL supports major versions 18, 17, 16, 15 and 14 at present, with 14 scheduled to reach end of life on November 12, 2026. A pgAudit build is tied to the server major it was compiled against, so an upgrade from one major to the next means installing a build for the new major and retesting the configuration. Plan the extension into the upgrade runbook, check the pgAudit documentation for the target major before you start, and confirm that minor release updates keep your chosen extension version compatible. An audit configuration that works on one major but silently stops logging after an upgrade is a serious gap, so the post-upgrade check should include a test that a known audited statement still produces a record.
The extension is open source, so most of the cost sits in people and infrastructure rather than licenses. One-time work includes requirements mapping, installation, configuration, testing, and integration with your SIEM. Recurring work includes log storage, retention, periodic review of what is captured, and re-testing after each upgrade. Costs rise with the number of databases in scope, the breadth of statement classes, the volume of records your SIEM must ingest, and the number of compliance regimes the logs must satisfy. When you request a quote, ask for the scope in writing: which databases, roles and statement classes are covered, which SIEM receives the records, how upgrades are tested, and which deliverables and evidence you will receive at the end.
What pgAudit records in a PostgreSQL cluster
pgAudit records database activity in two modes, session and object logging, and each mode covers a different slice of statement types. The cards below cover the decisions that determine what is captured, who can read it, and what you must confirm against the version you install.
Session audit logging
Session audit logging records statements that a database session runs, filtered by the statement classes you enable. It answers who ran which kind of statement and when, which suits broad activity review. Check which roles and sessions fall in scope, and confirm with your auditors whether that level of detail meets the control text you are working to.
Object audit logging
Object audit logging records actions on specific tables, views and other relations, and it depends on database permissions. A statement is logged when the role the configuration references holds the privilege that applies to that object. Before go-live, list the sensitive relations, grant the audit role the privileges the configuration depends on, and test one read and one write on each object.
Statement classes in plain terms
Statement classes group SQL by intent: reads, writes, schema changes, privilege and role changes, function calls and a miscellaneous group. Each class answers a different investigation question, so select the classes that match your control requirements and avoid enabling every class by default. Take the exact class list from the pgAudit documentation for your installed version before you write any configuration.
Version matching and configuration
pgAudit is built against specific PostgreSQL major versions, so the extension release must match the server you run. Under the PostgreSQL versioning policy, PostgreSQL 14 support ends November 12, 2026, so plan that upgrade into your audit configuration. Confirm class names, settings and defaults against the pgAudit documentation for the installed version, and record that version in your change record so upgrades trigger a re-check.
Sensitive values in logged statements
Logged statements can contain literal values such as account numbers, personal data or credentials, and those values can reach server logs and the SIEM. Decide with your privacy and security leads which statements may carry such values, restrict who can read the log files, and inspect a sample logged statement in staging to see what the output contains for your version.
Which statement classes to log and where each choice fits
This table compares the main pgAudit configuration choices: session-wide versus object-level logging, broad versus narrow statement classes, and role-based versus cluster-wide settings. Read each row for the compliance or investigation need it serves and the log volume it tends to produce, then confirm the mix against your own assessor's expectations and your storage budget.
| Choice | Best fit | Log volume | Trade-off to confirm |
|---|---|---|---|
| Session-wide logging (pgaudit.log at the server level) | Broad reviews where any access to data must be traceable, such as a first assessment of a new cluster | High, because every matching statement across all sessions is recorded | Expect noise and higher storage and SIEM ingest; confirm the assessor accepts this breadth |
| Object-level logging (audit role with pgaudit.role) | Targeted evidence for named sensitive tables, such as cardholder or patient record tables | Low to moderate, limited to statements that touch the granted objects | Requires careful grant design; access paths that bypass the audit role may go unrecorded |
| Broad statement-class coverage (for example read, write, DDL, role and misc) | Investigations that need to reconstruct who changed schema, privileges or data | High, especially read and write on busy tables | Plan retention and SIEM indexing before enabling; confirm which classes your control mapping requires |
| Narrow statement-class coverage (for example ddl and role only) | Teams that need evidence of administrative change without recording every data read | Low, since routine application queries are excluded | May miss data access questions; document the gap and the compensating controls |
| Logging by role (pgaudit.role or per-role settings) | Separating audited service or administrator accounts from routine application traffic | Moderate, scales with the number of audited roles | Application roles shared across teams can hide who acted; confirm role-to-person mapping |
| Cluster-wide logging (settings applied to all roles and databases) | Uniform evidence across environments and a simple, reviewable configuration | Highest, and it grows with every new database or workload | Test performance and disk impact on each primary and standby before rollout |
| Log parameter detail (pgaudit.log_parameter) | Investigations that must show bound values behind a statement | Higher, because parameter values can be large or numerous | May capture sensitive values in logs; confirm handling and masking requirements first |
Matching pgAudit audit logging builds to each PostgreSQL major
A pgAudit build is compiled against one PostgreSQL major version, so the shared library must match the server major in every environment where audit logging runs. A library built for one major will not load into another, and the server will fail to start if pgaudit is listed in shared_preload_libraries with a mismatched build. Treat the pgAudit package as part of each PostgreSQL package set, covering development, test, staging, production and disaster recovery. Before you pin any version, confirm the matching pgAudit release for each PostgreSQL major in the project documentation, and record that pairing in your configuration management so that no environment drifts.
Support dates make this a planning issue, not only a build issue. The PostgreSQL versioning policy lists support for version 14 until November 12, 2026, version 15 until November 11, 2027, version 16 until November 9, 2028, version 17 until November 8, 2029 and version 18 until November 14, 2030. Any fleet running version 14 or 15 will need a major upgrade inside the audit retention period your compliance team has set. Each of those upgrades is also a pgAudit upgrade, because the new server needs a new build. Ask your team which majors run today, which end-of-support date falls first, and whether the pgAudit release for the target major has been tested against your audit configuration.
Minor releases are simpler but still need a check. PostgreSQL minor releases do not change the internal storage format, and updating usually means replacing executables and restarting the server. Your package manager may not replace the pgAudit library at the same time, so confirm after each minor update that the library on disk matches the running server. Then verify that the server starts with pgaudit loaded, that the extension is present in each audited database, and that the reported extension version matches the version you approved. Log these checks as part of the change record, because auditors and internal reviewers often ask how the audit component was verified after patching.
A major upgrade has three common paths: pg_upgrade, a dump and restore with pg_dumpall, or logical replication into a new cluster. Each path affects pgAudit differently. With pg_upgrade, the pgAudit library for the new major must be installed before the upgrade runs. With logical replication, the new cluster needs its own preload configuration and extension setup before cutover, and the audit settings must be in place before any production traffic reaches it. Confirm with your DBA which server settings and extension objects each method carries over, and test that audit events appear on the new cluster before the old one is retired.
How to roll out pgAudit audit logging from test to production
The sequence moves pgAudit from a version check to a production change with a documented audit scope, verified log content, a working SIEM feed and a tested way back. Each step closes with a record that a reviewer can inspect before the next step begins.
Confirm PostgreSQL major and pgAudit build
Record the exact PostgreSQL major and minor version on the test and production clusters, then select the pgAudit build that targets that major. Confirm the major is still within its supported window for your planned change period, and check that the extension loads against the server binaries you run. A mismatched build is a common reason the library fails to load, so ask your package source or maintainer to confirm the pairing in writing. The output is a compatibility record that names both versions, the package source and the engineer who checked them.
Define statement classes and roles in scope
Decide which statement classes pgAudit records, such as reads, writes, data definition and role changes, and which database roles it watches. Session logging captures activity by the roles you select, while object logging targets specific tables and privileges through a dedicated audit role. Agree the list with the compliance owner and the database owner, and name each excluded class with a documented reason. The output is a signed-off audit scope document that maps each control requirement to a specific setting, so the next step can be checked against it.
Configure pgAudit in a non-production cluster
Install the extension, add it to shared_preload_libraries, and set the logging parameters from the approved scope on a non-production cluster. Changes to shared_preload_libraries require a server restart, so schedule that restart in the test window and record when it happened. Keep the configuration in version control so every change has an author and a reason. The output is a reviewed configuration file and a change record that a second engineer has read and approved.
Verify log content with test statements
Run a scripted set of test statements that cover each in-scope class, executed under both the session role and the object audit role. Include a few statements that the scope excludes, so you can confirm they do not appear. Compare the PostgreSQL log entries against the expected list, checking statement text, user, database and timestamp. Parameter logging can expose sensitive values, so confirm how values appear and decide whether that setting stays enabled. The output is a test evidence set: the scripts, the expected results and the captured log lines, stored with the change record.
Connect the SIEM feed
Route the PostgreSQL log output to your SIEM through the agent or forwarder your team already operates. Confirm that pgAudit entries arrive as distinct events, that the fields your detection rules rely on are parsed, and that timestamps match other log sources. Send one of the test statements through the feed and locate it in the SIEM. Ask the SIEM team which parser they will apply, because that choice decides which fields are searchable. The output is a documented ingestion path and a record of a successful end-to-end test.
Set volume controls before production
Measure how many audit entries the test workload produces for each statement class, then estimate the load that production traffic will generate. Adjust the statement classes, role scope and log settings so output stays within the storage and SIEM ingestion limits your team has agreed. For each high-volume class, decide whether it stays in the audit trail, moves to a separate retention setting, or leaves scope with sign-off. The output is a documented volume baseline and the agreed settings that produce it.
Move to production with a rollback path
Apply the signed configuration to production during an agreed change window, using the same parameter and restart steps as the test cluster. Set the audit log retention period in the documented retention setting, then confirm that rotation and archival run as expected. Before the change, write the rollback steps: the previous configuration, the restart order and the check that confirms logging has stopped without losing data. Rehearse the rollback in the test cluster first, then verify production with a small set of test statements. The output is a production change record, a documented retention setting and a rollback runbook that has been rehearsed.
How pgAudit output connects to your SIEM and the rest of the platform
pgAudit entries reach your SIEM only through a chain of links: PostgreSQL log output, a collection agent, a parser, identity mapping, standby servers and backups. Each link needs a decision and a test, and each one should be confirmed against the log shipper and SIEM you plan to use.
Log destination and collection agent
pgAudit records reach the SIEM only if PostgreSQL writes them to a destination that a collection agent can read. Decide between the logging collector, syslog or another destination your platform supports, and confirm how the agent follows, rotates and recovers from truncated files. Run a test statement and check that it appears in the SIEM within the delay your monitoring team accepts. Confirm each setting with your chosen log shipper and SIEM before go-live.
SIEM parsing and retention
pgAudit writes its entries inside the PostgreSQL log line, so the SIEM needs a parser that separates the log prefix, the audit label and the statement text. Test that parser with statements containing quotes, multi-line queries and parameter values, since those are common causes of broken fields. Retention periods come from your compliance and legal requirements, and the SIEM storage tier must hold them at the volume your chosen statement classes produce. Confirm parsing rules and retention settings with your SIEM vendor.
Identity from authentication methods
A pgAudit entry records the database role that ran a statement, and that role may be a shared application account, so the record does not always name a person. Identity starts with the authentication method, and PostgreSQL documents password, GSSAPI, SSPI, LDAP, RADIUS, certificate, PAM and OAuth, each passing different identity details to the server. Map every role to an accountable user or service in your identity system, and confirm which identifier reaches the log for each method you use.
Replicas and failover continuity
Hot standbys serve read-only queries, and each standby needs its own audit configuration and log shipping. Decide whether reads on standbys fall within your audit scope, since they can expose sensitive data. PostgreSQL does not detect a primary failure or notify a standby, so the failover tooling you choose must also keep audit settings and log shipping active on the promoted server. Test a switchover and confirm that statements from the new primary reach the SIEM without a gap.
Backup handling for audit records
Base backups and archived WAL files protect the database itself, while pgAudit output lives in server log files that follow their own rotation and retention. Decide where rotated logs are kept, who can read them and how long they are held, and treat that copy path as part of your backup design. When you plan point-in-time recovery, confirm that a restore does not overwrite or discard audit records for the period you must preserve. Confirm the approach with your backup tooling and your SIEM team.
What drives pgAudit effort and cost, and how to keep it down
This table lists the six drivers that change the effort and cost of a pgAudit deployment, with what makes each one larger and what keeps it contained. Read each row against your own inventory of PostgreSQL clusters, systems and log destinations. The main takeaway is that most effort comes from repeating the same work across clusters and environments, so standardizing configuration early keeps later work small.
| Driver | What makes it larger | What keeps it contained |
|---|---|---|
| PostgreSQL majors and clusters | Each major needs a matched pgAudit build, and each cluster needs its own configuration and testing. | Standardize on one supported major per fleet and apply one configuration template to every cluster. |
| Statement classes and tables | Logging every class on every table produces noisy logs and more SIEM rules to maintain. | Agree the classes and audited tables per system in writing, and change them only through review. |
| SIEM parser work | Each new field, prefix or format change needs a parser update and a test event before production. | Pin the log format, use one parser for all clusters, and test with recorded sample events before each change. |
| Identity mapping | Database roles in logs must map to people or service owners, which grows with shared and application accounts. | Remove shared logins, give each service its own role, and keep the mapping under change control. |
| Number of environments | Development, test, staging and production each need matching settings, and drift creates separate evidence trails. | Deploy settings from one versioned configuration source and check every environment against it on a schedule. |
| Evidence packaging for an assessor | Assessors ask for configuration history, sample logs, retention settings and access records, gathered control by control. | Collect evidence from the start, store it alongside the configuration in version control, and name an owner per control. |
Controlling log volume so audit records stay useful
Broad audit logging fills the SIEM quickly and makes the records harder to use. pgAudit can be configured to record a very wide range of activity, and when every read, write and DDL statement from every role is written to the PostgreSQL log, the volume reaches the SIEM as well, where it adds ingestion charges and storage cost. The first decision is therefore which activity actually needs an audit trail. Start from the control requirements your organization must evidence, then map each requirement to the pgAudit settings that produce it. Anything beyond that map is a candidate for exclusion, and the exclusion should be written down with its reason.
The main levers are statement classes, role filters and object logging. The pgaudit.log setting selects statement classes such as READ, WRITE, FUNCTION, ROLE, DDL and MISC, and it is the most powerful source of volume, so enable only the classes your requirements name. Object logging through pgaudit.role grants audit scope to a designated role, so you log specific tables and columns that hold sensitive data instead of the whole database. Confirm in the pgAudit documentation for your extension version how each setting behaves, including whether statements run by superusers or by the audit role are captured, and test the effect on a non-production cluster before changing production.
Keeping volume and coverage in balance is a recurring task, not a one-time setting. Set a review cadence, for example at each PostgreSQL minor release and at each change to the monitored application, and compare the SIEM's event counts by source and by event type against the expected profile. Look for two failures: noisy classes that produce records nobody queries, and gaps where a sensitive table or role has stopped being logged after a schema change or a migration. Record each change to pgaudit settings in version control so auditors can see what was in scope on any given date.
Thresholds, alert levels, and retention periods should not be chosen by the database team alone. Retention depends on the records your policy, contracts and applicable regulations require you to keep, and how long they must remain searchable. Daily ingestion limits, index sizing and storage tiers depend on the SIEM vendor's sizing guidance and your own event rates. Ask the SIEM owner for their sizing method, measure the volume produced by a representative workload for a period you can defend, and agree the retention schedule with the compliance owner before go-live.
Plan for the cost of this work. Scoping and testing take effort from the database engineer and the security analyst, and the review loop recurs for as long as the database is in service. A quote for this section should state who will define the scope, who will test log volume, who owns the SIEM sizing conversation, and how often the review will happen. Ask for those items in writing, because they determine whether the audit trail remains useful after the first year.
Questions teams ask before implementing pgAudit audit logging
What drives the cost of a pgAudit implementation project?
Scope drives most of the cost: how many clusters you run, which statement classes you log, how many roles and objects need object-level logging, and how the logs reach your SIEM. Labor is the largest one-time component, covering design, configuration, testing in non-production environments, runbooks and handover. Recurring costs sit mostly outside the engagement: log storage, SIEM ingestion and retention, and effort to retest logging after each minor and major upgrade. DeployOpen does not publish a price list. Engagements are fixed-scope, so a quote follows from a written scope that names the clusters, statement classes, SIEM platform, retention period and test environments. Ask each bidder to separate one-time work from ongoing support and to state which log storage costs they assume you carry.
How long does pgAudit logging take to implement?
No duration can be stated in general, because the timeline depends on the scope agreed in writing before work starts. The main drivers are the number of PostgreSQL clusters, whether logging must be tested in every environment, how quickly your SIEM team can accept and parse the new log format, your change-approval calendar, and any major upgrade scheduled in the same window. Work usually runs in phases: design of statement classes and retention, configuration in a non-production environment, validation with test activity, SIEM onboarding, and then a controlled production change. Ask for a schedule with milestones and named dependencies on your side. Treat any single number from a bidder as an estimate until the scope is written down.
What does pgAudit need from our DBA team?
Your DBAs own the database side of the work, so plan their time before the project starts. They confirm which PostgreSQL clusters and databases are in scope, review the server configuration for loading the extension, and approve the parameter changes that control which statement classes are logged. They also decide how audit settings apply to each role, because the volume and content of logs depend on how roles are used. Server configuration changes usually need a restart window, so DBAs must agree the change approach with the operations team. They should also review the effect of logging on write-heavy workloads and check the log destination and rotation. Confirm in the pgAudit documentation which parameters require a restart on your PostgreSQL major version before scheduling.
What does our security and SIEM team need to provide?
Your security and SIEM teams define what the logs must answer, and that decides what gets logged. Before the build, they should document the events they need to see, such as privileged activity, schema changes and access to regulated tables, along with the retention period their policies require. They provide the log destination, the collection or forwarding path, field mapping or parsing rules, and access controls for who can read or change audit records. They also set alert logic and agree how logging volume will be reviewed over time. Separation of duties matters here: the team that administers the database should not be the only group able to alter or delete its audit trail. Ask bidders how their deliverables include test events that prove the SIEM receives, parses and retains each log category.
Can our internal team implement pgAudit without outside help?
Yes, if your team already runs PostgreSQL in production and has someone who can own logging design and SIEM onboarding. Teams usually struggle with three things: deciding which statement classes to log without flooding the SIEM, keeping the extension aligned with each PostgreSQL major, and proving the audit trail works through testing. An internal build fits when the scope is small, the team has time for testing, and the same people will operate it afterward. Outside help fits when the scope spans many clusters, a compliance deadline sets the schedule, or the team lacks SIEM parsing experience. A fixed-scope engagement can cover only the parts you choose, such as design review, configuration and test evidence, so day-to-day operations stay in-house.
How does pgAudit fit with a PostgreSQL major upgrade?
The extension has to match the new major version, so each upgrade needs a logging check on the target version before cutover. Supported majors are 14 through 18, and 14 reaches end of support on November 12, 2026, so that planning window is close. PostgreSQL major upgrades use pg_dumpall with restore, pg_upgrade, or logical replication, and logical replication can work between major versions with only several seconds of downtime. Whichever path you choose, confirm that the audit settings carry over, that the extension is installed and loaded on the target, and that test activity produces the same log categories before traffic moves. Plan the SIEM parsing update in the same window so logs from the new cluster are read correctly from the first event.
Which PostgreSQL versions can run pgAudit logging today?
Run pgAudit only on a PostgreSQL major that is still supported, and confirm the matching extension release for that major with the pgAudit project before you install. PostgreSQL currently supports versions 14 through 18, with support ending for 14 on November 12, 2026, 15 on November 11, 2027, 16 on November 9, 2028, 17 on November 8, 2029 and 18 on November 14, 2030. Versions 13 and earlier no longer receive fixes from PostgreSQL. A cluster on an unsupported version should be upgraded before you build audit logging on it. Ask whether your target major and your planned extension version are both listed as tested in the project's release notes.
How DeployOpen scopes and delivers pgAudit audit logging
DeployOpen plans, builds and documents pgAudit configuration for PostgreSQL inside your own infrastructure, starting from a written scope that names the statement classes, databases and log destinations in use. The work ends with handover material your operations and security teams can run and audit without us.
How we scope
- Engagements are fixed-scope, with the pgAudit deliverables, environments and timelines agreed in writing before work starts.
- We inventory PostgreSQL major versions in use and confirm the matching pgAudit build for each one before configuration begins.
- The scope lists which statement classes and object-level targets are logged, and which are deliberately excluded, with reasons.
How we build
- We configure session and object audit settings in a non-production cluster first and review the generated log lines for completeness.
- Log volume is measured against test workloads so retention and storage sizing can be planned before production rollout.
- We wire log output to your SIEM pipeline and verify parsing, timestamps and field mapping with sample events.
How we hand over
- You receive configuration files, the statement-class rationale and a runbook for changing audit scope under change control.
- Test evidence shows which events were captured, which were filtered and how each one appears in the SIEM.
- Upgrade notes cover how pgAudit is kept aligned with each PostgreSQL major upgrade that your team schedules.
How we support
- Ongoing support, upgrades and periodic review of audit scope are available as a retainer after handover.
- Retainer work can include re-testing pgAudit after PostgreSQL minor and major upgrades and after SIEM changes.
- Compliance readiness material is prepared for your assessors, who decide whether the controls meet their requirements.
