Re: function privileges

On Thu, Feb 02, 2006 at 05:45:16PM -0500, Murat Tasan wrote:
> Quick question on function privileges: what exactly does GRANT/REVOKE
> ON FUNCTION fun_name() actually do?

It grants or revokes the ability to execute the function.  It doesn't
automatically grant or revoke access to objects that the function
might reference; you have to set those objects' permissions separately.

> I've tried a few scenarios out, and cannot figure out when this is
> used.  Let us say I have a function F(int4) that does some selection
> on a table T.
>
> If user A comes along and has no SELECT privileges on T, but has
> EXECUTE privileges on F, A is not permitted to run the function, with
> an error stating access to T is needed.

The user *is* permitted to run the function but the function is
failing because of the permissions on T.

> Now, if user B comes along and has SELECT privileges on T, but not
> EXECUTE privileges on F, B is permitted to run the function.

A function's default privileges allow anybody to execute it; revoking
a particular user's permission has no effect unless you've also
revoked permission from PUBLIC.  I'm guessing you did something
like this:

CREATE FUNCTION f(integer) ...
REVOKE ALL ON FUNCTION f(integer) FROM UserB;

The problem is that the function still has "allow everybody"
privileges so user B can still execute it.  If you want to allow
user A to execute the function but not user B then do this:

CREATE FUNCTION f(integer) ...
REVOKE ALL ON FUNCTION f(integer) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION f(integer) TO UserA;

You might also be interested in the difference between SECURITY
INVOKER (the default) and SECURITY DEFINER; for more information
see the CREATE FUNCTION documentation.

--
Michael Fuhr