Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

On Fri, Jun 07, 2013 at 12:26:59PM -0400, Tom Lane wrote:
> 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.

> 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".

Seems fine. I might have instead changed it to a test of the caller's
permissions. Though not a security risk, it's unnecessary and mildly strange
to let a user personally attach default privileges to a schema on which he has
no CREATE privilege. A possible objection is that non-superuser pg_restore
operations could fail more than they otherwise would, but they could also fail
less often: a non-superuser schema owner is unable to restore default
privilege entries of unrelated users, so preventing mischievous users from
adding them is a slight help.

Concerning the proposal to emit a NOTICE, I wouldn't. NOTICEs are good when
the difference between the user's probable intent and the actual outcome will
be easy to miss. Here, the affected user will see clearly enough that he yet
lacks the CREATE privilege.

> 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.

Roles and their memberships will be dumped in the globals portion of
pg_dumpall, whereas ALTER DEFAULT PRIVILEGES will be dumped for individual
databases. How might a restore-order hazard arise? In the absence of such a
hazard, I would probably refrain from back-patching on the grounds that
there's an easily-tolerated workaround.

All that being said, each of the just-discussed variations would be valid.

Thanks,
nm

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com