A connection from TCP to first query · PostgreSQL

“The application cannot connect to the database” is one of the most common tickets an operator receives and one of the least informative. It covers a network path that never reached the host, a server that is not listening on the address tried, a rule that refused the client, a password that did not match, a database that does not exist, and a server that is running but has no connection slots left. These have nothing in common except the word connect.

The way to stop guessing is to know the sequence, because each stage fails in its own way and produces its own evidence.

The six stages

flowchart TD
    A["1. Client resolves the host\nand opens a TCP connection"] --> B["2. Postmaster accepts\non a listening socket"]
    B --> C["3. Optional TLS negotiation"]
    C --> D["4. pg_hba.conf is matched\nfirst rule wins"]
    D --> E["5. Authentication\nby the method that rule named"]
    E --> F["6. Backend is forked\nand the session initialised"]
    F --> G["ReadyForQuery"]

Stage 1 — the client gets to the host. DNS resolution, routing, firewalls. PostgreSQL is not involved and knows nothing about failures here. The evidence is a connection refused, a timeout, or a name that did not resolve, and it appears only on the client. Nothing is written to the PostgreSQL log, because nothing reached PostgreSQL.

Stage 2 — the postmaster accepts. The server must be listening on an address the client can reach. listen_addresses decides this and it is postmaster context, so a change needs a restart. The default in a package installation is frequently localhost, which is why a freshly installed server refuses remote connections while working perfectly from the host itself.

Stage 3 — TLS, if requested. The client asks whether the server supports TLS and the two negotiate before any credentials are sent. Part V covers this properly.

Stage 4 — pg_hba.conf is matched. The server walks the rules top to bottom and uses the first one whose connection type, database, user and address all match. That rule decides the authentication method. It is not a policy evaluation and later rules are not consulted, which is the single most misunderstood fact about PostgreSQL access control and has its own lesson in Part V.

Stage 5 — authentication. The method named by the matched rule is carried out. Failure here means the rule matched and the credential did not satisfy it.

Stage 6 — fork and session initialisation. The postmaster forks a backend. The backend attaches to shared memory, sets its database, and loads enough catalogue to be useful. Then it sends ReadyForQuery and the connection is usable.

Watching it happen

PostgreSQL 18 changed log_connections from a boolean into a setting that selects which stages to log, and all reports each one separately.

psql -U postgres -c "ALTER SYSTEM SET log_connections = 'all'"
psql -U postgres -c "ALTER SYSTEM SET log_disconnections = on"
psql -U postgres -c 'SELECT pg_reload_conf()'

Read-only / Safeone complete connection, PostgreSQL 18.6, log_connections = 'all'— Four stages logged separately, then the session's own lifetime. Captured from a live server.

$ docker logs rbpg-base18

2026-08-27 17:04:58.152 UTC [1] LOG:  received SIGHUP, reloading configuration files
2026-08-27 17:04:58.153 UTC [1] LOG:  parameter "log_connections" changed to "all"
2026-08-27 17:04:58.153 UTC [1] LOG:  parameter "log_disconnections" changed to "on"
2026-08-27 17:04:59.203 UTC [425] LOG:  connection received: host=[local]
2026-08-27 17:04:59.203 UTC [425] LOG:  connection authenticated: user="postgres" method=trust (/var/lib/postgresql/18/docker/pg_hba.conf:117)
2026-08-27 17:04:59.203 UTC [425] LOG:  connection authorized: user=postgres database=shop application_name=psql
2026-08-27 17:04:59.204 UTC [425] LOG:  connection ready: setup total=1.498 ms, fork=0.445 ms, authentication=0.127 ms
2026-08-27 17:04:59.204 UTC [425] LOG:  disconnection: session time: 0:00:00.001 user=postgres database=shop host=[local]

That sequence is worth reading line by line, because each line marks a stage boundary and the boundaries are exactly where connections fail.

connection received is stage 2 completing: a client reached the listening socket. If your log shows nothing at all for a failing client, the problem is upstream of PostgreSQL and no amount of pg_hba.conf editing will help.

connection authenticated is stages 4 and 5 completing, and it names the file and line number of the rule that matched — pg_hba.conf:117. This single detail resolves most access disputes outright. When somebody insists their rule should have matched, the log names the rule that actually did, and the answer is almost always that an earlier, broader rule matched first.

connection authorized is the database and user being accepted, with the application name the client supplied.

connection ready is stage 6, and PostgreSQL 18 breaks the timing into components: setup total=1.498 ms, fork=0.445 ms, authentication=0.127 ms. The fork cost from the process-model lesson is measured here rather than asserted — about half a millisecond on an idle local cluster. On a loaded host, or with an authentication method that consults an external directory, these numbers grow, and this line is how you find out which component grew.

log_connections is the cheapest connection diagnostic you have

It costs one line per connection, and for an application holding a pool of connections that is a handful of lines a day. For an application opening a connection per request it is a lot of lines, and that volume is itself the finding.

Turn it on before an incident, not during one. log_connections and log_disconnections are superuser-backend context, which has a specific consequence worth knowing: a reload applies them with no client disruption, but each backend fixes the value at connection time, so only connections opened after the reload are logged. Sessions already established are unaffected and will never appear.

That is usually what you want — you are trying to see new connection behaviour — but it means enabling it during an incident tells you nothing about the connections that are already the problem. pg_stat_activity covers those. Part XVI covers what else belongs in a production logging configuration and, importantly, what does not.

Attributing a failure to a stage

This is the table that converts an error string into a stage, and therefore into a next action.

What the client reports	Stage	What it means	Look at
`could not translate host name`	1	DNS	resolver, not the database
`Connection refused`	1–2	Nothing is listening on that address and port	`listen_addresses`, `port`, is the server running
`Connection timed out`	1	Packets are being dropped	firewall, routing, security group
`no pg_hba.conf entry for host ...`	4	Reached the server; no rule matched	the rule set, and the address it saw
`password authentication failed for user`	5	A rule matched and the credential failed	the credential, and which rule matched
`database "x" does not exist`	6	Authenticated successfully; the target does not exist	`pg_database`
`sorry, too many clients already`	6	Server reached `max_connections`	`pg_stat_activity`
`the database system is starting up`	6	Recovery in progress	the log, and how long recovery has left

The most valuable distinction in that table is between the fourth and fifth rows, because they look similar and mean opposite things.

no pg_hba.conf entry means the connection reached the server and no rule matched it. The server tells you exactly what it was matching on — user, database, address, and whether TLS was in use — and the fix is a rule, not a credential.

password authentication failed means a rule did match and the credential did not satisfy it. Adding rules will not help. Note also that PostgreSQL deliberately returns this same message whether the role does not exist or the password was wrong, so that the error cannot be used to enumerate valid usernames.

Adding a permissive pg_hba.conf rule to fix an authentication failure

The two errors above are easy to conflate under pressure, and the tempting repair for either is a broad rule near the top of the file — host all all 0.0.0.0/0 trust or similar — to “eliminate access control as a variable”.

That rule does not merely widen access for the client you are debugging. Because matching stops at the first rule, a broad rule placed above the existing ones replaces the authentication method for every connection it covers, including every application already working. A trust line accepts any user without a password from any address that reaches the port. It is not a diagnostic step; it is a change that removes authentication from the cluster, and it is routinely left in place afterwards because nothing subsequently fails.

The actual diagnostic is already in the log. connection authenticated names the file and line of the matching rule, and a failure names what the server was matching on. Read those, then write the specific rule. Part V covers rule ordering and the safe way to test a change.

Diagnosing from both ends

A connection problem is one of the few cases where you genuinely need evidence from the client side and the server side, because the whole question is where along the path it stopped.

# From the client host: does the TCP path exist at all?
# Replace with the real host and port for your estate.
PGHOST=db01.internal
PGPORT=5432
timeout 5 bash -c "cat < /dev/null > /dev/tcp/$PGHOST/$PGPORT" \
  && echo "tcp reachable" || echo "tcp NOT reachable"

# What does the server think it is listening on?
psql -U postgres -c 'SHOW listen_addresses' -c 'SHOW port'

# From the server host: is it actually bound where it claims?
ss -ltnp 2>/dev/null | grep -E '5432|postgres' || true

# Is the server accepting connections at all, independent of credentials?
pg_isready -h "$PGHOST" -p "$PGPORT"

pg_isready is underused. It reports whether the server is accepting connections without needing valid credentials, so it separates “the server is not there” from “the server is there and rejected me” in one command, which is precisely the stage-2-versus-stage-4 distinction.

What the backend does between fork and ReadyForQuery

The gap between the fork and the connection being usable is not empty, and on a busy system it is where connection latency accumulates.

The new backend attaches to the shared memory segment it inherited, then registers itself in the array of active backends so that other processes can find it — this is the entry that later appears as a row in pg_stat_activity.

It then sets its database. This means reading pg_database from the shared catalogue in global/ to find the database’s OID, then locating that database’s directory under base/. From this point the backend can only see that database’s catalogues, which is the mechanism behind the per-database isolation established in the first lesson of this part.

Next it populates its local caches. PostgreSQL keeps a relation cache and a catalogue cache in each backend’s private memory, because looking up a table’s definition on every query would be prohibitive. These start empty, which is why the first query in a session that touches an unfamiliar table does more catalogue work than the second.

Finally it applies configuration in precedence order — the values set for the database, then for the role, then anything the client sent in its startup packet — and sends ReadyForQuery. Part III covers that precedence, which is a common source of surprise when a session’s settings do not match the file on disk.

The fork=0.445 ms and setup total=1.498 ms figures in the log capture this work. The roughly one millisecond difference between them is the initialisation described here, and it is what a connection pool saves you on every request.

Production discipline

Establish which stage failed before proposing a fix. The error text names the stage. Fixing stage 4 when the failure is at stage 1 is the commonest wasted hour in this class of incident.
Enable log_connections before you need it. It is a reload, and the rule file and line number it records settle access disputes that otherwise become arguments.
Use pg_isready to separate reachability from rejection. It answers the question without credentials.
Never add a broad pg_hba.conf rule as a diagnostic. First match wins, so a broad rule near the top changes authentication for every connection it covers, and it will not fail afterwards to remind you.
Read no pg_hba.conf entry and password authentication failed as opposites. The first needs a rule; the second needs a credential and already has a rule.

Cross-course references

Linux for Production Sysadmins — Part XXII (Network troubleshooting) covers ss, packet capture and the reachability questions that stage 1 raises, and Part XXV (Firewall) covers the rules that silently drop stage-1 traffic.
Secrets, PKI & Certificate Management — Part VII (TLS for operators) covers the negotiation at stage 3 and what each sslmode actually verifies.
Observability for Production Sysadmins — Part XI (Blackbox) covers probing a listener from outside, which is the automated form of the pg_isready check.