Guide: PostgreSQL Secuirty

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?

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 $$
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

-- log_statement=all generates this log info:

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

2020-12-20 23:40:11 UTC:[9091]: LOG: AUDIT: SESSION,4,1,FUNCTION,DO,,,"DO $$
            EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
        END $$;",<not logged>
2020-12-20 23:40:11 UTC:[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:

  1. 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.
  2. Create a function that inserts new values into the MYTABLE_LOG shadow table, and populates the audit columns.
  3. 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.

Learn more:

Last updated on

January 30, 2024

The information provided in this article and elsewhere on this website is meant purely for educational discussion and contains only general information about legal, commercial and other matters. It is not legal advice and should not be treated as such. Information on this website may not constitute the most up-to-date legal or other information. The information in this article is provided “as is” without any representations or warranties, express or implied. We make no representations or warranties in relation to the information in this article and all liability with respect to actions taken or not taken based on the contents of this article are hereby expressly disclaimed. You must not rely on the information in this article as an alternative to legal advice from your attorney or other professional legal services provider. If you have any specific questions about any legal matter you should consult your attorney or other professional legal services provider. This article may contain links to other third-party websites. Such links are only for the convenience of the reader, user or browser; we do not recommend or endorse the contents of any third-party sites.