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

On 12/09/2016 10:19 AM, Michael Paquier wrote:
> On Fri, Dec 9, 2016 at 5:11 PM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>> Couple of things I should write down before I forget:
>>
>> 1. It's a bit cumbersome that the scram verifiers stored in
>> pg_authid.rolpassword don't have any clear indication that they're scram
>> verifiers. MD5 hashes are readily identifiable by the "md5" prefix. I think
>> we should use a "scram-sha-256:" for scram verifiers.
>
> scram-sha-256 would make the most sense to me.
>
>> Actually, I think it'd be awfully nice to also prefix plaintext passwords
>> with "plain:", but I'm not sure it's worth breaking the compatibility, if
>> there are tools out there that peek into rolpassword. Thoughts?
>
> pgbouncer is the only thing coming up in mind. It looks at pg_shadow
> for password values. pg_dump'ing data from pre-10 instances will also
> need to adapt. I see tricky the compatibility with the exiting CREATE
> USER PASSWORD command though, so I am wondering if that's worth the
> complication.
>
>> 2. It's currently not possible to use the plaintext "password"
>> authentication method, for a user that has a SCRAM verifier in rolpassword.
>> That seems like an oversight. We can't do MD5 authentication with a SCRAM
>> verifier, but "password" we could.
>
> Yeah, that should be possible...

The tip of the work branch can now do SCRAM authentication, when a user 
has a plaintext password in pg_authid.rolpassword. The reverse doesn't 
work, however: you cannot do plain "password" authentication, when the 
user has a SCRAM verifier in pg_authid.rolpassword. It gets worse: plain 
"password" authentication doesn't check if the string stored in 
pg_authid.rolpassword is a SCRAM authenticator, and treats it as a 
plaintext password, so you can do this:

PGPASSWORD="scram-sha-256:mDBuqO1mEekieg==:4096:17dc259499c1a184c26ee5b19715173d9354195f510b4d3af8be585acb39ae33:d3d713149c6becbbe56bae259aafe4e95b79ab7e3b50f2fbd850ea7d7b7c114f" 
  psql postgres  -h localhost -U scram_user

I think we're going to have a more bugs like this, if we don't start to 
explicitly label plaintext passwords as such.

So, let's add "plain:" prefix to plaintext passwords, in 
pg_authid.rolpassword. With that, these would be valid values in 
pg_authid.rolpassword:

plain:foo
md55a962ce7a24371a10e85627a484cac28
scram-sha-256:mDBuqO1mEekieg==:4096:17dc259499c1a184c26ee5b19715173d9354195f510b4d3af8be585acb39ae33:d3d713149c6becbbe56bae259aafe4e95b79ab7e3b50f2fbd850ea7d7b7c114f

But anything that doesn't begin with "plain:", "md5", or 
"scram-sha-256:" would be invalid. You shouldn't have invalid values in 
the column, but if you do, all the authentication mechanisms would 
reject it.

It would be nice to also change the format of MD5 passwords to have a 
colon, as in "md5:<hash>", but that's probably not worth breaking 
compatibility for. Almost no-one stores passwords in plaintext, so 
changing the format of that wouldn't affect many people, but there might 
well be tools out there that peek into MD5 hashes.

- Heikki