Re: MD5 authentication needs help

On 03/06/2015 08:19 AM, Stephen Frost wrote:
> * Alvaro Herrera (alvherre(at)2ndquadrant(dot)com) wrote:
>> Stephen Frost wrote:
>> 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.

Using pgbouncer on the DB server is common, but less common that using
it on an intermediate server or even the app server itself. So anything
we create needs to be implementable with all three configurations in
some way.

What I'd particularly like to see, of course, is some way for a
pgbouncer-like proxy to authenticate a client connection, then to use
those credentials to authenticate to the database server, without every
storing a reusable auth token (such as the current md5) on the pgbouncer
server. And, of course, TLS support.

> 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.

It's not that pgbouncer users don't want more secure connections; it's
that they can't have them. I know users who are using stunnel to
connect to pgbouncer because that's the only way they can secure it.

>> (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.

No, there's active development; look for some new pgbouncer features
over the next 6-9 months.

More importantly, pgbouncer is *used* by a large portion of our users --
somewhere between 20% and 50% based on my experience with our clients
and at conferences (our clients are actually around 70%
pgbouncer-using). So if our md5-replacement solution isn't compatible
with pgbouncer, or doesn't arrive with a replacement for pgbouncer with
which it is compatible, will result in us supporting old-style md5 for a
long time or a lot of people using "password".

>>> 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...

PGX would be happy to implement this if someone wanted to find funding.
I'd expect that a couple other consulting companies would jump at it as
well. It's just that so far nobody has wanted to foot the bill.

Kickstarter, maybe?

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