Re: pg_dump does not handle indirectly-granted permissions properly

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> AFAICT, pg_dump has no notion that it needs to be careful about the order
>> in which permissions are granted. I did

> I'm afraid that's correct, though I believe that's always been the case.
> I spent some time looking into this today and from what I've gathered so
> far, there's essentially an implicit (or at least, I couldn't find any
> explicit reference to it) ordering in ACLs whereby a role which was
> given a GRANT OPTION always appears in the ACL list before an ACL entry
> where that role is GRANT'ing that option to another role, and this is
> what pg_dump was (again, implicitly, it seems) depending on to get this
> correct in prior versions.

Yeah, I suspected that was what made it work before. I think the ordering
just comes from the fact that new ACLs are added to the end, and you can't
add an entry as a non-owner unless there's a grant WGO there already.

> Pulling apart the ACL list and rebuilding it to handle adding/revoking
> of default options on objects ends up, in some cases, changing the
> ordering around for the ACLs and that's how pg_dump comes to emit the
> GRANT commands in the wrong order.

Right.

> I don't, at the moment, think we actually need to do any checks in the
> backend code to make sure that the implicit ordering is always held,
> assuming we make this change in pg_dump. I do wonder if it might be
> possible, with the correct set of GRANTs (perhaps having role
> memberships coming into play also, as discussed in the header of
> select_best_grantor()) to generate an ACL list with an "incorrect"
> ordering which would end up causing issues in older releases with
> pg_dump. We've had precious little complaints from the field about this
> and so, even if we were to generate such a case, I'm not sure that we'd
> want to add all the code necessary to avoid it and then back-patch it.

I suspect it would be easier to modify the backend code that munges ACL
lists so that it takes care to preserve that property, if we ever find
there are cases where it does not. It seems plausible to me that
pg_dump is not the only code that depends on that ordering.

regards, tom lane