Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

I wrote:
> Noah Misch <noah(at)leadboat(dot)com> writes:
>> The particular restriction at hand, namely that a role have CREATE rights on a
>> schema before assigning role-specific default privileges, seems like needless
>> paternalism. It would be akin to forbidding ALTER ROLE ... PASSWORD on a
>> NOLOGIN role. I'd support removing it when such a proposal arrives.

> Hm. I defended that restriction earlier, but it now occurs to me to
> wonder if it doesn't create a dump/reload sequencing hazard. I don't
> recall that pg_dump is aware of any particular constraints on the order
> in which it dumps privilege-grant commands. If it gets this right,
> that's mostly luck, I suspect.

This issue just came up again on -general, in an even more annoying
form:

regression=# create role foo;
CREATE ROLE
regression=# create schema s1;
CREATE SCHEMA
regression=# grant create on schema s1 to public;
GRANT
regression=# alter default privileges for role foo in schema s1 grant select on tables to public;
ALTER DEFAULT PRIVILEGES
regression=# revoke create on schema s1 from public;
REVOKE
regression=# alter default privileges for role foo in schema s1 revoke select on tables from public;
ERROR: permission denied for schema s1

That is, it complains about lack of create permissions for the target
role even if you're trying to remove default privileges not add them.
Even if you think such a check is sane for the GRANT case, it seems
pretty hard to defend for the REVOKE case.

At this point I'm prepared to support just removing the check
altogether, ie diking out this test in SetDefaultACLsInSchemas():

aclresult = pg_namespace_aclcheck(iacls->nspid, iacls->roleid,
ACL_CREATE);
if (aclresult != ACLCHECK_OK)
aclcheck_error(aclresult, ACL_KIND_NAMESPACE,
nspname);

Essentially the argument for allowing this without a permissions check
is "I'm not really doing anything to the schema, just preconfiguring the
rights that will be attached to a new object if I later (successfully)
create one in this schema".

Thoughts? If we change this, should we back-patch it? I'm inclined to
think it's a bug (especially if the restore-ordering hazard is real)
so we should back-patch.

regards, tom lane