Hi, On 2023-11-14 17:40:02 -0500, Tom Lane wrote: > Andres Freund <andres@xxxxxxxxxxx> 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
