Help Center/ Relational Database Service_RDS for PostgreSQL/ User Guide/ Log Management/ Enabling SQL Audit
Updated on 2026-10-16 GMT+08:00
View PDF
Share

Enabling SQL Audit

Scenarios

After SQL audit is enabled for RDS for PostgreSQL DB instances, the system records SQL operations and uploads logs every half an hour or when the size of a single record reaches 100 MB. The generated audit logs are stored in OBS. If there is not enough free backup space available for generated audit logs, the additional space required is billed.

Precautions

  • SQL audit is disabled for DB instances by default because enabling it increases database loads.
  • To ensure good performance, SQL audit uses the Coordinated Universal Time (UTC) format and is not affected by the time zone configuration.
  • When you enable SQL audit, the system automatically installs the pgAudit plug-in for a specific database. If you want to audit another database after SQL audit is enabled, manually install pgAudit for this database. For details, see pgAudit.
  • After SQL audit is enabled, READ, WRITE, and DDL SQL statements are logged by default. To adjust the audit scope, change the value of pgaudit.log on the Parameters page. For details, see Modifying Parameters of an RDS for PostgreSQL Instance.

Supported Database Versions

Only the following versions support SQL audit. If your DB engine version is too low, upgrade it to the latest version by referring to Upgrading the Minor Version or Upgrading the Major Version of a DB Instance on the Console.
  • Latest minor versions of RDS for PostgreSQL 12 and 13
  • All versions of RDS for PostgreSQL 14 and above

Performance

The pgAudit impact on database performance depends on how many audit logs there are and how often they are generated. More audit logs mean more impact on the database performance. pgAudit can decrease the database performance by about 20%. You need to configure parameters for pgAudit based on your workloads to achieve a balance between audit requirements and database performance.

Procedure

  1. Log in to the RDS console.
  2. Click in the upper left corner and select a region.
  3. On the Instances page, click the target instance name to go to the Summary page.
  4. In the navigation pane, choose Logs.
  5. Click the SQL Audit Logs tab and click Set SQL Audit.
  6. In the displayed dialog box, set the number of days for storing SQL audit logs, choose a database, and click OK.

    Audit logs can be retained from 1 to 3,660 days and are retained for 7 days by default.

    Figure 1 Setting SQL audit

  7. To disable SQL audit, toggle off the Audit Logging switch, select the confirmation check box, and click OK.

    After SQL audit is disabled, all audit logs will be deleted immediately and cannot be recovered. Exercise caution when performing this operation.

    Figure 2 Disabling SQL audit

  1. Log in to the RDS console.
  2. Click in the upper left corner and select a region.
  3. On the Instances page, click the target instance name to go to the Summary page.
  4. In the navigation pane, choose Logs.
  5. On the SQL Audit Logs tab page, click Ingest Logs to LTS in the upper right corner.
  6. In the displayed dialog box, select Ingest Logs to LTS, select Audit log under Log Types, select a log group and log stream, and click OK.

    Figure 3 Enabling audit log ingestion to LTS

  7. If audit log ingestion to LTS is enabled, you can switch to the analysis view in the upper right corner of the page to see more details.

    Figure 4 List view
    Figure 5 Analysis view
    • On the Log Search tab page, you can view structured fields and index configuration fields under Quick Analysis as well as view and download log contents. For more information, see Searching and Analyzing Logs.

      For explanations of the fields on the Raw Logs page in the analysis view, see Table 1.

      Table 1 LTS audit log field description

      Parameter

      Description

      _resource_id

      Resource ID. Fixed value: DBS_AUDIT_LOG. Ignore this parameter.

      _resource_name

      Resource name. Fixed value: DBS_AUDIT_LOG. Ignore this parameter.

      _service_type

      Service type. Fixed value: POSTGRESQL.

      transaction_id

      Application name, for example, PostgreSQL JDBC Driver.

      audit_type

      Audit type. The value can be SESSION, OBJECT, or CLIENT_AUTHENTICATION.

      statement_id

      Unique statement ID for this session.

      sub_statement_id

      ID of each substatement in the main statement.

      class_or_result

      Type of the operation executed.

      • class values: See the pgaudit.log options (including READ and ROLE).
      • result values: SUCCESS and FAIL

      pid

      Process ID.

      statement_start_time

      Statement start timestamp, in us.

      application_name

      Application name, such as PSQL and JDBC.

      user_name

      Login username.

      database_name

      Name of the database the user logged in to.

      remote_host

      Host address.

      command

      Type of the executed SQL command, such as ALTER TABLE and SELECT.

      object_type

      Object type, such as TABLE, INDEX, and VIEW.

      object_name

      Object name.

      sql_text

      Content of the SQL statement executed at the backend.
    • On the Charts tab page, you can view scenario-specific data in multiple chart types, including tables, bar charts, and line charts. For more information, see Statistical Charts.
    • On the Log Analysis tab page, you can search for and analyze collected log data. For more information, see Log Search and Analysis.
    • On the Real-Time Logs tab page, you can view logs reported in real time to quickly search for and analyze log data. For more information, see Viewing Real-Time Logs.

Parent Topic: Log Management