pgAudit: Auditing Database Operations Part 1

The PostgreSQL Audit extension (pgaudit) provides detailed session and/or object audit logging via the standard PostgreSQL logging facility.

Basic statement logging can be provided by the standard logging facility in PostgreSQL. Out of the box logging provided by PostgreSQL is acceptable for monitoring and other usages but does not provide the level of detail generally required for an audit.

pgAudit enhances PostgreSQL's logging abilities by allowing administrators to audit specific classes of operations or choosing specific objects to monitor.

Getting Started

This guide assumes pgAudit has already been installed on the target DB server. For more instructions on installing pgAudit, see the official documentation here.

Session Auditing

Session auditing allows administrators to choose classes of statements to log:

• READ (SELECT and COPY when the source is a relation or a query)
• WRITE (INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation)
• FUNCTION (Functions and DO blocks)
• ROLE (GRANT, REVOKE, CREATE/ALTER/DROP ROLE)
• DDL (All DDL not included in ROLE)
• MISC (DISCARD, FETCH, CHECKPOINT, VACUUM)

READ Example

First, create a test table and insert some data:

CREATE TABLE pgauditExample(id SERIAL, name TEXT);
INSERT INTO pgauditExample(name) VALUES ('crunchy');

Next, configure pgAudit to audit the read class by altering the pgaudit.log parameter:

ALTER SYSTEM SET pgaudit.log TO 'read';
SELECT pg_reload_conf();

With pgAudit set to audit the read class, SELECT from our test table:

SELECT name FROM pgauditExample;

Finally, check pg_log for an audit entry:

$ grep AUDIT postgresql-Fri.log | grep READ
2016-09-30 00:16:24.688 UTC postgres postgres LOG: AUDIT: SESSION,1,1,READ,SELECT,,,SELECT name FROM pgauditExample;,<none>

WRITE Example

In the last example we configured pgAudit to audit the READ class of statements. Building on the previous example, add WRITE:

ALTER SYSTEM SET pgaudit.log TO 'read, write';
SELECT pg_reload_conf();

With pgAudit set to audit the read and write classes, INSERT, UPDATE and DELETE from our test table:

INSERT INTO pgauditExample(name) VALUES ('postgres');
UPDATE pgauditExample SET name = 'awesome' WHERE name = 'postgres';
DELETE FROM pgauditExample WHERE name = 'awesome';

Finally, check pg_log for the audit entries:

$ grep AUDIT postgresql-Fri.log | grep WRITE

2016-09-30 00:25:05.785 UTC postgres postgres LOG: AUDIT: SESSION,2,1,WRITE,INSERT,,,INSERT INTO pgauditExample(name) VALUES ('postgres');,<none>
2016-09-30 00:25:05.787 UTC postgres postgres LOG: AUDIT: SESSION,3,1,WRITE,UPDATE,,,UPDATE pgauditExample SET name = 'awesome' WHERE name = 'postgres';,<none>
2016-09-30 00:25:06.476 UTC postgres postgres LOG: AUDIT: SESSION,4,1,WRITE,DELETE,,,DELETE FROM pgauditExample WHERE name = 'awesome';,<none>

Function Example

So far we've configured pgAudit to audit READ and WRITE. Next, add FUNCTION to the watch list.

ALTER SYSTEM SET pgaudit.log TO 'read, write, function';
SELECT pg_reload_conf();

With pgAudit set to audit the function class, execute an anonymous function:

DO $$
BEGIN
 RAISE NOTICE 'pgAudit rocks!';
END
$$;

Finally, check pg_log for the audit entries:

$ tail -5 postgresql-Fri.log

2016-09-30 14:51:19.036 UTC postgres postgres LOG: AUDIT: SESSION,1,1,FUNCTION,DO,,,"DO $$
 BEGIN
 RAISE NOTICE 'pgAudit rocks!';
 END
 $$;",<none>

Role Example

In the last example we configured pgAudit to audit the READ, WRITE and FUNCTION classes of statements. Building on the previous example, add ROLE. Instead of adding role to the pgaudit.log parameter, notice the configuration is different this time:

ALTER SYSTEM SET pgaudit.log TO 'all, -misc, -ddl';
SELECT pg_reload_conf();

This time the configuration specifies all classes except misc and ddl (read, write, function, role).

With pgAudit set to audit the role class, create, alter and drop some roles:

CREATE ROLE bob;
CREATE ROLE alice;
ALTER ROLE bob LOGIN;
ALTER ROLE alice LOGIN CONNECTION LIMIT 1;
GRANT ALL ON TABLE pgauditExample TO bob;
GRANT SELECT ON TABLE pgauditExample TO alice;
REVOKE ALL ON TABLE pgauditExample FROM bob;
REVOKE ALL ON TABLE pgauditExample FROM alice;
DROP ROLE bob;
DROP ROLE alice;

Finally, check pg_log for the audit entries:

2016-09-30 15:03:11.522 UTC postgres postgres LOG: AUDIT: SESSION,2,1,ROLE,CREATE ROLE,,,CREATE ROLE bob;,<none>
2016-09-30 15:03:11.523 UTC postgres postgres LOG: AUDIT: SESSION,3,1,ROLE,CREATE ROLE,,,CREATE ROLE alice;,<none>
2016-09-30 15:03:11.524 UTC postgres postgres LOG: AUDIT: SESSION,4,1,ROLE,ALTER ROLE,,,ALTER ROLE bob LOGIN;,<none>
2016-09-30 15:03:11.526 UTC postgres postgres LOG: AUDIT: SESSION,5,1,ROLE,ALTER ROLE,,,ALTER ROLE alice LOGIN CONNECTION LIMIT 1;,<none>
2016-09-30 15:03:11.528 UTC postgres postgres LOG: AUDIT: SESSION,6,1,ROLE,GRANT,,,GRANT ALL ON TABLE pgauditExample TO bob;,<none>
2016-09-30 15:03:11.529 UTC postgres postgres LOG: AUDIT: SESSION,7,1,ROLE,GRANT,,,GRANT SELECT ON TABLE pgauditExample TO alice;,<none>
2016-09-30 15:03:11.531 UTC postgres postgres LOG: AUDIT: SESSION,8,1,ROLE,REVOKE,,,REVOKE ALL ON TABLE pgauditExample FROM bob;,<none>
2016-09-30 15:03:11.532 UTC postgres postgres LOG: AUDIT: SESSION,9,1,ROLE,REVOKE,,,REVOKE ALL ON TABLE pgauditExample FROM alice;,<none>
2016-09-30 15:03:11.534 UTC postgres postgres LOG: AUDIT: SESSION,10,1,ROLE,DROP ROLE,,,DROP ROLE bob;,<none>
2016-09-30 15:03:11.876 UTC postgres postgres LOG: AUDIT: SESSION,11,1,ROLE,DROP ROLE,,,DROP ROLE alice;,<none>

DDL Example

In the last example we configured pgAudit to audit all classes except misc and ddl. Building on the previous example, add ddl:

ALTER SYSTEM SET pgaudit.log TO 'all, -misc';
SELECT pg_reload_conf();

With pgAudit set to audit the ddl class, have some fun with tables:

CREATE TABLE pgauditDDLExample(id SERIAL);
ALTER TABLE pgauditDDLExample ADD COLUMN name text;
CREATE POLICY namePolicy ON pgauditDDLExample FOR ALL USING (current_user = 'postgres');
DROP POLICY namePolicy on pgauditDDLExample;
DROP TABLE pgauditDDLExample;

Finally, check pg_log for the audit entries:

2016-09-30 15:07:02.776 UTC postgres postgres LOG: AUDIT: SESSION,2,1,DDL,CREATE TABLE,,,CREATE TABLE pgauditDDLExample(id SERIAL);,<none>
2016-09-30 15:08:18.054 UTC postgres postgres LOG: AUDIT: SESSION,3,1,DDL,ALTER TABLE,,,ALTER TABLE pgauditDDLExample ADD COLUMN name text;,<none>
2016-09-30 15:09:18.095 UTC postgres postgres LOG: AUDIT: SESSION,4,1,DDL,CREATE POLICY,,,CREATE POLICY namePolicy ON pgauditDDLExample FOR ALL USING (current_user = 'postgres');,<none>
2016-09-30 15:09:37.562 UTC postgres postgres LOG: AUDIT: SESSION,5,1,DDL,DROP POLICY,,,DROP POLICY namePolicy on pgauditDDLExample;,<none>
2016-09-30 15:09:45.378 UTC postgres postgres LOG: AUDIT: SESSION,6,1,DDL,DROP TABLE,,,DROP TABLE pgauditDDLExample;,<none>

MISC Example

The last class is MISC, configure pgAudit to audit all classes:

ALTER SYSTEM SET pgaudit.log TO 'all';
SELECT pg_reload_conf();

With pgAudit set to audit all classes, here's a demonstration of the misc class:

CHECKPOINT;
VACUUM pgauditExample;

Finally, check pg_log for the audit entries:

$ grep AUDIT postgresql-Fri.log | grep MISC
2016-09-30 15:17:45.214 UTC postgres postgres LOG: AUDIT: SESSION,3,1,MISC,CHECKPOINT,,,CHECKPOINT;,<none>
2016-09-30 15:17:47.474 UTC postgres postgres LOG: AUDIT: SESSION,4,1,MISC,VACUUM,,,VACUUM pgauditExample;,<none>

Wrap Up

From the examples above, pgAudit was configured to session audit entire classes of SQL using pgAudit. Session auditing is a great feature, however, a lot of logs can be generated using session auditing. In part 2 of this series on pgAudit, an auditor role will be configured to watch specific objects instead of classes. Stay tuned!