Postgres Pro
Downloads
Home   >   mailing lists

Thread: Revoking Function Execute Privilege

Revoking Function Execute Privilege

From
"David Johnston"
Date:

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

Re: Revoking Function Execute Privilege

From
Tom Lane
Date:
"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