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
Change:
host all all 127.0.0.1/32 scram-sha-256
To:
host all all 127.0.0.1/32 md5
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
Step 3 — Verify
sudo cat /etc/pgpool2/pool_passwd
# Should show:
# postgres:md5xxxxxxxxxxxxxxxxxxxxxxxx
Step 4 — Restart
sudo systemctl restart pgpool2
psql -h 127.0.0.1 -p 9999 -U postgres -d your_db
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
Update the pg_hba.conf
sudo nano /etc/postgresql/*/main/pg_hba.conf
Change:
host all all 127.0.0.1/32 scram-sha-256
To:
host all all 127.0.0.1/32 md5
Step 2 — Change the default auth method in postgresql.conf
sudo nano /etc/postgresql/*/main/postgresql.conf
Find and change (it can be commented):
password_encryption = scram-sha-256
To:
password_encryption = md5
Step 3 — Reset the postgres password so it’s stored as MD5
sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'StrongPassword123';"
This re-hashes the password using MD5 now that password_encryption is set to md5.
Step 4 — Restart PostgreSQL
sudo systemctl restart postgresql
Step 5 — Add md5 entry to pgpool pool_passwd
pg_md5 -m -u postgres StrongPassword123
sudo systemctl restart pgpool2
Step 6 — Test
psql -h 127.0.0.1 -p 9999 -U postgres -d your_db
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';"
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';"
Step 2 — Rebuild pool_passwd cleanly
# Clear old entries
sudo truncate -s 0 /etc/pgpool2/pool_passwd
# Add fresh md5 entry
pg_md5 -m -u postgres StrongPassword123# Verify
sudo cat /etc/pgpool2/pool_passwd
# Must show: postgres:md5xxxxxxxxxxxxxxxx
Step 3 — Make sure pool_hba.conf uses MD5
sudo cat /etc/pgpool2/pool_hba.conf | grep -v "^#" | grep -v "^$"
Should showmd5, not scram-sha-256:
host all all 127.0.0.1/32 md5
Step 4 — Restart both services in order
sudo systemctl restart postgresql
sudo systemctl restart pgpool2
Step 5 — Test
psql -h 127.0.0.1 -p 9999 -U postgres -d testdb