This topic describes how to use the pgAudit plug-in in ApsaraDB RDS for PostgreSQL.The pgAudit plug-in provides audit logs, which must comply with requirements in the public service and financial sectors or ISO requirements. Audit logs help you analyze faults and behavior on your RDS instance to obtain information about data queries.

Prerequisites

Your RDS instance runs one of the following PostgreSQL versions:
  • PostgreSQL 13
  • PostgreSQL 12
  • PostgreSQL 11
  • PostgreSQL 10

Precautions

  • The pgAudit plug-in can generate a large number of audit logs. The number of audit logs that are generated varies based on the settings of the pgAudit plug-in. Before you use the pgAudit plug-in to audit specific objects, we recommend that you evaluate these objects to prevent the pgAudit plug-in from generating a large number of audit logs that exhaust the disk space.
  • After an object is renamed, the new audit logs that are generated by the pgAudit plug-in for the object are associated with the new object name.

Enable or disable the pgAudit plug-in

  • Enable the pgAudit plug-in.
    CREATE EXTENSION pgaudit;
  • Disable the pgAudit plug-in.
    DROP EXTENSION pgaudit;

Configure audit logs

After the pgAudit plug-in is enabled, you must configure audit logging by using the following method:
  • Session audit logging: Use the pgaudit.log parameter to specify the types of statements that you want to audit. Session audit logging provides detailed logs of all the statements of the specified types that are executed.
  • Object audit logging: Use the pgaudit.role parameter to specify the role that you want to audit. If the role has the permissions on specific statements or inherits the permissions from another role, all the statements that are executed by the role on the tables and views are audited and logged.
Configure session audit logging
SET pgaudit.log = 'read, ddl';
Note In this example, session audit logging is used to log SELECT and DDL statements. For more information, see pgAudit documentation.
Configure object audit logging
SET pgaudit.role = 'auditor';
 
GTANT SELECT, DELETE
   ON public.account
   TO auditor;
Note In this example, the pgAudit plug-in audits the auditor role that has the SELECT and DELETE permissions on a table named account. All the SELECT or DELETE statements that are executed by the auditor role on the account table are logged.

References

For more information, see pgAudit documentation.