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

Craig Ringer wrote:
> Hi all
>
> 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.

Not sure this helps you any ... Chilean $2 which are probably not worth
much [currently], I guess.

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