Getting started with audit logging
In this guide, you set up audit log delivery for an Aurora DSQL cluster, choose which statements to audit, and confirm that audit records arrive in your Amazon S3 bucket.
Prerequisites
Before you begin, confirm the following:
-
You've created an Aurora DSQL cluster, and the cluster has finished creating. For a multi-Region cluster, you've also finished setting up the multi-Region configuration. For instructions, see Step 1: Create an Aurora DSQL single-Region cluster or Create a multi-Region cluster.
-
You've created an Amazon S3 bucket to receive the audit records. Consider a bucket that you use only for audit logs, so that you can control who can access the records and how long you keep them. For more information, see Creating a general purpose bucket and Bucket policies for Amazon S3 in the Amazon S3 User Guide.
-
Your IAM identity has the permissions listed in Caller permissions. To run the AWS CLI examples in this guide, install the AWS CLI and configure it with credentials for that identity.
-
You can connect to the cluster as a database role that can change audit settings, such as the built-in
adminrole. For more information, see Audit administration roles.
Setting up audit log delivery
In this step, you connect your cluster to your Amazon S3 bucket. You register the cluster as a delivery source and the bucket as a delivery destination, and then create a delivery that connects them. For a multi-Region cluster, set up delivery for the cluster in each AWS Region. For more information, see Multi-Region audit logging configuration.
Confirm that delivery is set up
Connect to your cluster and run the following SQL query. The query returns one row for each AWS Region of the cluster:
SELECT * FROM sys.audit_delivery_status();
The has_delivery column shows t (true) for each Region where
the cluster has a delivery source, and f (false) for each Region where it
doesn't. Audit records reach your bucket only when a delivery also connects the delivery
source to a delivery destination. After you set up delivery, it can take a few minutes for the
query to show the change.
The following example output shows a cluster that has a delivery source:
region | has_delivery -----------+-------------- us-east-1 | t (1 row)
The following example output shows a cluster that doesn't have a delivery source. If the
query shows f for a Region, set up delivery for the cluster in that Region.
region | has_delivery -----------+-------------- us-east-1 | f (1 row)
Creating an audit administrator role
This step is optional. By default, the built-in admin role can change audit settings. If your
compliance requirements call for separation of duties, create a dedicated audit administrator
role before you continue. For instructions on creating the role, see Audit administration roles.
To also audit roles that you create later, such as roles that a database administrator
creates, we recommend that you set a cluster-wide baseline with ALTER SYSTEM. For
more information, see Setting a cluster-wide baseline.
Setting up audit logging
If you created an audit administrator role in the previous step, connect to your cluster
as that role. Otherwise, connect as admin.
Then choose what to audit for each database role. The following SQL example audits the
DDL statements, role and privilege changes, and writes of the admin role:
SELECT sys.set_audit_config('admin', 'dsql_audit.log', 'ddl,role,write');
To also record the number of rows that each statement read or changed, turn on
dsql_audit.log_rows for the role:
SELECT sys.set_audit_config('admin', 'dsql_audit.log_rows', 'on');
To audit other roles, run the same statements with their names. Changes to audit settings apply to new connections, and can take a few minutes to apply. For all audit settings, see Configuring audit settings.
Confirm your audit settings
Run the following SQL query to list the audit settings that you set for each role:
SELECT r.rolname, s.setconfig FROM pg_db_role_setting s JOIN pg_roles r ON r.oid = s.setrole;
The following example output shows the audit settings from the previous examples:
rolname | setconfig
---------+----------------------------------------------------------
admin | {"dsql_audit.log=ddl,role,write",dsql_audit.log_rows=on}
(1 row)
To see the audit settings that apply to your connection, reconnect to your cluster as
admin, and then run the following command. If the output doesn't show your
changes, wait a few minutes and reconnect.
SHOW dsql_audit.log;
The following example output shows the setting from the previous examples. If the output
is none, Aurora DSQL doesn't audit the statements of your connection.
dsql_audit.log ---------------- ddl,role,write (1 row)
Verifying that audit records arrive
Reconnect to your cluster as admin, and run a statement that your audit
settings cover. For
example, the following SQL statements create and drop a table, which generates
DDL records:
CREATE TABLE audit_test (id int PRIMARY KEY); DROP TABLE audit_test;
To verify that the audit records arrived, run the following AWS CLI command to list the
audit log files in your bucket. Replace
amzn-s3-demo-destination-bucket with the name of your bucket, and
111122223333 with your AWS account ID. It can take several
minutes for records to arrive. If the output doesn't include any files, wait a few minutes
and run the command again.
aws s3 ls s3://amzn-s3-demo-destination-bucket/AWSLogs/111122223333/dsql/ --recursive
To print the audit records of a file, run the following AWS CLI command. Replace
object-key with the key of a file from the output of the previous
command:
aws s3 cp s3://amzn-s3-demo-destination-bucket/object-key- | gunzip
Each line of the output is one audit record in JSON format. For an example record and descriptions of the fields, see Understanding audit records. If records don't arrive, see Troubleshooting missing audit records.
Cleaning up
If you set up audit logging only to try it, turn it off to avoid ongoing charges.
First, connect to your cluster as admin, and reset the audit settings that
you chose in Setting up audit logging. Reset them before you delete the delivery,
because Aurora DSQL accepts changes to audit settings only while delivery is set up:
SELECT sys.reset_audit_config('admin', 'dsql_audit.log'); SELECT sys.reset_audit_config('admin', 'dsql_audit.log_rows');
Then run the following AWS CLI command to find the ID of the delivery. Replace
region with the AWS Region of your cluster:
aws logs describe-deliveries \ --regionregion
Run the following AWS CLI commands to delete the delivery, the delivery source, and the
delivery destination. Replace delivery-id with the ID of the
delivery from the output of the previous command:
aws logs delete-delivery \ --iddelivery-id\ --regionregionaws logs delete-delivery-source \ --namemy-cluster-audit-logs\ --regionregionaws logs delete-delivery-destination \ --namemy-audit-log-destination\ --regionregion
To avoid charges for storing the audit records, delete the audit log files from your bucket. If you created the bucket only for this guide, you can delete the bucket.
Managing audit log delivery
Listing deliveries
To list the deliveries in a Region, run the following AWS CLI command. Replace
region with the AWS Region of your cluster:
aws logs describe-deliveries \ --regionregion
The output includes the ID and the delivery source name of each delivery. You need the ID to delete a delivery.
Turning off audit logging
To stop auditing statements, reset the audit settings. Reset them before you delete the
delivery, because Aurora DSQL accepts changes to audit settings only while delivery is set up.
Reset the settings of each role that you audit. The following SQL example resets the
dsql_audit.log setting of the app_user role:
SELECT sys.reset_audit_config('app_user', 'dsql_audit.log');
To stop delivering audit records, run the following AWS CLI commands to delete the
delivery and the delivery source. Replace delivery-id with the ID of
the delivery from the output of describe-deliveries,
my-cluster-audit-logs with the name of the delivery source, and
region with the AWS Region of your cluster:
aws logs delete-delivery \ --iddelivery-id\ --regionregionaws logs delete-delivery-source \ --namemy-cluster-audit-logs\ --regionregion
Deleting a cluster
When you delete a cluster, Aurora DSQL stops generating its audit records. Delete the delivery and the delivery source of the cluster, because they remain in CloudWatch Logs after you delete the cluster.