Thread: Execute access on functions
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
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.