Re: pg_authid.rolpassword format (was Re: Password identifiers, protocol aging and SCRAM protocol)

On 12/14/2016 12:27 PM, Magnus Hagander wrote:
> I would so like to just drop support for plain passwords completely :) But
> there's a backwards compatibility issue to think about of course.
>
> But -- is there any actual usecase for them anymore?

Hmm. At the moment, I don't think there is.

But, a password stored in plaintext works with either MD5 or SCRAM, or
any future authentication mechanism. So as soon as we have SCRAM
authentication, it becomes somewhat useful again.

In a nutshell:

auth / stored MD5 SCRAM plaintext
-----------------------------------------
password Y Y Y
md5 Y N Y
scram N Y Y

If a password is stored in plaintext, it can be used with any
authentication mechanism. And the plaintext 'password' authentication
mechanism works with any kind of a stored password. But an MD5 hash
cannot be used with SCRAM authentication, or vice versa.

I just noticed that the manual for CREATE ROLE says:

> Note that older clients might lack support for the MD5 authentication
> mechanism that is needed to work with passwords that are stored
> encrypted.

That's is incorrect. The alternative to MD5 authentication is plain
'password' authentication, and that works just fine with MD5-hashed
passwords. I think that sentence is a leftover from when we still
supported "crypt" authentication (so I actually get to blame you for
that ;-), commit 53a5026b). Back then, it was true that if an MD5 hash
was stored in pg_authid, you couldn't do "crypt" authentication. That
might have left old clients out in the cold.

Now that we're getting SCRAM authentication, we'll need a similar notice
there again, for the incompatibility of a SCRAM verifier with MDD5
authentication and vice versa.

> If not, another option could be to just specifically check that it's *not*
> "md5<something>" or "scram-<something>:<something>". That would invalidate
> plaintext passwords that have those texts in them of course, but what's the
> likelyhood of that in reality?

Hmm, we have dismissed that risk for the MD5 hashes (and we also have a
length check for them), but as we get new hash formats, the risk
increases. Someone might well want to use "plain:of:jars" as password.
Perhaps we should use a more complicated pattern.

I googled around for how others store SCRAM and other password hashes.
Many other systems seem to have similar naming schemes. The closest
thing to a standard I could find was:

https://github.com/P-H-C/phc-string-format/blob/master/phc-sf-spec.md

Perhaps we should also use something like "$plain$<password>" or
"$scram-sha-256$<iterations>$<salt>$<key>$"?

There's also https://tools.ietf.org/html/rfc5803, which specifies how to
store SCRAM verifiers in LDAP. I don't understand enough of LDAP to
understand what those actually look like, though, and there were no
examples in the RFC.

I wonder if we should also worry about storing multiple verifiers in
rolpassword? We don't support that now, but we might in the future. It
might come handy, if you could easily store multiple hashes in a single
string, separated by commas for example.

- Heikki