A PostgreSQL elephant dressed as Legolas uses a magnifying glass to audit a holographic screen showing hobbits being taken to an INSERT command, symbolizing pgAudit session auditing.

pgAudit Session Auditing in PostgreSQL: Part 2 – My God, It’s Full of Logs!

By evandroPostgresql, Security

Or, “They’re Taking the Hobbits to INSERT… And Our Logs Will See It All.”

Welcome back, fellow Keepers of the Data! In Part 1, we successfully forged our ring of power by installing the pgAudit extension. But an unblinking, un-seeing eye is just a creepy digital paperweight. To make it useful, we must give it instructions. We must teach it what to watch.

So, how do you get started with logging in pgAudit? The first step is Session Auditing, which is the broad-strokes, “cast a wide net” approach to logging. This level of auditing logs entire classes of commands for a user’s session, giving you a comprehensive overview of the types of activities happening in your database.

Let’s turn that firehose of data into a structured, useful stream of knowledge.

A Quick Word of Warning (The Disclaimer)

Disclaimer: This article is a technical showcase, not a substitute for professional consultancy. The configurations provided are for educational purposes. They haven’t been battle-tested in a production environment under the watchful eye of a dragon (or your friendly neighborhood compliance auditors). Use these configurations at your own risk.

Configuring pgaudit.log for Session Auditing

Let’s start with the first commandment of session auditing: it’s all controlled by one crucial parameter: pgaudit.log. This setting determines which classes of SQL statements are logged. You can set this globally in postgresql.conf, or more granularly per-database or per-user.

The available classes are:

  • READ: Logs SELECT and COPY when the source is a relation or query.
  • WRITE: Logs INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.
  • FUNCTION: Logs function calls and DO blocks.
  • ROLE: Logs statements related to roles and privileges, like GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
  • DDL: Logs all Data Definition Language (CREATE, ALTER, DROP) statements that are not covered by the ROLE class.
  • MISC: Logs miscellaneous commands, e.g., DISCARD, FETCH, LISTEN, VACUUM.
  • ALL: Includes all of the above. A bit like turning the volume up to 11.
  • NONE: Disables session auditing.

For a detailed explanation of each class, refer to the official pgAudit documentation.

A common starting point for robust auditing is to log all data changes and privilege modifications. You can set this in your postgresql.conf file or with the ALTER SYSTEM command:

-- In postgresql.conf
pgaudit.log = 'WRITE, ROLE, DDL'

-- Or via psql
ALTER SYSTEM SET pgaudit.log = 'WRITE, ROLE, DDL';

Remember to run pg_ctl reload or SELECT pg_reload_conf(); after making this change.

Screenshot of the psql terminal where the 'ALTER SYSTEM SET pgaudit.log' command is used to configure session auditing in PostgreSQL.
A common starting point for robust auditing is to log all data changes and privilege modifications using the pgaudit.log parameter.

A Field Guide to Audit Logs

Once you’ve enabled logging, you’ll start seeing new entries in your PostgreSQL log file. They all follow a consistent, comma-delimited format. Let’s look at a few examples.

Example: WRITE Statement

If a user runs an INSERT statement:

INSERT INTO fellowship (character, race) VALUES ('Gimli', 'Dwarf');

The corresponding pgAudit log entry will look like this:

AUDIT: SESSION,1,1,WRITE,INSERT,TABLE,public.fellowship,"INSERT INTO fellowship (character, race) VALUES ('Gimli', 'Dwarf');",<not logged>

Here you can clearly see the CLASS is WRITE and the COMMAND is INSERT.

Example of a pgAudit log entry in PostgreSQL for a WRITE operation, specifically showing an INSERT statement being audited.
A typical pgAudit log entry clearly identifies the audit type (SESSION), the class (WRITE), the command (INSERT), and the object being modified.

Example: DDL Statement

If a user creates a new table:

CREATE TABLE artifacts (id serial, name text, owner text);

The log entry will be:

AUDIT: SESSION,2,1,DDL,CREATE TABLE,TABLE,public.artifacts,"CREATE TABLE artifacts (id serial, name text, owner text);",<not logged>

The CLASS is DDL and the COMMAND is CREATE TABLE.

Example of a pgAudit log entry in PostgreSQL for a DDL operation, specifically showing a CREATE TABLE statement being audited.
The DDL class in pgAudit captures all data definition language, such as this CREATE TABLE command, providing a clear record of any changes to your database schema.

Example: ROLE Statement

If a user grants privileges to another user:

GRANT SELECT ON artifacts TO aragorn;

The log entry will be:

AUDIT: SESSION,3,1,ROLE,GRANT,TABLE,public.artifacts,"GRANT SELECT ON artifacts TO aragorn;",<not logged>

The CLASS is ROLE and the COMMAND is GRANT.

Example of a pgAudit log entry in PostgreSQL for a ROLE operation, specifically showing a GRANT statement being audited.
The ROLE class is crucial for security, as it logs all privilege changes like this GRANT command, showing who can access what.

Preventing Duplicate Logs and Password Leaks with log_statement

This might be the most important setting you’ll ever make. If you take only one thing away from this article, let it be this: when you enable pgaudit.log, you must disable PostgreSQL’s native statement logging.Set log_statement = 'none' in your postgresql.conf file.You can read more about this parameter in the official PostgreSQL documentation.Why is this so critical? Two reasons:
  1. It Prevents Duplicate Logs: If both log_statement and pgaudit.log are active, you’ll get two log entries for every single statement, creating a noisy, confusing mess.
  2. It Protects Your Passwords: This is the big one. If you have log_statement set to ddl or all, and a user runs ALTER USER frodo WITH PASSWORD 'some_cleartext_password', that cleartext password will be written directly to your database logs. This is a catastrophic security failure. pgAudit, however, is smart enough not to log the password part of that statement, even when auditing the ROLE class.
By setting log_statement = 'none', you let pgAudit handle the logging intelligently and securely.

To Log or Not to Log Parameters

Another key setting is pgaudit.log_parameter. When enabled, it includes the parameters of a statement in the audit log.
-- Example setting
pgaudit.log_parameter = 'on'
While this can be useful for debugging, it’s a significant security risk. If you log parameters, you could easily expose sensitive data (credit card numbers, personal information, etc.) in your logs.Recommendation: Keep pgaudit.log_parameter set to off unless you have a very specific, temporary need for it and have confirmed that no sensitive data will be exposed.

Enriching Your Logs: Essential Companion Settings

pgAudit is powerful, but it becomes a true surveillance system when paired with some of PostgreSQL’s native logging parameters. These settings add crucial context to every log entry.

Customizing the Log Line Prefix

The log_line_prefix parameter is your best friend for adding context. It defines a string that is printed at the beginning of every log line. A well-crafted prefix can tell you the timestamp, username, client host, and application for every single audited event.A highly recommended setting that is readable for both humans and machines is:
# In postgresql.conf
log_line_prefix = '%m [%p]: [%u@%d] [%h] '
This prefix adds the following information:
  • %m: Timestamp with milliseconds
  • %p: Process ID (PID)
  • %u: User name
  • %d: Database name
  • %h: Client IP address and port
With this prefix, our previous GRANT log example transforms from this: AUDIT: SESSION,3,1,ROLE,GRANT...Into this, which is far more useful: 
2025-07-31 10:30:00.123 BST [12345]: [gandalf@middle_earth] [192.168.1.10] AUDIT: SESSION,3,1,ROLE,GRANT...
Now you know exactly when, who, from where, and to which database the command was issued. For a full list of all prefix codes, see the official PostgreSQL documentation.
Example of an enriched PostgreSQL log entry showing how log_line_prefix adds a timestamp, user, database, and client IP address to a pgAudit log.
A custom log_line_prefix transforms a standard audit log into a rich source of information, telling you exactly who did what, from where, and when.

Other Important Settings

  • pgaudit.log_level: The default is LOG, which writes to a standard PostgreSQL log file. You can change this to NOTICE or other levels for debugging, but LOG is almost always what you want.
  • log_connections & log_disconnections: These are native PostgreSQL parameters, not part of pgAudit, but they are essential companions. Set both to on in postgresql.conf to get a complete picture of who is connecting to and disconnecting from your database.

What’s Next?

We’ve now configured pgAudit to watch broad categories of activity. This is powerful, but what if you don’t care about every SELECT statement, just the ones that touch your customers table?

That requires a more focused, sniper-rifle approach. In Part 3 of this series, we will dive into Object-Level Auditing, where we’ll learn how to tell pgAudit to watch specific tables, columns, and views.

By evandro

You might also like:

A PostgreSQL elephant dressed as a wizard looks into a Palantír of logs, shielding its eyes from a dangerous log entry showing a cleartext password, symbolizing the importance of pgAudit password protection.

pgAudit Log Interpretation & Password Protection in PostgreSQL: Part 4 – The Palantír of Logs

A cartoon PostgreSQL elephant holds up a glowing orange ring with the word 'GRANT' inscribed on it, symbolizing the power of object-level auditing in pgAudit.

pgAudit Object-Level Auditing in PostgreSQL: Part 3 – One GRANT to Rule Them All

A cartoon PostgreSQL elephant character looks at a holographic interface displaying a glowing orange Eye of Sauron, symbolizing the installation of the pgAudit extension.

Installing pgAudit in PostgreSQL Part 1: The Eye of Sauron

Leave a Reply

Your email address will not be published. Required fields are marked *