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 (yum, apt, brew, etc):
$ 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 pg_hba.conf:
# 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 /etc:
[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)
Success!
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.
(doug@127.0.0.1:6432/doug[92825]) #
Huzzah!
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.
(doug@127.0.0.1:6432/doug[97966]) \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
the 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:
(doug@127.0.0.1:6432/doug[92825]) # CREATE ROLE pgbouncer ENCRYPTED PASSWORD 'secret';
CREATE ROLE
Time: 4.102 ms
(doug@127.0.0.1:6432/doug[92825]) # GRANT CONNECT ON DATABASE doug TO pgbouncer;
GRANT
Time: 13.531 ms
(doug@[local]:5432/doug[92825]) # ALTER ROLE pgbouncer LOGIN;
ALTER ROLE
Time: 6.457 ms
(doug@127.0.0.1:6432/doug[92825]) # 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
(doug@127.0.0.1:6432/doug[92825]) # REVOKE ALL ON FUNCTION public.user_lookup(text) FROM public, pgbouncer;
REVOKE
Time: 7.330 ms
(doug@127.0.0.1:6432/doug[92825]) # 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.
(doug@127.0.0.1:6432/doug[7076]) # 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 pgbouncer.ini:
$ 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 5432:
$ 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.
WIN WIN
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.
☮️