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

* Alvaro Herrera (alvherre(at)2ndquadrant(dot)com) wrote:
> Craig Ringer wrote:
> > For some time I've wanted a way to "SET SESSION AUTHORISATION" or "SET
> > ROLE" in a way that cannot simply be RESET, so that a connection may be
> > handed to a less-trusted service or application to do some work with.
>
> Some years back, I checked the SQL standard for insight on how they
> handle this stuff (courtesy of Jim Nasby IIRC). It took me a while to
> figure out that the way they do it is not to have a RESET command in the
> first place! In their model, you enter a secure execution context (for
> example, an SQL function) by calling SET SESSION AUTHORIZATION; and once
> there, the only way to revert to the original session authorization is
> to exit the execution context -- and once that happens, the "attacker"
> no longer has control. Since they have reduced privileges, they can't
> call SET SESSION AUTHORIZATION themselves to elevate their access. In
> this model, you're automatically protected.
>
> I mentioned this in some developer meeting; got blank stares back, IIRC.
> I mentioned it to Stephen in hallway track, and as I recall he was in
> agreement with what I was proposing. Biggest problem is, I can't recall
> in detail what it was.

The issue here ends up being that you don't get the pooling advantage
because the connection pooler ends up having to drop the connection
after using it.

I'm not against a 'SET-and-never-return' concept, but I don't think it'd
help what Craig's after.

Thanks!

Stephen