Re: scram and \password

On Tue, Mar 14, 2017 at 5:14 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Without md5-only, a user who uses \password to change their password from a
>> newer client would lock themselves out of connecting again from older
>> clients. As a conscious decision (either of the DBA or the user) that
>> would be OK, but to have it happen by default would be unfortunate.
>
> That's a point, but what it implies is that \password needs some input
> from the user about whether to generate a SCRAM or MD5-hashed password.
> It would be a fatal error to try to drive that off the auth method
> that had been used for the current connection, even if \password had a
> way to find that out. By definition, your concern is about clients
> other than the current one, which might well be coming in from other
> addresses and getting challenges based on other pg_hba entries. So
> you can't say that "I came in on a SCRAM connection" is sufficient
> reason to generate a SCRAM password.

To some extent that seems like a question of system policy. Either
the DBA wants users to use SCRAM passwords, or the DBA wants users to
use MD5 passwords, or either is permissible. In the last case, the
user can do what they like, but it seems like a fairly bad idea from a
user perspective to let the user configure a password using a system
that will lock them out. We shouldn't assume the user even has any
knowledge of what's in pg_hba.conf, or that they would know what those
contents meant if they had them. There ought to be something like a
PGC_SUSER GUC that sets the kinds of password verifiers that a user is
allowed to configure, and maybe \password should default to the first
one in the list (but possibly be overridable?).

> In short, I don't think that argument refutes my position that "md5"
> in pg_hba.conf should be understood as allowing SCRAM passwords too.

I'm not sure that's a bad idea, but my first reaction is not to like
it. md5 is a funny spelling of md5-or-scram.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company