Fixing PostgreSQL SCRAM-SHA-256 Auth Failure: password authentication failed for user with pg_hba.conf md5

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

---

TL;DR

• What broke: PostgreSQL 14+ defaults password_encryption to scram-sha-256, but your pg_hba.conf still specifies md5 — or vice versa — causing a handshake mismatch that hard-blocks every login for appuser.
• How to fix it: Align pg_hba.conf auth method with password_encryption, then re-hash the password (ALTER USER appuser WITH PASSWORD '...') so the stored verifier matches the negotiated method.
• Fast path: Use our Client-Side Sandbox below to auto-refactor this — paste your pg_hba.conf and get a corrected diff without sending secrets to any server.

---

The Incident (What Does the Error Mean?)

Raw error from PostgreSQL logs / client:

FATAL:  password authentication failed for user "appuser"
DETAIL:  Connection matched pg_hba.conf line 87: "host all appuser 10.0.0.0/8 md5"

Or the inverse — client library error:

ERROR:  auth method "scram-sha-256" requires libpq >= 10
FATAL:  password authentication failed for user "appuser"

What is actually failing: PostgreSQL's authentication handshake is a two-part contract. The server advertises an auth method (from pg_hba.conf). The stored password verifier in pg_authid must have been hashed with a compatible algorithm at CREATE USER / ALTER USER time. If these two are misaligned — or if the client library is too old to speak SCRAM — the handshake aborts before a single query runs. Every connection attempt fails. Your app is down.

---

The Attack Vector / Blast Radius

This is not just an inconvenience — it is a security regression in disguise.

Why MD5 is the problem, not the fix: MD5 password hashing for PostgreSQL auth (RFC: md5 in pg_hba.conf) stores a salted MD5 hash of password + username. MD5 is cryptographically broken. A leaked pg_authid table — via a SQL injection COPY TO attack, a misconfigured pg_dump, or a compromised replica — exposes password hashes that can be cracked offline with commodity hardware in minutes using hashcat.

SCRAM-SHA-256 (RFC 5802) is the mandated replacement because:

• The verifier stored in pg_authid is not directly usable for replay attacks.
• The protocol performs mutual authentication — the client proves it knows the password without transmitting it in a recoverable form.
• Channel binding (scram-sha-256-plus) ties the auth to the TLS session, defeating MITM.

Blast radius of leaving MD5 in place:

1. Leaked replica or backup → offline crack of all MD5-hashed passwords.
2. If appuser password is reused elsewhere (common in legacy apps) → lateral movement.
3. PCI-DSS, SOC 2, and HIPAA auditors will flag MD5 auth as a finding.

Blast radius of the mismatch itself:

• 100% connection failure for the affected user/host combination.
• If this is a connection pool (PgBouncer, RDS Proxy), the pool exhausts retries and cascades into app-layer 500s within seconds.

---

How to Fix It

Step 1 — Diagnose the stored hash type

-- Run as superuser
SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'appuser';

• Starts with md5 → password was hashed with MD5. Must be re-set.
• Starts with SCRAM-SHA-256$ → hash is correct; the problem is pg_hba.conf.
• NULL → no password set at all.

Step 2 — Check server default

SHOW password_encryption;
-- Should return: scram-sha-256

---

Basic Fix — Align pg_hba.conf and re-hash

pg_hba.conf correction:

# /etc/postgresql/15/main/pg_hba.conf

- host    all    appuser    10.0.0.0/8    md5
+ host    all    appuser    10.0.0.0/8    scram-sha-256

Force re-hash of the stored password (critical — do not skip):

- -- Password verifier currently stored as MD5 in pg_authid
- -- ALTER USER appuser WITH PASSWORD 'yourpassword';  ← run when password_encryption=md5

+ -- Ensure server will hash with SCRAM before running ALTER USER
+ SET password_encryption = 'scram-sha-256';
+ ALTER USER appuser WITH PASSWORD 'yourpassword';

Then reload (no restart needed for pg_hba.conf):

sudo systemctl reload postgresql
# or in psql:
SELECT pg_reload_conf();

---

Enterprise Best Practice — Enforce at the cluster level + secret rotation

postgresql.conf — lock the default:

- #password_encryption = md5
+ password_encryption = scram-sha-256

Use channel binding if TLS is enforced (highest security):

- host    all    appuser    10.0.0.0/8    scram-sha-256
+ hostssl all    appuser    10.0.0.0/8    scram-sha-256

Rotate via Vault / AWS Secrets Manager (never hardcode):

- # App reads password from environment variable set in Dockerfile
- ENV DB_PASSWORD=plaintextpassword

+ # App reads password from Secrets Manager at runtime
+ # aws secretsmanager get-secret-value --secret-id prod/appuser/db
+ # Inject via ECS task role — no static secret in image

Verify the fix:

psql "host=yourhost dbname=yourdb user=appuser sslmode=require" -c "SELECT current_user;"
# Expected: appuser

---

💡 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

1. Checkov — scan Terraform RDS / PostgreSQL modules:

# .checkov.yml
checks:
  - CKV_AWS_129   # RDS: ensure IAM auth enabled (supplements password auth)
  - CKV2_AWS_60  # RDS: enforce SSL
# Add custom check: flag any pg_hba.conf committed to repo with 'md5'

2. OPA/Conftest policy — block md5 in pg_hba.conf committed to IaC:

package postgresql

deny[msg] {
  line := input.pg_hba_lines[_]
  contains(line, "md5")
  msg := sprintf("pg_hba.conf line uses deprecated md5 auth: %v", [line])
}

3. GitHub Actions — validate pg_hba.conf on PR:

- name: Reject md5 auth in pg_hba.conf
  run: |
    if grep -rE '\bmd5\b' ./postgres/pg_hba.conf; then
      echo "ERROR: md5 auth method detected. Use scram-sha-256."
      exit 1
    fi

4. Terraform aws_db_parameter_group — enforce password_encryption:

 resource "aws_db_parameter_group" "postgres" {
   family = "postgres15"
+  parameter {
+    name  = "password_encryption"
+    value = "scram-sha-256"
+    apply_method = "immediate"
+  }
 }

5. Monitor for auth failures in production:

# CloudWatch Logs Insights (RDS) or grep for on-prem
filter @message like /password authentication failed/
| stats count(*) by bin(5m)
# Alert if > 5 failures/min — may indicate credential stuffing or misconfigured deploy