From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephen Frost <sfrost@snowman.net>
cc: Jacob Champion <champion.p@gmail.com>,
        Andrew Dunstan <andrew@dunslane.net>,
        Akshat Jaimini <destrex271@gmail.com>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: pg_dump needs SELECT privileges on irrelevant extension table
In-reply-to: <ZTA8D6w8Pg3T80CS@tamriel.snowman.net>
References: <00d46a48-3324-d9a0-49bf-e7f0f11d1038@timescale.com>
 <954419.1679334206@sss.pgh.pa.us>
 <eed220a4-c7f3-2164-02f2-b42783694221@timescale.com>
 <a185f66f-1edc-426e-41a4-091dad230c75@dunslane.net>
 <CAAWbhmjsm+bJrwC1eTGpGk+6MQFG_1vA2g1+Fqa0xE78v1Xncg@mail.gmail.com>
 <CAAWbhmi+fu6Aiucjf76Ss2GPEijrz6VLriby1LaG=p9zNTOxZg@mail.gmail.com>
 <2905669.1697573572@sss.pgh.pa.us> <2984517.1697577076@sss.pgh.pa.us>
 <ZTA8D6w8Pg3T80CS@tamriel.snowman.net>
Comments: In-reply-to Stephen Frost <sfrost@snowman.net>
	message dated "Wed, 18 Oct 2023 16:11:59 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3152155.1697660739.1@sss.pgh.pa.us>
Date: Wed, 18 Oct 2023 16:25:39 -0400
Message-ID: <3152156.1697660739@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3152156.1697660739%40sss.pgh.pa.us>
Precedence: bulk

Stephen Frost <sfrost@snowman.net> writes:
> Greetings,
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> I wrote:
>>> Why are we marking extension member objects as being subject to SECLABEL
>>> or POLICY dumping?

> This change would mean that policies added by a user after the extension
> is created would just be lost by a pg_dump/reload, doesn't it?

Yes.  But I'd say that's unsupported, just like making other ad-hoc
changes to extension objects is unsupported (and the effects will be
lost on dump/reload).  We specifically have support for user-added
ACLs, and that's good, but don't claim that we have support for
doing the same with policies.

As far as I can see, the current behavior is that we'll dump and
try to reload policies (and seclabels) on extension objects even
if those properties were set by the extension creation script.
That has many more problems than just the one Jacob is moaning
about: you'll see failures at reload if you're not superuser,
and if the destination installation has a newer version of the
extension than what was dumped, the old properties might be
completely inappropriate.  So IMO there's basically nothing
that works properly about this.  To make it work, we'd need
infrastructure comparable to the pg_init_privs infrastructure.

			regards, tom lane