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
andCOPY
when the source is a relation or a query) - WRITE (
INSERT
,UPDATE
,DELETE
,TRUNCATE
, andCOPY
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!