pgAudit: Auditing Database Operations Part 2

In the last blog post, pgAudit was configured to audit entire classes of statements (session auditing). Session auditing works great, but it can generate a lot of logs and not every administrator needs all that information. In this blog post pgAudit will be configured to use an auditing role to watch only specific objects.

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.

pgAudit auditor role only supports the following commands against objects:

• SELECT
• INSERT
• UPDATE
• DELETE

Setup

First, create a role to designate as the auditing role:

CREATE ROLE auditor NOLOGIN;

Next, configure pgAudit to use that role for auditing:

ALTER SYSTEM SET pgaudit.role TO 'auditor';
SELECT pg_reload_conf();

That's it! pgAudit is now configured to use an auditing role.

Auditor Example

With pgAudit configured to use an auditing role, auditor can be assigned objects to audit.

First, lets create a test table and insert some data:

CREATE TABLE public.pgauditExample(id SERIAL, name TEXT, secret TEXT, age INT);
INSERT INTO public.pgauditExample(name, secret, age) VALUES ('crunchy', 'my-secret', 30);

Next, grant the operations to the auditing role that should be monitored:

GRANT SELECT (name, secret), UPDATE (secret) ON public.pgauditExample TO auditor;

Notice how SELECT is granted to name and secret, however, auditor is also granted UPDATE on secret.

Next, trigger some audit logging on the objects that were just configured:

SELECT name FROM public.pgauditExample;
SELECT secret FROM public.pgauditExample;
UPDATE public.pgauditExample SET secret = 'new-secret' WHERE 'name' = 'crunchy';
SELECT age FROM public.pgauditExample;

Finally, check pg_log for the audit entries:

$ grep AUDIT postgresql-Fri.log | grep OBJECT
2016-10-12 13:54:54.836 UTC postgres postgres LOG: AUDIT: OBJECT,5,1,READ,SELECT,TABLE,public.pgauditexample,SELECT name FROM pgauditExample;,<none>
2016-10-12 13:54:54.837 UTC postgres postgres LOG: AUDIT: OBJECT,6,1,READ,SELECT,TABLE,public.pgauditexample,SELECT secret FROM pgauditExample;,<none>
2016-10-12 13:54:54.838 UTC postgres postgres LOG: AUDIT: OBJECT,7,1,WRITE,UPDATE,TABLE,public.pgauditexample,UPDATE pgauditExample SET secret = 'new-secret' WHERE 'name' = 'crunchy';,<none>

Notice how there is no audit log for the SELECT on age. The auditor role has not been granted privileges on that column, thus it does not watch the object.

Multiple Auditor Roles

In the last example pgAudit was configured to use the auditor role to watch specific objects. Although pgAudit can only be assigned one master auditor role, multiple roles can be configured to audit objects by granting them to the master role.

First, create new roles to also be used for auditing:

CREATE ROLE read_auditor NOLOGIN;
CREATE ROLE write_auditor NOLOGIN;
CREATE ROLE delete_auditor NOLOGIN;

Next, grant these roles to the auditor role:

GRANT read_auditor TO auditor;
GRANT write_auditor TO auditor;
GRANT delete_auditor TO auditor;

With the auditor role configured to use multiple roles, create a test table:

CREATE TABLE public.pgauditAuditorExample(id SERIAL, name TEXT, secret TEXT, age INT);
INSERT INTO public.pgauditAuditorExample(name, secret, age) VALUES ('crunchy', 'my-secret', 30);

Next, grant the new roles access to various operations on the test table:

GRANT SELECT (name, secret, age) ON public.pgauditAuditorExample TO read_auditor;
GRANT INSERT(secret), UPDATE (secret) ON public.pgauditAuditorExample TO write_auditor;
GRANT DELETE ON public.pgauditAuditorExample TO delete_auditor;

With the new roles assigned to their objects to audit, trigger the audit logging:

SELECT secret FROM public.pgauditAuditorExample;
INSERT INTO public.pgauditAuditorExample(name, secret, age) VALUES ('postgres', 'new-secret', 100);
UPDATE public.pgauditAuditorExample SET secret = 'new-secret' WHERE name = 'crunchy';
DELETE FROM public.pgauditAuditorExample WHERE age = 100;

Finally, check pg_log for the audit entries:

$ grep AUDIT postgresql-Wed.log | grep OBJECT
2016-10-12 14:22:01.416 UTC postgres postgres LOG: AUDIT: OBJECT,4,1,READ,SELECT,TABLE,public.pgauditauditorexample,SELECT secret FROM public.pgauditAuditorExample;,<none>
2016-10-12 14:22:05.447 UTC postgres postgres LOG: AUDIT: OBJECT,5,1,WRITE,INSERT,TABLE,public.pgauditauditorexample,"INSERT INTO public.pgauditAuditorExample(name, secret, age) VALUES ('postgres', 'new-secret', 100);",<none>
2016-10-12 14:22:12.299 UTC postgres postgres LOG: AUDIT: OBJECT,6,1,WRITE,UPDATE,TABLE,public.pgauditauditorexample,UPDATE public.pgauditAuditorExample SET secret = 'new-secret' WHERE name = 'crunchy';,<none>
2016-10-12 14:22:17.198 UTC postgres postgres LOG: AUDIT: OBJECT,7,1,WRITE,DELETE,TABLE,public.pgauditauditorexample,DELETE FROM public.pgauditAuditorExample WHERE age = 100;,<none>

Wrap up

These examples show how pgAudit can be tuned to watch specific objects using auditor role(s). This gives administrators the ability to finely tune what is audited in their database by either using session auditing or object auditing.

pgAudit is shipped with the Crunchy Certified PostgreSQL distribution and can also be obtained from the project repository.