5.3 Client Authentication & pg_hba.conf Architecture

5.3 Client Authentication & pg_hba.conf Architecture

[!NOTE] The Meaning of HBA: In PostgreSQL, HBA stands for Host-Based Authentication. Client access is controlled by the configuration file pg_hba.conf, situated in the cluster data directory ($PGDATA). It determines whether a client connecting from a specific network host is permitted to connect, which database and user identity they can claim, and what authentication method is enforced.

Securing access to a PostgreSQL database server begins at the connection perimeter. Long before SQL parsing, role permissions, or table ACLs are evaluated, PostgreSQL's postmaster and backend processes consult pg_hba.conf to validate incoming client connections.

---

The First-Matching-Rule Engine

Understanding how PostgreSQL parses pg_hba.conf is critical for avoiding catastrophic lockouts or inadvertent security holes:

Incoming Connection Request:
[Type: host | DB: app_db | User: app_user | IP: 192.168.1.50]
                           │
                           ▼
+--------------------------------------------------------------------------+
|                      Scan pg_hba.conf Line by Line                       |
+--------------------------------------------------------------------------+
| Line 1: host  finance_db  all       192.168.1.0/24  scram-sha-256        |
|         ├── Matches DB? NO (finance_db != app_db) -> Continue            |
|                                                                          |
| Line 2: host  app_db      app_user  192.168.1.50/32 reject               |
|         ├── Matches Type, DB, User, and IP? YES!                         |
|         └── FIRST MATCH LOCKED IN!                                       |
|             Method is 'reject' -> CONNECTION TERMINATED IMMEDIATELY!     |
|                                                                          |
| Line 3: host  app_db      all       192.168.1.0/24  scram-sha-256        |
|         └── NEVER REACHED! Line 2 already matched and decided fate.      |
+--------------------------------------------------------------------------+

Core Rules of the Evaluation Engine

1. Sequential Evaluation: When a client initiates a connection, PostgreSQL reads pg_hba.conf from top to bottom, line by line.
2. First Match Wins: The first record whose connection type, requested database, requested user, and client IP address match the connection parameters is selected.
3. No Fall-Through: Once a record matches, PostgreSQL uses that record's designated authentication method to authenticate the client. If authentication fails under that record, the connection is aborted immediately. PostgreSQL never continues evaluating subsequent lines.
4. Default Rejection: If the server reaches the end of pg_hba.conf without matching any record, the connection is rejected outright with a fatal error in the server log.

[!IMPORTANT] Rule Ordering Best Practice: Always place highly specific rules (individual IP addresses /32, individual databases, specific privileged users) at the top of pg_hba.conf, and broad catch-all rules (e.g., 0.0.0.0/0, database all, user all) at the bottom.

---

Anatomy of a pg_hba.conf Record

Each active line in pg_hba.conf consists of 4 to 7 whitespace-separated fields. A local record has no ADDRESS field, so its minimum form is only four fields (TYPE DATABASE USER METHOD); host-style records add the address and may add options:

# TYPE   DATABASE   USER         ADDRESS           METHOD        [OPTIONS]
hostssl  customers  +app_team    192.168.1.0/24    scram-sha-256
local    all        postgres                       peer

Field 1: TYPE (Connection Mechanism)

Specifies the underlying communication protocol used by the client:

• local: Matches connections made via local UNIX domain sockets (e.g., /tmp/.s.PGSQL.5432 or /var/run/postgresql/.s.PGSQL.5432). It requires no IP address in the ADDRESS field.
• host: Matches standard TCP/IP connections (both unencrypted plain TCP and SSL/TLS encrypted TCP).
• hostssl: Matches TCP/IP connections that must use SSL/TLS encryption. Plain unencrypted connection attempts matching this line are rejected immediately.
• hostnossl: Matches only unencrypted plain TCP/IP connections.
• hostgssenc / hostnogssenc: Matches TCP/IP connections using GSSAPI encryption.

Field 2: DATABASE (Target Database)

Specifies which database(s) the rule applies to:

• A specific database name (e.g., production, analytics).
• A comma-separated list of database names (e.g., db1,db2,db3).
• all: Matches all databases on the cluster (except replication connections).
• sameuser: Matches if the requested database has the exact same name as the connecting user.
• samerole: Matches if the connecting user is a member of a role with the exact same name as the requested database.
• replication: Special keyword matching physical or logical replication connections (used by standbys and backup tools like pg_basebackup).
• @filename: Reads a list of database names from a separate external text file.

Field 3: USER (Target Role)

Specifies which PostgreSQL role(s) the rule applies to:

• A specific role name (e.g., postgres, app_user).
• A comma-separated list of role names.
• all: Matches all database roles.
• +group_role: Matches the specified role AND any role that is directly or indirectly a member of that group role.
• @filename: Reads a list of role names from an external text file.

Field 4: ADDRESS (Client Network Address)

(Present only on host, hostssl, hostnossl, hostgssenc, and hostnogssenc records):

• IPv4 with CIDR: 192.168.1.100/32 (single host), 10.0.0.0/16 (subnet), 127.0.0.1/32 (IPv4 localhost), 0.0.0.0/0 (any IPv4 host).
• IPv6 with CIDR: ::1/128 (IPv6 localhost), fe80::/10 (link-local), ::/0 (any IPv6 host).
• Legacy Netmask: 192.168.1.0 255.255.255.0 (specified as two separate fields).
• Keywords: all (matches any IP), samehost (matches any IP address assigned to the PostgreSQL server machine itself), samenet (matches any IP on the server's directly connected subnets).

Field 5: METHOD (Authentication Method)

Specifies how PostgreSQL verifies the client's identity. Detailed below.

Field 6: OPTIONS (Optional Configuration Parameters)

Key-value options specific to certain authentication methods (e.g., clientcert=verify-full for cert, map=admin_map for peer or ident).

---

Authentication Methods in Detail

Deep Dive: scram-sha-256 vs. md5 vs. trust

• scram-sha-256 (Default in PG 14+): PostgreSQL generates a random cryptographic salt and iteration count. Authentication involves a multi-step cryptographic handshake where neither party reveals the secret key over the wire. This is mandatory for compliance standards such as PCI-DSS and SOC 2.
• trust (The Danger Zone): Setting host all all 0.0.0.0/0 trust creates an open proxy vulnerability: any attacker across the internet can connect as superuser postgres without entering any credentials.
• peer (Local UNIX Sockets): Standard on Linux installations (Ubuntu, Debian, RHEL). If you log into the OS shell as user postgres, running psql connects automatically because the OS user matches the DB role. If user ubuntu runs psql -U postgres, connection fails with: FATAL: Peer authentication failed for user "postgres".

---

Reloading HBA Configuration Online

One of PostgreSQL's operational strengths is that changes to pg_hba.conf do not require restarting the database server.

How to Reload Configuration

Administrators can trigger an online reload using any of three equivalent techniques:

# Method 1: Using the pg_ctl command-line utility
pg_ctl reload -D /var/lib/pgsql/16/data

# Method 2: Using standard systemd service manager on Linux
sudo systemctl reload postgresql-16

-- Method 3: Executing SQL administrative function (Requires superuser)
SELECT pg_reload_conf();

Operational Behavior of a Reload

• The postmaster receives a SIGHUP signal.
• It re-reads and parses pg_hba.conf.
• If a syntax error is present, PostgreSQL logs the syntax error in the server log and continues using the previously loaded, valid configuration.
• New rules take effect immediately for all subsequent incoming connections.
• Active, existing client connections are completely unaffected; they do not drop or re-authenticate.

---

Diagnosing Connection Rejections and the pg_hba_file_rules View

When a client cannot connect, PostgreSQL logs precise diagnostic error messages in the server log (log_directory).

Common Rejection Errors and Root Causes

1. No Matching HBA Record:

   FATAL: no pg_hba.conf entry for host "192.168.1.150", user "reporting_user", database "sales_prod", no encryption
   

   • Diagnosis: No line in pg_hba.conf matched the combination of client IP (192.168.1.150), user (reporting_user), database (sales_prod), and encryption state (unencrypted). Solution: Add an appropriate host or hostssl rule.

2. Explicit Reject Rule Triggered:

   FATAL: pg_hba.conf rejects connection for host "10.20.30.40", user "intern", database "payroll"
   

   • Diagnosis: The connection matched an explicit reject rule in pg_hba.conf.

3. Password Verification Failure:

   FATAL: password authentication failed for user "app_user"
   

   • Diagnosis: A rule matched with method scram-sha-256 or md5, but the client provided an invalid password.

4. Peer Authentication Mismatch:

   FATAL: Peer authentication failed for user "postgres"
   

   • Diagnosis: Connected over local UNIX socket, but the OS username of the client process does not match the requested DB role postgres.

Inspecting Rules via SQL: pg_hba_file_rules

PostgreSQL provides the pg_hba_file_rules system view to inspect the active HBA configuration and detect syntax errors directly through SQL:

SELECT line_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules
WHERE error IS NOT NULL;

If error is non-null, it reveals the exact line number and syntax flaw preventing the file from parsing cleanly.

---

Practical Example: A Hardened Production pg_hba.conf

# =========================================================================
# Production pg_hba.conf Configuration
# =========================================================================
# 1. Local admin access via UNIX domain socket (peer authentication)
local   all             postgres                                peer

# 2. Local application access via UNIX domain socket
local   all             all                                     scram-sha-256

# 3. Streaming replication standby nodes (Explicit IP addresses)
hostssl replication     rep_user        10.0.10.11/32           scram-sha-256
hostssl replication     rep_user        10.0.10.12/32           scram-sha-256

# 4. Explicit rejection of untrusted legacy subnet
host    all             all             192.168.99.0/24         reject

# 5. Production application tier (Enforce SSL + SCRAM)
hostssl prod_db         +app_roles      10.0.0.0/16             scram-sha-256

# 6. IPv4 and IPv6 localhost loopback for local utilities
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

# 7. Default fallback: reject everything else
host    all             all             all                     reject

---

Exam Tips and Common Pitfalls

• Exam Trap: Rule Order Sensitivity: The most common HBA exam question involves two conflicting rules: an explicit reject or trust rule followed by a scram-sha-256 rule. Always remember: the first matching line decides the connection. PostgreSQL does not check subsequent lines even if the first rule rejects the user.
• Exam Trap: Peer Authentication on TCP/IP: peer authentication works exclusively over local UNIX domain sockets. If an exam option suggests configuring host all all 192.168.1.0/24 peer, that configuration is completely invalid and will trigger a syntax error on reload.
• Exam Trap: Reload vs. Restart: Changing pg_hba.conf never requires a cluster restart (pg_ctl restart). It is always applied dynamically via pg_ctl reload or SELECT pg_reload_conf();, and active sessions are not interrupted.
• Exam Trap: Group Syntax in HBA: To reference a group role and all its member users in pg_hba.conf, the role name must be prefixed with a plus sign (+group_name). Without the plus sign, PostgreSQL treats it as a single literal username.