Setting up the pgAudit extension - Amazon Relational Database Service

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 Parameter groups for Amazon RDS. 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 AWS Management Console or the AWS 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 AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/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 AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/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.aws.amazon.com/rds/.

  16. Choose your RDS for PostgreSQL DB instance from the Databases list.

To setup pgAudit

To setup pgAudit using the AWS 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 AWS 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 AWS 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 AWS CLI.

    labdb=> \q

  5. Use the following AWS 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 AWS CLI command.

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