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

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