How to use the pgAudit extension to audit logs - PolarDB

PolarDB for PostgreSQL (Compatible with Oracle) supports the pgAudit extension for generating audit logs. These logs are often required for government, finance, or ISO certifications. You can use audit logs to analyze database faults, track user behavior, and monitor data operations.

Scope

The following versions of PolarDB for PostgreSQL (Compatible with Oracle) are supported:

Oracle compatibility 2.0 (minor engine version 2.0.14.5.1.0 and later)
Oracle compatibility 1.0 (minor engine version 2.0.11.9.25.0 and later)

Note

You can view the minor engine version in the console or by running the SHOW polardb_version; statement. If your cluster does not meet the minor engine version requirement, you can upgrade the minor engine version.

Notes

You can view audit logs using the SQL Explorer feature.
A privileged account is required to set the extension parameters.

Usage guide

Install the extension

CREATE EXTENSION pgaudit;

Audit read operations

Run the following command to audit all read operations in the pgaudit_testdb database.

ALTER DATABASE pgaudit_testdb SET pgaudit.log = 'READ';

After you run the command, all read operations, such as SELECT, in the pgaudit_testdb database are audited. Write operations, such as INSERT and UPDATE, are not audited.

Audit read and write operations

Run the following command to audit all read and write operations in the pgaudit_testdb database.

ALTER DATABASE pgaudit_testdb SET pgaudit.log = 'READ,WRITE';

After you run the command, all read and write operations, such as SELECT, INSERT, and UPDATE, in the pgaudit_testdb database are audited.

Disable audit settings

ALTER DATABASE pgaudit_testdb SET pgaudit.log = 'NONE';

Audit the related operations of a specified object

Run the following command to create a user and a table in the pgaudit_testdb database, and grant all permissions on the table to that user.

CREATE USER audit_role;
ALTER DATABASE pgaudit_testdb SET pgaudit.role = 'audit_role';

CREATE TABLE test_audit (id INT);
GRANT ALL ON test_audit TO audit_role;

After the configuration is complete, only operations on the test_audit table in the pgaudit_testdb database are audited. Operations on other tables are not audited.

Uninstall a plugin

DROP EXTENSION pgaudit;

Related reference

For more information about pgAudit, see the official pgAudit documentation.