Thread: Execute permission for functions
Hi there, I'm a newbie to postgresql and I have some problems working with its permissions. For security purpose, I want that my application service account only has execution permissions to the functions I created. so what I did is: Create a group testgroup (not super user) Create a user testuser belongs to testgroup Now I want to give function execute permission to testgroup: first I grant the schema: GRANT USAGE ON SCHEMA "aaa" TO "testgroup"; second I grant the execution permission to the group: GRANT EXECUTE ON FUNCTION "aaa"."testFunction"(integer) TO "testgroup"; I thought that's it and logged in with testuser. But I found I couldn't execute the function and gave me error like: ERROR: permission denied for relation Tablename (the function select from that Tablename) Then I tried to give that testuser the select permission to that Tablename, testuser can then execute that function. so conclusion: it seems it's useless to give execution permission to a group. The group will have to have all the base table select/insert/ delete etc permisisons in order to execute function depending on what's in the function. Then I wonder why Postgresql is working like that? There's no way for me to lock down all base table access. Or is there something I miss? Please help me if you have any idea about locking down base table access and only give function execution access to a group. Thanks a lot!
On 06/30/2010 02:09 PM, mirthcyy wrote: > Hi there, > > I'm a newbie to postgresql and I have some problems working with its > permissions. > > For security purpose, I want that my application service account only > has execution permissions to the functions I created. so what I did > is: > > Create a group testgroup (not super user) > Create a user testuser belongs to testgroup > > Now I want to give function execute permission to testgroup: > > first I grant the schema: > > GRANT USAGE ON SCHEMA "aaa" TO "testgroup"; > > second I grant the execution permission to the group: > > GRANT EXECUTE ON FUNCTION "aaa"."testFunction"(integer) TO > "testgroup"; > > I thought that's it and logged in with testuser. But I found I > couldn't execute the function and gave me error like: > > ERROR: permission denied for relation Tablename (the function select > from that Tablename) > > Then I tried to give that testuser the select permission to that > Tablename, testuser can then execute that function. > > so conclusion: it seems it's useless to give execution permission to a > group. The group will have to have all the base table select/insert/ > delete etc permisisons in order to execute function depending on > what's in the function. Then I wonder why Postgresql is working like > that? There's no way for me to lock down all base table access. Or is > there something I miss? > > Please help me if you have any idea about locking down base table > access and only give function execution access to a group. > > Thanks a lot! > > From here: http://www.postgresql.org/docs/8.4/interactive/sql-createfunction.html Look at SECURITY DEFINER. -- Adrian Klaver adrian.klaver@gmail.com