On Thu, Dec 31, 2009 at 10:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Gurjeet Singh <singh.gurjeet@gmail.com> writes: > We are seeking to enable SET ROLE in security-definer functions, since @ > D.E Shaw there are scripts from the past that used this feature, and I think > you'd also agree that SET ROLE is security definer functions has it's uses.
Actually, I don't find that to be a given. Exactly what use-cases have you got that aren't solved as well or better by calling a SECURITY DEFINER function owned by the target role?
> As the code stands right now, I see that the only concern against > enabling SET ROLE in SecDef functions is that, that when the local-user-id > change context is exited, the GUC might be left out of sync.
This statement represents a complete lack of understanding of the actual security problem. The actual security problem is that SET ROLE allows you to become any role that the *session* user is allowed to become.
I understand that reasoning very well, its just that I forgot to cover that in the statement above.
The reason for locking it down in security-restricted contexts is that we don't want that to happen: we need to confine the available privileges to only those that, say, the owner of the table being vacuumed would have.
The patch submitted still prohibits SET ROLE in security restricted contexts, and yet allows it in security definer functions iff the function is not executed while security restrictions are enabled. I think I covered that here:
<quote> So SET ROLE would be prohibited in maintenance operations, but allowed in SecDef functions (only if they are not invoked on a stack where maintenance operation was initiated earlier). </quote>
While it's possible that we could design some mechanism that would enforce this properly, I fear that it would be tricky and a likely source of future new security problems. In any case the net result would be that SET ROLE would behave differently from spec, so it would still be non-standard-compliant, just differently from before. So IMHO you really need to offer a convincing reason why we should even try to solve this, as opposed to telling people to use security definer functions.
Ian would be in a better position to provide a use-case.