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

On 5/20/15 3:31 PM, Robert Haas wrote:
> On Wed, May 20, 2015 at 3:42 PM, Alvaro Herrera
> <alvherre(at)2ndquadrant(dot)com> wrote:
>> >Robert Haas wrote:
>> >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.
> It might be a good idea to do something like this, but it's
> significantly more complicated than a protocol-level SET SESSION
> AUTHORIZATION. Right now, you can never go backwards from an
> authenticated state to an unauthenticated state, and there may be code
> in the backend that relies on that in subtle ways. The initial
> bootstrap sequence is pretty complicated, and I'm pretty sure that any
> naive attempt to redo that stuff is going to have unpleasant, probably
> security-relevant bugs.

What about the middle-ground of not doing de-auth right now? That
eliminates your concerns but still allows getting rid of ugly things
like copies of the password file (FWIW, my understanding is pgBouncer
was meant more to run on the database server where you'd just point it
at the native password file).
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com