Log PostgreSQL connections and authentication

The fixpostgresql.conf

log_connections = on
log_disconnections = on
log_hostname = off
log_line_prefix = '%m [%p] %q%u@%d from %h '

SELECT pg_reload_conf();

Why it matters

This closes no exposure, which is why it is low and last. It changes what you can find out afterwards.

Without log_connections, a successful authentication leaves no trace. You cannot answer “who connected, from where, and how” — not during an incident, not during an audit, and not when you’re trying to work out which client is still on plaintext or still using md5. The log line names the authentication method, which makes it the most reliable way to confirm the migrations on the other pages in this cluster actually landed.

log_disconnections adds session duration, which is what turns a list of logins into something you can spot anomalies in.

log_hostname is a trap

log_hostname = on looks helpful — hostnames are friendlier than addresses. It performs a reverse DNS lookup on every connection, synchronously.

On a busy server that is a per-connection DNS round trip in the connection path, and if the resolver is slow or unreachable, connection latency follows it. Worse, the resulting name is attacker-influenced: reverse DNS is controlled by whoever owns the address, so a hostname in your logs is not evidence of anything.

Leave it off and log addresses. %h in log_line_prefix gives you the client address, which is both faster and more truthful.

log_statement = ‘all’ will log your passwords

The other common suggestion is log_statement = 'all'. Understand what it does before enabling it: it writes every SQL statement to the log, including

ALTER ROLE myapp WITH PASSWORD 'the-actual-secret';

in cleartext. It logs your data too — every value in every INSERT. You have moved sensitive data into a file with different permissions, different retention and different backup rules than the database, and probably shipped it to a log aggregator.

For an audit trail of changes, log_statement = 'ddl' is the defensible middle ground: schema changes without the data or the credentials. For real auditing, use the pgaudit extension, which is designed for it and can exclude sensitive statements.

Verify it worked

Confirm the settings are live:

SELECT name, setting FROM pg_settings
WHERE name IN ('log_connections','log_disconnections','log_hostname','log_statement');

Find where the log is actually going — this is often the harder question:

SELECT name, setting FROM pg_settings
WHERE name IN ('log_destination','logging_collector','log_directory','log_filename');

On Debian and Ubuntu the packaged cluster logs to /var/log/postgresql/postgresql-18-main.log; with log_destination = 'stderr' under systemd it may be in the journal instead:

sudo journalctl -u postgresql@18-main --since "1 hour ago" | grep -i 'connection authorized'

Then prove it — connect once and read the line back:

psql -h 127.0.0.1 -U appuser -d appdb -c 'SELECT 1'
sudo tail -2 /var/log/postgresql/postgresql-18-main.log

Expect a connection authorized line naming the user, database and the authentication method — that method field is the useful part.

Before you apply this

No lockout or outage risk. The realistic costs are disk and disclosure.

Disk. log_connections on a server behind a connection pool that reconnects aggressively can produce a very large number of lines. If logging_collector is on, set rotation and keep an eye on the volume:

log_rotation_age = '1d'
log_rotation_size = '100MB'

A full disk on the log volume can take the database down, which would make this the only page here that causes the outage it was meant to help you investigate.

Disclosure. The log now records which users connect from which addresses. That is exactly what you want during an incident and exactly what an attacker wants for reconnaissance. chmod the log directory accordingly, and think about who can read your log aggregator — it is frequently a much larger group than those with database access.

Related

• Use scram-sha-256, not md5 — the log line names the method, which is how you confirm it.
• Enable TLS — pg_stat_ssl for the live picture, logs for the history.