Re: lowering privs in SECURITY DEFINER function

On Apr 8, 2011, at 7:20 PM, Alvaro Herrera wrote:

> Excerpts from A.M.'s message of mié abr 06 19:08:35 -0300 2011:
>
>> 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?
>
> As I said to Jeff, it's up to the creator of the wrapper function to
> ensure that things are safe. Perhaps this new operation should only be
> superuser-callable, for example.
>
>> How would you prevent general SQL attacks when manually popping the
>> authentication stack is allowed?
>
> The popping and pushing operations would be restricted. You can only
> pop a single frame, and pushing it back before returning is mandatory.

It might be worth thinking about extending this functionality to cover the case for connection pooling. If some SQL can "re-tool" an existing connection to have the properties of a new connection by a different role, then that would reduce the use-case of connection pooling. If that authorization chain can be pushed and popped by a password or some security token, for example, then that would cover the use cases I mention in this thread:

http://archives.postgresql.org/pgsql-general/2006-04/msg00917.php

Cheers,
M