
I Tried Delivering Audit Logs Supported by Amazon Aurora DSQL to S3
This page has been translated by machine translation. View original
Introduction
On 2026-11-18, audit logging for Amazon Aurora DSQL was announced.
This feature records the SQL statements executed in a cluster and delivers them to S3 as structured JSON via CloudWatch Vended Logs. In this article, I set up delivery for a new cluster in the Tokyo region and checked the contents of the recorded records, the time it takes for them to reach S3, and how changes to the settings take effect.
Setting up delivery
To change the audit settings, the cluster must have a delivery source. So I created the delivery first and added the audit settings afterward.
Creating the delivery
Assuming the cluster and the S3 bucket have already been created, create the delivery source, the delivery destination, and the delivery, in that order.
REGION=ap-northeast-1
ACCOUNT_ID=111122223333
CLUSTER_ID=abcdefghijklmnopqrstuvwxyz
BUCKET=amzn-s3-demo-destination-bucket
aws logs put-delivery-source \
--name devio-audit-source \
--resource-arn "arn:aws:dsql:$REGION:$ACCOUNT_ID:cluster/$CLUSTER_ID" \
--log-type AUDIT_LOGS \
--region "$REGION"
aws logs put-delivery-destination \
--name devio-audit-destination \
--output-format json \
--delivery-destination-configuration destinationResourceArn="arn:aws:s3:::$BUCKET" \
--region "$REGION"
aws logs create-delivery \
--delivery-source-name devio-audit-source \
--delivery-destination-arn "arn:aws:logs:$REGION:$ACCOUNT_ID:delivery-destination:devio-audit-destination" \
--region "$REGION"
With AWS CLI 2.41.1, I was able to specify --log-type AUDIT_LOGS. After running create-delivery, a bucket policy allowing s3:PutObject for delivery.logs.amazonaws.com had been added to the bucket.
When I ran create-delivery without specifying the record fields, 23 fields were listed in recordFields in the response. The number of keys in the records that actually arrived was 20 to 23, depending on the statement class and the settings.
Checking the delivery source
You can check whether a delivery source exists with SQL. Connect as admin and run the following; while a delivery source exists, has_delivery returns t.
SELECT * FROM sys.audit_delivery_status();
region | has_delivery
----------------+--------------
ap-northeast-1 | t
When I checked about 15 seconds after create-delivery, has_delivery was t.
Audit settings
The statement classes to record are selected per role with SQL.
How to write the settings
Role-level values are set with sys.set_audit_config, and the cluster-wide baseline is set with ALTER SYSTEM.
SELECT sys.set_audit_config('admin', 'dsql_audit.log', 'ddl,role,write');
SELECT sys.set_audit_config('admin', 'dsql_audit.log_rows', 'on');
ALTER SYSTEM SET dsql_audit.log = 'ddl,role,write';
SHOW dsql_audit.log;
The return value of set_audit_config is the value that was set. For a role that has a role-level value set, that value is applied instead of the baseline.
Per-role settings and recorded results
With the baseline set to ddl,role,write, I ran the following 6 statements from 5 roles.
- A SELECT that fetches rows
- SELECT upper(...)
- An INSERT with literals
- An INSERT with bind parameters
- An UPDATE with bind parameters
- DELETE
I mapped the same IAM role to the 5 DB roles with AWS IAM GRANT. When connecting, I specified the DB role name to use.
| DB role | Setting | Records | Details |
|---|---|---|---|
| role_all | all, log_parameter = on, log_rows = on | 6 | READ 2 (SELECT upper(...) is also READ), WRITE 4. The parameters of the INSERT with bind parameters is 102,role_all-bound-secret,22, and the parameters of the statement with literals is <none>. rows_affected is present |
| role_nostmt | all, log_statement = off, log_parameter = on | 6 | statement_text and parameters are <not logged>. The class, command, acting principal, and time remain |
| role_replace | read only | 2 | Only READ 2. WRITE is 0; the role's value replaced the baseline |
| role_none | none | 0 | |
| role_base | No individual setting | 4 | The baseline applied, giving WRITE 4 (INSERT 2, UPDATE 1, DELETE 1). SELECT is 0. parameters is <not logged>, and there is no rows_affected |
To keep the values of bind parameters, set log_parameter to on. I additionally set log_parameter_max_size = 8 (bytes) for role_all. When I ran the INSERT with bind parameters twice, the long value was recorded as 601,<long param suppressed>,1 and the short value as 602,short,2.
Creating roles, configuring them, and statements run
Here I show only the steps for role_all. The other roles were created with the same steps, changing only the value passed to set_audit_config as shown in the "Setting" column of the table. The orders table is the one created with the CREATE TABLE in "Example records".
-- Run as admin
CREATE ROLE role_all WITH LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO role_all;
AWS IAM GRANT role_all TO 'arn:aws:iam::111122223333:role/my-dsql-role';
ALTER SYSTEM SET dsql_audit.log = 'ddl,role,write';
SELECT sys.set_audit_config('role_all', 'dsql_audit.log', 'all');
SELECT sys.set_audit_config('role_all', 'dsql_audit.log_parameter', 'on');
SELECT sys.set_audit_config('role_all', 'dsql_audit.log_rows', 'on');
Connect as role_all and run the following 6 statements. Because \bind is used, run them from psql (verified with psql 18.6).
SELECT id, status FROM orders WHERE id = 1;
SELECT upper('role_all-function-call');
INSERT INTO orders VALUES (101, 'role_all-literal', 11);
INSERT INTO orders VALUES ($1, $2, $3) \bind 102 'role_all-bound-secret' 22 \g
UPDATE orders SET status = $1 WHERE id = $2 \bind 'role_all-updated' 101 \g
DELETE FROM orders WHERE id = 102;
When connecting, choose the DB role with --username. I used a token as the password. For roles other than admin, I created it with aws dsql generate-db-connect-auth-token, and for admin with aws dsql generate-db-connect-admin-auth-token.
Roles that can change the settings
| Connected DB role | Statement run | Result |
|---|---|---|
| role_base (not a member of pg_dsql_audit_admin) | sys.set_audit_config, sys.reset_audit_config | permission denied for function set_audit_config, permission denied for function reset_audit_config |
| Same as above | SET dsql_audit.log = 'none', ALTER SYSTEM SET dsql_audit.log = 'none' | Both permission denied to set parameter "dsql_audit.log" |
| audit_admin (granted pg_dsql_audit_admin) | sys.set_audit_config('role_base', 'dsql_audit.log', 'all') | Succeeded. FUNCTION and ROLE records were recorded with session_user: audit_admin |
| db_admin (pg_dsql_admin, CREATEROLE) | sys.set_audit_config, sys.reset_audit_config | Both permission denied. CREATE ROLE created_by_dba succeeded and was recorded in the ROLE class |
Until the settings take effect
Even after changing a setting, it was sometimes not reflected in the records immediately.
| Condition | Change | Result |
|---|---|---|
| New connection (SHOW at 5-second intervals) | Set ddl,role,write for admin (01:39:34Z) | Returned the new value at 01:40:28Z, about 54 seconds later |
| Maintained connection (INSERT every 10 seconds, 18 times, about 3 minutes) | Changed role_none from none to write (02:21:51Z) | All 18 succeeded, but there were 0 audit records (the 15 run after the change completed were not recorded either) |
| New connection each time, INSERT (18 times), starting right after the change | Same as above | All 18 succeeded, with 17 records. The one at 02:22:00.6Z was not recorded |
| New connections (40) at 3-second intervals, running SHOW and INSERT on each | Changed role_none from none to write (02:53:53Z) | Connections 1–9 were none, 10–27 were write, 28–40 were none. The SHOW value and whether a record existed matched for all 40 |
The documentation says that configuration changes apply to new connections and can take several minutes to take effect. In my measurements too, a connection maintained for about 3 minutes still had the old setting even after the change was complete. Even with new connections, the old setting was sometimes returned about 6 minutes after the change.
Record contents
Example records
You can read the records that arrived with the following commands.
aws s3 ls "s3://$BUCKET/AWSLogs/$ACCOUNT_ID/dsql/" --recursive
aws s3 cp "s3://$BUCKET/<object-key>" - | gunzip
The object key had the following form.
AWSLogs/111122223333/dsql/ap-northeast-1/2026/11/19/01/111122223333_dsql_audit_logs_ap-northeast-1_20261119T01Z_<8桁>.log.gz
The hourly directory is determined not by the time the object was written, but by the hour of the record's event_timestamp (UTC). An object written at 02:00:21Z contained a record from 01:56, so it was placed under 01/.
The following two are a DDL record (CREATE TABLE) and a WRITE record (INSERT). The DDL record has object_type and object_name, while the WRITE record does not.
{
"resource_arn": "arn:aws:dsql:ap-northeast-1:111122223333:cluster/abcdefghijklmnopqrstuvwxyz",
"event_timestamp": 1795052452268,
"schema_version": "1.0",
"aws_region": "ap-northeast-1",
"cluster_id": "abcdefghijklmnopqrstuvwxyz",
"type": "SESSION",
"database": "postgres",
"session_id": "k2fq6nhv3cwdmb7xsr4tyl5gpa",
"iam_principal": "AROA123456789EXAMPLE",
"iam_arn": "arn:aws:iam::111122223333:role/my-dsql-role",
"session_user": "admin",
"user_id": "15579",
"client_application": "verify-phase-a",
"transaction_id": "m7tq4xw2bhkf5zdcvn3ryg6sla",
"statement_id": 1,
"substatement_id": 1,
"class": "DDL",
"command": "CREATE TABLE",
"object_type": "TABLE",
"object_name": "public.orders",
"statement_text": "CREATE TABLE orders (id int PRIMARY KEY, status text, amount int);",
"parameters": "<not logged>",
"rows_affected": 0
}
{
"resource_arn": "arn:aws:dsql:ap-northeast-1:111122223333:cluster/abcdefghijklmnopqrstuvwxyz",
"event_timestamp": 1795052831044,
"schema_version": "1.0",
"aws_region": "ap-northeast-1",
"cluster_id": "abcdefghijklmnopqrstuvwxyz",
"type": "SESSION",
"database": "postgres",
"session_id": "c5hq2mgy4bwgdk3tsx7qnrmb4e",
"iam_principal": "AROA123456789EXAMPLE",
"iam_arn": "arn:aws:iam::111122223333:role/my-dsql-role",
"session_user": "role_all",
"user_id": "26393",
"client_application": "work-role_all",
"transaction_id": "a3kq7zr2mfxv4c6tybw5hnde4q",
"statement_id": 4,
"substatement_id": 1,
"class": "WRITE",
"command": "INSERT",
"statement_text": "INSERT INTO orders VALUES ($1, $2, $3) ",
"parameters": "102,role_all-bound-secret,22",
"rows_affected": 1
}
Records by class
| Statement run | Class | Command | object_type / object_name |
|---|---|---|---|
| CREATE TABLE (with a primary key) | DDL (2 records) | CREATE TABLE, CREATE INDEX | Present (public.orders, public.orders_pkey) |
| CREATE INDEX ASYNC, DROP INDEX | DDL | CREATE INDEX, DROP INDEX | Present (public.idx_orders_status) |
| INSERT, UPDATE, DELETE | WRITE | Same name | None |
| SELECT (including SELECT upper(...) and SELECT 1) | READ | SELECT | None |
| CREATE ROLE, GRANT (granting privileges), GRANT ROLE (granting a role) | ROLE | CREATE ROLE, GRANT, GRANT ROLE | Only GRANT ... ON orders has object_type TABLE. No object_name |
| Call to sys.set_audit_config | FUNCTION and ROLE (1 record each per call) | EXECUTE, ALTER ROLE | FUNCTION is FUNCTION / sys.set_audit_config |
| ALTER SYSTEM SET dsql_audit.log | DDL | ALTER SYSTEM | None |
| SET, BEGIN, COMMIT, ROLLBACK, SHOW | MISC | Same name | None |
| EXPLAIN SELECT ... | READ and MISC (2 records) | SELECT, EXPLAIN | None |
The announcement lists "table name" among the recorded fields, but object_name was populated only in DDL and FUNCTION records, not in WRITE and READ. You read the table name from statement_text. statement_text contains the full statement including values for a statement with literals, and for a statement with bind parameters it contains placeholders such as $1 as they are. When log_parameter is off, parameters is <not logged>.
Calls to sys.set_audit_config were recorded in the FUNCTION class even when function was not in the settings. All the FUNCTION records recorded during the verification were from this call. An INSERT inside a transaction that was rolled back with ROLLBACK was also recorded as WRITE.
Fields identifying the acting principal
I connected to multiple DB roles with the same IAM role. iam_arn was the same ARN for all of them, and session_user contained the DB role I connected as. Use session_user to distinguish DB roles. client_application contains the value of the environment variable PGAPPNAME.
When I ran GRANT app_user TO admin as admin, I became able to use SET LOCAL ROLE app_user, and the record of the INSERT afterward had session_user still as admin, with user_id being the OID of app_user.
Delivery to S3
Time to arrival
I ran an INSERT containing a unique marker, checked S3 at 5-second intervals, and measured the time from the start of the SQL to the LastModified of the object containing the marker. Each row is a single measurement.
| Trial | SQL start (UTC) | Object LastModified | Elapsed |
|---|---|---|---|
| 1 | 01:46:20.5 | 01:50:21 | About 240 seconds |
| 2 | 01:51:24.1 | 01:55:23 | About 239 seconds |
| 3 | 01:56:27.4 | 02:00:21 | About 234 seconds |
| 4 | 02:09:40.9 | 02:15:23 | About 342 seconds |
| 5 | 02:19:01.5 | 02:20:22 | About 81 seconds |
| 6 | 02:46:16.9 | 02:50:22 | About 245 seconds |
Objects were written on a 5-minute cycle. The LastModified of all 13 objects that arrived during the verification was 21 to 25 seconds after a 5-minute mark (:00, :05, ...), and no object was created for cycles with no records.
CloudWatch metrics
The metrics I could confirm with list-metrics were three: AuditLogRecordsGenerated, AuditLogRecordsByCategory (Category dimension), and AuditLogMeteredBytes. The namespace is AWS/AuroraDSQL, and you specify the ClusterId dimension.
AuditLogRecordsGenerated matched the number of records in S3, both in total and by class. AuditLogMeteredBytes was the number of generated records × 4,096 at every 5-minute data point.
Summary
I confirmed that audit logging for Aurora DSQL can be set up via a CloudWatch Vended Logs delivery.
Previously, you could record connection logs to DSQL with CloudTrail, but with audit logging now supported, you can store DB operation logs as well.
If you are adopting DSQL as a production DB with strict compliance requirements, please try this update.
