Re: Successor of MD5 authentication, let's use SCRAM

On Fri, Oct 12, 2012 at 10:47 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Marko Kreen (markokr(at)gmail(dot)com) wrote:
>> As it works only on connect
>> time, it can actually be secure, unlike user switching
>> with SET ROLE.
>
> I'm guessing your issue with SET ROLE is that a RESET ROLE can be issued
> later..? If so, I'd suggest that we look at fixing that, but realize it
> could break poolers. For that matter, I'm not sure how the proposal to
> allow connections to be authenticated as one user but authorized as
> another (which we actually already support in some cases, eg: peer)
> *wouldn't* break poolers, unless you're suggesting they either use a
> separate connection for every user, or reconnect every time, both of
> which strike me as defeating a great deal of the point of having a
> pooler in the first place...

The point of pooler is to cache things. The TCP connection
is only one thing to be cached, all the backend-internal
caches are as interesting - prepared plans, compiled functions.

The fact that on role reset you need to drop all those things
is what is breaking pooling.

Of course, I'm speaking only about high-performance situations.
Maybe there are cases where indeed the authenticated
TCP connection is only interesting to be cached.
Eg. with dumb client with raw sql only, where there
is nothing to cache in backend. But it does not seem
like primary scenario we should optimize for.

--
marko