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    │
rolenosuper_1 │
 
│    5 │ a       │    2615 │ pg_namespace │ 42225 │        0 │       1260 │ pg_authid  │    42222 │ schema nosuper    │
rolenosuper_2 │
 
│    5 │ o       │    1259 │ pg_class     │ 42226 │        0 │       1260 │ pg_authid  │    42222 │ table nosuper.tbl │
rolenosuper_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