Excerpts from Jeff Davis's message of mié abr 06 19:39:27 -0300 2011:
> On Wed, 2011-04-06 at 18:33 -0300, Alvaro Herrera wrote:
> > (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.)
>
> I'm not sure I understand the use case. If it's within one function, why
> not just do it all as the privileged user in the security definer
> function?
>
> The only reason I can think of it if you wanted to make the unprivileged
> operation arbitrary SQL. But in the example you give, with triggers
> disabled, it's not safe to allow the user to execute arbitrary
> operations.
The point is precisely to let the caller to execute whatever operation
he is already authorized to execute, given his regular permissions.
(The actual request from the customer says "with londiste triggers
removed", in case it makes any difference. I am not familiar with
Londiste.) So there's a desire to check for permissions to execute the
arbitrary SQL call; the security-definer wrapper is really only needed
to remove the londiste triggers, not the operation in the middle.
One idea we floated around was to make the before and after operations
be part of security-definer function, and the user function would call
those. But the problem with this is that the user is then able to call
(say) only the "before" function and forget to call the "after" function
to cleanup, which would be a disaster.
Note that another possible option to go about this would be to have some
sort of "commit trigger"; the "before" function would set a flag stating
that the transaction is in unclean mode, and this commit trigger would
raise an error if the "after" function was not called to cleanup
properly. The same customer has asked for "commit triggers" in the
past, so perhaps we should explore that option instead. Thoughts?
> 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.
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support