How do I use the pgAudit extension to produce audit logs - PolarDB

PolarDB for PostgreSQL supports the pgAudit extension that can produce audit logs required to comply with government, financial, or ISO certifications. Audit logs help you analyze faults and operations on your clusters to obtain information about data queries.

Usage notes

To view the generated audit logs, you must enable the SQL Explorer feature.
The pgAudit extension filters only existing audit logs.
A privileged account is required to configure parameters related to the pgAudit extension.

Use the pgAudit extension to audit operations

Install the extension

CREATE EXTENSION pgaudit;

Audit read operations

Execute the following statement to audit all read operations on a database named pgaudit_testdb:

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

After the preceding statement is executed, all read operations such as SELECT operations on the pgaudit_testdb database are audited. Write operations such as the INSERT and UPDATE operations on the database are not audited.

Audit read and write operations

Execute the following statement to audit all read and operations on the pgaudit_testdb database:

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

After the preceding statement is executed, all read and write operations such as the SELECT, INSERT, and UPDATE operations on the pgaudit_testdb database are audited.

Disable log audit

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

Audit operations on a specified object

Execute the following statements to audit operations performed by the user of the pgaudit_testdb database, create a table named test_audit, and then grant all permissions on the table to the 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 preceding statements are executed, only all operations on the test_audit table in the pgaudit_testdb database are audited.

Uninstall the extension

DROP EXTENSION pgaudit;

References

For more information, see pgAudit documentation.