Thread: permission issue

Recently I saw that there are many changes regarding stored procedures and
I like all of them, but I have a problem with permission issue.
If I grant execution right on function to some user or group PostgreSQL
is unable
to execute this function unless it has all the necessary rights (select,
insert,...)
on all of the objects it (function) uses.
I find this behaviour of PostgreSQL strange. Seems to me that running
function under owners rights is the way to go.

If database administrator whish to grant minimum permissions possible
he certanly doesn't want to grant all the permisions required by the
function
to each group that can execute that function.

What is your opinion on this and how would you solve the problem
of granting rights to users?

Best regards,
         Rikard

On Sat, Nov 19, 2005 at 09:35:18AM +0100, Rikard Pavelic wrote:
> Recently I saw that there are many changes regarding stored procedures and
> I like all of them, but I have a problem with permission issue.
> If I grant execution right on function to some user or group PostgreSQL
> is unable
> to execute this function unless it has all the necessary rights (select,
> insert,...)
> on all of the objects it (function) uses.

Look in the documentation about SECURITY DEFINER.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Rikard Pavelic wrote:
> I find this behaviour of PostgreSQL strange. Seems to me that running
> function under owners rights is the way to go.

Then you should define your functions accordingly with the SECURITY
DEFINER clause.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/