pgAudit Object-Level Auditing in PostgreSQL: Part 3 – One GRANT to Rule Them All

Or, “Not All Those Who Wander Are SELECT *“

Greetings, fellowship of the database. In Part 2, we taught our auditing system to see the big picture with Session Auditing. It’s great for knowing that someone performed a WRITE operation, but it’s a bit like knowing an orc missed a shot. It happens a lot, and you don’t always care about the details.

But what if you only care about who accesses the secret ways into Mordor (secret_paths table)? Or who tries to alter the list of the White Council (white_council view)? For that, you need precision. You need Object-Level Auditing.

This is where pgAudit truly shines, allowing you to move beyond broad statement classes and focus your all-seeing eye on specific, critical database objects. This is how you stop watching the whole of Middle-earth and start watching the one weak link in Smaug’s armor that actually matters.

A Quick Word of Warning (The Disclaimer)

Disclaimer: This article is a technical showcase, not a substitute for professional consultancy. The configurations provided are for educational purposes. They haven’t been battle-tested in a production environment under the watchful eye of a dragon (or your friendly neighborhood compliance auditors). Use these configurations at your own risk.

How to Configure Object-Level Auditing with pgaudit.role

Object-level auditing works differently from session auditing. Instead of logging everything a user does, it logs every action that a specific audit role has been granted permission to do, regardless of which user actually performs the action.

The magic happens with the pgaudit.role parameter. You set this parameter to the name of a dedicated role you create for auditing purposes.

Let’s walk through the three-step process.

Step 1: Create a Dedicated Audit Role

First, you need a role that will act as your auditing proxy. This role doesn’t need to log in or have any special privileges on its own. It’s just a placeholder for permissions.

Connect to psql and create your audit role. We’ll call ours audit_tracker.

CREATE ROLE audit_tracker NOLOGIN;

The NOLOGIN part is important; it ensures no one can actually connect to the database using this role.

Step 2: Configure pgaudit.role

Next, you need to tell pgAudit to use this new role. You’ll do this in your postgresql.conf file or via the ALTER SYSTEM command.

-- In postgresql.conf
pgaudit.role = 'audit_tracker'

-- Or via psql
ALTER SYSTEM SET pgaudit.role = 'audit_tracker';

As always, you’ll need to reload the PostgreSQL configuration for this to take effect (SELECT pg_reload_conf();).

Step 3: Using GRANT to Target Specific Tables and Columns

This is where the real power lies. You now grant specific permissions on the objects you want to monitor to your audit role. pgAudit will then log any time a user performs an action that matches one of those granted permissions.

Let’s say we have a highly sensitive table called fellowship_missions.

CREATE TABLE fellowship_missions (
  id serial primary key,
  mission_name text not null,
  objective text,
  members text[]
);

We want to audit every single read and write operation on this table. To do this, we GRANT SELECT, INSERT, UPDATE, and DELETE on this table to our audit_tracker role.

GRANT SELECT, INSERT, UPDATE, DELETE ON fellowship_missions TO audit_tracker;

Now, if any user (even a superuser like postgres) performs one of these actions, pgAudit will log it because the audit_tracker role has permission to do it.

For example, if the user gimli runs:

SELECT mission_name, objective FROM fellowship_missions WHERE objective = 'Destroy the One Ring';

An audit log will be generated, even if pgaudit.log (our session audit parameter) is set to NONE. The log entry will look like this:

AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.fellowship_missions,"SELECT mission_name, objective FROM fellowship_missions WHERE objective = 'Destroy the One Ring';",<not logged>

Notice that the audit type is now OBJECT, not SESSION.

You can get even more granular and grant permissions on specific columns. For instance, to only audit when someone looks at the members column:

-- Only audit when someone looks at the members column
GRANT SELECT (members) ON fellowship_missions TO audit_tracker;

Now, if a user selects only the mission_name, no log is generated. But if they run the following query:

SELECT members FROM fellowship_missions WHERE mission_name = 'The Council of Elrond';

A specific audit log will be created:

AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.fellowship_missions,"SELECT members FROM fellowship_missions WHERE mission_name = 'The Council of Elrond';",<not logged>

This precision is incredibly powerful for meeting strict compliance requirements and reducing log noise by only monitoring the data that truly matters.