Re: WIP: SCRAM authentication

All,

* Peter Eisentraut (peter_e(at)gmx(dot)net) wrote:
> On 8/12/15 12:19 PM, Robert Haas wrote:
> > On Wed, Aug 12, 2015 at 10:50 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> >> I understand this idea, but I think it's not practical for many uses.
> >> There is no way to find out, on the server, whether all current clients
> >> would support a switch to SCRAM. Let alone all not-current clients.
> >> The only way to do such a switch would be to do the switch and then
> >> check for connection failures in the log, which is not good.
> >
> > Well, number one, I don't think we log anything currently that would
> > let you figure that out;
>
> We have options to log details about authenticated connections. This
> kind of thing could easily be added there.
>
> > and number two, I'm not sure I believe that's
> > the only way to make sure you've updated your clients.
>
> Well, if there is another one, I haven't heard about it so far.

Agreed.

> >> (Now that I think about this, to counter my point, this is very similar
> >> to the switch from crypt to md5. You couldn't turn that on until you
> >> were sure that all clients would support it. I don't remember the
> >> experience from back then, though.)
> >
> > Maybe we should try to find out how that played out. It could inform
> > the current discussion.
>
> I think in those days, installations weren't very long-lived. md5 was
> introduced in version 7.2, which is also the first version with lazy vacuum.

I don't believe that's any basis for comparison given that it was well
over 10 years ago that we released 7.2.

> > Another thing we might want to try to find out is: if we add SCRAM
> > authentication to 9.6, how committed are drivers authors to adding
> > that support to their drivers? If we poll the maintainers of the
> > drivers for Perl, Python, Ruby, Node.JS, Java, ODBC, etc. and involve
> > them in this conversation, we might learn useful things. This is a
> > big change we're talking about, and it's only to work (regardless of
> > the details) if the driver authors are on board. We haven't, AFAIK,
> > talked to them about this at all.
>
> I'm not concerned about whether drivers are willing to support this.
> These sort of things have usually worked out in the past. But just
> because a driver supports a new authentication method, that doesn't mean
> everyone can or will upgrade to it right away.

I certainly want driver authors to support this and would be willing to
work with them to make it happen. Discussing the change with them
certainly makes sense, in general.

As for the notion of dropping md5 from 9.6 or even forcing it to be
one-or-the-other on a per-role basis, I'm surprised we're even
discussing it. We've already got people who aren't upgrading to newer,
supported, versions of PG because the application they have won't work
for some reason, this is creating a much harder upgrade path for those
users, and I don't believe that the security concern holds any water
compared to that issue.

For starters, if the attacker has access to the md5 hash in the DB, the
game is over already, full stop, when it comes to accessing the database
as that user. We won't fix that until everyone's on SCRAM, so we should
be making it as easy as possible to get there. I don't believe having
the SCRAM password verifier in the DB will seriously improve an
attacker's chances of getting the plaintext password simply because our
md5 "salt" completely sucks (no one serious about security would use it)
and md5 simply doesn't stand up to attacks very well these days.

I'm all for having an eventual flag day where we ship a release that
doesn't support md5 on the server side as an authentication mechanism
and pg_upgrade has a check which prevents upgrades if there are any md5
entries left, but that's at least two and probably three releases out.
What we need to be doing now is getting that ball rolling or we're never
going to actually get there.

Thanks!

Stephen