SQL Explorer and Audit for ApsaraDB RDS for PostgreSQL - ApsaraDB RDS

The SQL Explorer and Audit feature helps you monitor and manage SQL statements that are executed in an ApsaraDB RDS for PostgreSQL instance for compliance audits, performance analysis, or troubleshooting. After you enable this feature, it automatically records SQL statements from the database kernel, along with the execution account, IP address, and execution details. This process does not affect the performance of your instance.

Prerequisites

You have purchased DAS Enterprise Edition.
After you enable DAS Enterprise Edition, you can use the SQL Explorer and Audit feature. In the console, you can enable only the latest version of DAS Enterprise Edition that is supported in the current region. Different versions of DAS Enterprise Edition are supported in different regions.
If you are a Resource Access Management (RAM) user, you must grant the AliyunRDSReadOnlyWithSQLLogArchiveAccess permission to the RAM user to use the Audit feature. For more information, see Use RAM to manage ApsaraDB RDS permissions.
Note
You can also create custom policies to grant a RAM user the permissions to use the search (including export) feature. For more information, see Use custom policies to grant a RAM user the permissions to use the search (including export) feature of SQL Explorer and Audit.

Billing

For more information, see Billing details.

Note

After you enable SQL Explorer and Audit, billing for the original SQL Audit (Database Audit) feature stops. The SQL Explorer and Audit feature is billed as part of DAS Enterprise Edition.

Feature introduction

Audit: Query and export the execution history of SQL statements and related information, such as the database, execution status, and running time.
Note
- Enabling or disabling audit logs on the SQL Explorer and Audit page in the console changes the log_statement kernel parameter.
  - Enabling audit logs sets log_statement = all.
  - Disabling audit logs sets log_statement = ddl.
- You can also call the ModifySqlLogConfig API operation to enable or disable audit logs.
SQL Explorer: Diagnose the health of SQL statements, troubleshoot performance issues, and analyze service traffic.

Usage notes

If the PgBouncer connection pool is enabled for your instance, SQL statements executed through PgBouncer are not recorded by SQL Explorer and Audit.

Enable SQL Explorer and Audit

Note

You can enable only the latest version of SQL Explorer and Audit that your instance supports.

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the navigation pane on the left, choose Autonomy Services > SQL Explorer and Audit.
Click Enable Audit Logs, select the features to enable, and then click Submit.

Use SQL Explorer and Audit

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the navigation pane on the left, choose Autonomy Services > SQL Explorer and Audit. Use the Audit and SQL Explorer features as needed.

Modify the data storage duration for SQL Explorer and Audit

Warning

If you reduce the data storage duration for SQL Explorer and Audit, DAS immediately deletes all SQL audit logs that exceed the new storage duration. Export and save the SQL audit logs to a local disk before you reduce the storage duration.

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the navigation pane on the left, choose Autonomy Services > SQL Explorer and Audit.
Click Service Settings.
On the Service Settings page, change the storage duration and click Submit.
Note
The storage space for SQL Explorer and Audit data is provided by DAS and does not occupy the storage space of your database instance.

Disable the SQL Explorer and Audit feature

Warning

After you disable the SQL Explorer and Audit feature, the associated logs are deleted. You must export and save the logs to a local disk before you disable the feature. If you re-enable the feature, logs are recorded only from the time of re-enabling.

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the navigation pane on the left, choose Autonomy Services > SQL Explorer and Audit.
In the Logs area, click Export.
You can select the fields and time range to export. If your SQL Explorer and Audit service uses hot and cold tiered storage, you must also select a CSV Separator when you export the data.
Configure the export task. After the export is complete, click Task list, and then download and save the exported file.
Click Service Settings to disable SQL Explorer and Audit.
If you have enabled DAS Enterprise Edition, clear the checkboxes for all SQL Explorer and Audit features and click Submit.
Important
- About 1 hour after you disable the SQL Explorer and Audit feature, the system releases the storage space that was occupied by the SQL Explorer and Audit data.
- If you enable the audit log collection feature for your ApsaraDB RDS for PostgreSQL instance in the CloudLens for RDS application of Simple Log Service, the SQL Explorer and Audit feature is automatically enabled for the instance. Therefore, you must also disable the audit log collection feature for the database instance. For more information, see CloudLens for RDS.

FAQ

Q: Why can't I query failed SQL statements in an ApsaraDB RDS for PostgreSQL instance?

A: For an ApsaraDB RDS for PostgreSQL instance, failed SQL statements are recorded in the instance's error log, not the audit log. To query the error log, see View logs.

Q: Why are the database names displayed in the log list inconsistent with those in SQL statements?

A: The database name in the log list is retrieved from the session. The database name in an SQL statement is specified by the user and depends on the query design. For example, in cross-database queries or dynamic SQL, the two names may be inconsistent.

Q: Why did the SQL Audit entry disappear from the console?

A: The SQL Explorer and Audit feature was updated. The entry is now named SQL Explorer and Audit.

Q: Can I still enable an older version of SQL Audit?

A: You can enable only the latest version of SQL Explorer and Audit that your instance supports. For more information, see Product series and supported features.