Skip site navigation (1) Skip section navigation (2)

Re: Execute permission for functions

From: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
To: mirthcyy <mirthcyy(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Execute permission for functions
Date: 2010-06-30 21:45:14
Message-ID: 4C2BBAEA.6040805@gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
  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(dot)klaver(at)gmail(dot)com


In response to

pgsql-general by date

Next:From: Tom LaneDate: 2010-06-30 22:03:52
Subject: Re: LINE 1: IDENTIFY_SYSTEM error infinitum
Previous:From: ZoidDate: 2010-06-30 21:24:58
Subject: Re: LINE 1: IDENTIFY_SYSTEM error infinitum

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group