How to use the pgAudit extension for audit logging-PolarDB(PolarDB)-阿里云帮助中心

pgAudit generates detailed audit logs for PolarDB for PostgreSQL (Compatible with Oracle). Use it to meet compliance requirements for government, finance, and ISO certifications, trace data access, investigate incidents, and analyze database activity.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for PostgreSQL (Compatible with Oracle) cluster running a supported minor engine version:
- Oracle syntax compatible 2.0: minor engine version 2.0.14.5.1.0 or later
- Oracle syntax compatible 1.0: minor engine version 2.0.11.9.25.0 or later
A privileged account to set extension parameters

To check your minor engine version, run SHOW polardb_version; or view it in the console. If your cluster does not meet the version requirement, upgrade the minor engine version.

Install the extension

CREATE EXTENSION pgaudit;

Configure audit logging

Audit read operations

To audit all read operations in a database:

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

SELECT and other read operations are audited. Write operations such as INSERT and UPDATE are not.

Audit read and write operations

To audit both read and write operations:

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

SELECT, INSERT, UPDATE, and other read and write operations are all audited.

Warning

Broad audit configurations can generate a large volume of logs and make it harder to identify important events. Use the narrowest scope that satisfies your compliance requirements, and monitor storage consumption.

Disable auditing

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

Audit operations on a specific object

Object-level auditing lets you track activity on individual tables or other database objects, rather than all operations in a database. To set this up:

Create a dedicated audit role.
```
CREATE USER audit_role;
```
Assign the audit role to the database. pgAudit audits operations only on objects granted to this role.
```
ALTER DATABASE pgaudit_testdb SET pgaudit.role = 'audit_role';
```
Create the table you want to audit.
```
CREATE TABLE test_audit (id INT);
```
Grant permissions on the table to the audit role.
```
GRANT ALL ON test_audit TO audit_role;
```

After completing these steps, only operations on test_audit in pgaudit_testdb are audited.

View audit logs

Audit logs are available in SQL Explorer.

Uninstall the extension

DROP EXTENSION pgaudit;

What's next

For the full list of pgAudit configuration options, see the pgAudit documentation.