Thread: Execute access on functions

Execute access on functions

From
Kathy Smith
Date:
I want to control access (update, delete) to my tables and have done that 
with other DBMSs using stored procedures.  Besides performance, I believe 
that's one of the primary advantages of stored procedures.  I grant execute 
on the procedure to a group containing the users with controlled update 
access.  Never on the table.  I cannot seem to find the equivalent in 
postgres.  I had hoped to be able to do this with user-defined functions 
but the following statement implies that if the SECURITY DEFINER is used, 
that *anyone* who can get to that function can execute it.  Hardly the 
solution I am looking for.  The alternative being that I must grant update 
to the table :(

The CREATE FUNCTION clause SECURITY DEFINER makes the function run with the 
privileges of the user who created it. Otherwise, the INVOKER's privileges 
are used.

Am I missing something here?  Is there another way?
Thanks,
Kathy


*******************************
Kathryn K Smith
Los Alamos National Laboratory
505-699-9330
505-665-0505

The gentlest thing in the world overcomes the hardest thing in the 
world.  --Tao Te Ching 



Re: Execute access on functions

From
Bruno Wolff III
Date:
On Wed, Mar 23, 2005 at 12:35:12 -0700, Kathy Smith <ksmith@lanl.gov> wrote:
> I want to control access (update, delete) to my tables and have done that 
> with other DBMSs using stored procedures.  Besides performance, I believe 
> that's one of the primary advantages of stored procedures.  I grant execute 
> on the procedure to a group containing the users with controlled update 
> access.  Never on the table.  I cannot seem to find the equivalent in 
> postgres.  I had hoped to be able to do this with user-defined functions 
> but the following statement implies that if the SECURITY DEFINER is used, 
> that *anyone* who can get to that function can execute it.  Hardly the 
> solution I am looking for.  The alternative being that I must grant update 
> to the table :(
> 
> The CREATE FUNCTION clause SECURITY DEFINER makes the function run with the 
> privileges of the user who created it. Otherwise, the INVOKER's privileges 
> are used.
> 
> Am I missing something here?  Is there another way?

You can control who can execute the function. By default 'public' can execute
functions, but you can revoke that access.