Audit Logging for PostgreSQL

Auditing is a requirement for many security regulations or standards, like PCI – Payment Card Industry, and even a good way to keep your data as safe as possible knowing what is happening in your databases.

In a PostgreSQL database, basic statement logging can be provided by the standard logging facility with log_statement = all. This is acceptable for monitoring and other basic usages but does not provide the level of detail generally required for auditing.

In this blog, we will see what the pgAudit extension is and how to install and use it in your PostgreSQL database using ClusterControl.

What is pgAudit?

The PostgreSQL Audit Extension (pgAudit) provides detailed session and object audit logging via the standard PostgreSQL logging facility.

It is not enough to have a list of all the operations performed against the database. It must also be possible to find particular statements that are of interest to an auditor. The standard logging facility shows what the user requested, while pgAudit focuses on the details of what happened while the database was satisfying the request.

Enabling pgAudit with ClusterControl

For this example, we will assume you have ClusterControl installed, and it is managing your PostgreSQL database. Otherwise, you can follow this blog post to have it up and running in an easy way, using ClusterControl.

ClusterControl UI

From the ClusterControl 1.8.2 version, lunched recently, you can easily enable the pgAudit plugin from the ClusterControl UI. To enable it, go to ClusterControl -> Select your PostgreSQL Cluster -> Security Tab -> Audit Log -> Enable.

You will need to specify the events that you want to audit in your PostgreSQL cluster. PgAudit will be enabled on all the nodes and it will require a database service restart to install it.

The events could be:

• ROLE: Statements related to roles and privileges: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.

• DDL: All DDL that is not included in the ROLE class.

• MISC: Miscellaneous commands, e.g. DISCARD, FETCH, CHECKPOINT, VACUUM, SET.

• READ: SELECT and COPY when the source is a relation or a query.

• WRITE: INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.

• FUNCTION: Function calls and DO blocks.

• ALL: Include all of the above.

You can monitor your pgAudit installation in the ClusterControl Activity section.

When it is finished, you can check your pgAudit log in the same ClusterControl UI. For this, go to ClusterControl -> Select your PostgreSQL Cluster -> Logs Tab -> System Logs.

Here, you will find all the necessary information to audit your PostgreSQL database.

ClusterControl CLI

Instead of enabling pgAudit from the ClusterControl UI, another option is to use the ClusterControl CLI to do the job. For this, you can run the following command from your ClusterControl server:

$ s9s cluster --setup-audit-logging --cluster-id=ID

Where ID is the PostgreSQL cluster id.

While it is running, you can monitor the status by checking the ClusterControl job. First, you will need the job id, that you can get from the job list:

$ s9s job --list
1600  48 RUNNING  test_dba                     admins 18:49:36            90% Setup Audit Logging

Now, check the job details:

$ s9s job --log --job-id=1600
Using SSH credentials from cluster.
Cluster ID is 48.
The username is 'root'.]
10.10.10.139:5432: Configuring audit logging.
10.10.10.139:5432: Installing 'pgaudit15_13'.
10.10.10.139: Installing pgaudit15_13.
10.10.10.139:5432: Setting pgaudit.log to ROLE,DDL,MISC.
Writing file '10.10.10.139:/var/lib/pgsql/13/data/postgresql.conf'.
10.10.10.139:5432: Restarting PostgreSQL node.
10.10.10.139: waiting for server to shut down.... done
server stopped
waiting for server to start....2021-03-24 18:49:43.468 UTC [16098] LOG:  pgaudit extension initialized
2021-03-24 18:49:43.505 UTC [16098] LOG:  redirecting log output to logging collector process
2021-03-24 18:49:43.505 UTC [16098] HINT:  Future log output will appear in directory "log".
 done
server started
10.10.10.139:5432: Waiting for node to be accessible.
10.10.10.139:5432: pgaudit 1.5.0 is enabled.

This action will require a database service restart that will be performed by ClusterControl in the same task. After restarting it, the pgAudit extension is enabled and ready to use:

postgres=# SELECT * FROM pg_available_extensions WHERE name LIKE '%audit%';
  name   | default_version | installed_version |             comment
---------+-----------------+-------------------+---------------------------------
 pgaudit | 1.5             | 1.5               | provides auditing functionality
(1 row)

Conclusion

Auditing is required for many security regulations. It is used to know what happened to your database, when it happened, and who is responsible for it.

In this blog, we talked about the pgAudit PostgreSQL extension as a good way to audit your PostgreSQL databases. We also showed you how to implement it quickly with ClusterControl from the UI and using the ClusterControl CLI.

Keep in mind that, depending on the configuration, pgAudit can generate an enormous volume of data. So, you should be careful to determine what you need to audit and for how long.