SCRAM Authentication

SCRAM-SHA-256 is PostgreSQL's default password authentication. The server stores a salted verifier, not the plaintext password, and the client proves knowledge through a challenge-response protocol. Pair SCRAM with TLS (hostssl) for production; add LDAP or cloud IAM when central identity is required.

Recipe

-- Server default for new passwords (postgresql.conf)
-- password_encryption = scram-sha-256
 
CREATE ROLE app_api LOGIN PASSWORD 'vault-generated-secret';

# pg_hba.conf
hostssl  orders  app_api  10.20.0.0/16  scram-sha-256

# Verify stored hash type
psql -c "SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'app_api';"
# rolpassword starts with SCRAM-SHA-256$

When to reach for this:

• All password-based roles in PostgreSQL 14+
• Migrating off md5 during upgrade or compliance audit
• Baseline before LDAP/SSO - even federated flows often map to a DB role

Working Example

Migrate legacy md5 role to SCRAM and enforce on connections.

-- Confirm encryption setting
SHOW password_encryption;  -- scram-sha-256
 
-- Re-set password to regenerate SCRAM verifier
ALTER ROLE app_api PASSWORD 'new-rotated-secret-from-vault';
 
-- Confirm verifier format
SELECT rolname,
       left(rolpassword, 14) AS hash_prefix
FROM pg_authid
WHERE rolname = 'app_api';

# pg_hba.conf - no md5 for application roles
hostssl  orders  app_api  10.20.0.0/16  scram-sha-256

# Client must use libpq/psql/driver with SCRAM support (PostgreSQL 10+ clients)
psql "host=db.internal dbname=orders user=app_api sslmode=verify-full"

What this demonstrates:

• ALTER ROLE ... PASSWORD regenerates SCRAM verifier when password_encryption = scram-sha-256
• pg_hba method must be scram-sha-256, not md5
• Old JDBC/psql without SCRAM will fail fast (upgrade clients)

Deep Dive

How SCRAM Works

1. Client requests authentication for user.
2. Server sends salt and iteration count from stored verifier.
3. Client derives proof from password + salt without sending password on wire.
4. Server verifies proof against pg_authid.rolpassword.

LDAP Integration Overview

# pg_hba.conf - LDAP bind (directory validates password)
hostssl  all  +ldap_users  10.20.0.0/16  ldap ldapserver=ldap.corp.internal ldapbasedn="dc=corp,dc=internal"

-- Map LDAP group to role (pattern)
CREATE ROLE ldap_users;
GRANT app_api TO ldap_users;  -- inherit app privileges via GRANT

LDAP delegates password checks to Active Directory/OpenLDAP. PostgreSQL still authorizes with GRANT. Use LDAPS or TLS to directory.

Cloud SSO / IAM Patterns

Static SCRAM passwords remain common for migration roles and third-party BI tools without IAM support.

PostgreSQL Notes

-- Force SCRAM for role password changes
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();
 
-- List roles still on md5
SELECT rolname FROM pg_authid
WHERE rolpassword LIKE 'md5%';

Gotchas

• md5 verifiers after upgrade - login works only if pg_hba still allows md5. Fix: re-ALTER ROLE PASSWORD for each login role.
• PgBouncer auth_query mismatch - pooler must support SCRAM to backend. Fix: PgBouncer 1.21+ with auth_type = scram-sha-256.
• ** JDBC old versions** - SCRAM unsupported. Fix: PostgreSQL JDBC 42.2.0+.
• Shared password in connection pool config - rotation requires coordinated deploy. Fix: vault sidecar or IAM tokens per instance.
• LDAP without TLS - directory password exposed. Fix: LDAPS and hostssl to Postgres.
• Logging failed auth at log_statement = all - noise only; use connection logs and external SIEM correlation.

Alternatives

FAQs

Related

• Security Basics - roles and grants
• SSL/TLS Configuration - encrypt login channel
• pg_hba.conf & Authentication - hba rules
• Connection Proxies - PgBouncer auth
• Security Best Practices - credential hygiene

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Y29kZWd1aWRlcy5pb3xjZ2lvMTA2NXwyMDI2MDc=