Re: lowering privs in SECURITY DEFINER function

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 done as a superuser, and something else you want to do in the middle as a non-super user. The goal here is to ensure that you 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, and then 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 that anyone 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's no way you can regain your higher-level privileges until the code block that requested lower privileges exits. That would actually work fine here, so long as you defined a sub-transaction (ie: an embedded BEGIN; END; block in plpgsql as 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(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net