3 Postgres Audit Methods: How to Choose?

What is Postgres Auditing?

Auditing is the systematic investigation of procedures, processes, and practices related to an organization’s IT infrastructure. Auditors typically require a comprehensive log of changes and activity related to software, business data, and security systems. They also need to track changes to organizational structure, security policies, and user account definitions.

PostgreSQL supports auditing with the following audit logging options. We’ll describe each of these methods, and help you understand which one is the most suitable for your scenario:

The log_statement = all configuration option lets you exhaustively log all activity in a PostgreSQL database
The pgAudit extension, provided by the Postgres community, lets you apply filtering and convert logs to a format required for auditing
Custom triggers let you create your own customized audit flows

This Postgres audit guide is part of our series of articles about PostgreSQL security.

In this article, you will learn:

What is log_statement=all?
What is pgAudit?
- Why Choose pgAudit Over log_statement=all?
- pgAudit Considerations
Defining Custom Triggers for Auditing
- How to Implement a Custom Trigger
- Pros and Cons of the Custom Trigger Methods
Postgres Auditing with Satori

What is log_statement=all?

The log_statement parameter in PostgreSQL configuration specifies which SQL statements are logged. Only superusers can change it. It has the following values:

none – no logging. This is the default option.
ddl – logging all data definition operations like CREATE, DROP, and ALTER
mod – same as ddl, and also logs operations that modify data, like INSERT, UPDATE and DELETE
all – logs all SQL queries.

For auditing purposes, it is advised to set log_statement to all.

What is pgAudit?

pgAudit stands for “PostgreSQL Audit Extension”. It is a native PostgreSQL extension that uses the standard PostgreSQL logging facility to provide detailed object and session audit logging. pgAudit was designed to extend native PostgreSQL capabilities, providing users with the ability to produce audit logs. It is often used to supply information for financial or regulatory compliance, as well as for ISO certifications.

Why Choose pgAudit Over log_statement=all?

The basic way to enable an audit trail in PostgreSQL is to enable log_statement=all. However, this will simply list all database operations—it won’t let you filter certain operations, and will not generate logs in the format needed for auditing. pgAudit provides these capabilities.

pgAudit also allows you to log specific database commands, for example read-only statements like SELECT and COPY, write commands like INSERT, UPDATE and DELETE, etc. You can also specify that only operations on specific objects should be logged.

Another important difference is that pgAudit logs the details of each operation, with its full internals, using a structured output that is suitable for audit searches.

For example, here is a DO statement, and the log generated by log_statement=all compared to the log generated by pgAudit. This code was shared in the pgAudit documentation:

				
					-- SQL statement

DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

-- log_statement=all generates this log info:

2020-12-20 23:40:11 UTC:157.230.232.139(53064):sgpostgres@test:[9091]: LOG: statement:
    DO $$
        BEGIN
            EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
        END $$;
        
-- pgAudit generates this expanded log info:

2020-12-20 23:40:11 UTC:157.230.232.139(53064):sgpostgres@test:[9091]: LOG: AUDIT: SESSION,4,1,FUNCTION,DO,,,"DO $$
        BEGIN
            EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
        END $$;",<not logged>
2020-12-20 23:40:11 UTC:157.230.232.139(53064):sgpostgres@test:[9091]: LOG: AUDIT: SESSION,4,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT),<not logged>

pgAudit Considerations

Depending on your settings, pgAudit can generate a large volume of logs. You should carefully determine the exact log content required for audits in your environment, to avoid an explosion of logs, which will make it more difficult to analyze logs and also have storage implications.

For example, when working in an OLAP environment, the log file can be several times the size of the actual data inserted, so you should not include it as part of your fact table. Disk space can quickly run out, and logging can have a significant impact on database performance. Try to limit the audit log to specific tables, and if you cannot, perform testing to evaluate the performance impact, and ensure you have enough disk space.

Defining Custom Triggers for Postgres Auditing

The basic idea behind custom trigger auditing is to create a shadow audit table for the database tables you need to track. Each change to the main table should be logged to the shadow table, using triggers.

It’s important to only implement triggers using database operations and stored procedures, not via application code. If triggers are created as part of application code, the code can break when ad-hoc changes are made to the data. You can also create triggers in PostgreSQL using PL, pgSQL, or Python.

How to Implement a Custom Trigger for Postgres Auditing

You can use the following process to define a shadow table and log all changes from a master table called MYTABLE to it:

Create the shadow table, naming it MYTABLE_LOG. Create at least the following audit columns: Change time, User, and Type of change, and in addition, the data columns from MYTABLE which are important for your auditing process.
Create a function that inserts new values into the MYTABLE_LOG shadow table, and populates the audit columns.
Create a trigger in MYTABLE to invoke the auditing function each time a row is altered. There are two options for defining the trigger:
1. Use an AFTER trigger to log the change without any impact on the table.
2. Use a BEFORE trigger for transactions impacting thousands of rows, to ensure you use less resources and minimize the likelihood of exceptions.

Pros and Cons of the Custom Trigger Methods for Postgres Auditing

The advantage is that custom triggers can explicitly log the information you need for your audits, separate audit data to its own table, and enable easy search of audit logs, by indexing shadow tables.
The disadvantage is that you need to write a stored procedure and manage the trigger definition over time. Triggers can also result in schema duplication, and inflate database size, because some or all of the data is duplicated to other tables.

Postgres Audit with Satori

It’s one thing to manage auditing for complex databases in Postgres. But when you have complex databases in multiple data stores, setup and maintenance quickly becomes overwhelming.

Satori audits all your data stores in a single location, including all your PostgreSQL data access. In addition, your audit is enriched with data types (for example: emails, names, phone numbers or blood types), and user identity. With Satori you can set access and security policies from a single interface, and enable self-service access workflows for your data users.