Re: RFC: Non-user-resettable SET SESSION AUTHORISATION

Robert Haas wrote:
> On Wed, May 20, 2015 at 11:27 AM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:

> > Now that we're on the topic of interesting things, would it make sense to
> > add protocol support for a sort of a "re-authenticate"? So a pooler could
> > first say "this user wants to log in from this host", then get back a
> > message saying how to authenticate that user, which the pooler could then
> > pass that on to the client.
>
> I don't think this will work, because the authentication dialogue is
> structured a series of challenges and responses.

After mulling over this a bit, I think that if we're to do something to
improve things here we should redesign the protocol so that it considers
poolers explicitely. Right now I think a pooler is pretty limited in
what it can do. If we were to have messages specifically for poolers,
life would be simpler: pooler authenticates to main server, client
authenticates to pooler. The pooler can change auth on the server
connection to whatever the client has, and begin passthrough of protocol
data; when client closes connection, pooler recycles connection and
de-authenticates it with main server so that it can be reused for
another client (re-auth). Client by itself cannot "de-auth" to steal
the connection under somebody else's name.

There's an issue that in order to authenticate a client, the pooler
needs to have info from the server about auth data. Last I checked
pgbouncer, you had to copy a list of username/passwords from the server
to a pgbouncer config file, which is ugly and dangerous (not to mention
tedious and error-prone). We could fix that sort of thing too, if we
were to design something here with poolers in mind.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services