Horton

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

jian he <jian.universality@gmail.com>

View thread View on postgr.es
From: jian he <jian.universality@gmail.com>
To: Fujii Masao <masao.fujii@gmail.com>
Cc: Fujii Masao <masao.fujii@oss.nttdata.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2025-09-04T09:00:02Z
Lists: pgsql-hackers 
On Wed, Sep 3, 2025 at 7:50 PM Fujii Masao <masao.fujii@gmail.com> wrote:
>
> > > > 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.
>
>

in _tocEntryRestorePass
if we do

    if ((strcmp(te->desc, "COMMENT") == 0 ||
         strcmp(te->desc, "SECURITY LABEL") == 0) &&
        strncmp(te->tag, "EVENT TRIGGER ", 14) == 0)
        return RESTORE_PASS_POST_ACL;

then RestorePass related comments also need to be adjusted for security label?

typedef enum
{
    RESTORE_PASS_MAIN = 0,        /* Main pass (most TOC item types) */
    RESTORE_PASS_ACL,            /* ACL item types */
    RESTORE_PASS_POST_ACL,        /* Event trigger and matview refresh items */

#define RESTORE_PASS_LAST RESTORE_PASS_POST_ACL
} RestorePass;

we do not support security label on extension, see SecLabelSupportsObjectType.
below the dumpExtension function code should be removed?

   /* Dump Extension Comments and Security Labels */
    if (extinfo->dobj.dump & DUMP_COMPONENT_SECLABEL)
        dumpSecLabel(fout, "EXTENSION", qextname,
                     NULL, "",
                     extinfo->dobj.catId, 0, extinfo->dobj.dumpId);

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.