On 27th of November, Robert Haas committed patch which adds new, interesting contrib module:

New contrib module, auth_delay.
 
KaiGai Kohei, with a few changes by me.

Well, the commit log is far from informative, so let's see what and how it does.

First a word of introduction – in case you don't know KaiGai Kohei is a guy that is behind SE-PostgreSQL “thing", so even without checking what the module does – we can assume it's something related to security.

So. The basic idea is – slow down brute-force password cracking attempts.

Let's imagine that some bad cracker has access to host that he can use to try to connect to PostgreSQL. He can then try to run something like this (of course smarter, but that's just example):

perl -le 'my $x = "a"; while (1) {print $x; $x++; last if $x eq "zz"}' | \
    while read PGPASSWORD
    do
        export PGPASSWORD
        if psql -U test -d postgres -c 'select 1' &>/dev/null
        then
            echo "$PGPASSWORD"
            break
        fi
    done

Which is pretty simple brute force password cracker. How fast does it work? On my laptop it tests 140 passwords per second. This is of course too slow for any serious cracking, but with some trivial changes to the approach I can get *much* better speeds.

Now. What does auth_delay do, and how?

First, we need to change postgresql.conf, and make sure we have something like these lines:

shared_preload_libraries = 'auth_delay'
custom_variable_classes = 'auth_delay'
auth_delay.milliseconds = '500'

and then restart PostgreSQL. For comparison. Before the change time of trying to authenticate with bad password:

=$ TIME PGPASSWORD=bad psql -U test -d postgres -c 'select 1'
psql: FATAL:  password authentication failed FOR USER "test"
 
REAL    0m0.009s
USER    0m0.004s
sys     0m0.004s

With auth_delay loaded:

=$ TIME PGPASSWORD=bad psql -U test -d postgres -c 'select 1'
psql: FATAL:  password authentication failed FOR USER "test"
 
REAL    0m0.509s
USER    0m0.000s
sys     0m0.004s

Which is pretty cool. As it changes my cracker to effectively 2 passwords per second (in single thread) – thus making it impractical to use this approach.

Of course – this makes sense only in case you can have attackers get access to PostgreSQL, which they shouldn't. But still – it's really nice approach to alleviate some threats.