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 meets the following requirements:

Precautions

  • The pgAudit plug-in can generate a large amount of audit log data. The amount of audit log data that is generated varies based on the configuration 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 amount of audit log data. A large amount of audit log data can exhaust the disk space of your RDS instance.
  • After an object is renamed, new audit log records that are generated by the pgAudit plug-in for the object are associated with the new name of the object.

Enable or disable the pgAudit plug-in

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

Configure the pgAudit plug-in

After the pgAudit plug-in is enabled, you must perform the following operations to configure the plug-in:
  • Session audit logging: Use the pgaudit.log parameter to specify the types of statements whose logs you want to audit. The pgAudit plug-in can log all specified types of statements that are executed.
  • Object audit logging: Use the pgaudit.role parameter to specify the role whose logs you want to audit. If the role has the permissions on specific statements or inherits the permissions from another role, the pgAudit plug-in can log all statements that are executed by the role on tables and views.
Configure the pgAudit plug-in to audit session logs:
SET pgaudit.log = 'read, ddl';
Note In this example, the pgAudit plug-in logs all SELECT and DDL statements that are executed. For more information, see the pgAudit documentation.
Configure the pgAudit plug-in to audit session logs:
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 SELECT or DELETE statements that are executed by the auditor role on the account table are logged.

References

For more information, see the pgAudit documentation.