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.