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 <alvhe...@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
