How to Fix PostgreSQL 'no pg_hba.conf entry' Error for Host, User, and Database

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 below to auto-refactor this — paste your pg_hba.conf and get a patched version with least-privilege entries generated locally in your browser.

---

The Incident (What Does the Error Mean?)

Raw error output from PostgreSQL logs or client:

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

PostgreSQL evaluates pg_hba.conf top-to-bottom, first-match wins. When no record matches all four dimensions simultaneously — connection type, client address, database, and user — Postgres issues a hard FATAL and drops the connection. The application never reaches password authentication. From the app's perspective this is indistinguishable from the database being down, triggering connection pool exhaustion, health check failures, and cascading service timeouts within seconds.

---

The Attack Vector / Blast Radius

This is a dual-edged failure:

Availability side: Every connection attempt from 192.168.1.100 fails instantly. If this is your primary app server, you have a full production outage. Connection pools will retry aggressively, hammering Postgres with rejected handshakes and potentially exhausting max_connections for other legitimate clients.

Security side — the misconfiguration that causes this is often fixed wrong: Engineers under pressure add an overly broad rule to stop the bleeding:

host  all  all  0.0.0.0/0  trust

That single line grants passwordless access to every database from every IP on the internet if the Postgres port is reachable. This is how production databases get exfiltrated. The trust auth method requires zero credentials — it is unconditional access. A misconfigured security group or a cloud firewall rule change later exposes this permanently.

The correct mental model: pg_hba.conf is a firewall AND an auth policy. Treat every line as a least-privilege ACL entry.

---

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 configs into ChatGPT? Generic AI tools log your company's ARNs, DB strings, and private keys. StackEngine is a zero-backend, pure Client-Side WASM utility. Drop your failing config into the sandbox above. We redact your secrets locally in the browser and auto-generate the refactored code using your own API key.

---

Prevention in CI/CD

This error should never reach production. Gate it at the pipeline level:

1. Checkov static analysis on pg_hba.conf:

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

Write a custom Checkov policy to reject md5 and trust methods org-wide.

2. OPA/Conftest policy for Terraform aws_db_instance or Helm chart values:

# 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])
}

3. Ansible/Chef enforcement: Manage pg_hba.conf as code. Any manual edit to the file triggers a drift alert via your CSPM tool (AWS Config, Wiz, Orca). The file should be owned by your IaC pipeline, not edited by hand on the host.

4. Connection string validation in app CI: Assert that your application's DATABASE_URL includes sslmode=require or sslmode=verify-full. A missing SSL mode parameter is what generates the SSL off dimension of this exact error.

# 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)