When an application connects to a database, it can perform hundreds or thousands of SQL operations without anyone manually checking what happened inside the database. These operations may include reading customer information, creating orders, updating account details, changing permissions, or deleting records.
Application logs can tell you which API endpoint was called and whether a request succeeded. However, they do not always provide a complete record of the SQL statements that actually reached the database. This becomes a problem when you need to investigate suspicious activity, understand an unexpected data change, or demonstrate to an auditor which database operations were performed and by whom.
Database auditing helps solve this problem by recording SQL activity and storing the information in a location where it can be reviewed later.
Amazon Aurora DSQL has introduced a managed audit logging capability that records SQL statements executed on a cluster and delivers the resulting audit records to an Amazon S3 bucket. It also allows database administrators to decide which statement categories should be audited for individual database roles.
This is important because AWS CloudTrail and database audit logs serve different purposes. CloudTrail records AWS API activity and authenticated connection attempts, while Aurora DSQL audit logging records the SQL statements executed through database connections. Together, they provide a more complete picture of database access and activity. AWS documentation
In this article, we will understand how Aurora DSQL audit logging works, how to configure it, how to select SQL statements for auditing, and how to use the resulting records for security investigations and compliance monitoring.
What Is Database Auditing?
Database auditing is the process of recording activities performed against a database so that administrators and security teams can review them later.
A database audit record can answer important questions such as:
- Which database role executed a statement?
- Which AWS identity was associated with the connection?
- When did the statement execute?
- Which SQL command was executed?
- Which database object was affected?
- How many rows were changed?
- Which transaction contained the statement?
Consider an application that updates an order:
UPDATE orders
SET status = 'Shipped'
WHERE order_id = 1001;
Without database auditing, you might know that the application reported a successful order update. However, you may not have a separate database-level record showing the SQL statement, the database role that executed it, and the time at which it ran.
With audit logging enabled, Aurora DSQL can record details about the statement. Depending on your audit configuration, the record can include the SQL text, bound parameter values, database role, IAM identity, timestamp, transaction identifier, and affected row count.
This information is useful when investigating unexpected changes or reviewing access to sensitive database operations.
It is important to understand that auditing is different from application logging. Application logs describe what your application attempted to do, while database audit logs provide evidence of SQL activity that reached the database and was executed.
Understanding Aurora DSQL Audit Logging
Aurora DSQL audit logging has two main parts: audit log delivery and audit settings.
Audit log delivery determines where the audit records are sent. Aurora DSQL uses Amazon CloudWatch Vended Logs to deliver the records to an Amazon S3 bucket that you own.
Audit settings determine which SQL statements Aurora DSQL records. You configure these settings through SQL and can choose statement categories for individual database roles.
Both parts are required. Creating an S3 bucket alone does not enable auditing, and selecting SQL statement categories without configuring log delivery does not complete the setup.
The overall process looks like this:
- Create or select an Amazon S3 bucket for audit records.
- Configure CloudWatch Logs delivery for the Aurora DSQL cluster.
- Configure the database roles and statement categories to audit.
- Execute SQL statements covered by the audit configuration.
- Allow Aurora DSQL to generate and deliver audit records.
- Review the JSON records stored in S3.
- Use the records for investigation, reporting, or integration with security monitoring systems.
This separation gives administrators control over both the statements being recorded and the destination where the records are stored.
Understand the SQL Statement Categories
Aurora DSQL allows you to configure auditing by statement category rather than requiring you to enable every SQL statement immediately.
The available categories include the following:
| Category | What it records | Example |
|---|
| read | SELECT statements and certain COPY operations that read data | SELECT FROM customers |
| write | INSERT, UPDATE, DELETE, and relevant COPY operations | UPDATE orders |
| ddl | Data definition language statements | CREATE TABLE, ALTER TABLE |
| role | Role and permission management statements | GRANT, REVOKE, CREATE ROLE |
| function | Function calls | Calling a database function |
| misc | Statements that do not belong to other categories | Other supported utility statements |
| misc_set | SET statements | SET configuration |
These categories allow you to choose an auditing policy based on your application's security requirements.
For example, a financial application may need to audit data modifications and permission changes because those operations can directly affect financial records or database access.
A development environment may need broader auditing while investigating a specific issue. However, recording every read statement in a high-traffic production application can generate a large volume of audit records.
AWS recommends starting with the ddl, role, and write categories because they cover statements that can change data or permissions. You can add the read category when your compliance or investigation requirements justify recording read activity. AWS documentation
Configure Audit Log Delivery to Amazon S3
Before changing audit settings, prepare an S3 bucket that will receive the records.
For production environments, using a dedicated bucket for audit logs is a sensible approach. It makes it easier to control access, define retention policies, and separate audit records from normal application files.
You also need an AWS identity with the permissions required to configure CloudWatch log delivery and manage the destination.
The first step is to register the Aurora DSQL cluster as an audit log delivery source.
The following AWS CLI example creates a delivery source:
aws logs put-delivery-source \
--name my-cluster-audit-logs \
--resource-arn arn:aws:dsql:us-east-1:111122223333:cluster/cluster-id \
--log-type AUDIT_LOGS \
--region us-east-1
Replace the account ID, cluster ID, and Region with the values from your AWS environment.
Next, register the S3 bucket as a delivery destination:
aws logs put-delivery-destination \
--name my-audit-log-destination \
--output-format json \
--delivery-destination-configuration \
destinationResourceArn=arn:aws:s3:::my-audit-log-bucket \
--region us-east-1
The command returns a delivery destination ARN. You will need that ARN when creating the delivery connection.
Now connect the audit source to the S3 destination:
aws logs create-delivery \
--delivery-source-name my-cluster-audit-logs \
--delivery-destination-arn \
arn:aws:logs:us-east-1:111122223333:delivery-destination:my-audit-log-destination \
--region us-east-1
This establishes the delivery configuration between the Aurora DSQL cluster and the S3 bucket.
CloudWatch Logs handles the delivery process. The audit records are stored as gzip-compressed files, with one JSON audit record per line.
The delivery setup can take a few minutes to become active, so do not assume that records will appear immediately after creating the configuration. AWS setup documentation
Configure Which SQL Statements Should Be Audited
After configuring log delivery, you can choose which statement categories should be recorded.
Aurora DSQL provides the dsql_audit.log setting for this purpose.
For example, suppose your application connects using a database role named app_user. You want to audit schema changes, permission changes, and data modifications performed by that role.
Connect to the database using an account authorized to manage audit settings and execute:
SELECT sys.set_audit_config(
'app_user',
'dsql_audit.log',
'ddl,role,write'
);
This configures auditing for the app_user role.
The setting is role-specific. If your application uses multiple database roles, you need to configure each role according to your requirements.
For example, an application might use separate roles for API operations, reporting, background processing, and database administration. These roles may have different auditing requirements.
You can also configure a cluster-wide baseline using ALTER SYSTEM:
ALTER SYSTEM SET dsql_audit.log = 'ddl,role,write';
This setting applies to database roles that do not have their own value for the setting, including roles created later.
A cluster-wide baseline is useful when you want new database roles to follow a consistent audit policy automatically.
However, remember that a role-specific setting replaces the corresponding baseline value. If you configure a role with only write, you should not assume that it also inherits ddl and role from the cluster-wide setting.
Record Bound Parameters and Affected Row Counts
Recording the SQL statement text is useful, but some application queries use parameterized statements.
For example, an application may execute:
UPDATE orders
SET status = $1
WHERE order_id = $2;
The statement text tells you which operation was performed, but it does not show the actual values supplied by the application.
Aurora DSQL provides dsql_audit.log_parameter to control whether bound parameter values are recorded.
You can enable it for app_user:
SELECT sys.set_audit_config(
'app_user',
'dsql_audit.log_parameter',
'on'
);
You can also configure the maximum size of variable-length parameter values recorded in the audit log:
SELECT sys.set_audit_config(
'app_user',
'dsql_audit.log_parameter_max_size',
'4096'
);
This limits recorded variable-length values to 4 KB. Longer values are represented by a suppression marker rather than being recorded in full.
Another useful setting is dsql_audit.log_rows. When enabled, it records the number of rows read or changed by a statement in the rows_affected field.
SELECT sys.set_audit_config(
'app_user',
'dsql_audit.log_rows',
'on'
);
This can help when investigating an unexpected data modification.
For example, if an UPDATE statement was expected to change one order but the audit record shows a much larger affected-row count, that may indicate a problem with the application query or its conditions.
Bound parameters can contain sensitive information, so enable parameter logging only after reviewing your data protection requirements. Limiting parameter size does not automatically remove sensitive information from shorter values.
Verify That Audit Logging Is Working
After configuring audit delivery and SQL auditing, verify that Aurora DSQL is actually generating records.
First, check whether delivery is configured for the cluster:
SELECT *
FROM sys.audit_delivery_status();
The has_delivery column indicates whether an audit delivery source is configured for each Region.
Next, inspect the current audit setting for your database connection:
SHOW dsql_audit.log;
If the output is none, the current connection is not auditing SQL statements through this setting.
Remember that changes to audit settings apply to new connections and can take a few minutes to become effective. Reconnect after making changes before testing the configuration.
Now execute a statement covered by your audit policy:
CREATE TABLE audit_test (
id INT PRIMARY KEY
);
DROP TABLE audit_test;
These statements belong to the DDL category.
After allowing time for delivery, check the S3 bucket:
aws s3 ls \
s3://my-audit-log-bucket/AWSLogs/111122223333/dsql/ \
--recursive
To inspect a compressed audit file, use:
aws s3 cp \
s3://my-audit-log-bucket/path/to/audit-file.log.gz \
- | gunzip
Each line represents an individual JSON audit record.
Do not consider the configuration complete simply because the CLI command succeeded. Verify that the expected records actually arrive in S3 and contain the fields your security or compliance team needs.
Understand What an Audit Record Contains
Aurora DSQL audit records contain information about the SQL statement and the database session that executed it.
A record can include fields such as:
| Field | Purpose |
|---|
| event_timestamp | Time when the audit event occurred |
| cluster_id | Identifier of the Aurora DSQL cluster |
| database | Database associated with the statement |
| session_id | Database session identifier |
| iam_arn | AWS IAM identity associated with the connection |
| session_user | Database role used for the connection |
| client_application | Application name associated with the connection |
| transaction_id | Transaction identifier |
| class | SQL statement category |
| command | SQL command that was executed |
| statement_text | Recorded SQL statement text |
| parameters | Bound parameter values, when enabled |
| object_name | Database object associated with relevant statements |
| rows_affected | Number of rows read or changed, when enabled |
For example, an UPDATE statement may produce a record similar to this:
{
"database": "postgres",
"session_user": "app_user",
"client_application": "orders-service",
"class": "WRITE",
"command": "UPDATE",
"statement_text": "UPDATE orders SET status = $1 WHERE order_id = $2",
"parameters": "shipped,1001",
"rows_affected": 1
}
This example shows how the audit record can connect a database operation with the application role and client application responsible for it.
The complete record also contains other fields, including cluster and transaction information.
One important detail is that the statement text and parameter values depend on your audit settings. If statement logging is disabled, the corresponding text and parameter fields are not recorded. AWS audit record reference
Understand the Difference Between Aurora DSQL Audit Logs and CloudTrail
A common mistake is assuming that AWS CloudTrail already provides a complete record of everything happening inside an Aurora DSQL database.
CloudTrail is important, but it has a different responsibility.
CloudTrail records AWS API operations, such as cluster management activities, and authenticated connection attempts to Aurora DSQL. It helps identify which AWS identity performed an operation, when it happened, and where the request originated.
Aurora DSQL audit logging records SQL statements executed after a database connection has been established.
Consider an application administrator connecting to an Aurora DSQL cluster and executing:
DELETE FROM customers
WHERE customer_id = 1001;
CloudTrail can help establish the connection activity and the associated AWS identity. Database audit logging provides the SQL-level record of the DELETE operation, subject to the configured audit policy.
Both sources are useful during a security investigation.
CloudTrail helps investigate access to AWS resources and connection activity, while database audit logs help investigate SQL operations performed inside the database.
For a production security monitoring strategy, use them together rather than treating one as a replacement for the other. AWS CloudTrail documentation
Apply Different Audit Policies to Different Database Roles
Not every database role performs the same type of work. A production application may have separate roles for normal application requests, reporting, monitoring, and administrative operations.
Applying the same audit policy to every role can generate unnecessary records.
For example, a monitoring account may execute frequent health checks. Recording every read statement from that account could produce a large amount of data without adding much security value.
You can exclude a role from auditing with:
SELECT sys.set_audit_config(
'monitor_user',
'dsql_audit.log',
'none'
);
You can also audit all supported statement categories except reads:
SELECT sys.set_audit_config(
'reporting_user',
'dsql_audit.log',
'all,-read'
);
Be careful when using role-specific settings. A role's configured value replaces the cluster-wide baseline for that setting.
Only authorized administrators can change audit settings. Aurora DSQL provides the built-in pg_dsql_audit_admin role to separate audit administration from general database administration.
This separation is useful in environments where the people managing database objects should not automatically have permission to change audit policies.
Aurora DSQL also records changes to audit settings, making it possible to review when an auditing policy was modified and who made the change.
Important Production Considerations
Enabling audit logging is not the end of an auditing strategy. You also need to protect the records, manage their volume, and make sure the configuration continues working as your database environment changes.
First, review the information stored in audit records. SQL statements and bound parameters may contain customer identifiers, account information, or other sensitive values. Restrict access to the S3 bucket and use appropriate encryption, retention, and access policies.
Second, estimate the expected audit volume. Recording every SELECT statement in a high-traffic application can generate considerably more records than auditing only data modifications and permission changes.
Third, monitor the audit delivery process. A cluster can have audit settings configured while its delivery destination is missing or incorrectly configured. Check delivery status and investigate missing records rather than assuming that enabled settings guarantee successful delivery.
Fourth, review audit settings whenever you create new database roles. A cluster-wide baseline helps provide consistent coverage for roles that do not have individual overrides.
Finally, include restored and multi-Region clusters in your operational procedures. When a backup is restored to a new cluster, audit settings are restored, but audit log delivery is not. You must configure delivery again before the restored cluster generates audit records. For multi-Region clusters, audit settings are shared across the cluster group, but delivery must be configured separately for each Region.
Common Mistakes to Avoid
One common mistake is configuring SQL audit settings before setting up log delivery. Aurora DSQL requires delivery to be configured before you can change audit settings.
Another mistake is enabling every statement category without estimating the resulting log volume. This can increase DPU, CloudWatch Logs, and S3 storage costs.
Some teams also forget that audit settings apply to new connections. They change a role's configuration and immediately test using an existing connection, then assume the setting has not worked. Reconnecting and allowing time for the changes to apply is part of the verification process.
Recording bound parameters without reviewing their contents is another risk. Parameter values can contain sensitive application data, so the security team should decide whether they are necessary.
Finally, do not treat an S3 bucket as a complete audit solution by itself. You still need access controls, retention rules, monitoring, and a process for searching and reviewing records.
Summary
Amazon Aurora DSQL audit logging provides a managed way to record SQL statements executed on a database cluster and deliver the resulting JSON records to Amazon S3.
It allows you to select statement categories for individual database roles, record SQL text, optionally capture bound parameters and affected row counts, and associate database activity with the relevant database role and AWS IAM identity.
The key to using it effectively is to configure both audit settings and log delivery. You should also verify that records reach S3, protect the information they contain, and monitor the cost and volume of generated logs.
For production environments, start with the statement categories that change data or permissions, then expand auditing based on your security and compliance requirements.
Most importantly, combine Aurora DSQL audit logs with CloudTrail rather than relying on either source alone. This gives your team visibility into both AWS-level access activity and the SQL operations performed inside the database.
Join the conversation! Your thoughts help the community grow.