December 4th, 2010 by depesz | Tags: , , , , , , | 7 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

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');
(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:

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" ]]
if diff -q "$AUTH_FILE" "$TMPFILE" &>/dev/null

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.

  1. 7 comments

  2. # Marko Tiikkaja
    Dec 4, 2010

    Wow, that’s a cool idea! Thanks for sharing.

  3. # Konstantin
    Jan 2, 2011

    “greater-than” signs in script are incorrectly shown as & gt; entities

  4. Jan 3, 2011


    thanks, fixed.

  5. # Tom Davis
    Oct 20, 2011

    Thanks, this is awesome. And if your postgresql server is running on OS X, you can use a launchd plist to handle changes in global rather than using incron.

  6. # Cody Cutrer
    Jan 19, 2012

    Or, I just added support to pgbouncer to directly query pg_shadow, so no need to do this. The patch is at waiting for acceptance into pgbouncer.

  7. Jan 19, 2012

    That’s *great*. Thanks a lot.

  8. # Daniel Camolez
    Jul 19, 2012

    Isso ai é lindo!!!
    this is beautiful!!!

Leave a comment