Using pgAudit to log database activity - Amazon Relational Database Service
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

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 RDS for PostgreSQL DB instance, 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 RDS for PostgreSQL database log files.

The pgAudit extension redacts sensitive data such as cleartext passwords from the logs. If your RDS for PostgreSQL DB instance is configured to log data manipulation language (DML) statements as detailed in Turning on query logging for your RDS for PostgreSQL DB instance, 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 RDS for PostgreSQL versions. For a list of pgAudit versions supported by available RDS for PostgreSQL versions, see Extension versions for Amazon RDS for PostgreSQL in the Amazon RDS for PostgreSQL Release Notes.

Setting up the pgAudit extension

To set up the pgAudit extension on your RDS for PostgreSQL DB instance , you first add pgAudit to the shared libraries on the custom DB parameter group for your RDS for PostgreSQL DB instance. For information about creating a custom DB 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 RDS for PostgreSQL DB instance is associated with a custom DB parameter group.

To set up the pgAudit extension
  1. Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/.

  2. In the navigation pane, choose your RDS for PostgreSQL DB instance.

  3. Open the Configuration tab for your RDS for PostgreSQL DB instance. Among the Instance details, find the Parameter group link.

  4. Choose the link to open the custom parameters associated with your RDS for PostgreSQL DB instance.

  5. In the Parameters search field, type shared_pre to find the shared_preload_libraries parameter.

  6. Choose Edit parameters to access the property values.

  7. Add pgaudit to the list in the Values field. Use a comma to separate items in the list of values.

    Image of the shared_preload_libaries parameter with pgAudit added.
  8. Reboot the RDS for PostgreSQL DB instance so that your change to the shared_preload_libraries parameter takes effect.

  9. When the instance is available, verify that pgAudit has been initialized. Use psql to connect to the RDS for PostgreSQL DB instance, and then run the following command.

    SHOW shared_preload_libraries; shared_preload_libraries -------------------------- rdsutils,pgaudit (1 row)
  10. 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;
  11. Close the psql session.

    labdb=> \q
  12. Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/.

  13. 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.

    Image of the pgaudit.log parameter with setting.

    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).

  14. Choose Save changes.

  15. Open the Amazon RDS console at https://console.amazonaws.cn/rds/.

  16. Choose your RDS for PostgreSQL DB 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.

  1. 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
  2. Use the following Amazon CLI command to reboot the RDS for PostgreSQL DB instance so that the pgaudit library is initialized.

    aws rds reboot-db-instance \ --db-instance-identifier your-instance \ --region aws-region
  3. When the instance is available, you can verify that pgaudit has been initialized. Use psql to connect to the RDS for PostgreSQL DB instance, 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;
  4. Close the psql session so that you can use the Amazon CLI.

    labdb=> \q
  5. 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 RDS for PostgreSQL DB instance using the following Amazon CLI command.

    aws rds reboot-db-instance \ --db-instance-identifier your-instance \ --region aws-region

Auditing database objects

With pgAudit set up on your RDS for PostgreSQL DB instance 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
  1. Use psql to connect to the RDS for PostgreSQL DB instance.

    psql --host=your-instance-name.aws-region.rds.amazonaws.com --port=5432 --username=postgrespostgres --password --dbname=labdb
  2. Create a database role named rds_pgaudit using the following command.

    labdb=> CREATE ROLE rds_pgaudit; CREATE ROLE labdb=>
  3. 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.

  4. 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
  5. Use the following Amazon CLI command to reboot the RDS for PostgreSQL DB instance so that your changes to the parameters take effect.

    aws rds reboot-db-instance \ --db-instance-identifier your-instance \ --region aws-region
  6. 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 RDS log files.

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

Excluding users or databases from audit logging

As discussed in RDS for 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 RDS for PostgreSQL DB instance.

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 parameter group for the RDS for PostgreSQL DB instance. If you don't want database users to bypass your audit logging configuration settings, be sure to change their permissions.

Suppose that your RDS for PostgreSQL DB instance 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.