October 22, 2023
Summary: in this tutorial, you will learn how to setup audit logging in PostgreSQL.
Table of Contents
One of the important security features of PostgreSQL is to perform logging of a certain activity. When a certain modification is done to a Postgres table or when a table has been dropped, it is important to know whether that was intended to be a requirement or was performed illegally by misusing the access to the database. In this tutorial, we shall discuss the steps required to log certain types of activity required for auditing in PostgreSQL.
Getting ready
It is always important to know that logging additional information to PostgreSQL logs causes more disk writes and uses a lot of disk space. For this reason, it is important to monitor the disk usage and the IOPS increase when any extensive logging has been enabled. It is also important to segregate logs and data across different disks. This way, none of the logging-specific writes cause any I/O saturation for a data directory that contains database objects.
How to do it…
We will initiate the logging process using the following steps:
1. Set log_line_prefix to log more details, such as the username, IP address, timestamp, and application name, as an example:
$ psql -c "ALTER SYSTEM SET log_line_prefix TO '%t [%p]: [%l-1] host=%r,user=%u,db=%d,app=%a'"
2. Set log_statement to the type of activity that needs to be logged.
As CREATE, ALTER, and DROP are vital to be monitored, we could use the following setting:
$ psql -c "ALTER SYSTEM SET log_statement TO 'ddl'"
3. Reload the server to get the changes into effect:
$ psql -c "select pg_reload_conf()"
How it works…
Most of the damages to a database through security breaches may happen in the form of
DDLs. Thus, we could use the log_statement setting to log the DDLs, such as CREATE,
ALTER, and DROP statements, to a log file as seen in step 2. In order to capture maximum
information for future analysis, it is important to modify log_line_prefix to provide
more information regarding the connection. Information such as the remote host IP
address, port number, application name, user, and database name would help in trying to
identify the source from where a breach has happened so that action can be taken
accordingly. Modifications to both of the parameters discussed in this tutorial require a
reload, as seen in step 3, to come into effect.