pg_hba.conf and Authentication
Pedal's new reporting server cannot connect: pg_hba.conf rejects connection for host "10.0.2.14", user "asha", database "pedal", no encryption. The role exists, the password is right, the grants are right. Before PostgreSQL checks any of that, it asks a different question: is this kind of connection allowed in at all, and how must it prove who it is?
The rule
Top to bottom, first match wins; no match, no entry.
pg_hba.conf (host-based authentication) is a list of rules. For each new connection PostgreSQL reads the rules from the top and uses the first line whose type, database, user and address all match. That line's method decides how the client authenticates. If authentication fails, it does not try the next line. If no line matches, the connection is rejected.
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
hostssl pedal pedal_app 10.0.1.0/24 scram-sha-256
hostssl pedal +analysts 10.0.2.0/24 scram-sha-256
host replication replicator 10.0.9.5/32 scram-sha-256
host all all 0.0.0.0/0 rejectThe columns:
- TYPE:
localfor Unix-socket connections on the same machine,hostfor TCP with or without encryption,hostsslfor TCP with TLS only. - DATABASE: a name,
all, orreplicationfor streaming-replication connections. - USER: a role,
all, or+groupfor every member of a group role. - ADDRESS: an IP range in CIDR form, such as
10.0.2.0/24. - METHOD: how to authenticate.
The methods a DBA uses most:
| Method | Meaning |
|---|---|
scram-sha-256 |
Password check that never sends the password itself; the modern default |
peer |
Local only: the OS user name must equal the role name |
cert |
The client presents a TLS certificate |
reject |
Refuse outright |
trust |
Let anyone in without checking. Never on a shared server |
md5 |
Older password method, deprecated; move to SCRAM |
Two more things to know. Edits take effect after a reload, and SELECT * FROM pg_hba_file_rules; shows how the server parsed the file, including errors. And pg_hba.conf only matters for connections that reach the server: listen_addresses in postgresql.conf (default localhost) decides which network interfaces it listens on at all, and changing it needs a restart.
The shortcut
Trace a connection by holding up its four facts: type, database, user, address, and slide down the file until all four match. Read that one line's method. Stop.
Her four facts
TCP without TLS (
host), databasepedal, userasha, address10.0.2.14.
The trap is adding a broad trust line near the top "just to test". First match wins, so it silently disables passwords for everyone it matches.
Drills
Answer fast — trust the rule.
A connection matches line 2 of pg_hba.conf, but the password is wrong. What happens?
Which method should protect remote password logins on PostgreSQL 16 or 17?
You add a correct line for a new app server. It still cannot connect. What have you probably forgotten?
sudo -u postgres psql works, but psql -U postgres run as OS user asha fails on the same machine. Which method explains it?
What does +analysts mean in the USER column?
Remember
- Top to bottom, first match wins; no match, no entry.
- Trace type, database, user and address; read one method; stop.
- Reload after editing; listen_addresses gates the network; never leave trust on a shared server.