Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Noah Misch <noah(at)leadboat(dot)com> writes:
> On Fri, Jun 07, 2013 at 12:26:59PM -0400, Tom Lane wrote:
>> 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.

I thought a bit about that, but it seems rather unrelated to the
eventual use of the privileges.

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

There is also a check that the user doing the ALTER is a member of the
role being targeted, so it's not like unprivileged users would have free
rein to do anything at all here.

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

True. The majority position seems to be for no NOTICE, and I'm fine
with that.

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

The issue is that the A.D.P. must come out after a grant of CREATE
privileges on the schema. After looking at the code, I think pg_dump
does get this right (assuming that there actually are relevant CREATE
privileges granted at the time of dump), but it still seems rather
fragile and surprising.

regards, tom lane