Re: WIP: SCRAM authentication

On Fri, Aug 7, 2015 at 3:22 AM, Michael Paquier
<michael(dot)paquier(at)gmail(dot)com> wrote:
> On Tue, Aug 4, 2015 at 4:20 PM, Michael Paquier wrote:
>> I have been looking more in depths at this one, which adds essential
>> infrastructure to support multiple authentication hashes for more protocols.
>> Here are some comments:
>> [spec lines]
>> I am willing to write a patch for the next CF following more or less those
>> lines, depending of course on the outcome of the discussion we can have
>> here, so feel free to comment.
>
> OK, attached as 0001 is the patch that respects those lines for the
> support of multiple password verifiers in system catalogs. I have
> added a new catalog called pg_auth_verifiers that is used at
> authentication to fetch a password value depending on the protocol
> used. With only this patch attached there are two types of verifiers:
> plain and md5. This new catalog is REVOKE'd like pg_authid (pg_authid
> could be made readable be this seems sensitive to me so I am not
> changing it).
>
> I have as well done the following things:
> - Added PASSWORD VERIFIER (md5 = 'hoge', plain = 'hoge') which is used
> as well by pg_dump all to be able to specify password verifiers one by
> one.

Maybe I'm chiming in too late here but I am sorta unimpressed by this.
If the user's password is stored both MD5-hashed and hashed some other
way in the system catalogs, that's less secure than storing it in the
least secure of those ways. And I'm afraid that if we introduce this
new mechanism, we won't really gain any security, because everybody
will just pg_dump or pg_upgrade and the old passwords will stick
around in the system forever. In fact we might lose security if
somebody changes one password verifier but doesn't realize that the
other one is still floating around, memorializing the old password,
and still available to be used for login.

I think we should look for a solution that either (a) allows SCRAM
authentication without requiring any changes to the contents of
pg_authid, like what Heikki proposed before; or (b) forces a hard
break, where at each password change you can decide if you want the
old or new format (probably based on the current value of some
compatibility GUC).

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