Revoking Function Execute Privilege

Started by David G. Johnstonabout 15 years ago2 messagesgeneral
Jump to latest
#1David G. Johnston
david.g.johnston@gmail.com

I've executed the following in a clean database:

As postgres/superuser:

CREATE ROLE impotent NOLOGIN;

CREATE FUNCTION testfunc() RETURNS boolean AS $$

BEGIN

RETURN true;

END;

$$ LANGUAGE 'plpgsql';

REVOKE ALL ON FUNCTION testfunc() FROM impotent;

SET ROLE impotent;

SELECT has_function_privilege('impotent','testfunc()','execute');

SELECT testfunc();

The has_function_privilege returns true instead of false and the SELECT
testfunc() returns without an exception.

I expected failure due to the REVOKE ALL . FROM impotent so what am I
missing?

Thanks,

David J

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: David G. Johnston (#1)
Re: Revoking Function Execute Privilege

"David Johnston" <polobo@yahoo.com> writes:

REVOKE ALL ON FUNCTION testfunc() FROM impotent;

This does not get rid of the default public execute permissions on the
function. To limit execute rights, you first have to

REVOKE ALL ON FUNCTION testfunc() FROM PUBLIC;

and then grant rights back to the appropriate subset of users.

regards, tom lane