How do I use the pgaudit extension to audit my Amazon RDS DB instance that is running PostgreSQL?

Last updated: 2019-10-03

I want to audit all my databases, roles, relations, or columns, and I want to provide different levels of auditing to different roles. How can I configure the pgaudit extension for different role levels on an Amazon Relational Database Service (Amazon RDS) DB instance that is running PostgreSQL?

Short Description

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.

Resolution

Enabling the pgaudit extension on an Amazon RDS instance running PostgreSQL

1.    Create a specific database role called rds_pgaudit by running the following command:

CREATE ROLE rds_pgaudit;
CREATE ROLE

2.    Modify the DB parameter group that is associated with your DB instance to use the shared libraries that contain pgaudit and set the parameter pgaudit.role. Then, set the pgaudit.role to the role that you created, rds_pgaudit.

3.    Reboot the instance so that the changes to the parameter group are applied to the instance.

4.    Confirm that pgaudit is initialized by running the following command:

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

5.    Create the pgaudit extension by running the following command:

CREATE EXTENSION pgaudit;
CREATE EXTENSION

6.    Confirm that pgaudit.role is set to rds_pgaudit by running the following command:

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

7.    Configure the pgaudit.log parameter to audit any of the following:

  • ALL audits the following commands.
  • MISC_SET audits miscellaneous SET commands, such as SET ROLE.
  • MISC audits miscellaneous commands, such as DISCARD, FETCH, CHECKPOINT, VACUUM, SET.
  • DDL audits all data description language (DDL) that is not included in the ROLE class.
  • ROLE audits statements related to roles and privileges, such as GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
  • FUNCTION audits function calls and DO blocks.
  • WRITE audits INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.
  • READ audits SELECT and COPY when the source is a relation or a query.

Depending on what you want to audit, set the value of the pgaudit.log parameter for a database, role, or table.

Using the pgaudit extension to audit databases

1.    To set the value of the pgaudit.log parameter for a database, role, or table, set the parameter pgaudit.log to none at the parameter group level:

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

2.    Run the following command to override the system configuration for this parameter at this database only:

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

This changes the value of parameter pgaudit.log to All, so that test_database is the only database that is audited in the RDS DB instance.

3.    Connect to test_database and run the following query:

select * from test_table;

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>

Using the pgaudit extension to audit roles

Similarly to configuring the pgaudit.log parameter at the database level, the role is modified to have a different value for the pgaudit.log parameter. In the following example commands, the roles test1 and test2 are altered to have different pgaudit.log configurations.

1.    Set different values for the pgaudit.log parameter for test1 and test2 by running the following commands:

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

2.    Check that the modifications are made at the role level by running 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 

3.    Run the following queries for both test1 and test2:

CREATE TABLE test_table (id int);
CREATE TABLE
select * from test_table;
id 
----
(0 rows)

The log output is similar to the following for 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>
...

The log output is similar to the following for test2 after running the same queries:

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

Note: There is no audit entry for the SELECT query because the pgaudit.log parameter for test2 is configured to DDL only.

Using the pgaudit extension to audit tables

Configuring the pgaudit.log parameter audits and logs statements that affect a specific relation. Only SELECT, INSERT, UPDATE, and DELETE commands can be logged by the pgaudit extension. TRUNCATE isn't included in the object audit logging. If you grant the rds_pgaudit role access to an operation (such as SELECT, DELETE, INSERT, or UPDATE) on the table that you want to audit, any grant audit logs the corresponding statement. The following example grants the rds_pgaudit role access to SELECT and DELETE, so that all the SELECT and DELETE statements on test_table are audited.

1.    Grant the rds_pgaudit role access to SELECT and DELETE by running the following command:

grant select, delete on test_table to rds_pgaudit;

2.    Test that the audit logging is configured correctly by running 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:02 
UTC: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>

Using the pgaudit extension to audit columns

You can also set the auditing at a column level for a specific table, for example, when sensitive data exists in one column only. In the following example command, a payroll table is created, and the table has a sensitive column that includes salary data that must be audited:

create table payroll
(    
    name text,
    salary text
);

1.    Grant the rds_pgaudit role access to SELECT on the salary column so that any SELECT on this column is audited:

grant select (salary)  on payroll to rds_pgaudit;

2.    SELECT all the columns in the table, including the salary column:

select * from payroll;

In the following example output, any SELECT that includes the salary column is audited. However, a SELECT that doesn't contain the salary column isn't audited.

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>

Did this article help you?

Anything we could improve?


Need more help?