Re: pg_restore -L reordering of the statements does not work

Hi,

On 2023-11-14 17:40:02 -0500, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > On 2023-11-14 15:42:22 -0500, Tom Lane wrote:
> >> Moreover, reordering the GRANTs is no solution, because who promised that
> >> the schema owner granted you any permissions?
>
> > I'm not quite following - the schema is created in the dump, so the grant is
> > part of it?
>
> Yeah, but the GRANT will restore whatever permissions existed in the
> source database. If the restoring user isn't super, those permissions
> don't necessarily grant him access.

Sure - that could obviously fail. But I just don't think it's the failure at
hand, given that the problem occurs even with the restorer being a member of
all the roles involved in the dump.

If you reorder the dump so that "GRANT ALL ON SCHEMA nosuper TO nosuper_2"
happens earlier, before "ALTER TABLE nosuper.tbl OWNER TO nosuper_2", the
restore succeeds.

> >> I experimented with making the restoring user be a member with inherit
> >> of the nosuper_N roles, and indeed I still see the failure above,
> >> which makes me wonder if the ACL check is being done correctly for
> >> that specific case. The INHERIT bit ought to let it work.
>
> > The check is for nosuper_2 to have permission on the schema
>
> ... no, it should be for the user executing the ALTER to have permission.

That check succeed - what fails is a check on the new owner of the table. See
tablecmds.c ATExecChangeOwner:

/* New owner must have CREATE privilege on namespace */
aclresult = object_aclcheck(NamespaceRelationId, namespaceOid, newOwnerId,
ACL_CREATE);
if (aclresult != ACLCHECK_OK)
aclcheck_error(aclresult, OBJECT_SCHEMA,
get_namespace_name(namespaceOid));
}

If shared dependencies were taken into account and thus the
"GRANT ALL ON SCHEMA nosuper TO nosuper_2"
were happening before
"ALTER TABLE nosuper.tbl OWNER TO nosuper_2"
, it'd succeed.

Greetings,

Andres Freund