Re: WIP: SCRAM authentication

On 08/08/2015 10:23 AM, Heikki Linnakangas wrote:
> On 08/08/2015 04:27 PM, Robert Haas wrote:
>> I don't see that there's any good reason to allow the same password to
>> be stored in the catalog encrypted more than one way,
>
> Sure there is. If you want to be able to authenticate using different
> mechanism, you need the same password "encrypted" in different ways.
> SCRAM uses verifier that's derived from the password in one way, MD5
> authentication needs an MD5 hash, and yet other protocols have other
> requirements.

That's correct. However, one of the goals of implementing SCRAM
authentication is to allow security-conscious users to get rid of those
reusable md5 hashes, no?

Obviously the backwards-compatibility issues are pretty major ... it'll
be years before all drivers support SCRAM ... but we also want to
provide a path forwards for secure installations in which no md5 hashes
are stored.

This says "backwards-compatible GUC" to me. Here's one idea on how to
handle this:

1. we drop the parameter password_encryption

2. we add the parameter password_storage, which takes a list:
- plain : plain text
- md5 : current md5 hashes
- scram : new scram hashed passwords
This defaults to 'md5, scram' if not specified.
This list might be extended in the future.

3. All password types in the list are generated. This means having
multiple columns in pg_shadow, or an array. An array would support the
addition of future password storage methods.

4. CREATE ROLE / ALTER ROLE syntax is changed to accept a parameter to
ENCRYPTED in order to support md5, scram, and future methods. If no
parameter is supplied, ENCRYPTED will default to 'md5, scram'.

5. we add the superuser-only function pg_apply_password_policy(). This
applies the policy expressed by password_storage, generating or erasing
passwords for each user.

6. We add a new connection error for "authentication __method__ not
supported for user"

7. Two versions from now, we change the defaults.

I thought about the idea of determining password storage based on what's
in pg_hba.conf, but that seems like way too much implied authorization
to me, and liable to be a big foot-gun.

--Josh Berkus

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com