Understanding pgAudit: PostgreSQL Auditing Made Easy

PostgreSQL is one of the most robust and flexible relational database management systems available today, offering a wide array of features for database administrators (DBAs) and developers alike. However, like any database, security and auditing are critical concerns when managing sensitive data. pgaudit is an extension for PostgreSQL that helps with auditing database activity, providing a clear and comprehensive audit trail of database operations.

In this blog post, we’ll dive into what pgaudit is, how to install and configure it, and explore some uses that make it essential for security and compliance.

What is pgAudit?

pgaudit is an open-source PostgreSQL extension that enhances PostgreSQL’s logging functionality by adding detailed audit logging capabilities. It logs all the database activity that is required to track and monitor user interactions, including queries, user sessions, and system-level events. By default, PostgreSQL provides basic logging, which includes information such as the start and end times of queries. However, for many compliance frameworks (e.g., ISO), it is often necessary to capture more granular data. This is where pgAudit can play a major role, by providing detailed activity logs, including:

• SQL queries executed (including the values of parameters).
• Successful or failed login attempts.
• Changes to database objects (e.g., table creation or dropping).
• DML operations (INSERT, UPDATE, DELETE).
• SELECT queries with detailed information, such as the user issuing them.

Why pgAudit?

The need for robust auditing is more important than ever. Organizations dealing with sensitive information (such as financial or health data) need to comply with various regulations. pgaudit offers several key benefits:

• Compliance with Regulatory Standards like GDPR ,ISO etc
• Enhanced Security
• Transparency and Monitoring
• Simplified Troubleshooting

Installation and Configuration

To begin using pgaudit, you must first install the PostgreSQL development tools. Here , I have setup of Ubuntu 24.04 with PostgreSQL 17.2 .

sudo apt-get update
sudo apt-get install postgresql-contrib postgresql-server-dev-all

Once , we have the required tools installed, let’s install pgaudit

sudo apt-get install postgresql-17-pgaudit  
# Replace 17 with your PostgreSQL version

To complete the setup, we need to change a parameter in the postgresql.conf file

#In postgresql.conf file
shared_preload_libraries='pgaudit'
#It requires the restart of the postgresql services

Login to psql for creating and configuring the extension

postgres@ip-172-31-23-19:~$ psql
psql (17.2 (Ubuntu 17.2-1.pgdg24.04+1))
Type "help" for help.

postgres=# create extension pgaudit;
CREATE EXTENSION
postgres=# \dx
                   List of installed extensions
  Name   | Version |   Schema   |           Description
---------+---------+------------+---------------------------------
 pgaudit | 17.0    | public     | provides auditing functionality
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)
#Configuring the pgaudit.log to'read'
postgres=# alter system set pgaudit.log to 'READ';
ALTER SYSTEM
postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

Let‘s look at some commonly used pgaudit.log settings:

• WRITE – Log all SQL writes like INSERT, UPDATE, DELETE, TRUNCATE
• FUNCTION – Log calls to functions and stored procedures
• ROLE – Log user creation, role grants etc.
• DDL – Log all DDL statements like CREATE TABLE
• DML – Enable logging for all DML statements
• MISC – Log miscellaneous commands like VACUUM, EXPLAIN

Verifying the log file

Let’s verify in our log file

2024-11-27 11:37:01.857 UTC [10329] LOG:  parameter "pgaudit.log" changed to "READ"
2024-11-27 11:38:00.473 UTC [10495] postgres@postgres LOG:  AUDIT: SESSION,1,1,READ,SELECT,,,"SELECT pg_catalog.lower(name) FROM pg_catalog.pg_settings  WHERE pg_catalog.lower(name) LIKE pg_catalog.lower('log\_dire%')

Here , we can see that pgaudit extension captures the select query as we set the pgaudit.log to 'read'.

Conclusion

pgaudit is a powerful extension for PostgreSQL that provides the necessary auditing capabilities for businesses to comply with regulations, enhance security, and track database activities. Its ability to log detailed information about SQL queries, user actions, and changes to database objects makes it an invaluable tool for DBAs, security teams, and compliance officers.

With pgaudit in place, you can ensure that your PostgreSQL databases are fully auditable, which is crucial for regulatory compliance and maintaining a secure database environment. Implementing it correctly in your PostgreSQL system can go a long way toward improving your organization’s security posture and reducing the risk of unauthorized data access or manipulation.