Question 1

Which format does PostgreSQL use?

Accepted Answer

PostgreSQL 10 (2017) introduced SCRAM-SHA-256 per RFC 7677. PostgreSQL 14 (2021) made it the default. Older PostgreSQL versions (and pre-14 installs not yet migrated) use a legacy MD5 format: `md5` + MD5(password + username). Both formats are stored in pg_authid.rolpassword. PostgreSQL detects the format by prefix on login. For new installations, SCRAM-SHA-256 is the right choice, it's salted, iterated (4,096 rounds default), and resists offline brute-force much better than the legacy MD5.

Question 2

How do I set the hash directly?

Accepted Answer

Use ALTER ROLE with a pre-hashed password. For SCRAM: `ALTER ROLE u WITH PASSWORD 'SCRAM-SHA-256$4096:$:';`. For MD5: `ALTER ROLE u WITH PASSWORD 'md5';`. PostgreSQL detects the hash format from the prefix and stores it directly without re-hashing. This is useful for migration scripts or when you need to set a password without having the plaintext in your session history.

Question 3

Why does the MD5 format need the username?

Accepted Answer

PostgreSQL salts the legacy MD5 with the username (MD5(password + username)) so two users with the same password produce different hashes. This was an attempt to prevent rainbow-table attacks against captured pg_authid contents. It's weak protection (the username is public, and the iteration count is 1), which is why PostgreSQL 14 switched the default to SCRAM-SHA-256.

PostgreSQL Password Hash Generator

Frequently Asked Questions