Re: lowering privs in SECURITY DEFINER function

On Apr 6, 2011, at 5:33 PM, Alvaro Herrera wrote:

> Hi,
>
> A customer of ours has for a long time the desire to be able to return
> to the previous privilege level (i.e. the caller privs) inside a
> SECURITY DEFINER function. I find that this notion is not at all
> covered in the SQL standard, yet the use case is certainly valid from a
> security-concious point of view.
>
> (Consider, for example, that you may want to enable a user to run some
> operation to which he is authorized, but you want to carry out some
> privileged operation before/after doing so: for example, disable
> triggers, run an update, re-enable triggers.)
>
> An easy way to "somewhat solve" this problem is to provide another
> security definer function that calls the intermediate operation, owned
> by a role with lower privileges. But this doesn't really solve the
> problem, because you are then providing a way to return to an arbitrary
> role, not to the specific role that's calling the function.
>
> I think part of the solution here would be to be able to tell what's the
> "previous role", i.e. the one just below the topmost stack item in the
> authorization stack. Then, at least you know what to call SET SESSION
> AUTHORIZATION to.
>
> Thoughts? This area seems fraught with security problems, yet it is a
> necessary piece on the security puzzle.

That's really strange considering that the new role may not normally have permission to switch to the original role. How would you handle the case where the security definer role is not the super user?

How would you prevent general SQL attacks when manually popping the authentication stack is allowed?

Cheers,
M