How do I use the pgAudit extension to audit my Amazon RDS for PostgreSQL DB instance?

Resolution

There are different parameters that you can set to log activity on your RDS for PostgreSQL DB instance. After you activate the pgAudit extension, you can configure the pgaudit.log parameter to audit specific databases, roles, tables, and columns. First, set the pgaudit.log parameter value to none in the parameter group for a database, role, or table:

> show pgaudit.log;+---------------+
| pgaudit.log   |
|---------------|
| none          |
+---------------+
SHOW

Use the pgAudit extension to audit databases

Complete the following steps:

1. Run the following command to override the system configuration for the pgaudit.log parameter in only your database:

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

   Note: Replace test_database with your database name. The preceding command changes the value of the pgaudit.log parameter to All so that pgAudit audits only the test_database.
2. Connect to your database, and then run the following query to audit the database:

   select * from aud_table;

   Note: Replace aud_table with the table that you want to audit.
   The output of the error log is similar to the following:

   2019-06-25 19:21:35 UTC:192.0.2.7(39330):testpar@test_database:[21638]:LOG: AUDIT: SESSION,2,1,READ,SELECT,,,select * from test_table;,<not logged>

Use the pgAudit extension to audit roles

You can also modify the roles in the pgaudit.log parameter to have different values. To set different values for the pgaudit.log parameter for your roles, run the following commands:

ALTER ROLE test1 set pgaudit.log='All';

ALTER ROLE test2 set pgaudit.log='DDL';

Note: Replace test1 and test2 with your role names.

To check whether the modifications are made at the role level, run the following query:

> select rolname,rolconfig from pg_roles where rolname in ('test1','                  test2');
+-----------+----------------------+
| rolname   | rolconfig            |
|-----------+----------------------|
| test1     | [u'pgaudit.log=All'] |
| test2     | [u'pgaudit.log=DDL'] |
+-----------+----------------------+
SELECT 2
Time: 0.010s

Note: Replace test1 and test2 with your role names.

To test the pgAudit logging behavior of your roles, run the following queries for each role:

CREATE TABLE test_table (id int);CREATE TABLE

select * from test_table;id 
----
(0 rows)

The log output for two different roles is similar to the following examples.

test1:

...2019-06-26 14:51:12 UTC:192.0.2.7(44754):test1@postgres:[3547]:LOG:  
AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE test_table (id 
int);,<not logged>

2019-06-26 14:51:18 UTC:192.0.2.7(44754):test1@postgres:[3547]:LOG:  
AUDIT: SESSION,2,1,READ,SELECT,,,select * from test_table;,<not 
logged>
...

test2:

...2019-06-26 14:53:54 UTC:192.0.2.7(44772):test2@postgres:[5517]:LOG:  
AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE test_table (id 
int);,<not logged>
...

In the test2 example output, there isn't an audit entry for the SELECT query because the pgaudit.log parameter for test2 is set only to DDL. This test verifies that the test2 role logs only DDL operations.

Use the pgAudit extension to audit tables

Note: The pgAudit extension can log only SELECT, INSERT, UPDATE, and DELETE commands. The extension can't audit TRUNCATE.

To audit your tables, run the following command to grant the rds_pgaudit role access to the commands:

grant select, delete on test_table to rds_pgaudit;

Note: The preceding command allows the rds_pgaudit role to access SELECT and DELETE. Replace select and delete with the commands that you want to audit.

To verify that the audit logging is configured correctly, run a statement on the table. The following example command runs a DELETE statement on test_table:

Time: 0.008s DELETE 1
>delete from test_table where pid=5050;

The output for the DELETE statement is similar to the following:

2019-06-25 17:13:02UTC:192.0.2.7(41810):postgresql104saz@postgresql104saz:[24976]:LOG: 
AUDIT: OBJECT,3,1,WRITE,DELETE,TABLE,public.t1,delete from test_table where 
pid=5050,<not logged>

Use the pgAudit extension to audit columns

You can audit columns for a specific table, such as when sensitive data exists in only one column. The following example command creates a payroll table that has a sensitive column with salary data that must be audited:

create table payroll(    
    name text,
    salary text
);

To audit the salary column, first, run the following command to grant the rds_pgaudit role access to SELECT on the salary column:

grant select (salary)  on payroll to rds_pgaudit;

Then, run the following command to SELECT all the columns in the table, including the salary column:

select * from payroll;

If the SELECT query doesn't contain the salary column, then pgAudit doesn't audit that column.

In the following example output, pgAudit audits any SELECT that includes the salary column:

2019-06-25 18:25:02
UTC:192.0.2.7(42056):postgresql104saz@postgresql104saz:[4118]:LOG: 
AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.payroll,select * from 
payroll,<not logged>

Related information

Common DBA tasks for Amazon RDS for PostgreSQL

Using pgAudit to log database activity