PostgreSQL Connection Authentication and pg_hba.conf Cheat Sheet

# pg_hba.conf: first matching record wins
host        all       all               10.24.9.18/32  reject
hostnossl   all       all               0.0.0.0/0      reject
hostssl     orders    +orders_login     10.24.0.0/16   scram-sha-256
hostssl     metrics   +reporting_login  10.24.0.0/16   scram-sha-256
host        all       all               0.0.0.0/0      reject
host        all       all               ::0/0          reject

Note: The address exception must precede the /16 allow. IPv4 and IPv6 are matched separately, so make the intended disposition explicit for both families.

# Do not expect the second line to run after a bad SCRAM password.
hostssl orders orders_app 10.24.0.0/16 scram-sha-256
hostssl orders orders_app 10.24.0.0/16 cert map=client_certs

Note: Both records have the same match fields. The first one always selects SCRAM, and an authentication failure ends the attempt.

hostssl orders       +orders_login 10.24.0.0/16  scram-sha-256
hostssl sameuser     analysts       10.24.20.0/24 scram-sha-256
hostssl replication  replicator     10.24.40.12/32 scram-sha-256

Note: Grant role membership independently with SQL; an HBA match authenticates identity but does not grant object privileges.

# Names in these files are separated by commas or whitespace.
hostssl @app-databases +app_login 10.24.0.0/16 scram-sha-256

# app-databases
orders
billing

Note: An @ file in a database or user field supplies names, while an include directive inserts complete HBA records. Keep the two mechanisms distinct during review.

# pg_hba.conf
include "pg_hba.break-glass.conf"
include_dir "pg_hba.allow.d"
include_if_exists "pg_hba.local.conf"
include "pg_hba.default-deny.conf"

# Suggested pg_hba.allow.d order:
# 10-local.conf
# 20-applications.conf
# 30-replication.conf

Note: Keep directly included files outside the included directory, and place the final deny after every intentional allow and optional override.

SHOW hba_file;

SELECT file_name, line_number, error
FROM pg_hba_file_rules
WHERE error IS NOT NULL
ORDER BY file_name, line_number;

SELECT rule_number, file_name, line_number, type, database,
       user_name, address, auth_method, options
FROM pg_hba_file_rules
ORDER BY rule_number NULLS LAST, file_name, line_number;

Note: An empty error query is necessary but not sufficient: syntactically valid rules can still be overly broad, shadowed, or ordered incorrectly.

SELECT pg_reload_conf() AS reload_signal_sent;

Note: Confirm server logs, then exercise both expected-success and expected-denial cases through new connections from representative networks.

SELECT CASE
         WHEN rolpassword IS NULL THEN 'no-password'
         WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'scram-sha-256'
         WHEN rolpassword LIKE 'md5%' THEN 'md5'
         ELSE 'other'
       END AS verifier_type,
       count(*) AS login_roles
FROM pg_authid
WHERE rolcanlogin
GROUP BY verifier_type
ORDER BY verifier_type;

Note: Run this only through an appropriately privileged administrative path. Do not export pg_authid.

# Stage 1: verify every client library supports SCRAM.
# Set this in postgresql.conf/ALTER SYSTEM, reload, then verify with SHOW.
password_encryption = 'scram-sha-256'

# Stage 2: rotate passwords; keep this compatibility rule temporarily.
hostssl orders orders_app 10.24.0.0/16 md5

# Stage 3: after verifier inventory and canary tests are clean.
hostssl orders orders_app 10.24.0.0/16 scram-sha-256

Note: The compatibility behavior belongs to the md5 HBA method, not to an MD5 verifier: a SCRAM verifier triggers SCRAM automatically under that temporary rule.

# Server: pg_hba.conf
hostssl orders +orders_login 10.24.0.0/16 scram-sha-256 clientcert=verify-full

# Client: pg_service.conf
[orders-prod]
host=db.example.com
port=5432
dbname=orders
user=orders_app
sslmode=verify-full
gssencmode=disable
sslrootcert=/etc/postgresql/app-ca.crt
sslcert=/run/secrets/orders-app.crt
sslkey=/run/secrets/orders-app.key
channel_binding=require
require_auth=scram-sha-256
passfile=/run/secrets/orders.pgpass

Note: Use the DNS name covered by the server certificate. A bare hostaddr cannot provide the host name needed by verify-full.

# pg_hba.conf
hostssl orders +orders_login 10.24.0.0/16 cert map=client_certs

# pg_ident.conf
client_certs orders-app.prod.example.com orders_app

Note: cert already performs full client-certificate validation, so adding clientcert is redundant. Keep mappings exact and anchored when regular expressions are unavoidable.

# pg_hba.conf
local admin postgres peer map=local_admins

# pg_ident.conf
local_admins db-operator postgres

Note: Protect the db-operator OS account and the local socket directory. Database authorization still follows the privileges of postgres after login.

# pg_ident.conf
corp_gss /^(orders_app)@EXAMPLE\.COM$ \1

Note: PostgreSQL regular expressions can match substrings by default. Anchors prevent an unexpected principal from matching only part of the pattern.

hostssl all +directory_login 10.24.0.0/16 ldap ldapserver=ldap.example.com ldaptls=1 ldapbasedn="ou=people,dc=example,dc=com" ldapsearchattribute=uid

Note: ldaptls=1 protects the server-to-directory connection. hostssl separately protects the client-to-PostgreSQL connection.

# pg_hba.conf
hostgssenc all +staff 10.24.0.0/16 gss include_realm=1 krb_realm=EXAMPLE.COM map=corp_gss

# pg_ident.conf: maintain an explicit principal allowlist
corp_gss analyst@EXAMPLE.COM analyst
corp_gss operator@EXAMPLE.COM operator

Note: Use a dedicated server keytab readable by the PostgreSQL service account and test the fully qualified service host name used by clients.

# ~/.pg_service.conf: non-secret policy
[orders-prod]
host=db.example.com
port=5432
dbname=orders
user=orders_app
sslmode=verify-full
gssencmode=disable
sslrootcert=/etc/postgresql/app-ca.crt
channel_binding=require
require_auth=scram-sha-256
passfile=/run/secrets/orders.pgpass

# /run/secrets/orders.pgpass: mode 0600 on Unix
db.example.com:5432:orders:orders_app:replace-with-managed-secret

Note: Do not commit either a real passfile or a service file that contains a password. Secret scanners and log redaction remain defense in depth.

psql service=orders-prod

Note: The service profile selects the protected passfile. Ensure the service definition itself cannot redirect the application to an untrusted server.

1. Preserve reviewed copies of pg_hba.conf, included files, and pg_ident.conf.
2. Keep one known-good administrator session open; verify its database, role, source, and TLS state.
3. Add the narrow canary rule above the rule it will replace.
4. Query pg_hba_file_rules; resolve every error and review the complete order.
5. Reload, inspect server logs, and open a new canary connection from the real client network.
6. Test wrong role, wrong database, wrong source, missing TLS, and invalid credential cases.
7. Expand in measured stages while watching authentication failures and connection pools.
8. Remove compatibility rules only after every client path is proven.
9. If validation fails, restore the reviewed files and reload from the retained session or console.

Note: Connection pools can hide policy mistakes by reusing old sessions. Force new physical connections during every test stage.

SELECT a.usename, a.datname, a.client_addr, s.ssl, s.version, s.cipher
FROM pg_stat_activity AS a
LEFT JOIN pg_stat_ssl AS s USING (pid)
WHERE a.pid = pg_backend_pid();

Note: A null client_addr commonly indicates a local Unix-domain socket. Capture the result in the change record without recording credentials.