Auditing PostgreSQL using pgaudit

pgaudit is an extension in PostgreSQL to enable extensive auditing that satisfies several security compliances. The advantage of pgaudit over logging through Postgres parameters is that the latter is more general and does not handle the logging of SQL injections well. Additionally, pgaudit provides fine-grained control over what sessions or objects need to be monitored. In this recipe, we shall discuss the steps required to install pgaudit as an extension.

Getting ready

In order to install and use pgaudit as an extension, we must have the contrib module installed in the PostgreSQL server: the postgresql-contrib-12 package on Ubuntu/Debian or the postgresql12-contrib package on CentOS/Red Hat.

Additionally, pgaudit is available on the PGDG repository. So, if the repository has been configured in the database server, installing pgaudit is very easy.

Only a superuser can modify user-level settings for pgaudit.

How to do it...

We will do this using the following...