View a markdown version of this page

Configuring audit settings - Amazon Aurora DSQL

Configuring audit settings

Connect to your cluster as admin or as a member of pg_dsql_audit_admin. Then use SQL to choose what to audit for each database role.

Before you can change audit settings, set up audit log delivery for the cluster. For instructions on setting up audit log delivery, see Setting up audit log delivery.

Audit settings

The following table describes the audit settings that you can change.

Setting Default Description
dsql_audit.log none The classes of statements to audit. Specify a comma-separated list of classes, or all or none. To exclude a class, put a minus sign (-) in front of it, as in all,-misc. For the list of classes, see Statement classes.
dsql_audit.log_parameter off When on, records the values of the bound parameters of each statement in the parameters field.
dsql_audit.log_parameter_max_size 0 The maximum size, in bytes, of a text, bytea, or other variable-length parameter value to record. Longer values appear as <long param suppressed>. The default, 0, records values of any size. To reduce your log delivery and storage charges, AWS recommends 4096 (4 KB). Applies only when dsql_audit.log_parameter is on.
dsql_audit.log_rows off When on, records the number of rows that each statement read or changed in the rows_affected field.
dsql_audit.log_statement on When on, records the statement text in the statement_text field. When off, the statement_text and parameters fields contain <not logged>.

Statement classes

The following table describes the classes that you can use in the dsql_audit.log setting. Class names aren't case sensitive.

Class Statements
read SELECT, and COPY when the source is a table or a query.
write INSERT, UPDATE, DELETE, and COPY when the destination is a table.
function Function calls.
role Statements that manage roles and privileges, such as GRANT, REVOKE, CREATE ROLE, ALTER ROLE, DROP ROLE, AWS IAM GRANT, and AWS IAM REVOKE.
ddl All data definition language (DDL) statements that aren't in the role class.
misc Statements that aren't in another class, such as SET. This class includes misc_set.
misc_set SET statements.
Note

Keep the following in mind when you choose what to audit:

  • You choose what to audit by statement class and by database role.

  • Aurora DSQL always audits changes to audit settings, including the statement text and the parameter values of the change.

  • Statements that fail before they run, such as statements with syntax errors, have no audit records.

  • Aurora DSQL doesn't audit statements that access only the system catalog tables in the pg_catalog schema.

Setting a cluster-wide baseline

To set an audit setting for all database roles, use ALTER SYSTEM. The setting applies to every role that doesn't have its own value for the setting. This includes roles that you create later. Run ALTER SYSTEM outside an explicit transaction block.

The following SQL example audits DDL statements, role and privilege changes, and writes for all roles:

ALTER SYSTEM SET dsql_audit.log = 'ddl,role,write';

To remove a cluster-wide setting, reset it, as in the following SQL example:

ALTER SYSTEM RESET dsql_audit.log;

Setting audit settings for a role

To set an audit setting for a specific database role, use the sys.set_audit_config function. Specify the role, the setting, and the value.

The following SQL example audits DDL statements, role and privilege changes, and writes of the app_user role. It also records the bound parameter values and the row counts of the role's statements:

SELECT sys.set_audit_config('app_user', 'dsql_audit.log', 'ddl,role,write'); SELECT sys.set_audit_config('app_user', 'dsql_audit.log_parameter', 'on'); SELECT sys.set_audit_config('app_user', 'dsql_audit.log_rows', 'on');

To return a setting of a role to its default value, use the sys.reset_audit_config function. 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');

Excluding a role from audit logging

A cluster-wide baseline applies to every database role, including roles that run frequent, routine statements, such as monitoring or health-check roles. To reduce the volume of audit records, exclude these roles from auditing. To exclude a role, set its dsql_audit.log setting to none. The following SQL example excludes the monitor_user role:

SELECT sys.set_audit_config('monitor_user', 'dsql_audit.log', 'none');

To exclude only some classes of statements, set the classes to audit for the role. The role's value replaces the baseline, so include every class that you want to audit. The following SQL example audits all statements of the reporting_user role except reads:

SELECT sys.set_audit_config('reporting_user', 'dsql_audit.log', 'all,-read');

To audit the role with the cluster-wide baseline again, reset its setting, as in the following SQL example:

SELECT sys.reset_audit_config('monitor_user', 'dsql_audit.log');

Only admin and members of pg_dsql_audit_admin can change audit settings, so other roles can't exclude themselves. Aurora DSQL records every change to audit settings, so your audit trail shows who excluded a role and when. To check which roles you excluded, see Viewing audit settings.

How Aurora DSQL applies audit settings

The following details describe how Aurora DSQL applies audit settings to the statements of each connection.

  • Settings of the connecting role. Aurora DSQL applies the audit settings of the database role that you connect as. If you change the current role in a transaction, for example with SET LOCAL ROLE, the settings of the role that you connected as still apply.

  • Changes apply to new connections. Changes to audit settings apply to new connections, and can take a few minutes to apply. To make sure that a connection uses the latest settings, reconnect.

Viewing audit settings

Settings of your connection

To see the audit settings that apply to your current connection, run SHOW:

SHOW dsql_audit.log;
Settings of specific roles

To see the audit settings that you set for specific roles, query the pg_db_role_setting catalog:

SELECT r.rolname, s.setconfig FROM pg_db_role_setting s JOIN pg_roles r ON r.oid = s.setrole;