pg_dump, pg_dumpall, pg_restore: Add --no-policies option.

Add --no-policies option to control row level security policy handling
in dump and restore operations. When this option is used, both CREATE
POLICY commands and ALTER TABLE ... ENABLE ROW LEVEL SECURITY commands
are excluded from dumps and skipped during restores.

This is useful in scenarios where policies need to be redefined in the
target system or when moving data between environments with different
security requirements.

Author: Nikolay Samokhvalov <nik@postgres.ai>
Reviewed-by: Greg Sabino Mullane <htamfids@gmail.com>
Reviewed-by: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: newtglobal postgresql_contributors <postgresql_contributors@newtglobalcorp.com>
Discussion: https://postgr.es/m/CAM527d8kG2qPKvbfJ=OYJkT7iRNd623Bk+m-a4ngm+nyHYsHog@mail.gmail.com

diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 1975054d7bfbbd685a318d3ee19634bf5f667043..0ae40f9be58d28beb33160d50f87ab4d49ee488b 100644 (file)
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -1105,6 +1105,15 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--no-policies</option></term>
+      <listitem>
+       <para>
+        Do not dump row security policies.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--no-publications</option></term>
       <listitem>

diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index c2fa5be95193b79471a73004a53d195d1f5ac6e9..ae5afb3c7d536683bec2539632641163c15831c2 100644 (file)
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -441,6 +441,15 @@ exclude database <replaceable class="parameter">PATTERN</replaceable>
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--no-policies</option></term>
+      <listitem>
+       <para>
+        Do not dump row security policies.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--no-publications</option></term>
       <listitem>

diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index 199ea3345f30cc361175f504321fd212563e15f9..351401878078f0c953f1719ccd04b2dce74280a6 100644 (file)
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -723,6 +723,16 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--no-policies</option></term>
+      <listitem>
+       <para>
+        Do not output commands to restore row security policies, even if
+        the archive contains them.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--no-publications</option></term>
       <listitem>

diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index e783cc68d8924cbfcf066294af579eefe616ab51..658986de6f8367986b861a3d5e52f7020c137b5a 100644 (file)
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -111,6 +111,7 @@ typedef struct _restoreOptions
    int         column_inserts;
    int         if_exists;
    int         no_comments;    /* Skip comments */
+   int         no_policies;    /* Skip row security policies */
    int         no_publications;    /* Skip publication entries */
    int         no_security_labels; /* Skip security label entries */
    int         no_subscriptions;   /* Skip subscription entries */
@@ -181,8 +182,9 @@ typedef struct _dumpOptions
    int         column_inserts;
    int         if_exists;
    int         no_comments;
-   int         no_security_labels;
+   int         no_policies;    /* Skip row security policies */
    int         no_publications;
+   int         no_security_labels;
    int         no_subscriptions;
    int         no_toast_compression;
    int         no_unlogged_table_data;

diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 7480e122b61f2aee4134533e138606851a34516f..82d51c89ac67baacce637648019974f05980e64c 100644 (file)
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -188,6 +188,7 @@ dumpOptionsFromRestoreOptions(RestoreOptions *ropt)
    dopt->disable_dollar_quoting = ropt->disable_dollar_quoting;
    dopt->dump_inserts = ropt->dump_inserts;
    dopt->no_comments = ropt->no_comments;
+   dopt->no_policies = ropt->no_policies;
    dopt->no_publications = ropt->no_publications;
    dopt->no_security_labels = ropt->no_security_labels;
    dopt->no_subscriptions = ropt->no_subscriptions;
@@ -2966,6 +2967,12 @@ _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH)
    if (ropt->no_comments && strcmp(te->desc, "COMMENT") == 0)
        return 0;
 
+   /* If it's a policy, maybe ignore it */
+   if (ropt->no_policies &&
+       (strcmp(te->desc, "POLICY") == 0 ||
+        strcmp(te->desc, "ROW SECURITY") == 0))
+       return 0;
+
    /*
     * If it's a publication or a table part of a publication, maybe ignore
     * it.

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index c371570501ac0f5bece8e4adbc79f50f3dfbbec0..428ed2d60fca80f526148fabce57ee947aab6817 100644 (file)
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -500,6 +500,7 @@ main(int argc, char **argv)
        {"use-set-session-authorization", no_argument, &dopt.use_setsessauth, 1},
        {"no-comments", no_argument, &dopt.no_comments, 1},
        {"no-data", no_argument, NULL, 19},
+       {"no-policies", no_argument, &dopt.no_policies, 1},
        {"no-publications", no_argument, &dopt.no_publications, 1},
        {"no-schema", no_argument, NULL, 20},
        {"no-security-labels", no_argument, &dopt.no_security_labels, 1},
@@ -1152,6 +1153,7 @@ main(int argc, char **argv)
    ropt->disable_dollar_quoting = dopt.disable_dollar_quoting;
    ropt->dump_inserts = dopt.dump_inserts;
    ropt->no_comments = dopt.no_comments;
+   ropt->no_policies = dopt.no_policies;
    ropt->no_publications = dopt.no_publications;
    ropt->no_security_labels = dopt.no_security_labels;
    ropt->no_subscriptions = dopt.no_subscriptions;
@@ -1259,6 +1261,7 @@ help(const char *progname)
    printf(_("  --load-via-partition-root    load partitions via the root table\n"));
    printf(_("  --no-comments                do not dump comment commands\n"));
    printf(_("  --no-data                    do not dump data\n"));
+   printf(_("  --no-policies                do not dump row security policies\n"));
    printf(_("  --no-publications            do not dump publications\n"));
    printf(_("  --no-schema                  do not dump schema\n"));
    printf(_("  --no-security-labels         do not dump security label assignments\n"));
@@ -4035,6 +4038,7 @@ dumpLOs(Archive *fout, const void *arg)
 void
 getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
 {
+   DumpOptions *dopt = fout->dopt;
    PQExpBuffer query;
    PQExpBuffer tbloids;
    PGresult   *res;
@@ -4056,6 +4060,10 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
    if (fout->remoteVersion < 90500)
        return;
 
+   /* Skip if --no-policies was specified */
+   if (dopt->no_policies)
+       return;
+
    query = createPQExpBuffer();
    tbloids = createPQExpBuffer();
 

diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index e08672425263bbe029ef74a698780cf942bcec59..2935cac2c467c4fcf25ceb4583fb61426521298f 100644 (file)
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -101,6 +101,7 @@ static int  no_table_access_method = 0;
 static int no_tablespaces = 0;
 static int use_setsessauth = 0;
 static int no_comments = 0;
+static int no_policies = 0;
 static int no_publications = 0;
 static int no_security_labels = 0;
 static int no_data = 0;
@@ -173,6 +174,7 @@ main(int argc, char *argv[])
        {"use-set-session-authorization", no_argument, &use_setsessauth, 1},
        {"no-comments", no_argument, &no_comments, 1},
        {"no-data", no_argument, &no_data, 1},
+       {"no-policies", no_argument, &no_policies, 1},
        {"no-publications", no_argument, &no_publications, 1},
        {"no-role-passwords", no_argument, &no_role_passwords, 1},
        {"no-schema", no_argument, &no_schema, 1},
@@ -457,6 +459,8 @@ main(int argc, char *argv[])
        appendPQExpBufferStr(pgdumpopts, " --no-comments");
    if (no_data)
        appendPQExpBufferStr(pgdumpopts, " --no-data");
+   if (no_policies)
+       appendPQExpBufferStr(pgdumpopts, " --no-policies");
    if (no_publications)
        appendPQExpBufferStr(pgdumpopts, " --no-publications");
    if (no_security_labels)
@@ -681,6 +685,7 @@ help(void)
    printf(_("  --load-via-partition-root    load partitions via the root table\n"));
    printf(_("  --no-comments                do not dump comment commands\n"));
    printf(_("  --no-data                    do not dump data\n"));
+   printf(_("  --no-policies                do not dump row security policies\n"));
    printf(_("  --no-publications            do not dump publications\n"));
    printf(_("  --no-role-passwords          do not dump passwords for roles\n"));
    printf(_("  --no-schema                  do not dump schema\n"));

diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c
index 13e4dc507e0462777c4a1e96a28eb25ccbf4c441..d947b2d2068096163633a02569e27d468e096870 100644 (file)
--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -74,6 +74,7 @@ main(int argc, char **argv)
    static int  use_setsessauth = 0;
    static int  no_comments = 0;
    static int  no_data = 0;
+   static int  no_policies = 0;
    static int  no_publications = 0;
    static int  no_schema = 0;
    static int  no_security_labels = 0;
@@ -129,6 +130,7 @@ main(int argc, char **argv)
        {"use-set-session-authorization", no_argument, &use_setsessauth, 1},
        {"no-comments", no_argument, &no_comments, 1},
        {"no-data", no_argument, &no_data, 1},
+       {"no-policies", no_argument, &no_policies, 1},
        {"no-publications", no_argument, &no_publications, 1},
        {"no-schema", no_argument, &no_schema, 1},
        {"no-security-labels", no_argument, &no_security_labels, 1},
@@ -385,6 +387,7 @@ main(int argc, char **argv)
    opts->noTablespace = outputNoTablespaces;
    opts->use_setsessauth = use_setsessauth;
    opts->no_comments = no_comments;
+   opts->no_policies = no_policies;
    opts->no_publications = no_publications;
    opts->no_security_labels = no_security_labels;
    opts->no_subscriptions = no_subscriptions;
@@ -505,6 +508,7 @@ usage(const char *progname)
    printf(_("  --no-data                    do not restore data\n"));
    printf(_("  --no-data-for-failed-tables  do not restore data of tables that could not be\n"
             "                               created\n"));
+   printf(_("  --no-policies                do not restore row level security policies\n"));
    printf(_("  --no-publications            do not restore publications\n"));
    printf(_("  --no-schema                  do not restore schema\n"));
    printf(_("  --no-security-labels         do not restore security labels\n"));

diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index c7bffc1b045319a6585829e20e2f9ea57ffe1335..d281e27aa677883b5edf440be9bd6970ec06a1c8 100644 (file)
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -579,6 +579,14 @@ my %pgdump_runs = (
            'postgres',
        ],
    },
+   no_policies => {
+       dump_cmd => [
+           'pg_dump', '--no-sync',
+           '--file' => "$tempdir/no_policies.sql",
+           '--no-policies',
+           'postgres',
+       ],
+   },
    no_privs => {
        dump_cmd => [
            'pg_dump', '--no-sync',
@@ -803,6 +811,7 @@ my %full_runs = (
    no_toast_compression => 1,
    no_large_objects => 1,
    no_owner => 1,
+   no_policies => 1,
    no_privs => 1,
    no_statistics => 1,
    no_table_access_method => 1,
@@ -1328,6 +1337,7 @@ my %tests = (
        unlike => {
            exclude_dump_test_schema => 1,
            exclude_test_table => 1,
+           no_policies => 1,
            only_dump_measurement => 1,
        },
    },
@@ -2948,6 +2958,7 @@ my %tests = (
        unlike => {
            exclude_dump_test_schema => 1,
            exclude_test_table => 1,
+           no_policies => 1,
            only_dump_measurement => 1,
        },
    },
@@ -2969,6 +2980,7 @@ my %tests = (
        unlike => {
            exclude_dump_test_schema => 1,
            exclude_test_table => 1,
+           no_policies => 1,
            only_dump_measurement => 1,
        },
    },
@@ -2990,6 +3002,7 @@ my %tests = (
        unlike => {
            exclude_dump_test_schema => 1,
            exclude_test_table => 1,
+           no_policies => 1,
            only_dump_measurement => 1,
        },
    },
@@ -3011,6 +3024,7 @@ my %tests = (
        unlike => {
            exclude_dump_test_schema => 1,
            exclude_test_table => 1,
+           no_policies => 1,
            only_dump_measurement => 1,
        },
    },
@@ -3032,6 +3046,7 @@ my %tests = (
        unlike => {
            exclude_dump_test_schema => 1,
            exclude_test_table => 1,
+           no_policies => 1,
            only_dump_measurement => 1,
        },
    },
@@ -3053,6 +3068,7 @@ my %tests = (
        unlike => {
            exclude_dump_test_schema => 1,
            exclude_test_table => 1,
+           no_policies => 1,
            only_dump_measurement => 1,
        },
    },