If you want to monitor and manage SQL statements that are executed on an ApsaraDB RDS for PostgreSQL instance in scenarios such as security compliance audit, performance analysis, and troubleshooting, you can use the SQL Explorer and Audit feature. The feature enables you to record and perform aggregate analysis on the execution of SQL statements. After this feature is enabled, SQL statements from database kernels, the accounts that are used to execute the SQL statements, IP addresses, and execution details are automatically recorded. Instance performance is not affected.
Prerequisites
Database Autonomy Service (DAS) Enterprise Edition is purchased by using your Alibaba Cloud account. For more information, see Enable and manage DAS Cost-efficient Edition and DAS Enterprise Edition.
After you enable DAS Enterprise Edition, you can use the SQL Explorer and Audit feature. In the ApsaraDB RDS console, you can only enable the most recent version of DAS Enterprise Edition that is supported in the current region. Different versions of DAS Enterprise Edition are supported in different regions. For more information, see Supported databases and regions.
Your RDS instance runs RDS High-availability Edition.
If you use the credentials of a RAM user to use the search feature, make sure that the AliyunRDSReadOnlyWithSQLLogArchiveAccess policy is attached to the RAM user. For more information about how to grant permissions to a RAM user, see Use RAM to manage ApsaraDB RDS permissions.
NoteYou can also create a custom policy to grant the RAM user the permissions to use the search feature, including the log export feature. For more information, see Use custom policies to grant a RAM user the permissions to use the search and export features in the SQL Explorer and Audit module.
Billing rules
For more information, see Billing.
After the SQL Explorer and Audit feature is enabled, ApsaraDB RDS stops billing for the SQL Audit feature. The SQL Explorer and Audit feature is billed based on the pricing of DAS Enterprise Edition. For more information, see Use the SQL Audit feature.
Feature description
Search: queries and exports information about the SQL statements that are executed. The information includes the database, status, and execution time.
SQL Explorer: diagnoses the SQL statements, troubleshoots performance issues, and analyzes business traffic.
Usage notes
If PgBouncer is enabled for your RDS instance, SQL statements that are executed by using PgBouncer are not recorded by the SQL Explorer and Audit feature.
Enable the SQL Explorer and Audit feature
- 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 .
If the SQL Explorer and Audit feature is not enabled, click Enable on the page that appears. In the dialog box that appears, enable DAS Enterprise Edition.
NoteIf the Search and SQL Explorer tabs are displayed on the page that appears, the SQL Explorer and Audit feature is enabled.
In the ApsaraDB RDS console, you can only enable the most recent version of DAS Enterprise Edition that is supported in the current region. The update to DAS Enterprise Edition introduces more flexible billing methods and reduces costs.
Query information on the Search tab
- 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 .
On the page that appears, click the Search tab. Then, specify the search conditions to query the relevant information. For more information about the search feature, see Search.
To export logs, click Export and configure the Exported Fields parameter in the Export SQL Records dialog box. You can configure the Export Time Range parameter to export logs that are generated both within and beyond a time range of 24 hours.
Query information on the SQL Explorer tab
- 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 .
On the page that appears, click the SQL Explorer tab to view the relevant information. For more information about the SQL Explorer feature, see Use the SQL Explorer feature.
Modify the storage duration of data generated by the SQL Explorer and Audit feature
After you reduce the storage duration of the data that is generated by SQL Explorer and Audit, DAS immediately deletes the SQL audit logs that are retained for a longer period of time than the storage duration. We recommend that you export and save SQL audit logs to your computer and then reduce the storage duration of the data that is generated by 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 of the page that appears, choose .
Click Service Settings.
In the Service Settings dialog box, modify the storage duration of the data generated by the SQL Explorer and Audit feature and submit the changes.
If you have enabled DAS Enterprise Edition V3, you can modify the storage duration for different sub-features.
NoteThe storage that is occupied by the data generated by the SQL Explorer and Audit feature is provided by DAS and does not consume the storage of your RDS instance.
Disable the SQL Explorer and Audit feature
After you disable the SQL Explorer and Audit feature, all logs that are generated by the feature are deleted. We recommend that you export and save the logs that are generated by the SQL Explorer and Audit feature to your computer and then disable the SQL Explorer and Audit feature. If you enable the SQL Explorer and Audit feature again, logs that are generated by the feature are recorded from the point in time at which the SQL Explorer and Audit feature is enabled again.
- 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 .
In the Logs section of the Search tab, click Export.
In the dialog box that appears, configure the Exported Fields and Export Time Range parameters, and click OK.
After the logs are exported, download the log file and save it to your computer.
The methods for downloading SQL audit logs vary based on the versions of DAS Enterprise Edition. You can download the SQL audit logs based on the actual situation in the ApsaraDB RDS console.
On the Search tab, click View Exported Logs to download the exported file.
On the Task list tab, click Download to download the exported log file.
In the Service Settings dialog box, turn off SQL insight and audit.
If you have enabled DAS Enterprise Edition V3, clear all sub-features of the SQL Explorer and Audit feature.
NoteIf you enable the audit log collection feature for your RDS instance in the CloudLens for RDS application of Simple Log Service, the SQL Explorer and Audit feature is automatically enabled for the RDS instance. Therefore, you must also disable the audit log collection feature for the RDS instance. For more information, see CloudLens for RDS.