Revoking Function Execute Privilege
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
"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