pg_dump, pg_dumpall, pg_restore: Add --no-policies option
Started by Nikolay Samokhvalovabout 1 year ago9 messages
pg_dump[all] and pg_restore have a lot of "--no-XXX" options,
I noticed there is no "--no-policies"; the patch implements it for pg_dump,
pg_dumpall, and pg_restore.
This can be useful in scenarios where policies need to be redefined in the
target system or when moving data between environments with different
security requirements.
Looking for feedback.
Nik
Attachments:
0001-pg_dump-pg_dumpall-pg_restore-Add-no-policies-option.patchapplication/octet-stream; name=0001-pg_dump-pg_dumpall-pg_restore-Add-no-policies-option.patchDownload
From d3ce496cf30ec928ba8b2be39ef85fccdd647d24 Mon Sep 17 00:00:00 2001
From: Nikolay Samokhvalov <nik@postgres.ai>
Date: Thu, 9 Jan 2025 20:21:39 +0000
Subject: [PATCH] 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.
---
doc/src/sgml/ref/pg_dump.sgml | 9 +++++++++
doc/src/sgml/ref/pg_dumpall.sgml | 9 +++++++++
doc/src/sgml/ref/pg_restore.sgml | 10 ++++++++++
src/bin/pg_dump/pg_backup.h | 2 ++
src/bin/pg_dump/pg_backup_archiver.c | 7 +++++++
src/bin/pg_dump/pg_dump.c | 6 ++++++
src/bin/pg_dump/pg_dumpall.c | 5 +++++
src/bin/pg_dump/pg_restore.c | 4 ++++
8 files changed, 52 insertions(+)
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index d66e901f51b..2f8c7b38048 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -1080,6 +1080,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 014f2792589..78bd7c73247 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -422,6 +422,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 b8b27e1719e..2673702a6b8 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -703,6 +703,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 f0f19bb0b29..3084f1ec417 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -110,6 +110,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,6 +182,7 @@ typedef struct _dumpOptions
int no_comments;
int no_security_labels;
int no_publications;
+ int no_policies; /* Skip row security policies */
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 707a3fc844c..efa49610491 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -183,6 +183,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;
@@ -2944,6 +2945,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 8f73a5df956..821e1942d47 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -498,6 +498,7 @@ main(int argc, char **argv)
{"no-toast-compression", no_argument, &dopt.no_toast_compression, 1},
{"no-unlogged-table-data", no_argument, &dopt.no_unlogged_table_data, 1},
{"no-sync", no_argument, NULL, 7},
+ {"no-policies", no_argument, &dopt.no_policies, 1},
{"on-conflict-do-nothing", no_argument, &dopt.do_nothing, 1},
{"rows-per-insert", required_argument, NULL, 10},
{"include-foreign-data", required_argument, NULL, 11},
@@ -1219,6 +1220,7 @@ help(const char *progname)
printf(_(" --inserts dump data as INSERT commands, rather than COPY\n"));
printf(_(" --load-via-partition-root load partitions via the root table\n"));
printf(_(" --no-comments do not dump comment commands\n"));
+ printf(_(" --no-policies do not dump row security policies\n"));
printf(_(" --no-publications do not dump publications\n"));
printf(_(" --no-security-labels do not dump security label assignments\n"));
printf(_(" --no-subscriptions do not dump subscriptions\n"));
@@ -4171,6 +4173,10 @@ dumpPolicy(Archive *fout, const PolicyInfo *polinfo)
if (!dopt->dumpSchema)
return;
+ /* Skip if --no-policies was specified */
+ if (dopt->no_policies)
+ return;
+
/*
* If polname is NULL, then this record is just indicating that ROW LEVEL
* SECURITY is enabled for the table. Dump as ALTER TABLE <table> ENABLE
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 396f79781c5..4cba798b884 100644
--- 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_subscriptions = 0;
@@ -168,6 +169,7 @@ main(int argc, char *argv[])
{"role", required_argument, NULL, 3},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
{"no-comments", no_argument, &no_comments, 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-security-labels", no_argument, &no_security_labels, 1},
@@ -447,6 +449,8 @@ main(int argc, char *argv[])
appendPQExpBufferStr(pgdumpopts, " --use-set-session-authorization");
if (no_comments)
appendPQExpBufferStr(pgdumpopts, " --no-comments");
+ if (no_policies)
+ appendPQExpBufferStr(pgdumpopts, " --no-policies");
if (no_publications)
appendPQExpBufferStr(pgdumpopts, " --no-publications");
if (no_security_labels)
@@ -663,6 +667,7 @@ help(void)
printf(_(" --inserts dump data as INSERT commands, rather than COPY\n"));
printf(_(" --load-via-partition-root load partitions via the root table\n"));
printf(_(" --no-comments do not dump comment commands\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-security-labels do not dump security label assignments\n"));
diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c
index 88ae39d938a..44c33a74238 100644
--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -71,6 +71,7 @@ main(int argc, char **argv)
static int outputNoTablespaces = 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_subscriptions = 0;
@@ -124,6 +125,7 @@ main(int argc, char **argv)
{"transaction-size", required_argument, NULL, 5},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
{"no-comments", no_argument, &no_comments, 1},
+ {"no-policies", no_argument, &no_policies, 1},
{"no-publications", no_argument, &no_publications, 1},
{"no-security-labels", no_argument, &no_security_labels, 1},
{"no-subscriptions", no_argument, &no_subscriptions, 1},
@@ -372,6 +374,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;
@@ -493,6 +496,7 @@ usage(const char *progname)
printf(_(" --no-comments do not restore comment commands\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 security policies\n"));
printf(_(" --no-publications do not restore publications\n"));
printf(_(" --no-security-labels do not restore security labels\n"));
printf(_(" --no-subscriptions do not restore subscriptions\n"));
--
2.47.0
Re: pg_dump, pg_dumpall, pg_restore: Add --no-policies option
Looks good to me. Would ideally like to see some tests: should be easy
enough to add to t/002_pg_dump.pl, but probably not worth it just for a
simple flag like this? We don't test a lot of other flags, but on the other
hand, that's what a test suite is supposed to do.
Cheers,
Greg
Re: pg_dump, pg_dumpall, pg_restore: Add --no-policies option
Thank you Greg.
002_pg_dump.pl already deals with CREATE POLICY and ALTER TABLE .. ENABLE
ROW LEVEL SECURITY, so I just added "--no-policies" there, to have basic
coverage.
Nik
On Fri, Jan 10, 2025 at 9:44 AM Greg Sabino Mullane <htamfids@gmail.com>
wrote:
Show quoted text
Looks good to me. Would ideally like to see some tests: should be easy
enough to add to t/002_pg_dump.pl, but probably not worth it just for a
simple flag like this? We don't test a lot of other flags, but on the other
hand, that's what a test suite is supposed to do.Cheers,
Greg
Attachments:
0002-pg_dump-pg_dumpall-pg_restore-Add-no-policies-option.patchapplication/x-patch; name=0002-pg_dump-pg_dumpall-pg_restore-Add-no-policies-option.patchDownload
From d66470a0188be437789934e1d03795ca687553e5 Mon Sep 17 00:00:00 2001
From: Nikolay Samokhvalov <nik@postgres.ai>
Date: Thu, 9 Jan 2025 20:21:39 +0000
Subject: [PATCH] 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.
---
doc/src/sgml/ref/pg_dump.sgml | 9 +++++++++
doc/src/sgml/ref/pg_dumpall.sgml | 9 +++++++++
doc/src/sgml/ref/pg_restore.sgml | 10 ++++++++++
src/bin/pg_dump/pg_backup.h | 2 ++
src/bin/pg_dump/pg_backup_archiver.c | 7 +++++++
src/bin/pg_dump/pg_dump.c | 6 ++++++
src/bin/pg_dump/pg_dumpall.c | 5 +++++
src/bin/pg_dump/pg_restore.c | 4 ++++
src/bin/pg_dump/t/002_pg_dump.pl | 9 +++++++++
9 files changed, 61 insertions(+)
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index d66e901f51b..2f8c7b38048 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -1080,6 +1080,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 014f2792589..78bd7c73247 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -422,6 +422,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 b8b27e1719e..57802e3c9ef 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -703,6 +703,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 f0f19bb0b29..3084f1ec417 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -110,6 +110,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,6 +182,7 @@ typedef struct _dumpOptions
int no_comments;
int no_security_labels;
int no_publications;
+ int no_policies; /* Skip row security policies */
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 707a3fc844c..305e7955c1c 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -183,6 +183,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;
@@ -2944,6 +2945,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 8f73a5df956..821e1942d47 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -498,6 +498,7 @@ main(int argc, char **argv)
{"no-toast-compression", no_argument, &dopt.no_toast_compression, 1},
{"no-unlogged-table-data", no_argument, &dopt.no_unlogged_table_data, 1},
{"no-sync", no_argument, NULL, 7},
+ {"no-policies", no_argument, &dopt.no_policies, 1},
{"on-conflict-do-nothing", no_argument, &dopt.do_nothing, 1},
{"rows-per-insert", required_argument, NULL, 10},
{"include-foreign-data", required_argument, NULL, 11},
@@ -1219,6 +1220,7 @@ help(const char *progname)
printf(_(" --inserts dump data as INSERT commands, rather than COPY\n"));
printf(_(" --load-via-partition-root load partitions via the root table\n"));
printf(_(" --no-comments do not dump comment commands\n"));
+ printf(_(" --no-policies do not dump row security policies\n"));
printf(_(" --no-publications do not dump publications\n"));
printf(_(" --no-security-labels do not dump security label assignments\n"));
printf(_(" --no-subscriptions do not dump subscriptions\n"));
@@ -4171,6 +4173,10 @@ dumpPolicy(Archive *fout, const PolicyInfo *polinfo)
if (!dopt->dumpSchema)
return;
+ /* Skip if --no-policies was specified */
+ if (dopt->no_policies)
+ return;
+
/*
* If polname is NULL, then this record is just indicating that ROW LEVEL
* SECURITY is enabled for the table. Dump as ALTER TABLE <table> ENABLE
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 396f79781c5..4cba798b884 100644
--- 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_subscriptions = 0;
@@ -168,6 +169,7 @@ main(int argc, char *argv[])
{"role", required_argument, NULL, 3},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
{"no-comments", no_argument, &no_comments, 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-security-labels", no_argument, &no_security_labels, 1},
@@ -447,6 +449,8 @@ main(int argc, char *argv[])
appendPQExpBufferStr(pgdumpopts, " --use-set-session-authorization");
if (no_comments)
appendPQExpBufferStr(pgdumpopts, " --no-comments");
+ if (no_policies)
+ appendPQExpBufferStr(pgdumpopts, " --no-policies");
if (no_publications)
appendPQExpBufferStr(pgdumpopts, " --no-publications");
if (no_security_labels)
@@ -663,6 +667,7 @@ help(void)
printf(_(" --inserts dump data as INSERT commands, rather than COPY\n"));
printf(_(" --load-via-partition-root load partitions via the root table\n"));
printf(_(" --no-comments do not dump comment commands\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-security-labels do not dump security label assignments\n"));
diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c
index 88ae39d938a..cce13a164cb 100644
--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -71,6 +71,7 @@ main(int argc, char **argv)
static int outputNoTablespaces = 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_subscriptions = 0;
@@ -124,6 +125,7 @@ main(int argc, char **argv)
{"transaction-size", required_argument, NULL, 5},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
{"no-comments", no_argument, &no_comments, 1},
+ {"no-policies", no_argument, &no_policies, 1},
{"no-publications", no_argument, &no_publications, 1},
{"no-security-labels", no_argument, &no_security_labels, 1},
{"no-subscriptions", no_argument, &no_subscriptions, 1},
@@ -372,6 +374,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;
@@ -493,6 +496,7 @@ usage(const char *progname)
printf(_(" --no-comments do not restore comment commands\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-security-labels do not restore security labels\n"));
printf(_(" --no-subscriptions do not restore subscriptions\n"));
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index bf65d44b942..a0003297446 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -523,6 +523,13 @@ my %pgdump_runs = (
'-B', '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',
@@ -1234,6 +1241,7 @@ my %tests = (
exclude_dump_test_schema => 1,
exclude_test_table => 1,
only_dump_measurement => 1,
+ no_policies => 1,
},
},
@@ -2840,6 +2848,7 @@ my %tests = (
exclude_dump_test_schema => 1,
exclude_test_table => 1,
only_dump_measurement => 1,
+ no_policies => 1,
},
},
--
GitLab
Re: pg_dump, pg_dumpall, pg_restore: Add --no-policies option
hi.
around src/bin/pg_dump/pg_dump.c line 1117
right after "ropt->no_comments = dopt.no_comments;"
we also need add
ropt->no_policies = dopt.no_policies;
?
overall looks good to me.
The tests seem wrong per
https://cirrus-ci.com/github/postgresql-cfbot/postgresql/cf%2F5499
I have no idea how to improve the test.
Re: pg_dump, pg_dumpall, pg_restore: Add --no-policies option
On Wed, 15 Jan 2025 at 13:21, jian he <jian.universality@gmail.com> wrote:
hi.
around src/bin/pg_dump/pg_dump.c line 1117
right after "ropt->no_comments = dopt.no_comments;"
we also need add
ropt->no_policies = dopt.no_policies;
?overall looks good to me.
The tests seem wrong per
https://cirrus-ci.com/github/postgresql-cfbot/postgresql/cf%2F5499
I have no idea how to improve the test.
Here is a rebased version along with the test failure fixes, please
accept the change if you are ok with it.
Regards,
Vignesh
Attachments:
v3-0001-pg_dump-pg_dumpall-pg_restore-Add-no-policies-opt.patchtext/x-patch; charset=US-ASCII; name=v3-0001-pg_dump-pg_dumpall-pg_restore-Add-no-policies-opt.patchDownload
From 2df5cd6aaaacb3f0679f239b3aede3a98e122be2 Mon Sep 17 00:00:00 2001
From: Vignesh <vignesh21@gmail.com>
Date: Thu, 27 Feb 2025 15:32:45 +0530
Subject: [PATCH v3] 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.
---
doc/src/sgml/ref/pg_dump.sgml | 9 +++++++++
doc/src/sgml/ref/pg_dumpall.sgml | 9 +++++++++
doc/src/sgml/ref/pg_restore.sgml | 10 ++++++++++
src/bin/pg_dump/pg_backup.h | 2 ++
src/bin/pg_dump/pg_backup_archiver.c | 7 +++++++
src/bin/pg_dump/pg_dump.c | 6 ++++++
src/bin/pg_dump/pg_dumpall.c | 5 +++++
src/bin/pg_dump/pg_restore.c | 4 ++++
src/bin/pg_dump/t/002_pg_dump.pl | 15 +++++++++++++++
9 files changed, 67 insertions(+)
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 1975054d7bf..0ae40f9be58 100644
--- 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 c2fa5be9519..ae5afb3c7d5 100644
--- 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 199ea3345f3..35140187807 100644
--- 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 e783cc68d89..ada80c6cf9a 100644
--- 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 */
@@ -183,6 +184,7 @@ typedef struct _dumpOptions
int no_comments;
int no_security_labels;
int no_publications;
+ int no_policies; /* Skip row security policies */
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 632077113a4..7453d5a6fdf 100644
--- 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 7c38c89bf08..6a39464709f 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -508,6 +508,7 @@ main(int argc, char **argv)
{"no-toast-compression", no_argument, &dopt.no_toast_compression, 1},
{"no-unlogged-table-data", no_argument, &dopt.no_unlogged_table_data, 1},
{"no-sync", no_argument, NULL, 7},
+ {"no-policies", no_argument, &dopt.no_policies, 1},
{"on-conflict-do-nothing", no_argument, &dopt.do_nothing, 1},
{"rows-per-insert", required_argument, NULL, 10},
{"include-foreign-data", required_argument, NULL, 11},
@@ -1262,6 +1263,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"));
@@ -4218,6 +4220,10 @@ dumpPolicy(Archive *fout, const PolicyInfo *polinfo)
if (!dopt->dumpSchema)
return;
+ /* Skip if --no-policies was specified */
+ if (dopt->no_policies)
+ return;
+
/*
* If polname is NULL, then this record is just indicating that ROW LEVEL
* SECURITY is enabled for the table. Dump as ALTER TABLE <table> ENABLE
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index e0867242526..2935cac2c46 100644
--- 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 13e4dc507e0..d947b2d2068 100644
--- 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 c7bffc1b045..4c674232fae 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -579,6 +579,13 @@ 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 +810,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 +1336,7 @@ my %tests = (
unlike => {
exclude_dump_test_schema => 1,
exclude_test_table => 1,
+ no_policies => 1,
only_dump_measurement => 1,
},
},
@@ -2948,6 +2957,7 @@ my %tests = (
unlike => {
exclude_dump_test_schema => 1,
exclude_test_table => 1,
+ no_policies => 1,
only_dump_measurement => 1,
},
},
@@ -2969,6 +2979,7 @@ my %tests = (
unlike => {
exclude_dump_test_schema => 1,
exclude_test_table => 1,
+ no_policies => 1,
only_dump_measurement => 1,
},
},
@@ -2990,6 +3001,7 @@ my %tests = (
unlike => {
exclude_dump_test_schema => 1,
exclude_test_table => 1,
+ no_policies => 1,
only_dump_measurement => 1,
},
},
@@ -3011,6 +3023,7 @@ my %tests = (
unlike => {
exclude_dump_test_schema => 1,
exclude_test_table => 1,
+ no_policies => 1,
only_dump_measurement => 1,
},
},
@@ -3032,6 +3045,7 @@ my %tests = (
unlike => {
exclude_dump_test_schema => 1,
exclude_test_table => 1,
+ no_policies => 1,
only_dump_measurement => 1,
},
},
@@ -3053,6 +3067,7 @@ my %tests = (
unlike => {
exclude_dump_test_schema => 1,
exclude_test_table => 1,
+ no_policies => 1,
only_dump_measurement => 1,
},
},
--
2.43.0
Re: pg_dump, pg_dumpall, pg_restore: Add --no-policies option
Re-reviewed this patch: still compiles, tests pass, and does what it says
on the tin. +1
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
Re: pg_dump, pg_dumpall, pg_restore: Add --no-policies option
Hi
On 27.02.25 15:37, vignesh C wrote:
Here is a rebased version along with the test failure fixes, please
accept the change if you are ok with it.
Patch LGTM. +1
It applies cleanly and works as described:
== pg_dump ==
$ /usr/local/postgres-dev/bin/pg_dump db > dump.out
$ grep "POLICY" dump.out | tee /dev/tty | wc -l
-- Name: t p; Type: POLICY; Schema: public; Owner: jim
CREATE POLICY p ON public.t FOR DELETE;
2
$ /usr/local/postgres-dev/bin/pg_dump db --no-policies > dump.out
$ grep "POLICY" dump.out | tee /dev/tty | wc -l
0
== pg_dumpall ==
$ /usr/local/postgres-dev/bin/pg_dumpall > dumpall.out
$ grep "POLICY" dumpall.out | tee /dev/tty | wc -l
-- Name: t p; Type: POLICY; Schema: public; Owner: jim
CREATE POLICY p ON public.t FOR DELETE;
2
$ /usr/local/postgres-dev/bin/pg_dumpall --no-policies > dumpall.out
$ grep "POLICY" dumpall.out | tee /dev/tty | wc -l
0
== pg_restore ==
$ /usr/local/postgres-dev/bin/pg_dump -Fc db > dump.out
$ /usr/local/postgres-dev/bin/pg_restore -l dump.out | grep POLICY | tee
/dev/tty | wc -l
3375; 3256 16388 POLICY public t p jim
1
$ /usr/local/postgres-dev/bin/pg_restore --no-policies -l dump.out |
grep POLICY | tee /dev/tty | wc -l
0
check-world passes and the documentation is consistent with the existing
"--no*" options of pg_dump, pg_dumpall, and pg_restore.
The new status of this patch is: Ready for Committer
Best regards, Jim
Re: pg_dump, pg_dumpall, pg_restore: Add --no-policies option
The following review has been posted through the commitfest application:
make installcheck-world: not tested
Implements feature: not tested
Spec compliant: not tested
Documentation: not tested
Hi,
Tested this patch with `--no-policies` option works as expected by ensuring that policy definitions are not included in database backups. Successfully tested using `pg_dump`, `pg_dumpall`, and `pg_restore`, confirming that policies are excluded upon restoration. The `admin_full_access` policy was correctly applied, granting full access to the `admin` role for the `users` table. Additionally, the `read_only_access` policy was verified to restrict the `readonly` role to only performing `SELECT` operations.
Regards,
Newt Global PostgreSQL Contributors
Re: pg_dump, pg_dumpall, pg_restore: Add --no-policies option
Greg Sabino Mullane <htamfids@gmail.com> writes:
Re-reviewed this patch: still compiles, tests pass, and does what it says
on the tin. +1
Pushed with minor corrections:
* The patch still hadn't absorbed jian he's point that pg_dump main()
needs to fill ropt->no_policies from dopt.no_policies. It's possible
that that had no externally-visible effect, but just as a matter of
style we should fill the RestoreOptions fully.
* It seems better to me to implement the no_policies filter in
getPolicies() not dumpPolicy(). That way we don't waste effort
on retrieving catalog data we aren't going to use. It might be
problematic if we had to deal with dependency chains involving
policies, but AFAIK there is nothing that depends on a policy.
* I fixed a couple bits of sloppy alphabetization and updated
the Perl style in the test script.
regards, tom lane