Thread: Utility of GRANT EXECUTE

It seems that any user has the right to execute a function, whether or not it has been granted the EXECUTE privilege on it. Even a REVOKE EXECUTE has no impact. A privilige error will be raised only if the function tries to access an object (ex.: a table) for witch the user doesn't have the appropriate privilege(s).

Is there any utility to the GRANT EXECUTE then ?


Thanks,
Paul

On Tue, Mar 14, 2006 at 09:24:52AM +0100, Paul Mackay wrote:
> It seems that any user has the right to execute a function, whether or not
> it has been granted the EXECUTE privilege on it. Even a REVOKE EXECUTE has
> no impact. A privilige error will be raised only if the function tries to
> access an object (ex.: a table) for witch the user doesn't have the
> appropriate privilege(s).

Revoking EXECUTE from an individual user has no effect if public
still has privileges, which is does by default.

> Is there any utility to the GRANT EXECUTE then ?

If you revoke public's privileges then GRANT EXECUTE has an effect.

test=> create function foo() returns integer as 'select 1' language sql;
CREATE FUNCTION
test=> revoke all on function foo() from public;
REVOKE
test=> grant execute on function foo() to user1;
GRANT
test=> \c - user1
You are now connected as new user "user1".
test=> select foo();
 foo
-----
   1
(1 row)

test=> \c - user2
You are now connected as new user "user2".
test=> select foo();
ERROR:  permission denied for function foo

--
Michael Fuhr

Is there a way to change the default prvilege on functions, i.e. that like for tables, only the creator has privilege on it by default ?

Thanks.
Paul

On Tue, Mar 14, 2006 at 09:57:54AM +0100, Paul Mackay wrote:
> Is there a way to change the default prvilege on functions, i.e. that like
> for tables, only the creator has privilege on it by default ?

Not that I'm aware of.  You could revoke USAGE on the functions'
schema so attempts to call the functions would fail with "permission
denied for schema" but that might be too sweeping a solution.  The
idea of having default privileges has come up before; the developers'
TODO list has an item that mentions "GRANT SELECT ON NEW TABLES."

Is there a particular problem you're trying to solve?

--
Michael Fuhr