ApsaraDB RDS:Use the pgAudit extension to generate audit logs

Prerequisites

Before you begin, make sure that you have:

• An ApsaraDB RDS for PostgreSQL instance that runs PostgreSQL 10 or later with minor engine version 20210531 or later. If your instance runs PostgreSQL 17, minor engine version 20241030 or later. For more information about how to view and update the minor engine version, see Update the minor engine version

• pgaudit added to the shared_preload_libraries parameter of your RDS instance. For more information, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance

How pgAudit works

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

pgAudit supports two audit logging modes:

• Session audit logging: Logs all statements of the specified classes during a session. You control which statement classes to log by setting the pgaudit.log parameter.

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

Usage notes

• pgAudit can generate a large amount of audit log data. The amount varies based on the extension configuration. Before you enable auditing, evaluate which objects and statement classes to audit. Logging excessive data can exhaust the storage capacity of your RDS instance.

• After you rename an object, new audit log records that pgAudit generates for the object are associated with the new name.

Enable the pgAudit extension

Connect to your RDS instance and run the following statement:

CREATE EXTENSION pgaudit;

Configure session audit logging

Session audit logging records all statements of the specified classes that a user runs during a database session. Set the pgaudit.log parameter 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. It uses the PostgreSQL permission system to determine which operations to log.

To configure object audit logging:

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

2. Create the audit role:

      CREATE ROLE auditor;

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

      GRANT SELECT, DELETE ON orders TO auditor;

After you configure object audit logging, pgAudit logs any SELECT or DELETE statement that accesses the orders table, regardless of which user runs the statement.

-- 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 table describes the parameters that control session audit logging behavior. To modify these parameters, configure them in the RDS console. For more information, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.

Some parameters, such as pgaudit.log_client, pgaudit.log_rows, and pgaudit.log_statement, may not be available in the RDS console parameter settings page. These parameters use their default values. To check the current value, run SHOW <parameter_name>; after you connect to your RDS instance.

Audit log output format

pgAudit generates log entries in the following format. You can view these entries 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 log entry contains the following fields, separated by commas:

Best practices

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

• Monitor storage usage: Audit logging generates additional data. Regularly check the storage usage of your RDS instance to prevent storage exhaustion.

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

• Exclude catalog queries: Keep pgaudit.log_catalog set 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 the following statement:

DROP EXTENSION pgaudit;

References

For more information about pgAudit, see pgAudit documentation.