pg_hba.conf Rule Matching, Authentication Methods, SCRAM, Certificates, and OAuth Concepts

Learning outcomes

ServiceHub is moving from a single developer laptop to multiple application processes, administrators, and monitoring clients. Before PostgreSQL checks whether a role has SELECT on a table, it must decide whether the incoming network or local-socket connection may authenticate at all. That decision begins with pg_hba.conf: the Host-Based Authentication file.

1. Inspect the active HBA source before editing it

SHOW hba_file;SELECT rule_number, file_name, line_number,       type, database, user_name,       address, netmask, auth_method,       options, errorFROM pg_hba_file_rulesORDER BY rule_number;

pg_hba_file_rules parses the server's configured HBA files and includes syntax/configuration errors. It does not prove that a firewall permits the client, that DNS behaves as expected, or that a supplied password/certificate/token is valid. It proves how PostgreSQL currently parses the rules.

2. First match wins—there is no backup rule

For every connection attempt, PostgreSQL scans HBA records in order. The first record whose connection type, database, role, and client address match is selected. If authentication then fails, PostgreSQL does not continue searching for a friendlier later line.

# TYPE  DATABASE        USER            ADDRESS          METHODhost    servicehub_lab  ch20_api_login  127.0.0.1/32     rejecthost    servicehub_lab  ch20_api_login  127.0.0.1/32     scram-sha-256host    all             all             127.0.0.1/32     scram-sha-256

The first line matches the ServiceHub API login and rejects it. Adding a broader allow rule below cannot override that decision. The repair is to remove/reorder the specific rule according to policy, not append increasingly broad records until the connection happens to work.

# TYPE  DATABASE        USER            ADDRESS          METHODhost    servicehub_lab  ch20_api_login  127.0.0.1/32     scram-sha-256host    servicehub_lab  ch20_admin      127.0.0.1/32     scram-sha-256host    all             all             127.0.0.1/32     reject

Specific permitted identities come before the terminal local-TCP reject. Real production rules should constrain actual networks rather than copying loopback addresses blindly.

3. Create a SCRAM login without leaking credentials

DROP ROLE IF EXISTS ch20_api_login;CREATE ROLE ch20_api_login LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION;SHOW password_encryption;SET password_encryption = 'scram-sha-256';

\password ch20_api_login

The password is entered interactively so it is not written into SQL history or this lesson. PostgreSQL stores a verifier, not the plaintext password. Reading pg_authid is privileged; even in an admin lab, inspect only the verifier format rather than printing the verifier itself.

SELECT rolname,       CASE         WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'SCRAM-SHA-256 verifier'         WHEN rolpassword LIKE 'md5%' THEN 'legacy MD5 verifier'         WHEN rolpassword IS NULL THEN 'no password'         ELSE 'other/unknown'       END AS stored_password_formatFROM pg_authidWHERE rolname = 'ch20_api_login';

PostgreSQL 18 deprecates MD5-encrypted password authentication. For new deployments, use SCRAM-capable clients and scram-sha-256 HBA rules. When TLS is used, modern libpq can also use SCRAM channel binding; requiring channel binding is a client-side anti-spoofing control, not a replacement for TLS certificate verification.

4. Reload and validate configuration

SELECT pg_reload_conf();SELECT rule_number, line_number, type, database, user_name,       address, auth_method, options, errorFROM pg_hba_file_rulesORDER BY rule_number;

On Unix-like systems, HBA edits are reread on SIGHUP/reload. PostgreSQL documentation notes that Windows applies HBA changes to subsequent new connections immediately. Either way, existing authenticated sessions do not become re-authenticated simply because the file changed.

5. Diagnose one failed connection by layer

psql "host=127.0.0.1 port=55432 dbname=servicehub_lab user=ch20_api_login application_name=ch20_hba_test" 

If it fails, classify the failure in order: TCP/socket reachability → whether an HBA record matched → authentication method → supplied credential/token/certificate → role LOGIN state → database CONNECT. A later SQL permission denied for table means authentication already succeeded and the incident has moved into authorization.

SELECT rolname, rolcanlogin, rolvaliduntilFROM pg_rolesWHERE rolname = 'ch20_api_login';SELECT has_database_privilege(  'ch20_api_login',  'servicehub_lab',  'CONNECT') AS can_connect_to_database;

6. Authentication methods belong to different trust models

Do not choose a method by perceived modernity. Choose it by identity source, threat model, client/platform support, operational ownership, and incident/revocation requirements.

7. Peer authentication is not a remote password substitute

# TYPE  DATABASE        USER            ADDRESS  METHODlocal   servicehub_lab  ch20_local_dba           peer

Peer asks the operating system who owns the connecting local process and compares/maps that identity to the requested PostgreSQL role. It is useful for tightly controlled local administration. It cannot authenticate arbitrary remote TCP clients because the server cannot ask its local kernel who owns a process on another machine.

8. Certificate authentication combines TLS with client identity

# Requires TLS server configuration and a trusted client CA.hostssl servicehub_lab ch20_cert_login 10.20.0.0/24 cert map=ch20_cert_map

The cert method requires a valid trusted client certificate and compares certificate identity to the requested database role directly or through a username map. Lesson 2 first focuses on the opposite direction: the client verifying the PostgreSQL server.

9. PostgreSQL 18 OAuth is infrastructure, not a password-replacement keyword

# Example shape only; values are provider-specific:hostssl servicehub_lab ch20_oauth_user 10.20.0.0/24 oauth   issuer="https://identity.example.test/tenant-a"   scope="postgresql"   validator="company_oauth_validator"   map=ch20_oauth_map

PostgreSQL 18 adds the oauth HBA method. The issuer and scope are required; a server-side OAuth validator module validates bearer tokens, and libpq client support must have been enabled at build time. PostgreSQL is the OAuth resource server—not the authorization server. The mandatory chapter lab does not invent an identity provider or validator; production OAuth requires a separately secured, tested identity architecture.

10. Checkpoint

Authoritative references

Authentication, TLS, authorization, and policy behavior is security- and version-sensitive. The lesson uses these PostgreSQL 18 primary sources.

• PostgreSQL 18 — pg_hba.conf
• PostgreSQL 18 — Password Authentication
• PostgreSQL 18 — Certificate Authentication
• PostgreSQL 18 — OAuth Authorization/Authentication
• PostgreSQL 18 — SASL/SCRAM Protocol