Re: [HACKERS] SCRAM auth and Pgpool-II - Mailing list pgsql-hackers

On Sat, Jul 8, 2017 at 9:30 AM, Stephen Frost <sfrost@snowman.net> wrote:
> Michael,
>
> * Michael Paquier (michael.paquier@gmail.com) wrote:
>> On Sat, Jul 8, 2017 at 1:24 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> > IIUC, things will get even worse once channel binding is committed,
>> > presumably for PostgreSQL 11.  The point of channel binding is to
>> > guarantee that you are conducting the authentication exchange with the
>> > target server, not some intermediate proxy that might be conducting a
>> > hostile MITM attack.  pgpool may not be a hostile attack, but it is
>> > acting as a MITM, and channel binding is going to figure that out and
>> > fail the authentication.  So unless I'm misunderstanding, the solution
>> > you are proposing figures to have a very limited shelf life.
>>
>> We may be misunderstanding each other then. The solution I am
>> proposing, or at least the solution that I imagine should be done but
>> my pgpool-fu is limited, is to keep around connection context and SSL
>> context to handle properly connections messages, and switch between
>> them. When using channel binding, the client needs the server
>> certificate for end-point and the TLS finish message which are both
>> stored in the SSL context after the handshake. So you need to cache
>> that for each server.
>
> I'm not sure what you mean here- the whole point of channel binding is
> to prevent exactly the kind of MITM that pgPool would essentially be in
> this case.  If you're suggesting that things be changed such that a
> server provides information necessary to pgPool to pass along to the
> client to make the client believe that it's talking to the server and
> that there's no man-in-the-middle then the whole point of channel
> binding goes out the window.  If what you're suggesting doesn't require
> any help from either the client or the server to work then I fear we've
> done something wrong in the implementation of channel binding (I've not
> looked at it very closely).

Sorry if my last reply sounded confusing. I have re-read the thread,
my replies and what pgpool does and I can see now what can be
confusing in my words.

What I am suggesting here is that in order to handle properly SCRAM
with channel binding, pgpool has to provide a different handling for
client <-> pgpool and pgpool <-> Postgres. In short, I don't have a
better answer than having pgpool impersonate the server and request
for a password in cleartext through an encrypted connection between
pgpool and the client if pgpool does not know about it, and then let
pgpool do by itself the SCRAM authentication on a per-connection basis
with each Postgres instances. When using channel binding, what would
matter is the TLS finish (for tls-unique) or the hash server
certificate between Postgres and pgpool, not between the client and
pgpool. But that's actually the point you are raising here:

> I don't have any perfect answers for pgPool here, unfortunately.  One
> approach would be to give it a list of usernames/passwords to use,
> though that's hardly ideal.  Perhaps it would be possible for pgPool to
> impersonate the server to the client and the client to the server if it
> was able to query the database as a superuser on some other connection,
> but I don't *think* so because of the way SCRAM works and because pgPool
> wouldn't have access to the client's cleartext password.
>
> Of course, if pgPool could convince the client to use 'password' auth
> and get the cleartext password from the client then that would work to
> authenticate against the server and there wouldn't be any channel
> binding involved since the client is talking the basic cleartext
> password protocol and not SCRAM.

Yes, that's what I am coming at. The only way to defeat a password
sent to pgpool in cleartext would be to force the client to do things
through an encrypted connection.

>> One problem is that pgpool does not use directly libpq but tries to
>> handle things by itself at protocol level, so it needs to replicate a
>> lot of existing APIs. Postgres-XC/XL use a connection pooler,
>> presumably XL uses even a background worker for this stuff since it
>> has been integrated with Postgres 9.3. And this stuff use libpq. This
>> makes life way easier to handle context data on a connection basis.
>> Those don't need to handle protocol-level messages either, which is
>> perhaps different from what pgpool needs.
>
> I don't really think that pgpool using or not using libpq makes any
> real difference here.  What'll be an issue for pgpool is when clients
> get updated libpq libraries that don't support password auth...

There is no need to duplicate the protocol level handling, so life is
simplified for pgpool. But that cannot happen as Ishii-san said
upthread as pgpool needs to control things at a higher level.
-- 
Michael