Allow peer/ident to fall back to md5?

Hi all

At pgconf-eu Álvaro and I were discussing the idea of allowing 'peer'
and 'ident' authentication to fall back to md5 if the peer/ident check
failed.

This can be done backwards compatibly and without protocol changes. A
client won't be able to tell that the pg_hba.conf line wasn't 'md5'.


I think this would be a nice usability improvement, especially for new
users who have no idea what a unix socket is and who're quite confused
by this:

$ sudo -u postgres psql -q
postgres=# CREATE USER alpha WITH PASSWORD 'beta';
postgres=#
$ psql -W -U alpha
Password for user alpha:
psql: FATAL:  Peer authentication failed for user "alpha"

... or client-library equivalents using connstrings where the fact that
any supplied password is ignored is less obvious.


In terms of BC impact, it'd possibly cause clients to prompt for a
password where they'd otherwise exit with an error - but that's kind of
the idea. If anyone's concerned about that I think it'd be reasonable to
add an option in pg_hba.conf to allow 'ident' and 'peer' to be qualified
with a no_md5_fallback mode.

On the server side, what we'd do is check ident/peer if set in
pg_hba.conf . If it's rejected we'd store the details of why we rejected
it, then send a password request packet for md5 authentication. If
password auth succeeds we'd log in normally. If password auth fails,
we'd emit an error along the lines of:

FATAL: Peer authentication and md5 fallback both failed for user "alpha"
DETAIL: Local user "me" doesn't match remote user "alpha" and md5
password was not sent or did not match.


... and we'd log the usual info about remote and local user not matching
to the server error log too.


Reasonable?

I'm also inclined to add a DEBUG1 message along the lines of "peer
authentication failed because of username mismatch but fallback md5
authentication succeeded" to the client on successful auth. They
wouldn't normally see it, but it'd make it easier to trace what's going
on if you ask for it.

IMO doing this would give us the most important benefits of a proper
authentication negotiation handshake with fallback - without requiring
protocol changes.

It doesn't let you do fancier stuff like "try GSSAPI, fall back to md5"
... but personally, if it deals with the issues around peer/ident, I'll
be happy.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services