Thread: Grant execute on functions; related objects permissions ?

Hello,

PostgreSQL 7.3 happily introduced permissions on functions. Now, having
granted execution to a given function to a given user, I find myself with
"access denied" errors on the objects that the function actually uses (e.g.
a table on which it makes a select). So:

1. Am I missing something about how the GRANT ... ON FUNCTION works ?

2. Is there any way to automatically obtain privileges on the objects the
function uses, without having to GRANT specific permissions on those object
(the same way it works on MS-SQL) ?

3. Am I following the wrong direction (I understand question 2. might
actually introduce a permission "hole" on the objects that functions might
use; a user might obtain access to these objects through defining its own
function, am I right ?) ?

Thank you for you help,

Cédric Dufour - Cogito Ergo Soft _____

Cogito Ergo Soft - Your think partner

In PostgreSQL 7.3 you have option to execute function with owner's rights or caller's rights. Default is caller's
rights(as it was before 7.3), you probably want owner's rights. See development version of docs: 

http://developer.postgresql.org/docs/postgres/sql-createfunction.html

btw, views "execute" also with owner's rights, ie if you grant select on view, you do not have to grant select on every
tableused in view. Still current_user in view returns "caller", while maybe it should return owner?  
 Tambet

>
> PostgreSQL 7.3 happily introduced permissions on functions.
> Now, having
> granted execution to a given function to a given user, I find
> myself with
> "access denied" errors on the objects that the function
> actually uses (e.g.
> a table on which it makes a select). So:
>
> 1. Am I missing something about how the GRANT ... ON FUNCTION works ?
>
> 2. Is there any way to automatically obtain privileges on the
> objects the
> function uses, without having to GRANT specific permissions
> on those object
> (the same way it works on MS-SQL) ?
>
> 3. Am I following the wrong direction (I understand question 2. might
> actually introduce a permission "hole" on the objects that
> functions might
> use; a user might obtain access to these objects through
> defining its own
> function, am I right ?) ?
>

On Mon, Jan 06, 2003 at 12:45:25 +0200, Tambet Matiisen <t.matiisen@aprote.ee> wrote:
> btw, views "execute" also with owner's rights, ie if you grant select on view, you do not have to grant select on
everytable used in view. Still current_user in view returns "caller", while maybe it should return owner? 
 

I don't think so. Knowing the caller is useful for granting access only
to some rows that depend on which user is using the view.