Re: pg_restore 14 skips ACL COLUMN when --schema is used - Mailing list pgsql-bugs
| From | Kong Man |
|---|---|
| Subject | Re: pg_restore 14 skips ACL COLUMN when --schema is used |
| Date | |
| Msg-id | DM4PR11MB7397E0F1BFD8A839199B59838B05A@DM4PR11MB7397.namprd11.prod.outlook.com Whole thread Raw |
| In response to | Re: pg_restore 14 skips ACL COLUMN when --schema is used (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: pg_restore 14 skips ACL COLUMN when --schema is used
|
| List | pgsql-bugs |
Tom,
You are right. 'Neither a TABLE nor a COLUMN ACL' are pg_restore-d, when I use -n or --schema without the TABLE toc entry.
Not knowing the behavioral change to pg_restore since PG11, I struggled to search for the root cause and made a wrong assumption based on the end result from my script.
What I do in my dbsnapshot script (to build DB baselines at each release cycle for DevOps) is (1) to pg_restore using only relevant SCHEMA toc entries, then (2) with pg_restore -n to scope out only relevant schemas it restores everything from $dumpfile. It has been working well on PG10 for several years until we upgraded a few months ago.
Step 2 there should have included the 'public.event' table (that I showed in the previous message), because the table actually got restored with the table privileges. I still struggle to find out why the column privileges are still missing.
Here are some simplified snippets from my script.
tocfile=/tmp/DBA-710.toc
dumpfile=/tmp/DBA-710.dump
pg_restore --list $dumpfile >$tocfile
DB=appdb
SCHEMAS=(public appschema1 appschema2)
exclusion='londiste|POLICY|PUBLICATION TABLE'
restopts=(-d "service=target dbname=$DB")
Log "[$DB]" " restore SCHEMAs: "$(IFS='|'; cat <<<"${SCHEMAS[*]}")
pg_restore "${restopts[@]}" \
--use-list=<(grep -A1 SCHEMA $tocfile| # only relevant SCHEMA & ACL SCHEMA entries
grep -E "\s("$(IFS='|'; cat <<<"${SCHEMAS[*]}")")\s") \
$dumpfile
restopts+=($(printf " -n %s" ${SCHEMAS[@]})) # Scope out for only relevant schemas
Log "[$DB]" " restore data definitions"
pg_restore "${restopts[@]}" \
--use-list=<(grep -Ev "$exclusion" $tocfile) \
$dumpfile
Thanks,
-Kong
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, July 28, 2023 8:24 PM
To: Kong Man <kong_mansatiansin@hotmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: pg_restore 14 skips ACL COLUMN when --schema is used
Sent: Friday, July 28, 2023 8:24 PM
To: Kong Man <kong_mansatiansin@hotmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: pg_restore 14 skips ACL COLUMN when --schema is used
Kong Man <kong_mansatiansin@hotmail.com> writes:
> pg_restore --schema in our script used to work well against pg_restore 10. Now that we are on PostgreSQL 14. pg_restore 14 skips ACL COLUMN, but not ACL TABLE, when --schema is used.
That's not the behavior I'm seeing. Would you mind providing a
*complete* reproducer, not some fragments?
The behavior I'm seeing is that neither a TABLE nor a COLUMN ACL
will be restored, because (since v11) ACLs are restored only
if their table is restored, and the --use-list option you are
using excludes the table.
We could perhaps imagine special-casing that, but I think it would be
a wart, because for every other kind of object --use-list can only
filter stuff out, not filter it in. (That is, if you are using
--use-list along with other selectivity options, an object must pass
both restrictions to be output. I don't want to make --use-list
override other rules just for ACLs.)
It would be interesting to see your actual use-case, because
I suspect you may be doing something that there's a better way
to do now. What set of objects are you trying to extract?
regards, tom lane
> pg_restore --schema in our script used to work well against pg_restore 10. Now that we are on PostgreSQL 14. pg_restore 14 skips ACL COLUMN, but not ACL TABLE, when --schema is used.
That's not the behavior I'm seeing. Would you mind providing a
*complete* reproducer, not some fragments?
The behavior I'm seeing is that neither a TABLE nor a COLUMN ACL
will be restored, because (since v11) ACLs are restored only
if their table is restored, and the --use-list option you are
using excludes the table.
We could perhaps imagine special-casing that, but I think it would be
a wart, because for every other kind of object --use-list can only
filter stuff out, not filter it in. (That is, if you are using
--use-list along with other selectivity options, an object must pass
both restrictions to be output. I don't want to make --use-list
override other rules just for ACLs.)
It would be interesting to see your actual use-case, because
I suspect you may be doing something that there's a better way
to do now. What set of objects are you trying to extract?
regards, tom lane
pgsql-bugs by date: