Waiting for 9.3 – Report pg_hba line number and contents when users fail to log in

On 10th of March, Magnus Hagander committed patch:

Report pg_hba line number and contents when users fail to log in
 
Instead of just reporting which user failed to log in, log both the
line number in the active pg_hba.conf file (which may not match reality
in case the file has been edited and not reloaded) and the contents of
the matching line (which will always be correct), to make it easier
to debug incorrect pg_hba.conf files.
 
The message to the client remains unchanged and does not include this
information, to prevent leaking security sensitive information.
 
Reviewed by Tom Lane and Dean Rasheed

As it happens, the commit message describes quite well what's going on, so I'll just show how the messages look like.

First, I created pg_hba.conf file with following content:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
LOCAL   ALL         ALL                               trust
host    ALL         ALL         127.0.0.1/32          md5
host    ALL         ALL         ::1/128               md5
host    ALL         ALL         0.0.0.0/0             md5

And then I tried to log with bad password:

=$ psql -h localhost
Password:
psql: FATAL:  password authentication failed FOR USER "depesz"

In pg logs I got:

2013-03-12 09:53:58.388 CET [UNKNOWN]@[UNKNOWN] 20943  LOG:  connection received: host=127.0.0.1 port=33193
2013-03-12 09:54:00.548 CET [UNKNOWN]@[UNKNOWN] 20945  LOG:  connection received: host=127.0.0.1 port=33194
2013-03-12 09:54:00.549 CET depesz@depesz 20945 127.0.0.1(33194) FATAL:  password authentication failed FOR USER "depesz"
2013-03-12 09:54:00.549 CET depesz@depesz 20945 127.0.0.1(33194) DETAIL:  Connection matched pg_hba.conf line 3: "host    all         all         127.0.0.1/32          md5"

The DETAIL: line is the new one, and it shows which line was used to authenticate me.

It is important to note that this line is not shown when pg_hba.conf renders “reject" method. In such case, all you'll get is:

2013-03-12 09:51:16.274 CET depesz@depesz 20736 127.0.0.1(33169) FATAL:  pg_hba.conf rejects connection FOR host "127.0.0.1", USER "depesz", DATABASE "depesz"

In case of failed peer (ident) authentication, I get:

2013-03-12 09:55:48.352 CET [UNKNOWN]@[UNKNOWN] 21082  LOG:  connection received: host=[LOCAL]
2013-03-12 09:55:48.353 CET pgdba@pgdba 21082 [LOCAL] LOG:  provided USER name (pgdba) AND authenticated USER name (depesz) do NOT MATCH
2013-03-12 09:55:48.353 CET pgdba@pgdba 21082 [LOCAL] FATAL:  Peer authentication failed FOR USER "pgdba"
2013-03-12 09:55:48.353 CET pgdba@pgdba 21082 [LOCAL] DETAIL:  Connection matched pg_hba.conf line 2: "local   all         all                               peer"

This looks really useful. Will definitely help us (irc users) help new users when they'll hit authentication issues. Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.