Re: WIP: SCRAM authentication - Mailing list pgsql-hackers

On 08/09/2015 08:09 AM, Robert Haas wrote:
> Why do we need to be able to authenticate using more than one
> mechanism?  If you have some clients that can't support SCRAM yet, you
> might as well continue using MD5 across the board until that changes.
> You're not going to get much real security out of using MD5 for some
> authentication attempts and SCRAM for other ones, 

Speaking as someone who has sheperded several clients through
infrastructure upgrades, I have to disagree with this.

First, people don't upgrade large infrastructures with multiple
applications, ETL processes and APIs which connect with the database all
at once.  They do it one component at a time, verify that component is
working, and then move on to the next one.  Even within a single
application, there could be many servers to upgrade, and you can't do
them all simultaneously.

Now, for shops where they've had the foresight to set up group roles
which own objects so that a new user with SCRAM can be assigned in the
group role, this is no problem.  But for the other 98% of our large-app
users, setting up that kind of infrastructure would itself require a
weekend-long downtime, due to the locking required to reassign object
permissions and all of the app testing required.

Second, you're forgetting hosted PostgreSQL, where there may be only one
user available to each database owner.  So assigning a new login role
for SCRAM isn't even an option.

Plus all of the above requires that some login roles have a SCRAM
verifier, and others have MD5, for some period.  Even if we don't
support multiple verifiers for one login, that still means we need to
deal with "what verifier gets created for a new role" and the required
support functions and GUCs for that.  Switching "across the board" on a
per-installation basis is a complete nonstarter for any running application.

Frankly, switching on a per-postmaster basis isn't even worth discussing
in my book, because some languages/platforms will take years longer than
others to support SCRAM.

Overall, it's to the PostgreSQL project's benefit to have users switch
to SCRAM once we have it available.  For that reason, we should try to
make it easy for them to make the switch.

However ...

> and the amount of
> infrastructure we're proposing to introduce to support that is pretty
> substantial.

... during my exchange with Michael, I was thinking about the bug
potential of taking the password field and multiplexing it in some way,
which is significant.  There is a definite risk of "making this too
complicated" and we'll need to contrast that against ease-of-migration,
because complicated mechanisms tend to be less secure due to user error.

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