After creating and logging into a new database run this script. The initial ALTER DEFAULT PRIVILEGES should make all users unable to execute functions unless given explicit permissions elsewhere. However, the first call to “testfunc()” succeeds. When I explicitly REVOKE ALL for the specific function (or all functions in schema) and roles I DO get a permission denied exception as expected. For those that read the other thread I had assumed PUBLIC had its EXECUTE privileges revoked due to executing the ALTER DEFAULT statement. It apparently did not and thus all users were still able to execute functions via PUBLIC even if their explicit role had execute revoked.
SET ROLE postgres;
CREATE ROLE impotent;
ALTER DEFAULT PRIVILEGES
IN SCHEMA public
REVOKE ALL ON FUNCTIONS
FROM PUBLIC, impotent;
CREATE FUNCTION testfunc() RETURNS boolean AS $$
BEGIN
RETURN true;
END;
$$ LANGUAGE 'plpgsql';
SET ROLE impotent;
SELECT testfunc(); -- SUCCEEDES
SET ROLE postgres;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM PUBLIC, impotent;
SET ROLE impotent;
SELECT testfunc(); --FAILS
David J