7

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/[email protected]:43286 login attempt: db=postgres user=postgres tls=no
2019-08-20 13:46:01.080 16446 LOG C-0x1028ce0: postgres/[email protected]:43286 closing because: client unexpected eof (age=0)
2019-08-20 13:46:06.980 16446 LOG C-0x1028ce0: postgres/[email protected]:43414 login attempt: db=postgres user=postgres tls=no
2019-08-20 13:46:06.980 16446 LOG C-0x1028ce0: postgres/[email protected]:43414 closing because: auth failed (age=0)
2019-08-20 13:46:06.980 16446 WARNING C-0x1028ce0: postgres/[email protected]:43414 pooler error: auth failed
2
  • What error message appears in the database server's log file?
    – jjanes
    Commented Aug 20, 2019 at 15:19
  • @jjanes Nothing I can see in the log that gives any indication of an error(assuming I got the right log - ubuntu 16.04 postgres 9.3 | /var/log/postgresql/postgresql-9.3-main.log )
    – Josh Kirby
    Commented Aug 20, 2019 at 16:35

2 Answers 2

12

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.

3
  • 1
    in psql: SELECT concat('"', usename, '" "', passwd, '"') FROM pg_shadow;
    – echo
    Commented Jul 15, 2020 at 14:41
  • Thanks you so much. you made my day. I was struggling with this problem for so long. Commented May 3, 2021 at 18:14
  • thanks, ChatGPT wasted my 3 hours, for this simple solution Commented Apr 27, 2023 at 15:47
1

Managed to resolve the problem, I was using an md5 sum of my Postgres password in the user list. I did this because I saw it in 3 different guides but upon looking at the official docs I saw you need to put your password in plain text while having the auth type still md5. This makes sense now but why 3 different guides on different sites all gave wrong info I am not sure. Got it resolved though so happy about that.

1
  • 1
    Well, you're slightly missing the point, which I shall address in a proper response. auth_type as md5 also supports plain text passwords. So when you put the password in plain text, you're not using the md5 auth feature.
    – RCross
    Commented Nov 21, 2019 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.