Auditing is an essential practice in database management, enabling administrators to monitor and record activities within a PostgreSQL database. This ensures accountability, enhances security, and supports compliance with regulatory standards. While PostgreSQL provides robust general logging capabilities, the pgaudit extension is purpose-built for detailed and structured auditing of SQL statements. In this guide, we’ll dive deep into installing, configuring, and using pgaudit with PostgreSQL 17, equipping you with the knowledge to implement effective auditing for your database.
What is pgaudit?
The pgaudit extension (PostgreSQL Audit) is designed to provide comprehensive session and object-level auditing. Unlike general PostgreSQL logging, which captures broad server activities, pgaudit focuses on SQL-level events, offering granular insights into database operations. Key features include:
- Detailed SQL Logging: Captures Data Definition Language (DDL), Data Manipulation Language (DML), role changes, and function calls.
- Integration with PostgreSQL Logs: Audit logs are written to PostgreSQL’s standard log files (e.g., /var/log/postgresql/postgresql-17-main.log), avoiding the need for separate tables or external storage.
- Customizable Logging: Allows fine-tuned control over what to log, balancing detail and performance.
This guide assumes you have PostgreSQL 17 installed and are working on a Debian/Ubuntu-based system. Let’s walk through the setup and usage of pgaudit step by step.
Installing pgaudit
To begin, you need to install the pgaudit extension for PostgreSQL 17. On Debian/Ubuntu systems, this is straightforward:
Install the pgaudit Package:
Run the following command to install pgaudit:
sudo apt update
sudo apt install postgresql-17-pgaudit
This installs the necessary files, including pgaudit.control and SQL scripts, typically located in:
/usr/share/postgresql/17/extension/
Verify Installation:
Confirm that the pgaudit files are present:
ls /usr/share/postgresql/17/extension/pgaudit*
Loading pgaudit
pgaudit is a shared library that requires loading at server startup to function. This is achieved by modifying the shared_preload_libraries parameter in the PostgreSQL configuration file.
Edit postgresql.conf:
Locate your PostgreSQL configuration file (typically at /etc/postgresql/17/main/postgresql.conf) and add or modify the following line:
shared_preload_libraries = 'pgaudit'
- If other libraries are already loaded, append pgaudit with a comma,
e.g., shared_preload_libraries = 'pg_stat_statements, pgaudit'.
Restart PostgreSQL:
Apply the changes by restarting the PostgreSQL service:
sudo systemctl restart postgresq
Verify Loading:
Check that pgaudit is loaded by connecting to PostgreSQL and running:
SHOW shared_preload_libraries;
Creating the pgaudit Extension
After loading the library, you need to enable the pgaudit extension in the specific database you want to audit.
Connect to the Database:
Use psql to connect to your target database (e.g., averigo_18):
psql -U postgres -d new_db
Create the Extension:
Run the following SQL command:
\dx+ pgaudit
CREATE EXTENSION pgaudit;
Verify Extension Installation:
Check the installed extension and its objects:
\dx+ pgaudit
Expected output includes:
Object Description
---------------------------------------------
event trigger pgaudit_ddl_command_end
event trigger pgaudit_sql_drop
function pgaudit_ddl_command_end()
function pgaudit_sql_drop()
These objects include event triggers and functions that capture DDL and DROP events.
Configuring pgaudit Parameters
pgaudit offers several configuration parameters to control auditing behavior. These are set in postgresql.conf or via SQL commands (e.g., ALTER SYSTEM SET). To view all pgaudit-related settings, run:
SELECT * FROM pg_settings WHERE name LIKE 'pgaudit%';
Parameters Related to pg_audit Extension
- pgaudit.log
Purpose: Specifies classes of statements to log (e.g., DDL, DML, ROLE, FUNCTION).
Recommended Setting: 'ddl, read, write, role, function'
- pgaudit.log_catalog
Purpose: Logs statements affecting system catalog tables (e.g., pg_catalog).
Recommended Setting: off (enable only if needed)
- pgaudit.role
Purpose: Audits actions by a specific role.
Recommended Setting: 'postgres' or your target role
- pgaudit.log_client
Purpose: Sends audit logs to the client (e.g., psql).
Recommended Setting: off (for production to reduce noise)
- pgaudit.log_parameter
Purpose: Includes query parameters in audit logs.
Recommended Setting: off (enable for debugging)
- pgaudit.log_parameter_max_size
Purpose: Limits the length of logged variable-length parameters.
Recommended Setting: 0 (no limit, adjust for performance)
- pgaudit.log_relation
Purpose: Logs each relation (table) referenced in DML/SELECT statements.
Recommended Setting: off (enable only for detailed auditing).
- pgaudit.log_statement
Purpose: Includes full statement text in audit logs.
Recommended Setting: on (recommended for clarity)
- pgaudit.log_rows
Purpose: Logs the number of rows affected/retrieved by DML/SELECT.
Recommended Setting: off (due to performance overhead)
- pgaudit.log_statement_once
Purpose: Logs statement text only once per session for repeated statements.
Recommended Setting: off (for full audit trail)
- pgaudit.log_level
Purpose: Sets the log level for audit entries (e.g., log, info, notice).
Recommended Setting: 'log' (standard for audit logs)
To configure pgaudit to log DDL, DML, and role changes, add the following to postgresql.conf:
pgaudit.log = 'ddl, read, write, role'
pgaudit.log_statement = on
pgaudit.log_level = log
pgaudit.log_client = off
pgaudit.log_parameter = off
Alternatively, set these dynamically in a session:
SET pgaudit.log = 'ddl, read, write, role';
SET pgaudit.log_statement = on;
SET pgaudit.log_level = 'log';
After changing settings in postgresql.conf, reload the configuration:
sudo systemctl reload postgresql
Tip: Balance auditing needs with performance. Enabling options like pgaudit.log_rows or pgaudit.log_relation can increase log volume and impact performance, so use them judiciously.
Testing pgaudit
To understand how pgaudit works, let’s execute various SQL statements and observe the resulting logs.
DDL Commands:
Create, alter, and drop a table to trigger DDL auditing:
CREATE TABLE test_audit (
id SERIAL PRIMARY KEY,
name TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
ALTER TABLE test_audit ADD COLUMN updated_at TIMESTAMPTZ;
DROP TABLE test_audit;
DML Commands:
Perform data manipulation operations:
CREATE TABLE some_table (id SERIAL PRIMARY KEY, col TEXT);
INSERT INTO some_table (col) VALUES ('test');
UPDATE some_table SET col = 'new' WHERE col = 'test';
DELETE FROM some_table WHERE col = 'new';
Role and Permission Changes:
Create a role and grant permissions:
CREATE ROLE audit_test LOGIN PASSWORD 'secure_password';
GRANT SELECT ON some_table TO audit_test;
Viewing and Understanding Audit Logs
pgaudit writes audit entries to PostgreSQL’s standard log files, typically located at /var/log/postgresql/postgresql-17-main.log on Debian/Ubuntu systems. To monitor logs in real-time:
tail -f /var/log/postgresql/postgresql-17-main.log
Example Log Entries
Here’s what you might see after running the above commands:
2025-09-20 15:08:30.713 IST [508516] postgres@cybro_db LOG: AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.test_audit,"CREATE TABLE test_audit(id serial PRIMARY KEY, name text, created_at timestamptz DEFAULT now())",<not logged>
2025-09-20 15:08:31.214 IST [508516] postgres@cybro_db LOG: AUDIT: SESSION,2,1,DDL,ALTER TABLE,TABLE,public.test_audit,"ALTER TABLE test_audit ADD COLUMN updated_at timestamptz",<not logged>
2025-09-20 15:08:32.015 IST [508516] postgres@cybro_db LOG: AUDIT: SESSION,3,1,WRITE,INSERT,TABLE,public.some_table,"INSERT INTO some_table (col) VALUES ('test')",<not logged>
2025-09-20 15:08:32.816 IST [508516] postgres@cybro_db LOG: AUDIT: SESSION,4,1,ROLE,CREATE ROLE,ROLE,audit_test,"CREATE ROLE audit_test LOGIN PASSWORD ",<not logged>
Log Entry Breakdown
Each audit log entry follows a structured format:
- AUDIT: SESSION: Indicates a session-level audit (pgaudit also supports OBJECT auditing for specific roles).
- Sequence Numbers: E.g., 1,1 represents the statement ID and sub-statement ID.
- Statement Type: E.g., DDL, WRITE, ROLE, indicating the type of operation.
- Command: E.g., CREATE TABLE, INSERT, specifying the exact command.
- Object Type and Name: E.g., TABLE,public.test_audit, identifying the affected object.
- Statement Text: The full SQL statement executed./
- Parameters: unless pgaudit.log_parameter is enabled.
Conclusion
The pgaudit extension is a robust and flexible tool for auditing PostgreSQL databases, offering detailed insights into SQL activities without complicating your database schema. By mastering its installation, configuration, and log analysis, you can ensure transparency, accountability, and compliance in your database operations. Whether you’re a database administrator or a developer, pgaudit is an essential tool for maintaining a secure and auditable PostgreSQL environment.
For further exploration, consider experimenting with advanced configurations or integrating audit logs with external monitoring systems to enhance your database’s security posture.