lowering privs in SECURITY DEFINER function

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.

--
Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>