Thread: Alter Default Privileges Does Not Work For Functions
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
"David Johnston" <polobo@yahoo.com> writes:
> 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.
You haven't read the fine manual very closely. It saith
Default privileges that are specified per-schema are added to
whatever the global default privileges are for the particular
object type.
and
As explained under GRANT, the default privileges for any object
type normally grant all grantable permissions to the object
owner, and may grant some privileges to PUBLIC as well. However,
this behavior can be changed by altering the global default
privileges with ALTER DEFAULT PRIVILEGES.
If you want to revoke the default execute privileges for functions, you
have to do it globally, ie, not per-schema. There's no way to reduce
the default privileges at the per-schema level.
regards, tom lane
I understand now; it wasn't the reading that was causing me problems it was
figuring out the implications of the structure of the default privileges
system.
I suggest that ALTER DEFAULT PRIVILEGES (and probably REVOKE) raise a notice
when attempting to REVOKE a [DEFAULT] PRIVILEGE that does not exist. Since
I was issuing the REVOKE against the schema but the GRANT I was trying to
revoke existed in GLOBAL there was nothing to revoke. If I had seen such a
notice I would have realized (hopefully) that Global meant per-database and
that it was my schema restriction that was causing the problem.
Thanks again for helping me get my head around some of this stuff.
David J
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, February 14, 2011 7:05 PM
To: David Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Alter Default Privileges Does Not Work For Functions
"David Johnston" <polobo@yahoo.com> writes:
> 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.
You haven't read the fine manual very closely. It saith
Default privileges that are specified per-schema are added to
whatever the global default privileges are for the particular
object type.
and
As explained under GRANT, the default privileges for any object
type normally grant all grantable permissions to the object
owner, and may grant some privileges to PUBLIC as well. However,
this behavior can be changed by altering the global default
privileges with ALTER DEFAULT PRIVILEGES.
If you want to revoke the default execute privileges for functions, you have
to do it globally, ie, not per-schema. There's no way to reduce the default
privileges at the per-schema level.
regards, tom lane