Re: pg_restore 14 skips ACL COLUMN when --schema is used

On Mon, Jul 31, 2023, at 8:00 PM, Kong Man wrote:
> In this example, I generate the public.event table's ToC entries for TABLE, ACL TABLE, and ACL COLUMN, then use the result on --use-list. pg_restore generates only the CREATE TABLE and GRANTs statements at the table, but not the column, level.

You didn't provide a test case as requested but I investigated this issue
according to your description. The following commands is sufficient to produce
the analysis below.

CREATE ROLE role1;
CREATE ROLE role2;

CREATE TABLE event (
col1 text,
col2 text,
col3 text
);

GRANT SELECT ON TABLE event TO role1;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE event TO role2;

GRANT SELECT (col1, col2) ON TABLE event TO role2;
GRANT SELECT, INSERT, UPDATE (col3) ON TABLE event TO role2;

I created these objects and ran pg_restore using a debugger.

pg_dump -Fc -f /tmp/a.dump -d test
pg_restore -l /tmp/a.dump > /tmp/a.toc
gdb --args pg_restore --use-list=/tmp/a.toc -f - /tmp/a.dump

I set a breakpoint to _tocEntryRequired function to inspect the TocEntry values.

(gdb) b _tocEntryRequired

After a few 'continue' and 'p *te' commands I got:

(gdb) c
Continuing.

Breakpoint 1, _tocEntryRequired (te=0x5555555a6910, curSection=SECTION_PRE_DATA, AH=0x5555555a05b0) at pg_backup_archiver.c:2773
2773 }
(gdb) p *te
$8 = {prev = 0x5555555a67a0, next = 0x5555555a6be0, catalogId = {tableoid = 0, oid = 0}, dumpId = 3327, section = SECTION_NONE, hadDumper = false,
tag = 0x555555585d30 "COLUMN event.col1", namespace = 0x5555555858b0 "public", tablespace = 0x0, tableam = 0x0, owner = 0x555555585340 "euler", desc = 0x555555585df0 "ACL",
defn = 0x5555555a6a00 "GRANT SELECT(col1) ON TABLE public.event TO role2;\n", dropStmt = 0x0, copyStmt = 0x0, dependencies = 0x5555555a6a40, nDeps = 2, dataDumper = 0x0,
dataDumperArg = 0x0, formatData = 0x555555585c70, dataLength = 0, reqs = 0, created = false, pending_prev = 0x0, pending_next = 0x0, depCount = 0, revDeps = 0x0, nRevDeps = 0,
lockDeps = 0x0, nLockDeps = 0}
(gdb) p te->dependencies[0]
$18 = 214
(gdb) p *AH->tocsByDumpId[214]
$17 = {prev = 0x5555555a64e0, next = 0x5555555a67a0, catalogId = {tableoid = 1259, oid = 183415}, dumpId = 214, section = SECTION_PRE_DATA, hadDumper = false,
tag = 0x555555584e00 "event", namespace = 0x555555584f80 "public", tablespace = 0x555555585040 "", tableam = 0x555555584380 "heap", owner = 0x555555584440 "euler",
desc = 0x555555584ec0 "TABLE", defn = 0x5555555a6740 "CREATE TABLE public.event (\n col1 text,\n col2 text,\n col3 text\n);\n",
dropStmt = 0x5555555a61f0 "DROP TABLE public.event;\n", copyStmt = 0x0, dependencies = 0x0, nDeps = 0, dataDumper = 0x0, dataDumperArg = 0x0, formatData = 0x555555584d40,
dataLength = 0, reqs = 1, created = false, pending_prev = 0x0, pending_next = 0x0, depCount = 0, revDeps = 0x0, nRevDeps = 0, lockDeps = 0x0, nLockDeps = 0}
(gdb) p te->dependencies[1]
$21 = 3326
(gdb) p *AH->tocsByDumpId[3326]
$22 = {prev = 0x5555555a6650, next = 0x5555555a6910, catalogId = {tableoid = 0, oid = 0}, dumpId = 3326, section = SECTION_NONE, hadDumper = false,
tag = 0x5555555854c0 "TABLE event", namespace = 0x555555585670 "public", tablespace = 0x0, tableam = 0x0, owner = 0x555555585730 "euler", desc = 0x555555585580 "ACL",
defn = 0x5555555a6890 "GRANT SELECT ON TABLE public.event TO role1;\nGRANT SELECT,INSERT,DELETE,UPDATE ON TABLE public.event TO role2;\n", dropStmt = 0x0, copyStmt = 0x0,
dependencies = 0x5555555a6050, nDeps = 1, dataDumper = 0x0, dataDumperArg = 0x0, formatData = 0x555555585400, dataLength = 0, reqs = 1, created = false, pending_prev = 0x0,
pending_next = 0x0, depCount = 0, revDeps = 0x0, nRevDeps = 0, lockDeps = 0x0, nLockDeps = 0}

It means that an ACL for columns has 2 dependencies (nDeps = 2) the function
_tocEntryRequired() returns 0 (see code below).

else if (ropt->schemaNames.head != NULL ||
ropt->schemaExcludeNames.head != NULL ||
ropt->selTypes)
{
/*
* In a selective dump/restore, we want to restore these dependent
* TOC entry types only if their parent object is being restored.
* Without selectivity options, we let through everything in the
* archive. Note there may be such entries with no parent, eg
* non-default ACLs for built-in objects.
*
* This code depends on the parent having been marked already,
* which should be the case; if it isn't, perhaps due to
* SortTocFromFile rearrangement, skipping the dependent entry
* seems prudent anyway.
*
* Ideally we'd handle, eg, table CHECK constraints this way too.
* But it's hard to tell which of their dependencies is the one to
* consult.
*/
if (te->nDeps != 1 ||
TocIDRequired(AH, te->dependencies[0]) == 0)
return 0;
}

Hence, ProcessArchiveRestoreOptions() sets te->reqs to 0 that avoids the ACL
for columns to be restored.

te->reqs = _tocEntryRequired(te, curSection, AH);

The discussion [1] in the commit 0d4e6ed3085 does not explain if the 'if' logic
covers all cases. It certainly doesn't for the OP case. The only (hackish)
suggestion I have ATM is to detect ACL for columns and returns 1.

[1] https://www.postgresql.org/message-id/32668.1516848577%40sss.pgh.pa.us

--
Euler Taveira
EDB https://www.enterprisedb.com/