Hi, On 2023-10-27 14:34:46 -0400, Tom Lane wrote: > Use of an -L switch overrides the dependency sort, but not this > pass mechanism. Aditya pinged me on this issue internally. I think there's a real bug here, and the use of -L was to work around that. A schema like: CREATE USER nosuper_1; CREATE USER nosuper_2; GRANT nosuper_2 TO nosuper_1; GRANT nosuper_1 TO nosuper_3; CREATE SCHEMA nosuper AUTHORIZATIOn nosuper_1; SET ROLE nosuper_1; GRANT ALL ON SCHEMA nosuper TO nosuper_2; CREATE TABLE nosuper.tbl(); ALTER TABLE nosuper.tbl OWNER TO nosuper_2; results in the following, abbreviated, dump on HEAD: CREATE SCHEMA nosuper; ALTER SCHEMA nosuper OWNER TO nosuper_1; .. CREATE TABLE nosuper.tbl ( ); ALTER TABLE nosuper.tbl OWNER TO nosuper_2; ... GRANT ALL ON SCHEMA nosuper TO nosuper_2; Which is bad because the ALTER TABLE OWNER TO cannot be executed before the GRANT ALL: ERROR: 42501: permission denied for schema nosuper LOCATION: aclcheck_error, aclchk.c:2833 We don't allow the OWNER TO without the GRANT ... ON SCHEMA - which is scheduled subsequently. I think there are reasonable dependencies in the database - but pg_dump doesn't seem to actually process shared dependencies, unless I am missing something? SELECT dbid, deptype, classid, classid::regclass, objid, objsubid, refclassid, refclassid::regclass, refobjid, pg_describe_object(classid, objid, objsubid::int) objdesc, pg_describe_object(refclassid, refobjid, 0) refobjdesc FROM pg_shdepend ; ┌──────┬─────────┬─────────┬──────────────┬───────┬──────────┬────────────┬────────────┬──────────┬───────────────────┬────────────────┐ │ dbid │ deptype │ classid │ classid │ objid │ objsubid │ refclassid │ refclassid │ refobjid │ objdesc │ refobjdesc │ ├──────┼─────────┼─────────┼──────────────┼───────┼──────────┼────────────┼────────────┼──────────┼───────────────────┼────────────────┤ │ 5 │ o │ 2615 │ pg_namespace │ 42225 │ 0 │ 1260 │ pg_authid │ 42221 │ schema nosuper │ role nosuper_1 │ │ 5 │ a │ 2615 │ pg_namespace │ 42225 │ 0 │ 1260 │ pg_authid │ 42222 │ schema nosuper │ role nosuper_2 │ │ 5 │ o │ 1259 │ pg_class │ 42226 │ 0 │ 1260 │ pg_authid │ 42222 │ table nosuper.tbl │ role nosuper_2 │ └──────┴─────────┴─────────┴──────────────┴───────┴──────────┴────────────┴────────────┴──────────┴───────────────────┴────────────────┘ pg_depend does have the following dependency: ┌─────────┬─────────┬──────────┬───────┬──────────┬────────────┬──────────────┬──────────┬─────────────┬───────────────────┬────────────────┐ │ deptype │ classid │ classid │ objid │ objsubid │ refclassid │ refclassid │ refobjid │ refobjsubid │ objdesc │ refobjdesc │ ├─────────┼─────────┼──────────┼───────┼──────────┼────────────┼──────────────┼──────────┼─────────────┼───────────────────┼────────────────┤ │ n │ 1259 │ pg_class │ 42226 │ 0 │ 2615 │ pg_namespace │ 42225 │ 0 │ table nosuper.tbl │ schema nosuper │ └─────────┴─────────┴──────────┴───────┴──────────┴────────────┴──────────────┴──────────┴─────────────┴───────────────────┴────────────────┘ Without knowing about the dependency between the schema and the grant, pg_dump can't schedule them reasonably. The TOC shows the following: ; Selected TOC Entries: ; 4002; 0 0 ENCODING - ENCODING 4003; 0 0 STDSTRINGS - STDSTRINGS 4004; 0 0 SEARCHPATH - SEARCHPATH 4005; 1262 5 DATABASE - postgres andres 4006; 0 0 COMMENT - DATABASE postgres andres ; depends on: 4005 5; 2615 42225 SCHEMA - nosuper nosuper_1 4007; 0 0 ACL - SCHEMA nosuper nosuper_1 ; depends on: 5 217; 1259 42226 TABLE nosuper tbl nosuper_2 ; depends on: 5 3999; 0 42226 TABLE DATA nosuper tbl nosuper_2 ; depends on: 217 Given these dependencies, there's indeed no reason to schedule the GRANT before the ALTER TABLE. I feel like I must be missing something - there must be other negative consequences of not looking at pg_shdepend at all? I attached a script to create a schema in the problematic state. Greetings, Andres Freund
Attachment:
schemadep2_min.sql
Description: application/sql
