Many of our customers enable log_connections because of auditing requirements. This is a simple boolean which is either turned on or off. Once this is enabled and active every new connection to a PostgreSQL database is logged into the PostgreSQL log file. Up to PostgreSQL 17, a typical line in the log file for a logged connection looks like this:

2025-03-13 08:50:05.607 CET - 1 - 6195 - [local] - [unknown]@[unknown] - 0LOG:  connection received: host=[local]
2025-03-13 08:50:05.607 CET - 2 - 6195 - [local] - postgres@postgres - 0LOG:  connection authenticated: user="postgres" method=trust (/u02/pgdata/17/pg_hba.conf:117)
2025-03-13 08:50:05.607 CET - 3 - 6195 - [local] - postgres@postgres - 0LOG:  connection authorized: user=postgres database=postgres application_name=psql

As you can see, there are three stages logged: Connection received, authenticated and authorized. This gives you an idea of how long each of the stages took to complete by comparing the timestamps logged. A consequence of this is, that it can generate quite some noise in the log file if you have many connections.

With PostgreSQL 18 this will change, log_connections is not anymore a simple boolean but a list of supported values. The valid options are:

  • receipt
  • authentication
  • authorization
  • [empty string]

This list should already tell you what changed. You now have the option to enable logging of specific stages only, and not all of them at once if you don’t need them. An empty string disables connection logging.

So, e.g. if you are only interested in the authorization stage you can now configure that:

postgres@pgbox:/home/postgres/ [pgdev] psql
psql (18devel)
Type "help" for help.

postgres=# select version();
                              version                               
--------------------------------------------------------------------
 PostgreSQL 18devel on x86_64-linux, compiled by gcc-14.2.1, 64-bit
(1 row)

postgres=# alter system set log_connections = 'authorization';
ALTER SYSTEM
postgres=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

From now on only the “authorization” stage is logged into the log file:

2025-03-13 09:10:41.710 CET - 1 - 6617 - [local] - postgres@postgres - 0LOG:  connection authorized: user=postgres database=postgres application_name=psql

This reduces the amount of logging quite a bit, if you are only interested in that stage. Adding all stages will restore the old behavior of logging all stages:

postgres=# alter system set log_connections = 'authorization','receipt','authentication';
ALTER SYSTEM
postgres=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

With this setting, it looks exactly like before:

2025-03-13 09:14:19.520 CET - 1 - 6629 - [local] - [unknown]@[unknown] - 0LOG:  connection received: host=[local]
2025-03-13 09:14:19.521 CET - 2 - 6629 - [local] - postgres@postgres - 0LOG:  connection authenticated: user="postgres" method=trust (/u02/pgdata/PGDEV/pg_hba.conf:117)
2025-03-13 09:14:19.521 CET - 3 - 6629 - [local] - postgres@postgres - 0LOG:  connection authorized: user=postgres database=postgres application_name=psql

Nice, all details here, and as usual, thank you to all involved in this.

Post Views: 116
Thumbnail [60x60]
by
Daniel Westermann