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

Поиск
Список
Период
Сортировка
От Kong Man
Тема Re: pg_restore 14 skips ACL COLUMN when --schema is used
Дата
Msg-id DM4PR11MB73970D090F35B7E1C2EEEB108B05A@DM4PR11MB7397.namprd11.prod.outlook.com
обсуждение исходный текст
Ответ на Re: pg_restore 14 skips ACL COLUMN when --schema is used  (Kong Man <kong_mansatiansin@hotmail.com>)
Ответы Re: pg_restore 14 skips ACL COLUMN when --schema is used  ("Euler Taveira" <euler@eulerto.com>)
Список pgsql-bugs
Дерево обсуждения
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.

grep -P "TABLE public event\b|ACL public (COLUMN|TABLE) event\b" $tocfile

1640; 1259 23161 TABLE public event admin
10023; 0 0 ACL public TABLE event admin
10024; 0 0 ACL public COLUMN event.id admin
10025; 0 0 ACL public COLUMN event.event_notification_id admin
10026; 0 0 ACL public COLUMN event.type admin
10027; 0 0 ACL public COLUMN event.version admin
10028; 0 0 ACL public COLUMN event.partner_id admin
10029; 0 0 ACL public COLUMN event.object_id admin
10030; 0 0 ACL public COLUMN event.data admin
10031; 0 0 ACL public COLUMN event.reference_id1 admin
10032; 0 0 ACL public COLUMN event.reference_id2 admin
10033; 0 0 ACL public COLUMN event.reference_id3 admin
10034; 0 0 ACL public COLUMN event.updated admin
10035; 0 0 ACL public COLUMN event.created admin
10036; 0 0 ACL public COLUMN event.actor_id admin
10037; 0 0 ACL public COLUMN event.sla_type admin
10038; 0 0 ACL public COLUMN event.due admin
10039; 0 0 ACL public COLUMN event.completed admin
10040; 0 0 ACL public COLUMN event.instance_id admin
10041; 0 0 ACL public COLUMN event.pii_data admin


pg_restore -n public --use-list=<(grep -P "TABLE public event\b|ACL public (COLUMN|TABLE) event\b" $tocfile) -f - $dumpfile

--
-- PostgreSQL database dump
--
 
-- Dumped from database version 14.5 (Ubuntu 14.5-1.pgdg20.04+1)
-- Dumped by pg_dump version 14.5 (Ubuntu 14.5-1.pgdg20.04+1)
 
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
 
SET default_tablespace = '';
 
SET default_table_access_method = heap;

--
-- Name: event; Type: TABLE; Schema: public; Owner: admin
--

CREATE TABLE public.event (
    id text NOT NULL,
    event_notification_id text,
    type text NOT NULL,
    version text NOT NULL,
    partner_id text,
    object_id text,
    data text NOT NULL,
    reference_id1 text,
    reference_id2 text,
    reference_id3 text,
    updated timestamp with time zone DEFAULT now() NOT NULL,
    created timestamp with time zone DEFAULT now() NOT NULL,
    actor_id text,
    sla_type text,
    due timestamp with time zone,
    completed timestamp with time zone,
    instance_id text,
    pii_data json
);
 
 
ALTER TABLE public.event OWNER TO admin;
 
--
-- Name: TABLE event; Type: ACL; Schema: public; Owner: admin
--
 
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE public.event TO pii_writer;
GRANT SELECT ON TABLE public.event TO pii_reader;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE public.event TO covereditemadm;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE public.event TO claimhistoryadm;
 
 
--
-- PostgreSQL database dump complete
--

From: Kong Man <kong_mansatiansin@hotmail.com>
Sent: Monday, July 31, 2023 3:50 PM
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: pg_restore 14 skips ACL COLUMN when --schema is used
 
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
 
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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: (record = record) inconsistent with record_eq(arg1, arg2)
Следующее
От: "Andrey M. Borodin"
Дата:
Сообщение: Re: DROP DATABASE deadlocks with logical replication worker in PG 15.1