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. |
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_catalogschema.
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;