SQL Explorer and Audit for RDS PostgreSQL-ApsaraDB RDS(RDS)-阿里云帮助中心

For security compliance audits, performance analysis, and troubleshooting, you can use SQL Explorer and Audit to record and analyze the execution of SQL statements on your RDS PostgreSQL instance. When enabled, this feature automatically records SQL statements from the database kernel, along with the execution account, source IP address, and other details, with no impact on instance performance.

Prerequisites

You have purchased DAS Enterprise Edition.

SQL Explorer and Audit requires DAS Enterprise Edition. In the console, you can enable only the highest version of DAS Enterprise Edition that is available in the current region. Different DAS Enterprise Edition versions support different regions.
As a RAM user, you must have the AliyunRDSReadOnlyWithSQLLogArchiveAccess permission to use the audit feature. For information about how to grant permissions to a RAM user, see Use RAM to manage ApsaraDB RDS permissions.

Note
You can also use a custom policy to grant a RAM user permissions to use the search and export features. For more information, see Use a custom RAM policy to grant permissions for the search and export features of SQL Explorer and Audit.

Billing

For more information, see Billing details.

Note

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

Features

Audit: Query and export SQL statement execution history and related information, such as the database, execution status, and execution time.
Note
- Enabling or disabling the audit log on the SQL Explorer and Audit console page modifies the log_statement kernel parameter.
  - Enabling the audit log sets log_statement = all.
  - Disabling the audit log sets log_statement = ddl.
- You can also use the ModifySqlLogConfig API operation to enable or disable the audit log.
SQL Explorer: Perform health diagnostics, troubleshoot performance issues, and analyze traffic for SQL statements.

Limitations

If connection pooling with PgBouncer 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 left-side navigation pane, choose Autonomy Services > SQL Explorer and Audit.
Click Enable Audit Logs, select the features you want to enable, and click Submit.

On the enablement page, the Audit Scenario section lets you select SQL Log (cold storage with a configurable retention period) and Log Index (hot storage with a configurable retention period). The Insight Scenario section lets you select SQL Explorer (includes global SQL workload analysis, abnormal SQL detection, and traffic replay) and Security Audit (includes audit alerts and audit rule management).

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 left-side navigation pane, choose Autonomy Services > SQL Explorer and Audit. Use the Audit and SQL Explorer features as needed.

Change data retention period

Warning

If you reduce the data retention period, DAS immediately deletes SQL audit logs that exceed the new period. We recommend that you export and save your SQL audit logs to a local machine before you reduce the retention period.

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 left-side navigation pane, choose Autonomy Services > SQL Explorer and Audit.
Click Service Settings.
On the Service Settings page, modify the retention period and click Submit.

Note
DAS provides the storage for SQL Explorer and Audit data, which does not occupy your instance's storage space.

Disable SQL Explorer and Audit

Warning

Disabling SQL Explorer and Audit deletes its logs. We recommend that you export and save the logs to a local machine before you disable the feature. If you re-enable SQL Explorer and Audit, logs are recorded only from the time you re-enable it.

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 left-side navigation pane, 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 a mix of hot and cold storage, you must select a CSV Separator when you export data.
Configure the export task. After the task is complete, click Task list, download the exported file, and save it to a secure location.
Click Service Settings to disable SQL Explorer and Audit.

If you have DAS Enterprise Edition enabled, clear the checkboxes for all SQL Explorer and Audit features, and then click Submit.
Important
- About one hour after you disable the SQL Explorer and Audit feature, the system releases the storage space for its data.
- If you enabled audit log collection for your RDS PostgreSQL instance in CloudLens for RDS of Simple Log Service, the system automatically enables SQL Explorer and Audit for that instance. In this case, you must also disable audit log collection for the database instance. For more information, see CloudLens for RDS.

FAQ

Q: Why can't I find failed SQL statements for my RDS PostgreSQL instance?

A: For RDS PostgreSQL instances, 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 does the database name in the log list differ from the one in the SQL statement?

A: This discrepancy can occur due to user input or query designs like cross-database queries or dynamic SQL.

Q: Why has the SQL Audit entry disappeared from the instance console?

A: Due to a feature update, the entry for the latest version has been renamed to SQL Explorer and Audit.

Q: Can I still enable the old version of SQL Audit?

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