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.
To achieve this in PostgreSQL, we can make use of a simple configuration variable:
|
1 2 3 4 5 |
test=# SHOW log_connections; log_connections ----------------- on (1 row) |
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:
|
1 2 |
2024-06-11 11:44:26.429 CEST [19362] LOG: connection received: host=[local] |
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:
|
1 2 |
2024-06-11 11:44:26.431 CEST [19362] LOG: connection authenticated: user="hs" method=trust (/Users/hs/db17/pg_hba.conf:117) |
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.
|
1 2 |
2024-06-11 11:44:26.431 CEST [19362] LOG: connection authorized: user=hs database=postgres application_name=psql |
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.
+43 (0) 2622 93022-0
office@cybertec.at
You are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
I'm sure this is very useful, but what is the cost in terms of performance, disk space, memory, and CPU?
The performance cost depends on how many connections you establish. The burden on the CPU and memory should be negligible. The biggest problem could be disk space.
But if you want a performant database application, you will use connection pooling anyway. Then the number of connections created and ended should be low, and the overhead of logging those few connections won't be a problem.