Horton

Re: pg_restore --no-policies should not restore policies' comment

Fujii Masao <masao.fujii@gmail.com>

View thread View on postgr.es
From: Fujii Masao <masao.fujii@gmail.com>
To: jian he <jian.universality@gmail.com>
Cc: Fujii Masao <masao.fujii@oss.nttdata.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2025-09-03T11:50:09Z
Lists: pgsql-hackers

Attachments

On Wed, Aug 27, 2025 at 3:18 PM jian he <jian.universality@gmail.com> wrote:
> > Since --no-publications and --no-subscriptions have been around for a long time,
> > while --no-policies was added in v18, I wonder if it makes sense to first fix
> > the publications and subscriptions cases (and add tests for them) and back-patch
> > to all supported versions. Then we can handle the policies case and
> > back-patch it
> > only to v18. Does that sound reasonable?
> >
> works for me.

So I've split your v2-0001 patch into two patches:

* v4-0001 handles comments on publications and subscriptions when
   --no-publications / --no-subscriptions are specified. This will be
   backpatched to all supported versions.

* v4-0002 handles comments on policies when --no-policies is specified.
   This will be backpatched to v18, where --no-policies was added.

Both v4-0001 and v4-0002 are based on your patch, but I added
regression tests for them.


> > > 02: make pg_dump dump security label for shared database objects, like
> > > subscription, roles.

As I understand it, shared objects like roles are handled by pg_dumpall,
which already dumps their security labels via pg_shseclabel.
Subscriptions are an exception: pg_dump dumps them (and should dump
their security labels), but those labels are stored in pg_shseclabel,
which pg_dump doesn't query.

To fix this, making pg_dump query also pg_shseclabel when dumping
subscriptions would work. But your approach, having pg_dump query
pg_seclabels (covering both pg_seclabel and pg_shseclabel),
is simpler and sufficient. So I like your approach for now.

I also noticed pg_dump didn't dump security labels on event triggers,
so I extended your patch as v4-0003 to handle those as well.


> > > 03: make pg_restore not restore security labels if the associated
> > > object is excluded.

This patch looks good. I only applied minor cosmetic changes and
attached it as v4-0004.

Regards,

-- 
Fujii Masao

Commits

  1. pg_dump: Remove unnecessary code for security labels on extensions.

  2. pg_restore: Fix security label handling with --no-publications/subscriptions.

  3. pg_dump: Fix dumping of security labels on subscriptions and event triggers.

  4. pg_restore: Fix comment handling with --no-policies.

  5. pg_restore: Fix comment handling with --no-publications / --no-subscriptions.

  6. Core support for "extensions", which are packages of SQL objects.