On Apr 8, 2011, at 6:17 PM, Alvaro Herrera wrote:
>> In other words, if you wrap an unprivileged operation inside of
>> privileged operations, it seems like the unprivileged operation then
>> becomes privileged. Right?
>
> Well, it's in the hands of the creator of the overall wrapper function
> to ensure that the before/after functions are "safe" in that sense.
How do you do that in a safe way though? The problem you run into is if you have a pair of operations that need to be
doneas a superuser, and something else you want to do in the middle as a non-super user. The goal here is to ensure
thatyou MUST perform both operations out of the pair. The problem is: how do you enforce that the cleanup will actually
happen?
Right now, we're doing this through a single function that performs the first SU action, does whatever the user asked,
andthen performs the second SU action. I don't think there's any other way to do that, at least not in 8.3.
To make this robust, you can't just provide secdef functions that wrap your operations that require SU: that would mean
thatanyone could still call them, which means they could potentially call the 1st operation and not the 2nd.
I suspect there might be clever ways around this issue, but ISTM that there should be some reasonable way to handle
this.
BTW, Alvaro did some digging and discovered that the SQL spec allows you to drop to a lower privilege state, but then
there'sno way you can regain your higher-level privileges until the code block that requested lower privileges exits.
Thatwould actually work fine here, so long as you defined a sub-transaction (ie: an embedded BEGIN; END; block in
plpgsqlas a code block. With such a facility, you could do:
CREATE FUNCTION () SECURITY DEFINER AS $$
BEGIN;
privileged operation...
BEGIN; SET ROLE original_user; UNprivileged operation...
END;
privileged operation...
END;
$$;
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net