PostgreSQL pg_hba.conf Entry Auditor

Threat/Impact Level: HIGH | Exploitability/Downtime Risk: HIGH | Time to Fix: 5 mins

TL;DR

• What broke: PostgreSQL's host-based authentication (pg_hba.conf) has no rule matching the combination of client IP 192.168.1.100, user app, database prod, and SSL disabled — the connection is rejected before a password is even checked.
• How to fix it: Add a correctly ordered host record to pg_hba.conf that matches the CIDR block, user, database, and auth method, then reload Postgres without a full restart.
• Shortcut: Use our Client-Side Sandbox above to auto-refactor this — paste your pg_hba.conf and get a patched version with least-privilege entries generated locally in your browser.

FATAL:  no pg_hba.conf entry for host '192.168.1.100', user 'app', database 'prod', SSL off

host  all  all  0.0.0.0/0  trust

How to Fix It (The Solution)

Basic Fix

Locate pg_hba.conf (typically /etc/postgresql/<version>/main/pg_hba.conf on Debian/Ubuntu or /var/lib/pgsql/data/pg_hba.conf on RHEL). Add a scoped rule before any catch-all lines:

# TYPE  DATABASE  USER  ADDRESS          METHOD
- # (no matching rule existed for this combination)
+ host    prod      app   192.168.1.100/32  scram-sha-256

Reload without downtime:

# As postgres OS user or superuser inside psql:
SELECT pg_reload_conf();

# Or via OS:
systemctl reload postgresql
# or
pg_ctl reload -D /var/lib/pgsql/data

Verify the rule is active:

SELECT * FROM pg_hba_file_rules WHERE database = '{prod}' AND user_name = '{app}';

Enterprise Best Practice

A single IP /32 rule is fragile in dynamic environments (autoscaling groups, Kubernetes pod CIDR rotation). The production-grade pattern uses subnet-scoped rules + scram-sha-256 + SSL enforcement, with trust and md5 fully prohibited.

# TYPE  DATABASE  USER   ADDRESS            METHOD

- # Old permissive rule (NEVER do this in production)
- host    all       all    0.0.0.0/0          md5

+ # Enforce SSL for all remote app connections (Postgres 12+)
+ hostssl  prod     app    192.168.1.0/24     scram-sha-256

+ # Reject any non-SSL attempt from same subnet explicitly
+ hostnossl prod    app    192.168.1.0/24     reject

+ # Local socket access for DBA tooling only
+ local    all      postgres                  peer

+ # Deny everything else
+ host     all      all    0.0.0.0/0           reject

Key decisions explained:

• hostssl + scram-sha-256: Mandates TLS in transit and uses the strongest supported password hashing. Eliminates the SSL off attack surface entirely.
• Explicit reject for hostnossl: Prevents silent fallback to unencrypted connections if SSL is misconfigured on the client.
• /24 subnet instead of /32: Survives rolling deploys and autoscaling without pg_hba.conf changes, but still scoped to your private network range.
• Terminal reject catch-all: Any connection not matching an explicit allow rule is denied with a logged rejection rather than a silent drop.

💡 Tired of pasting proprietary infrastructure configs into public LLMs? Generic AI interfaces log your company's ARNs, connection strings, and private keys. StackEngine enforces client-side zero-trust boundaries—the sandbox above executes 100% locally in your browser. Drop your broken manifest above to redact secrets locally and synthesize deterministic patch drafts with your own API key.

(For CI/CD pipeline automation, install via npm i -g stackengine).

checkov -f pg_hba.conf --check CKV_PG_1  # flags trust auth

# policy/pg_hba.rego
package postgresql

deny[msg] {
  rule := input.hba_rules[_]
  rule.method == "trust"
  msg := sprintf("pg_hba rule for user '%v' uses 'trust' — forbidden", [rule.user])
}

deny[msg] {
  rule := input.hba_rules[_]
  rule.type == "host"  # not hostssl
  rule.address != "127.0.0.1/32"
  msg := sprintf("Non-SSL remote rule for '%v' forbidden outside localhost", [rule.user])
}

# Fail the build if sslmode is missing or set to disable
echo "$DATABASE_URL" | grep -E 'sslmode=(require|verify-full|verify-ca)' || \
  (echo "FATAL: DATABASE_URL missing required sslmode" && exit 1)