Re: MD5 authentication needs help

* Alvaro Herrera (alvherre(at)2ndquadrant(dot)com) wrote:
> Stephen Frost wrote:
> > * Alvaro Herrera (alvherre(at)2ndquadrant(dot)com) wrote:
> > > Perhaps one of the requirements of a new auth method should be to allow
> > > middlemen such as connection poolers. It's been over two years since I
> > > had a look, but IIRC pgbouncer had the very ugly requirement of its own
> > > copy of user/passwords in a file, and of course you had to update it
> > > separately if you changed the password in the server. We need to make
> > > it possible for it not to require any such thing.
> >
> > If we go this direction, we've got to be *very* careful that it's only
> > when the admin enables it. man-in-the-middle attacks are quite real and
> > you're essentially asking that we support them intentionally. I agree
> > that we want to support connection poolers but they have an inherent
> > MITM profile.
>
> Sure. I was thinking we would have some mechanism to authenticate the
> connection as coming from a pooler that has been previously authorized;
> something simple as a new pg_hba.conf entry type for "poolers" that are
> only authorized to connect to such-and-such databases, perhaps limit to
> such-and-such users, etc.

Well, server-side, we already have that- have pgbouncer run on the
database server (something which I'm typically in favor of anyway) and
use 'peer'. If it supported TLS then it could use certificates instead.
The question is what to do after the pooler has connected and that's
actually a generic issue which goes beyond poolers and into
applications, basically, "how can I re-authenticate this connection
using a different role." We have SET ROLE, but that gives a lot of
power to the role the pooler logs in as. It'd definitely be neat to
provide a way to use SCRAM or similar to do that re-authentication after
the initial connection.

> I think Kerberos implementations are uncommon, and the complexity of
> getting the whole thing up and running is probably the major reason; or
> at least there's the common belief that it's so.

Kerberos is *extremely* common- it's what Active Directory uses, after
all. Where it isn't used are hosting/cloud providers and the like where
they want to support any and every device their client might want to use
to connect, or places which don't realize that AD uses Kerberos and that
it can be leveraged to provide auth to PG.

> My guess is that since there are few users, pgbouncer devs see little
> reason to implement it also. Chicken and egg, perhaps.

pgbouncer isn't as necessary in the kinds of environments that use
Kerberos because you aren't having lots of connections/s from distinct
principals to a given server. It's just not the profile that pgbouncer
is built for, but that's kind of my point here- pgbouncer is far more
concerned with performance than security because the assumption going in
is that you have a trusted server connecting to a trusted server.
That's an acceptable assumption for a lot of environments, though not
all.

> (Actually, is pgbouncer under active maintenance at all these days?)

I understand Andrew has been helping with it, but my guess is that's
more maintenance and less active development.

> > Also, I don't expect we're going to remove md5 any time soon and,
> > frankly, people using pgbouncer probably aren't worried about the issues
> > which exist with that mechanism and care much more about performance, as
> > it doesn't even support TLS..
>
> I think the accept the issues because they have no other choice, not
> because they are really all that OK with them.

I'd certainly be very happy to see someone interested enough in this
issue to dedicate resources (either human time or funding) to implement
TLS for pgbouncer...

Thanks!

Stephen