Postgresql – Pgbouncer getting auth failing when trying to connect using psql

pgbouncerpostgresqlpostgresql-9.3

I'm trying to configure the latest pgbouncer to work with postgres 9. I can connect to my db using psql with the right password but when I use psql -p 6432 I can't connect with the error message of psql: ERROR: auth failed

This seems like it might be caused by my userlist.txt file, but I'm not sure of that. I checked and all required files are owned fully by Postgres system user

pgbouncer.ini

[databases]
postgres = host=localhost port=5433 auth_user=postgres dbname=postgres

[pgbouncer]
pidfile = /usr/local/pgbouncer-1.9.0/pgbouncer.pid
logfile = /usr/local/pgbouncer-1.9.0/log/pgbouncer.log

user = postgres

listen_addr = *
listen_port = 6432

auth_type = md5
auth_file = /usr/local/pgbouncer-1.9.0/etc/userlist.txt

Userlist.txt

"postgres" "md5<MD5 SUM>"

Command used to start pgbouncer

./bin/pgbouncer -d etc/pgbouncer.ini

Log output showing failure

2019-08-20 13:46:01.080 16446 LOG C-0x1028ce0: postgres/postgres@127.0.0.1:43286 login attempt: db=postgres user=postgres tls=no
2019-08-20 13:46:01.080 16446 LOG C-0x1028ce0: postgres/postgres@127.0.0.1:43286 closing because: client unexpected eof (age=0)
2019-08-20 13:46:06.980 16446 LOG C-0x1028ce0: postgres/postgres@127.0.0.1:43414 login attempt: db=postgres user=postgres tls=no
2019-08-20 13:46:06.980 16446 LOG C-0x1028ce0: postgres/postgres@127.0.0.1:43414 closing because: auth failed (age=0)
2019-08-20 13:46:06.980 16446 WARNING C-0x1028ce0: postgres/postgres@127.0.0.1:43414 pooler error: auth failed

Best Answer

To create an md5 password for PGBouncer (or PostgreSQL for that matter), the formula is:

"md5" + md5(password + username)

Here are 3 ways you can create one, where the username is "admin" and the password is "password123"...

Linux:

# echo -n "md5"; echo -n "password123admin" | md5sum | awk '{print $1}'
md53f84a3c26198d9b94054ca7a3839366d

MacOS:

➜ echo -n "md5"; md5 -qs "password123admin"                                                                                                                                                                                   
md53f84a3c26198d9b94054ca7a3839366d

Python 2:

>>> import hashlib
>>> print("md5" + hashlib.md5("password123" + "admin").hexdigest())
md53f84a3c26198d9b94054ca7a3839366d

Python 3:

As above, but use binary strings...

print("md5" + hashlib.md5(b"password123" + b"admin").hexdigest())

Config Files

The entry in your pgbouncer.ini should be:

auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

and the relevant entry in /etc/pgbouncer/userlist.txt should be:

"admin" "md53f84a3c26198d9b94054ca7a3839366d"

You can then test this by connecting to pgbouncer

psql -h localhost -p 6432 -U admin

... and then typing the plaintext version of your password when prompted.

Related Question