How to enable Audit Logs on AWS RDS for Postgres 9

There are different parameters that you can set to log activity on your PostgreSQL DB instance. To audit different databases, roles, tables, or columns, you can use the pgaudit extension. After you enable the pgaudit extension, you can configure the pgaudit.log parameter to audit specific databases, roles, tables, and columns. 1. Create a specific database role called rds_pgaudit. Use the following command to create the role.

CREATE ROLE rds_pgaudit;

2. Modify the parameter group that is associated with your DB instance to use the shared preload libraries that contain pgaudit and set the parameter pgaudit.role. The pgaudit.role must be set to the role rds_pgaudit. The following command modifies a custom parameter group.

aws rds modify-db-parameter-group --db-parameter-group-name rds-parameter-group-96 
   --parameters "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot"
   --parameters "ParameterName=pgaudit.log,ParameterValue=all,ApplyMethod=pending-reboot" 
   --parameters "ParameterName=pgaudit.log_level,ParameterValue=debug5,ApplyMethod=pending-reboot"
   --parameters "ParameterName=pgaudit.log_statement_once,ParameterValue=1,ApplyMethod=pending-reboot"
   --parameters "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot"
   --parameters "ParameterName=log_connections,ParameterValue=1,ApplyMethod=pending-reboot"
   --parameters "ParameterName=log_disconnections,ParameterValue=1,ApplyMethod=pending-reboot"
   --parameters "ParameterName=force_admin_logging_level,ParameterValue=debug5,ApplyMethod=pending-reboot"
   --region us-west-2

3. Reboot the instance so that the DB instance picks up the changes to the parameter group. The following command reboots a DB instance.

aws rds reboot-db-instance --db-instance-identifier rds-test-instance --region us-west-2

4. Run the following command to confirm that pgaudit has been initialized.

show shared_preload_libraries;
shared_preload_libraries 
--------------------------
rdsutils,pgaudit
(1 row)  

5. Run the following command to create the pgaudit extension.

CREATE EXTENSION pgaudit;

6. Run the following command to confirm pgaudit.role is set to rds_pgaudit.

show pgaudit.role;
pgaudit.role 
------------------
rds_pgaudit  

7. Then grant access to the Database to pgaudit

ALTER DATABASE database_name set pgaudit.log='All';

To test the audit logging, run several commands that you have chosen 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;
----
(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;

P.S You have to create a new Database Parameter that is different from the default Database Parameter as it cannot be modified.

aws rds copy-db-parameter-group 
    --source-db-parameter-group-identifier mygroup1 
    --target-db-parameter-group-identifier mygroup2 
    --target-db-parameter-group-description "DB parameter group 2"