I’ve noticed several individuals inquiring lately about pgBouncer and how they can avoid putting all users and their passwords in it’s
auth_file. After the most recent such inquiry (hi Richard!) I decided I’d write this"post"to
hopefully make it clearer how to use ‘pass-through auth’ and avoid maintaining your users and their passwords in an external file. So let’s see what this takes, shall we?
First, install pgBouncer as per your OS (
$ brew install pgbouncer Updating Homebrew... ==> Auto-updated Homebrew! Updated 1 tap (homebrew/core). ==> Updated Formulae <snip> ==> Downloading https://homebrew.bintray.com/bottles/pgbouncer-1.8.1.high_sierra ######################################################################## 100.0% ==> Pouring pgbouncer-1.8.1.high_sierra.bottle.tar.gz ==> Caveats The config file: /usr/local/etc/pgbouncer.ini is in the "ini" format and you will need to edit it for your particular setup. See: https://pgbouncer.github.io/config.html The auth_file option should point to the /usr/local/etc/userlist.txt file which can be populated by the /usr/local/opt/pgbouncer/bin/mkauth.py script. To have launchd start pgbouncer now and restart at login: brew services start pgbouncer Or, if you do not want/need a background service you can just run: pgbouncer -q /usr/local/etc/pgbouncer.ini ==> Summary 🍺 /usr/local/Cellar/pgbouncer/1.8.1: 17 files, 399.9KB
Great, so now we have pgBouncer installed.
To make life easier on ourselves, we’re going to temporarily enable trusted local socket connections in our
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust
Right now, this is the only line in my
pg_hba.conf. Let’s SIGHUP the postmaster so it takes affect:
$ pg_ctl -D $PGDATA reload server signaled
And test it:
$ unset PGPASSWORD ; psql -U doug -d doug -c "select now();" ┌───────────────────────────────┐ │ now │ ├───────────────────────────────┤ │ 2018-08-07 13:19:06.343245-04 │ └───────────────────────────────┘ (1 row) Time: 1.959 ms
OK, we can connect without issue.
Let’s configure pgBouncer now. As per the output above, I need to edit
/usr/local/etc/pgbouncer.ini but yours is probably in plain old
[databases] ; any db over Unix socket * = [pgbouncer] logfile = /Users/doug/pgbouncer.log pidfile = /Users/doug/pgbouncer.pid ; IP address or * which means all IPs listen_addr = 127.0.0.1 listen_port = 6432 unix_socket_dir = /tmp ; any, trust, plain, crypt, md5, cert, hba, pam auth_type = trust auth_file = /Users/doug/userlist.txt admin_users = doug stats_users = doug pool_mode = transaction server_reset_query = DISCARD ALL max_client_conn = 100 default_pool_size = 20
As you can see, we’re gonna pass connections to any db back to the postmaster via a local socket. I put the logs in my
$HOME for ease of use. I put the
auth_file in my
$HOME as well. Then I set myself up as both an admin and stats
user. I changed the mode into transaction which is usually the mode you want. Now, I add myself to the auth_file:
$ echo '"doug" "md5540094bd8172cd963fdfa773fe44b488"' > userlist.txt
(NOTE: I did a select on pg_shadow as a SUPERUSER to get these values.)
And start pgBouncer:
$ pgbouncer /usr/local/etc/pgbouncer.ini 2018-08-07 13:43:46.453 92057 LOG File descriptor limit: 7168 (H:-1), max_client_conn: 90, max fds possible: 100 2018-08-07 13:43:46.455 92057 LOG listening on 127.0.0.1:6432 2018-08-07 13:43:46.455 92057 LOG listening on unix:/tmp/.s.PGSQL.6432 2018-08-07 13:43:46.455 92057 LOG process up: pgbouncer 1.8.1, libevent 2.1.8-stable (kqueue), adns: evdns2, tls: OpenSSL 1.0.2o 27 Mar 2018
Now, we see if we can connect to pgBouncer’s internal db:
$ psql -h 127.0.0.1 -p 6432 -d pgbouncer -X psql (10.4, server 1.8.1/bouncer) Type "help" for help.
pgbouncer=# show pools; database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode +++++++++--++++++++++--++++++++++--+++++++++++++++++++++++--++++++++++++++++++++++++++++++--++++++++++-+++++++++++++++++++++++++++++++++-- pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | statement (1 row)
Now, can we connect to one of our PostgreSQL dbs through pgBouncer:
$ psql -h 127.0.0.1 -p 6432 -d doug psql (10.4) Type "help" for help. (email@example.com:6432/doug) #
We will now alter
pgbouncer.ini and set
auth_type = md5 and edit
pg_hba.conf to use md5 as well to make sure we’re not passing around plaintext passwords. Our retest looks like:
$ grep ^local $PGDATA/pg_hba.conf local all all md5 $ pg_ctl -D $PGDATA reload server signaled doug@ReturnOfTheMac ~> psql -h 127.0.0.1 -p 6432 -d doug Password:
psql (10.4) Type "help" for help. (firstname.lastname@example.org:6432/doug) \q
$ psql -h 127.0.0.1 -p 6432 -d pgbouncer -X Password:
psql (10.4, server 1.8.1/bouncer) Type "help" for help. pgbouncer= \q
Which, as you can see, we were now password prompted both times!
Now, that we know it all works, we can go about changing things to not expose users through
auth_file. First, we’ll create a
pgbouncer user on our db, then we will
create a SECURITY DEFINER function will allow the
pgbouncer user to
(essentially) ‘sudo’ as a superuser to look at the
pg_shadow table, and finally
we will ensure only our
pgbouncer user can execute that function:
(email@example.com:6432/doug) # CREATE ROLE pgbouncer ENCRYPTED PASSWORD 'secret'; CREATE ROLE Time: 4.102 ms (firstname.lastname@example.org:6432/doug) # GRANT CONNECT ON DATABASE doug TO pgbouncer; GRANT Time: 13.531 ms (doug@[local]:5432/doug) # ALTER ROLE pgbouncer LOGIN; ALTER ROLE Time: 6.457 ms (email@example.com:6432/doug) # CREATE OR REPLACE FUNCTION public.user_lookup(in i_username text, out uname text, out phash text) [more] - > RETURNS record AS $$ [more] $ > BEGIN [more] $ > SELECT usename, passwd FROM pg_catalog.pg_shadow [more] $ > WHERE usename = i_username INTO uname, phash; [more] $ > RETURN; [more] $ > END; [more] $ > $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE FUNCTION Time: 21.219 ms (firstname.lastname@example.org:6432/doug) # REVOKE ALL ON FUNCTION public.user_lookup(text) FROM public, pgbouncer; REVOKE Time: 7.330 ms (email@example.com:6432/doug) # GRANT EXECUTE ON FUNCTION public.user_lookup(text) TO pgbouncer; GRANT Time: 7.572 ms
(NOTE: Astute readers will note I’m connected as ‘doug’ to the db. This works cause in my setup that is a SUPERUSER. You should probably use the ‘postgres’ account.)
And, let’s tell PgBouncer to use this function:
$ grep ^auth /usr/local/etc/pgbouncer.ini auth_type = md5 auth_file = /Users/doug/userlist.txt auth_user = pgbouncer auth_query = SELECT * FROM public.user_lookup($1);
And let’s edit our
auth_file to only contain the
pgbouncer user’s info:
doug@ReturnOfTheMac ~> cat userlist.txt "pgbouncer" "md509d12ff67352814e4c467c7f55a3a1d7"
Restart pgBouncer, and let’s recheck:
doug@ReturnOfTheMac ~> psql -h 127.0.0.1 -p 6432 -d doug 2 Password:
psql (10.4) Type "help" for help. (firstname.lastname@example.org:6432/doug) # select now(); ┌───────────────────────────────┐ │ now │ ├───────────────────────────────┤ │ 2018-08-07 14:43:45.938919-04 │ └───────────────────────────────┘ (1 row) Time: 0.438 ms
It works! But what about the pgBouncer internal db?
$ psql -h 127.0.0.1 -p 6432 -d pgbouncer psql: ERROR: No such user: doug
Well, that makes sense. The
auth_file only has a
pgbouncer user. So, let’s edit the
$ grep '_users' /usr/local/etc/pgbouncer.ini admin_users = pgbouncer stats_users = pgbouncer
Retart pgBouncer once more and check:
doug@ReturnOfTheMac ~> psql -h 127.0.0.1 -p 6432 -d pgbouncer -U pgbouncer -X Password for user pgbouncer:
psql (10.4, server 1.8.1/bouncer) Type "help" for help. pgbouncer=# show clients; type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | wait | wait_us | ptr | link | remote_pid | tls ++++++++++++++++--++++++++++--+++++++--++++++++++--+++++++-+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-++++++++++++++++++++++++-- C | pgbouncer | pgbouncer | active | 127.0.0.1 | 54191 | 127.0.0.1 | 6432 | 2018-08-07 14:48:03 | 2018-08-07 14:48:06 | 0 | 0 | 0x7fa082005010 | | 0 | (1 row)
And we’re golden!
You can connect to pgBouncer internally using the
pgbouncer user, and you can connect to our normal PostgreSQL db as any valid user and it uses our function to do the auth!
To complete this setup, we’re gonna move PostgreSQL to port
5433 and pgBouncer to port
$ grep port /usr/local/etc/pgbouncer.ini listen_port = 5432 doug@ReturnOfTheMac ~> grep port $PGDATA/postgresql.conf port = 5433 # (change requires restart)
So now, if someone tries to connect to our PostgreSQL on the default TCP/IP port, it goes through PgBouncer transparently (and then pgBouncer connects locally via a socket). Our users/apps are none the wiser, and us DBAs can always
ssh into the box and connect directly to PostgreSQL via socket if needed. And we’re not exposing any user/app passwords in a text file on the OS.
One final note: this is only working for the ‘doug’ database currently. If I wanted this to also work for another database, say ‘postgres’ or ‘prod_app’ then I would need to GRANT CONNECT on those dbs to ‘pgbouncer’ and would need to create my function in them as well.