As you perhaps know I'm fan of pgbouncer – connection pooling solution for PostgreSQL.

It can do many really cool things, but has one slight issue.

Since it can reuse connections – it has to provide a way to check if user supplied password is correct without consulting database. And it lately (since 9.0 to be exact) became somewhat of a problem.

First let's check why pgbouncer needs to know your password.

As we know it pools connections. So when application connects to pgbouncer, and it (pgbouncer) doesn't have free db connection, it has to start one anyway, and it can consult the password then with PostgreSQL.

But what if pgbouncer actually has spare db connection? Well – enforcing authentication with Pg would kind-of defeat the purpose of connection pooler. So pgbouncer has to work around the problem.

One solution (quite often used) is to just “trust" the connections. It is actually less of a risk than some imagine, as this is usually done in closed, secure networks.

But what if you really want password protection. Well, the only solution is to give pgbouncer access to list of users and passwords from PostgreSQL. Up to 8.4 it was enough to add to pgbouncer.ini line like this:

auth_file = /PG/data/global/pg_auth

And you're set, as pg_auth was a file in which all passwords were stored, and pgbouncer was able to parse this file.

Since 9.0 – there is no pg_auth file. Passwords are stored in more-or-less normal table, and as such file has different format, and it could depend on version of pg.

The solution is to make the file by hand in format like:

"user1" "pass1"
"user2" "pass2"

And provide pgbouncer with link to it. Marko Kreen (author of pgbouncer) even wrote a python script to do it.

But it's not really perfect solution. It has to be run after every password change in PostgreSQL. And since you might have users changing their passwords at will, and we can't have triggers on system tables – we're pretty much hosed. Of course – I could put the script to crontab, and run every minute, but it has some slight issues:

• password change starts to work with some delay
• most of the times you're just burning cpu cycles, as most of the time the script will be called – there will be no passwords to change

Is there a better solution? Lucikly – yes.

To do it, you need incron. This is pretty cool daemon, which is like cron, but it runs programs based not on time, but on filesystem events.

So. We need to know which file to track. Unfortunately – name of pg_authid file (it's the table that holds passwords) is not fixed/constant. It can be always checked with:

$ SELECT pg_relation_filepath('pg_authid');
 pg_relation_filepath
----------------------
 global/11627
(1 ROW)

But it can change. Of course we can work around it, but I decided to simply make the password-regenerator work on any modification in global/

So. I wrote a simple script:

#!/bin/bash
export AUTH_FILE=/home/pgdba/etc/pgbouncer.auth
 
TMPFILE=$( mktemp )
trap 'rm -f "$TMPFILE"' EXIT
 
psql -qAtX -c 'SELECT $$"$$ || replace( usename, $$"$$, $$""$$) || $$" "$$ || replace( passwd, $$"$$, $$""$$ ) || $$"$$ from pg_shadow where passwd is not null order by 1' > "$TMPFILE"
 
if [[ ! -s "$TMPFILE" ]]
then
    exit
fi
 
if diff -q "$AUTH_FILE" "$TMPFILE" &>/dev/null
then
    exit
fi
 
cat "$TMPFILE" > "$AUTH_FILE"

And then added to incrontab:

/home/pgdba/data-9.0/global/ IN_MODIFY /home/pgdba/bin/regenerate.pgbouncer.pg_auth

Which basically means: on MODIFICATION of any file in /home/pgdba/data-9.0/global/ directory, run /home/pgdba/bin/regenerate.pgbouncer.pg_auth script.

Now. It can happen that you run “ALTER USER WITH PASSWORD" and the file is not immediately modified. In such case – just issue CHECKPOINT; just after ALTER USER.

it's not perfect, but I'm quite happy with results.