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
