Enhanced security: Logging PostgreSQL connections

Logging connections in PostgreSQL can be incredibly valuable for monitoring and troubleshooting purposes. However,  that’s not all: auditing is also an important aspect which should be taken into account. Being able to trace and track activity is therefore vital to ensuring security, compliance, and more. This information can help identify potential security risks, such as unauthorized access or suspicious activity, allowing you to take swift action to mitigate threats.

Using log_connections in PostgreSQL

To achieve this in PostgreSQL, we can make use of a simple configuration variable:

By default, this configuration variable is set to “off”. However, turning this feature on can give us some really important details into the activity of your PostgreSQL database server. 

The purpose of this variable is to instruct the servers to add additional information to the log file and provide us with some valuable insights. In PostgreSQL 17, the output we can expect consists of three log entries, which are as follows:

The first entry tells us a bit more about the connect string. In my case, I tried to connect to a database called “postgres” through a local UNIX socket. The “host” entry confirms that. 

However, the second line is even more important. Consider the following output:

This information helps us uncover what the authentication process is up to. First, we see which user is trying to connect. In my case, it is my local user called “hs”. The next piece of information indicates that PostgreSQL was configured to use “trust”. In other words, no password had to be supplied. What is really important here is that we can also see which line in which configuration file is responsible for this decision, making it really easy to debug. In this case, it is line 117 in pg_hba.conf to blame. 

If you have access to the filesystem, debugging your security system is much easier when you use log_connections. It is such a tiny feature, but it really adds value to security and allows you to detect leaks or mis-configurations more easily.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.