I Tried Delivering Audit Logs Supported by Amazon Aurora DSQL to S3

I Tried Delivering Audit Logs Supported by Amazon Aurora DSQL to S3

I delivered Amazon Aurora DSQL audit logs to S3 and checked the recorded SQL statements and record contents. Delivery took 81–342 seconds (6 measurements), READ and WRITE records had no table name field, and new connections sometimes used the old settings after about 6 minutes.
2026.11.19

This page has been translated by machine translation. View original

Introduction

On 2026-11-18, audit logging for Amazon Aurora DSQL was announced.

https://aws.amazon.com/jp/about-aws/whats-new/2026/11/amazon-aurora-dsql-audit-logging/

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.

https://docs.aws.amazon.com/aurora-dsql/latest/userguide/audit-logging-configure.html

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.

Share this article

AWSのお困り事はクラスメソッドへ