DEV Community

Arnob
Arnob

Posted on

PgPool2 & PostgreSQL — Bypass SCRAM entirely, use MD5

#database #postgres #security #tutorial

SCRAM + AES decryption is tricky to get right. The fastest working solution is to switch to MD5 in pool_hba.conf

Step 1 — Edit pool_hba.conf

sudo nano /etc/pgpool2/pool_hba.conf
Enter fullscreen mode Exit fullscreen mode

Change:

host    all    all    127.0.0.1/32    scram-sha-256
Enter fullscreen mode Exit fullscreen mode

To:

host    all    all    127.0.0.1/32    md5
Enter fullscreen mode Exit fullscreen mode

Step 2 — Add md5 password to pool_passwd

# This adds an md5 entry without needing pool_key at all
pg_md5 -m -u postgres StrongPassword123
Enter fullscreen mode Exit fullscreen mode

Step 3 — Verify

sudo cat /etc/pgpool2/pool_passwd
# Should show:
# postgres:md5xxxxxxxxxxxxxxxxxxxxxxxx
Enter fullscreen mode Exit fullscreen mode

Step 4 — Restart

sudo systemctl restart pgpool2
psql -h 127.0.0.1 -p 9999 -U postgres -d your_db
Enter fullscreen mode Exit fullscreen mode

This avoids the AES key complexity entirely. Share the diagnostic output if you want to keep SCRAM working.

You want to change PostgreSQL itself to use md5 instead of scram-sha-256. Here’s how:

Step 1 — Edit pg_hba.conf

# Find the file
sudo find / -name pg_hba.conf 2>/dev/null
Enter fullscreen mode Exit fullscreen mode

Update the pg_hba.conf

sudo nano /etc/postgresql/*/main/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

Change:

host    all    all    127.0.0.1/32    scram-sha-256
Enter fullscreen mode Exit fullscreen mode

To:

host    all    all    127.0.0.1/32    md5
Enter fullscreen mode Exit fullscreen mode

Step 2 — Change the default auth method in postgresql.conf

sudo nano /etc/postgresql/*/main/postgresql.conf
Enter fullscreen mode Exit fullscreen mode

Find and change (it can be commented):

password_encryption = scram-sha-256
Enter fullscreen mode Exit fullscreen mode

To:

password_encryption = md5
Enter fullscreen mode Exit fullscreen mode

Step 3 — Reset the postgres password so it’s stored as MD5

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'StrongPassword123';"
Enter fullscreen mode Exit fullscreen mode

This re-hashes the password using MD5 now that password_encryption is set to md5.

Step 4 — Restart PostgreSQL 

sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

Step 5 — Add md5 entry to pgpool pool_passwd 

pg_md5 -m -u postgres StrongPassword123
sudo systemctl restart pgpool2
Enter fullscreen mode Exit fullscreen mode

Step 6 — Test 

psql -h 127.0.0.1 -p 9999 -U postgres -d your_db
Enter fullscreen mode Exit fullscreen mode

This should resolve it completely. Let me know what you get!

The error changed — good progress! Now pgpool is connected, but pgpool itself can’t authenticate to the PostgreSQL backend. This means pgpool’s stored password doesn’t match what PostgreSQL expects.

Step 1 — Verify PostgreSQL is actually using md5 now 

sudo -u postgres psql -c "SELECT usename, passwd FROM pg_shadow WHERE usename='postgres';"
Enter fullscreen mode Exit fullscreen mode

The passwd column should start with md5, not SCRAM-SHA-256. If it still shows SCRAM-SHA-256, the password wasn't re-hashed — redo:

sudo -u postgres psql -c "SET password_encryption = 'md5'; ALTER USER postgres WITH PASSWORD 'StrongPassword123';"
Enter fullscreen mode Exit fullscreen mode

Step 2 — Rebuild pool_passwd cleanly 

# Clear old entries
sudo truncate -s 0 /etc/pgpool2/pool_passwd
Enter fullscreen mode Exit fullscreen mode 
# Add fresh md5 entry
pg_md5 -m -u postgres StrongPassword123# Verify
sudo cat /etc/pgpool2/pool_passwd
# Must show: postgres:md5xxxxxxxxxxxxxxxx
Enter fullscreen mode Exit fullscreen mode

Step 3 — Make sure pool_hba.conf uses MD5 

sudo cat /etc/pgpool2/pool_hba.conf | grep -v "^#" | grep -v "^$"
Enter fullscreen mode Exit fullscreen mode

Should showmd5, not scram-sha-256:

host             all            all          127.0.0.1/32            md5
Enter fullscreen mode Exit fullscreen mode

Step 4 — Restart both services in order 

sudo systemctl restart postgresql
sudo systemctl restart pgpool2
Enter fullscreen mode Exit fullscreen mode

Step 5 — Test 

psql -h 127.0.0.1 -p 9999 -U postgres -d testdb
Enter fullscreen mode Exit fullscreen mode

Top comments (0)