Configuring Authentication
1. Understanding pg_hba.conf
| Column | Values |
|---|---|
| TYPE | local, host, hostssl, hostnossl, hostgssenc |
| DATABASE | name, all, sameuser, replication |
| USER | name, all, +group |
| ADDRESS | CIDR, hostname, samehost, samenet |
| METHOD | trust, reject, scram-sha-256, md5, peer, ident, cert, ldap, radius, gss |
Warning: Rules match top-down; first match wins. Place specific rules before broad ones.
2. Configuring Local Connections
# TYPE DATABASE USER METHOD
local all postgres peer
local all all scram-sha-256| Method | Behavior |
|---|---|
| peer | Match OS username to DB role |
| trust | No password (DEV ONLY) |
| scram-sha-256 | Password challenge over Unix socket |
3. Configuring TCP/IP Connections
# TYPE DATABASE USER ADDRESS METHOD
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256
host all all 10.0.0.0/8 scram-sha-256
hostssl prod app 0.0.0.0/0 scram-sha-256| TYPE | When To Use |
|---|---|
| host | Either SSL or non-SSL |
| hostssl | SSL required |
| hostnossl | Plain TCP only |
4. Using MD5 Authentication
| Aspect | Detail |
|---|---|
| Status | LEGACY superseded by SCRAM |
| Storage | md5(password + username) |
| Migrate | ALTER USER x PASSWORD 'new'; with password_encryption=scram-sha-256 |
5. Using SCRAM-SHA-256 Authentication
SHOW password_encryption; -- should be 'scram-sha-256' (default)
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
ALTER USER alice PASSWORD 'S3cret!'; -- stored as SCRAM verifier| Property | Value |
|---|---|
| Default since | PostgreSQL 14 |
| Salted | Yes (per user) |
| Channel binding | Supported (SCRAM-SHA-256-PLUS) |
6. Using Peer Authentication
| Property | Detail |
|---|---|
| Scope | Local socket only |
| Mechanism | OS uid → role via pg_ident.conf |
| Map example | map=app deploy app_user |
# pg_hba.conf
local all all peer map=usermap
# pg_ident.conf
# MAPNAME SYSTEM-USERNAME PG-USERNAME
usermap deploy app_user7. Using Certificate Authentication
hostssl all all 0.0.0.0/0 cert clientcert=verify-full| Requirement | Setting |
|---|---|
| ssl | on |
| ssl_cert_file | Server cert (PEM) |
| ssl_key_file | Server private key |
| ssl_ca_file | Trusted CA bundle |
| Client CN | Must equal DB role name |
8. Configuring LDAP Authentication
host all all 0.0.0.0/0 ldap \
ldapserver=ldap.example.com \
ldapprefix="uid=" ldapsuffix=",ou=People,dc=example,dc=com"
# Search+bind mode
host all all 0.0.0.0/0 ldap \
ldapserver=ldap.example.com ldapbasedn="dc=example,dc=com" \
ldapbinddn="cn=svc,dc=example,dc=com" ldapbindpasswd="..." \
ldapsearchattribute="uid"9. Configuring RADIUS Authentication
| Parameter | Purpose |
|---|---|
| radiusservers | Comma-separated host list |
| radiussecrets | Shared secret per server |
| radiusports | Default 1812 |
| radiusidentifiers | NAS identifier |
10. Setting Connection Host Restrictions
| Pattern | Effect |
|---|---|
| samehost | Match server's own IPs |
| samenet | Match any IP in server's subnets |
| .example.com | Suffix DNS match (reverse-DNS, slow) |
| 10.0.0.0/24 | CIDR block |
| reject | Explicit deny (place above broader allows) |
11. Reloading Authentication Config
SELECT pg_reload_conf();
-- Inspect parsed rules
SELECT line_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules;Note: pg_hba.conf reloads without restart; syntax errors keep the previous valid ruleset active.