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

On 05/13/2015 06:03 AM, Alvaro Herrera 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 did address this same concern some four months ago, by suggesting to
implement an "IMPERSONATE" command, as part of the roles&attributes rework.
This thought was *precisely* oriented towards the sam goal as Craig's
suggestion.

Please keep in mind that SET ROLE and/or IMPERSONATE and/or SET SESSION
AUTHORIZATION [WITH COOKIE] should be doable SQL-only, so no protocol
changes whatsoever would be needed.

On the other hand, ISTM that what we all intend to achieve is some
Postgres equivalent of the SUID bit... so why not just do something
equivalent?
-------
LOGIN -- as user with the appropriate role membership / privilege?
...
SET ROLE / SET SESSION AUTHORIZATION WITH COOKIE / IMPERSONATE

... do whatever ... -- unprivileged user can NOT do the
"impersonate" thing

DISCARD ALL -- implicitly restore previous authz
-------
> I mentioned this in some developer meeting; got blank stares back, IIRC.

Let's hope something goes through this time. It seems to be a more
pressing need now than it was then :)

Thanks,

/ J.L.