ApsaraDB RDS:Generate audit logs with pgAudit

Prerequisites

Make sure that you have:

• An ApsaraDB RDS for PostgreSQL instance that runs PostgreSQL 10 or later with minor engine version 20210531 or later. PostgreSQL 17 requires minor engine version 20241030 or later. Update the minor engine version

• pgaudit added to the shared_preload_libraries parameter of your RDS instance. Modify the parameters of an ApsaraDB RDS for PostgreSQL instance

How pgAudit works

pgAudit intercepts SQL statements and logs them through the standard PostgreSQL logging facility with structured metadata such as statement class, command tag, object type, and object name.

pgAudit supports two logging modes:

• Session audit logging: Logs all statements of specified classes during a session. Configure which classes to log with the pgaudit.log parameter.

• Object audit logging: Logs statements that affect specific database objects. Grant permissions to a designated audit role specified by the pgaudit.role parameter. This provides finer granularity than session audit logging.

Usage notes

• pgAudit can generate a large volume of log data. The amount varies based on the extension configuration. Evaluate which objects and statement classes to audit before enabling auditing to avoid exhausting your instance storage.

• After you rename an object, subsequent audit log entries use the new name.

Enable the pgAudit extension

Connect to your RDS instance and run:

CREATE EXTENSION pgaudit;

Configure session audit logging

Session audit logging records all statements of specified classes during a database session. Set pgaudit.log to specify which statement classes to audit.

-- Create a table
CREATE TABLE employees (id int, name text, department text);

-- Insert a row
INSERT INTO employees VALUES (1, 'Alice', 'Engineering');

AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.employees,"CREATE TABLE employees (id int, name text, department text);",<not logged>
AUDIT: SESSION,2,1,WRITE,INSERT,,,"INSERT INTO employees VALUES (1, 'Alice', 'Engineering');",<not logged>

Configure object audit logging

Object audit logging records statements that affect specific database objects, using the PostgreSQL permission system to determine which operations to log.

To configure object audit logging:

1. Set pgaudit.role to the name of a designated audit role in the RDS console. For example, set pgaudit.role to auditor. Modify the parameters of an ApsaraDB RDS for PostgreSQL instance. > Note: pgaudit.role requires superuser privileges. On ApsaraDB RDS for PostgreSQL, configure this parameter through the RDS console instead of a SQL SET statement.

2. Create the audit role:

      CREATE ROLE auditor;

3. Grant permissions to the audit role on the objects to audit. For example, to audit SELECT and DELETE operations on a table named orders:

      GRANT SELECT, DELETE ON orders TO auditor;

After configuration, pgAudit logs any SELECT or DELETE statement on the orders table, regardless of which user runs it.

-- Grant SELECT on the target table to the audit role
GRANT SELECT ON employees TO auditor;

-- Any SELECT on the employees table is now logged
SELECT * FROM employees WHERE department = 'Engineering';

AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.employees,"SELECT * FROM employees WHERE department = 'Engineering';",<not logged>

Session audit logging parameters

The following parameters control session audit logging. Configure them in the RDS console. Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.

Parameters such as pgaudit.log_client, pgaudit.log_rows, and pgaudit.log_statement may not appear in the RDS console. They use default values. To check their current values, run SHOW <parameter_name>; after connecting to your RDS instance.

Audit log output format

pgAudit generates log entries in the following format, visible in the error logs of your RDS instance.

AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.orders,"CREATE TABLE orders (id int, name text);",<not logged>

Each entry contains these comma-separated fields:

Best practices

• Start with minimal logging: Begin with specific classes such as WRITE and DDL rather than ALL. Expand as needed.

• Monitor storage usage: Audit logging generates additional data. Regularly check storage usage to prevent exhaustion.

• Use object audit logging for fine-grained control: To audit specific tables or views, use object audit logging instead of session audit logging to reduce log volume.

• Exclude catalog queries: Set pgaudit.log_catalog to on only if you need to audit catalog queries. Setting it to off reduces noise from administrative tools.

Disable the pgAudit extension

To stop generating audit logs and remove the extension, run:

DROP EXTENSION pgaudit;

References

For pgAudit documentation, see the pgAudit GitHub repository.