Setting up object-level auditing using pgaudit

pgaudit can be used to enable the logging of SELECT, INSERT, UPDATE, and DELETE commands for a specific relation (or a table). This is finer-grained when compared to the global logging of every statement. In this recipe, we shall discuss the steps involved in enabling object-level logging using pgaudit.

Getting ready

In order to enable object-level logging using pgaudit, we must have the extension downloaded and loaded to shared_preload_libraries and created in the database that needs to be enabled for object-level auditing.

How to do it...

Let's do this using the following steps:

1. Create a role that does not have the LOGIN role:

$ psql -c "CREATE ROLE auditor"

2. Set the newly created role as the master role:

$ psql -c "ALTER SYSTEM SET pgaudit.role TO 'auditor'"

-- Reload to get the parameter changes into effect.
 $ psql -c "SELECT pg_reload_conf()"

3. Enable audit of selects on a specific table...