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

On 5/20/15 9:38 PM, Robert Haas wrote:
> On Wed, May 20, 2015 at 8:22 PM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:
>>> 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).
>
> Uh, I don't have a clue what you mean when you say "the middle ground
> of not doing de-auth right now".

Don't allow a backend to move back into a de-authenticated state.

Basically, allow a special connection mode that does nothing but provide
user authentication back to the pooler. This would allow the pooler to
defer all auth decisions to Postgres. Once the user was authenticated,
the pooler could then figure out what pool connection to give to the user.

I was thinking that if this authentication connection was never allowed
to run SQL then it should eliminate fears of being in a de-authenticated
state, but I see now that we've already started transaction machinery by
the time PerformAuthentication happens, presumably for good reason. So
maybe it's just as bad as trying to de-authenticate a full backend...
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com