Re: sudo-like behavior

"A.M." <agentm(at)themactionfaction(dot)com> writes:
> On Thu, April 20, 2006 4:21 pm, Tom Lane wrote:
>> I think the correct way to do what you want is via a SECURITY DEFINER
>> function.

> Perhaps I can't wrap my head around it- I have the SQL as a string in a
> table.

Well, the simplest thing would be

create function exec(text) returns void as $$
begin
execute $1;
end$$ language plpgsql strict security definer;

revoke execute on exec(text) from public;
grant execute on exec(text) to whoever-you-trust;

although personally I'd try to restrict what the function can be used
for a bit more than that. If the allowed commands are in a table, you
could perhaps pass the table's key to exec() and let it pull the string
from the table for itself.

> What about commands that can't be run from within transactions?

There aren't that many of those. Do you really need this for them?

For that matter, do you really need this at all? Have you considered
granting role membership as an alternative solution path? The SQL
permissions mechanism is quite powerful as of 8.1, and if it won't
do what you want, maybe you have not thought hard enough.

regards, tom lane