How to use the pgAudit extension for audit logging - PolarDB

PolarDB for PostgreSQL (Compatible with Oracle) supports the pgAudit extension. This extension generates audit logs that are often required for government, finance, or ISO certifications. You can use audit logs to troubleshoot issues, perform behavior analysis, and track how data is used in your database.

Scope

PolarDB for PostgreSQL (Compatible with Oracle) supports the following versions:

Oracle syntax compatible 2.0 (minor engine version 2.0.14.5.1.0 and later)
Oracle syntax compatible 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 run the SHOW polardb_version; statement. If your cluster does not meet the version requirement, upgrade the minor engine version.

Important notes

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

Usage guide

Install the extension

CREATE EXTENSION pgaudit;

Audit read operations

You can 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

You can 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 auditing

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

Audit operations on a specific object

Run the following commands to set the audit user for the pgaudit_testdb database, create a table, 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 you run the commands, only operations on the test_audit table in the pgaudit_testdb database are audited.

Uninstall the extension

DROP EXTENSION pgaudit;

References

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