Setting up the pgAudit extension Auditing database objects Excluding users or databases from audit logging Reference for pgAudit extension parameters

Using pgAudit to log database activity

Financial institutions, government agencies, and many industries need to keep audit logs to meet regulatory requirements. By using the PostgreSQL Audit extension (pgAudit) with your Aurora PostgreSQL DB cluster, you can capture the detailed records that are typically needed by auditors or to meet regulatory requirements. For example, you can set up the pgAudit extension to track changes made to specific databases and tables, to record the user who made the change, and many other details.

The pgAudit extension builds on the functionality of the native PostgreSQL logging infrastructure by extending the log messages with more detail. In other words, you use the same approach to view your audit log as you do to view any log messages. For more information about PostgreSQL logging, see Aurora PostgreSQL database log files.

The pgAudit extension redacts sensitive data such as cleartext passwords from the logs. If your Aurora PostgreSQL DB cluster is configured to log data manipulation language (DML) statements as detailed in Turning on query logging for your Aurora PostgreSQL DB cluster, you can avoid the cleartext password issue by using the PostgreSQL Audit extension.

You can configure auditing on your database instances with a great degree of specificity. You can audit all databases and all users. Or, you can choose to audit only certain databases, users, and other objects. You can also explicitly exclude certain users and databases from being audited. For more information, see Excluding users or databases from audit logging.

Given the amount of detail that can be captured, we recommend that if you do use pgAudit, you monitor your storage consumption.

The pgAudit extension is supported on all available Aurora PostgreSQL versions. For a list of pgAudit versions supported by Aurora PostgreSQL version, see Extension versions for Amazon Aurora PostgreSQL in the Release Notes for Aurora PostgreSQL.

Topics

Setting up the pgAudit extension
Auditing database objects
Excluding users or databases from audit logging
Reference for the pgAudit extension

Setting up the pgAudit extension

To set up the pgAudit extension on your Aurora PostgreSQL DB cluster, you first add pgAudit to the shared libraries on the custom DB cluster parameter group for your Aurora PostgreSQL DB cluster. For information about creating a custom DB cluster parameter group, see Working with parameter groups. Next, you install the pgAudit extension. Finally, you specify the databases and objects that you want to audit. The procedures in this section show you how. You can use the Amazon Web Services Management Console or the Amazon CLI.

You must have permissions as the rds_superuser role to perform all these tasks.

The steps following assume that your Aurora PostgreSQL DB cluster is associated with a custom DB cluster parameter group.

To set up the pgAudit extension

Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/.
In the navigation pane, choose your Aurora PostgreSQL DB cluster's Writer instance .
Open the Configuration tab for your Aurora PostgreSQL DB cluster writer instance. Among the Instance details, find the Parameter group link.
Choose the link to open the custom parameters associated with your Aurora PostgreSQL DB cluster.
In the Parameters search field, type shared_pre to find the shared_preload_libraries parameter.
Choose Edit parameters to access the property values.
Add pgaudit to the list in the Values field. Use a comma to separate items in the list of values.
Reboot the writer instance of your Aurora PostgreSQL DB cluster so that your change to the shared_preload_libraries parameter takes effect.
When the instance is available, verify that pgAudit has been initialized. Use psql to connect to the writer instance of your Aurora PostgreSQL DB cluster, and then run the following command.
```
SHOW shared_preload_libraries;
shared_preload_libraries 
--------------------------
rdsutils,pgaudit
(1 row) 
```
With pgAudit initialized, you can now create the extension. You need to create the extension after initializing the library because the pgaudit extension installs event triggers for auditing data definition language (DDL) statements.
```
CREATE EXTENSION pgaudit;
```
Close the psql session.
```
labdb=> \q
```
Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/.
Find the pgaudit.log parameter in the list and set to the appropriate value for your use case. For example, setting the pgaudit.log parameter to write as shown in the following image captures inserts, updates, deletes, and some other types changes to the log.

You can also choose one of the following values for the pgaudit.log parameter.
- none – This is the default. No database changes are logged.
- all – Logs everything (read, write, function, role, ddl, misc).
- ddl – Logs all data definition language (DDL) statements that aren't included in the ROLE class.
- function – Logs function calls and DO blocks.
- misc – Logs miscellaneous commands, such as DISCARD, FETCH, CHECKPOINT, VACUUM, and SET.
- read – Logs SELECT and COPY when the source is a relation (such as a table) or a query.
- role – Logs statements related to roles and privileges, such as GRANT, REVOKE, CREATE ROLE, ALTER ROLE, and DROP ROLE.
- write – Logs INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation (table).
Choose Save changes.
Open the Amazon RDS console at https://console.amazonaws.cn/rds/.
Choose your Aurora PostgreSQL DB cluster's writer instance from the Databases list to select it, and then choose Reboot from the Actions menu.

To setup pgAudit

To setup pgAudit using the Amazon CLI, you call the modify-db-parameter-group operation to modify the audit log parameters in your custom parameter group, as shown in the following procedure.

Use the following Amazon CLI command to add pgaudit to the shared_preload_libraries parameter.


aws rds modify-db-parameter-group \
   --db-parameter-group-name custom-param-group-name \
   --parameters "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot" \
   --region aws-region

Use the following Amazon CLI command to reboot the writer instance of your Aurora PostgreSQL DB cluster so that the pgaudit library is initialized.
```
aws rds reboot-db-instance \
    --db-instance-identifier writer-instance \
    --region aws-region
```
When the instance is available, you can verify that pgaudit has been initialized. Use psql to connect to the writer instance of your Aurora PostgreSQL DB cluster, and then run the following command.
```
SHOW shared_preload_libraries;
shared_preload_libraries 
--------------------------
rdsutils,pgaudit
(1 row) 
```
With pgAudit initialized, you can now create the extension.
```
CREATE EXTENSION pgaudit;
```
Close the psql session so that you can use the Amazon CLI.
```
labdb=> \q
```
Use the following Amazon CLI command to specify the classes of statement that want logged by session audit logging. The example sets the pgaudit.log parameter to write, which captures inserts, updates, and deletes to the log.
```
aws rds modify-db-parameter-group \
   --db-parameter-group-name custom-param-group-name \
   --parameters "ParameterName=pgaudit.log,ParameterValue=write,ApplyMethod=pending-reboot" \
   --region aws-region
```
You can also choose one of the following values for the pgaudit.log parameter.
- none – This is the default. No database changes are logged.
- all – Logs everything (read, write, function, role, ddl, misc).
- ddl – Logs all data definition language (DDL) statements that aren't included in the ROLE class.
- function – Logs function calls and DO blocks.
- misc – Logs miscellaneous commands, such as DISCARD, FETCH, CHECKPOINT, VACUUM, and SET.
- read – Logs SELECT and COPY when the source is a relation (such as a table) or a query.
- role – Logs statements related to roles and privileges, such as GRANT, REVOKE, CREATE ROLE, ALTER ROLE, and DROP ROLE.
- write – Logs INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation (table).
Reboot the writer instance of your Aurora PostgreSQL DB cluster using the following Amazon CLI command.
```
aws rds reboot-db-instance \
    --db-instance-identifier writer-instance \
    --region aws-region
```

Auditing database objects

With pgAudit set up on your Aurora PostgreSQL DB cluster and configured for your requirements, more detailed information is captured in the PostgreSQL log. For example, while the default PostgreSQL logging configuration identifies the date and time that a change was made in a database table, with the pgAudit extension the log entry can include the schema, user who made the change, and other details depending on how the extension parameters are configured. You can set up auditing to track changes in the following ways.

For each session, by user. For the session level, you can capture the fully qualified command text.
For each object, by user and by database.

The object auditing capability is activated when you create the rds_pgaudit role on your system and then add this role to the pgaudit.role parameter in your custom parameter parameter group. By default, the pgaudit.role parameter is unset and the only allowable value is rds_pgaudit. The following steps assume that pgaudit has been initialized and that you have created the pgaudit extension by following the procedure in Setting up the pgAudit extension.

Image of the PostgreSQL log file after setting up pgAudit.

As shown in this example, the "LOG: AUDIT: SESSION" line provides information about the table and its schema, among other details.

To set up object auditing

Use psql to connect to the writer instance of your Aurora PostgreSQL DB cluster.


psql --host=your-instance-name.aws-region.rds.amazonaws.com --port=5432 --username=postgrespostgres --password --dbname=labdb

Create a database role named rds_pgaudit using the following command.
```
labdb=> CREATE ROLE rds_pgaudit;
CREATE ROLE
labdb=> 
```
Close the psql session.
```
labdb=> \q
```
In the next few steps, use the Amazon CLI to modify the audit log parameters in your custom parameter group.

Use the following Amazon CLI command to set the pgaudit.role parameter to rds_pgaudit. By default, this parameter is empty, and rds_pgaudit is the only allowable value.


aws rds modify-db-parameter-group \
   --db-parameter-group-name custom-param-group-name \
   --parameters "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot" \
   --region aws-region

Use the following Amazon CLI command to reboot the writer instance of your Aurora PostgreSQL DB cluster so that your changes to the parameters take effect.
```
aws rds reboot-db-instance \
    --db-instance-identifier writer-instance \
    --region aws-region
```
Run the following command to confirm that the pgaudit.role is set to rds_pgaudit.
```
SHOW pgaudit.role;
pgaudit.role 
------------------
rds_pgaudit 
```

To test pgAudit logging, you can run several example commands that you want to audit. For example, you might run the following commands.


CREATE TABLE t1 (id int);
GRANT SELECT ON t1 TO rds_pgaudit;
SELECT * FROM t1;
id 
----
(0 rows)

The database logs should contain an entry similar to the following.


...
2017-06-12 19:09:49 UTC:...:rds_test@postgres:[11701]:LOG: AUDIT:
OBJECT,1,1,READ,SELECT,TABLE,public.t1,select * from t1;
...

For information on viewing the logs, see Monitoring Amazon Aurora log files.

To learn more about the pgAudit extension, see pgAudit on GitHub.

Excluding users or databases from audit logging

As discussed in Aurora PostgreSQL database log files, PostgreSQL logs consume storage space. Using the pgAudit extension adds to the volume of data gathered in your logs to varying degrees, depending on the changes that you track. You might not need to audit every user or database in your Aurora PostgreSQL DB cluster.

To minimize impacts to your storage and to avoid needlessly capturing audit records, you can exclude users and databases from being audited. You can also change logging within a given session. The following examples show you how.

Note

Parameter settings at the session level take precedence over the settings in the custom DB cluster parameter group for the Aurora PostgreSQL DB cluster's writer instance. If you don't want database users to bypass your audit logging configuration settings, be sure to change their permissions.

Suppose that your Aurora PostgreSQL DB cluster is configured to audit the same level of activity for all users and databases. You then decide that you don't want to audit the user myuser. You can turn off auditing for myuser with the following SQL command.


ALTER USER myuser SET pgaudit.log TO 'NONE';

Then, you can use the following query to check the user_specific_settings column for pgaudit.log to confirm that the parameter is set to NONE.


SELECT
    usename AS user_name,
    useconfig AS user_specific_settings
FROM
    pg_user
WHERE
    usename = 'myuser';

You see output such as the following.


 user_name | user_specific_settings
-----------+------------------------
 myuser    | {pgaudit.log=NONE}
(1 row)

You can turn off logging for a given user in the midst of their session with the database with the following command.


ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'none';

Use the following query to check the settings column for pgaudit.log for a specific user and database combination.


SELECT
    usename AS "user_name",
    datname AS "database_name",
    pg_catalog.array_to_string(setconfig, E'\n') AS "settings"
FROM
    pg_catalog.pg_db_role_setting s
    LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase
    LEFT JOIN pg_catalog.pg_user r ON r.usesysid = setrole
WHERE
    usename = 'myuser'
    AND datname = 'mydatabase'
ORDER BY
    1,
    2;

You see output similar to the following.


  user_name | database_name |     settings
-----------+---------------+------------------
 myuser    | mydatabase    | pgaudit.log=none
(1 row)

After turning off auditing for myuser, you decide that you don't want to track changes to mydatabase. You turn off auditing for that specific database by using the following command.


ALTER DATABASE mydatabase SET pgaudit.log to 'NONE';

Then, use the following query to check the database_specific_settings column to confirm that pgaudit.log is set to NONE.


SELECT
a.datname AS database_name,
b.setconfig AS database_specific_settings
FROM
pg_database a
FULL JOIN pg_db_role_setting b ON a.oid = b.setdatabase
WHERE
a.datname = 'mydatabase';

You see output such as the following.


 database_name | database_specific_settings
---------------+----------------------------
 mydatabase    | {pgaudit.log=NONE}
(1 row)

To return settings to the default setting for myuser, use the following command:


ALTER USER myuser RESET pgaudit.log;

To return settings to their default setting for a database, use the following command.


ALTER DATABASE mydatabase RESET pgaudit.log;

To reset user and database to the default setting, use the following command.


ALTER USER myuser IN DATABASE mydatabase RESET pgaudit.log;

You can also capture specific events to the log by setting the pgaudit.log to one of the other allowed values for the pgaudit.log parameter. For more information, see List of allowable settings for the pgaudit.log parameter.


ALTER USER myuser SET pgaudit.log TO 'read';
ALTER DATABASE mydatabase SET pgaudit.log TO 'function';
ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'read,function'

Reference for the pgAudit extension

You can specify the level of detail that you want for your audit log by changing one or more of the parameters listed in this section.

Controlling pgAudit behavior

You can control the audit logging by changing one or more of the parameters listed in the following table.

Parameter	Description
`pgaudit.log`	Specifies the statement classes that will be logged by session audit logging. Allowable values include ddl, function, misc, read, role, write, none, all. For more information, see List of allowable settings for the pgaudit.log parameter.
`pgaudit.log_catalog`	When turned on (set to 1), adds statements to audit trail if all relations in a statement are in pg_catalog.
`pgaudit.log_level`	Specifies the log level to use for log entries. Allowed values: debug5, debug4, debug3, debug2, debug1, info, notice, warning, log
`pgaudit.log_parameter`	When turned on (set to 1), parameters passed with the statement are captured in the audit log.
`pgaudit.log_relation`	When turned on (set to 1), the audit log for the session creates a separate log entry for each relation (TABLE, VIEW, and so on) referenced in a SELECT or DML statement.
`pgaudit.log_statement_once`	Specifies whether logging will include the statement text and parameters with the first log entry for a statement/substatement combination or with every entry.
`pgaudit.role`	Specifies the master role to use for object audit logging. The only allowable entry is `rds_pgaudit`.

List of allowable settings for the `pgaudit.log` parameter

Value	Description
none	This is the default. No database changes are logged.
all	Logs everything (read, write, function, role, ddl, misc).
ddl	Logs all data definition language (DDL) statements that aren't included in the `ROLE` class.
function	Logs function calls and `DO` blocks.
misc	Logs miscellaneous commands, such as `DISCARD`, `FETCH`, `CHECKPOINT`, `VACUUM`, and `SET`.
read	Logs `SELECT` and `COPY` when the source is a relation (such as a table) or a query.
role	Logs statements related to roles and privileges, such as `GRANT`, `REVOKE`, `CREATE ROLE`, `ALTER ROLE`, and `DROP ROLE`.
write	Logs `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, and `COPY` when the destination is a relation (table).

To log multiple event types with session auditing, use a comma-separated list. To log all event types, set pgaudit.log to ALL. Reboot your DB instance to apply the changes.

With object auditing, you can refine audit logging to work with specific relations. For example, you can specify that you want audit logging for READ operations on one or more tables.

Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Scheduling maintenance with the pg_cron extension

Using pglogical to synchronize data