PostgreSQL SCRAM-SHA-256 Auth Failure Debugger

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.

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