Skip to content

Commit cd3c451

Browse files
tglsfdctglsfdc
committed
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
1 parent 4489044 commit cd3c451

File tree

9 files changed

+71
-1
lines changed

9 files changed

+71
-1
lines changed

‎doc/src/sgml/ref/pg_dump.sgml

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1105,6 +1105,15 @@ PostgreSQL documentation
11051105
</listitem>
11061106
</varlistentry>
11071107

1108+
<varlistentry>
1109+
<term><option>--no-policies</option></term>
1110+
<listitem>
1111+
<para>
1112+
Do not dump row security policies.
1113+
</para>
1114+
</listitem>
1115+
</varlistentry>
1116+
11081117
<varlistentry>
11091118
<term><option>--no-publications</option></term>
11101119
<listitem>

‎doc/src/sgml/ref/pg_dumpall.sgml

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -441,6 +441,15 @@ exclude database <replaceable class="parameter">PATTERN</replaceable>
441441
</listitem>
442442
</varlistentry>
443443

444+
<varlistentry>
445+
<term><option>--no-policies</option></term>
446+
<listitem>
447+
<para>
448+
Do not dump row security policies.
449+
</para>
450+
</listitem>
451+
</varlistentry>
452+
444453
<varlistentry>
445454
<term><option>--no-publications</option></term>
446455
<listitem>

‎doc/src/sgml/ref/pg_restore.sgml

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -723,6 +723,16 @@ PostgreSQL documentation
723723
</listitem>
724724
</varlistentry>
725725

726+
<varlistentry>
727+
<term><option>--no-policies</option></term>
728+
<listitem>
729+
<para>
730+
Do not output commands to restore row security policies, even if
731+
the archive contains them.
732+
</para>
733+
</listitem>
734+
</varlistentry>
735+
726736
<varlistentry>
727737
<term><option>--no-publications</option></term>
728738
<listitem>

‎src/bin/pg_dump/pg_backup.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -111,6 +111,7 @@ typedef struct _restoreOptions
111111
int column_inserts;
112112
int if_exists;
113113
int no_comments; /* Skip comments */
114+
int no_policies; /* Skip row security policies */
114115
int no_publications; /* Skip publication entries */
115116
int no_security_labels; /* Skip security label entries */
116117
int no_subscriptions; /* Skip subscription entries */
@@ -181,8 +182,9 @@ typedef struct _dumpOptions
181182
int column_inserts;
182183
int if_exists;
183184
int no_comments;
184-
int no_security_labels;
185+
int no_policies; /* Skip row security policies */
185186
int no_publications;
187+
int no_security_labels;
186188
int no_subscriptions;
187189
int no_toast_compression;
188190
int no_unlogged_table_data;

‎src/bin/pg_dump/pg_backup_archiver.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -188,6 +188,7 @@ dumpOptionsFromRestoreOptions(RestoreOptions *ropt)
188188
dopt->disable_dollar_quoting = ropt->disable_dollar_quoting;
189189
dopt->dump_inserts = ropt->dump_inserts;
190190
dopt->no_comments = ropt->no_comments;
191+
dopt->no_policies = ropt->no_policies;
191192
dopt->no_publications = ropt->no_publications;
192193
dopt->no_security_labels = ropt->no_security_labels;
193194
dopt->no_subscriptions = ropt->no_subscriptions;
@@ -2966,6 +2967,12 @@ _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH)
29662967
if (ropt->no_comments && strcmp(te->desc, "COMMENT") == 0)
29672968
return 0;
29682969

2970+
/* If it's a policy, maybe ignore it */
2971+
if (ropt->no_policies &&
2972+
(strcmp(te->desc, "POLICY") == 0 ||
2973+
strcmp(te->desc, "ROW SECURITY") == 0))
2974+
return 0;
2975+
29692976
/*
29702977
* If it's a publication or a table part of a publication, maybe ignore
29712978
* it.

‎src/bin/pg_dump/pg_dump.c

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -500,6 +500,7 @@ main(int argc, char **argv)
500500
{"use-set-session-authorization", no_argument, &dopt.use_setsessauth, 1},
501501
{"no-comments", no_argument, &dopt.no_comments, 1},
502502
{"no-data", no_argument, NULL, 19},
503+
{"no-policies", no_argument, &dopt.no_policies, 1},
503504
{"no-publications", no_argument, &dopt.no_publications, 1},
504505
{"no-schema", no_argument, NULL, 20},
505506
{"no-security-labels", no_argument, &dopt.no_security_labels, 1},
@@ -1152,6 +1153,7 @@ main(int argc, char **argv)
11521153
ropt->disable_dollar_quoting = dopt.disable_dollar_quoting;
11531154
ropt->dump_inserts = dopt.dump_inserts;
11541155
ropt->no_comments = dopt.no_comments;
1156+
ropt->no_policies = dopt.no_policies;
11551157
ropt->no_publications = dopt.no_publications;
11561158
ropt->no_security_labels = dopt.no_security_labels;
11571159
ropt->no_subscriptions = dopt.no_subscriptions;
@@ -1259,6 +1261,7 @@ help(const char *progname)
12591261
printf(_(" --load-via-partition-root load partitions via the root table\n"));
12601262
printf(_(" --no-comments do not dump comment commands\n"));
12611263
printf(_(" --no-data do not dump data\n"));
1264+
printf(_(" --no-policies do not dump row security policies\n"));
12621265
printf(_(" --no-publications do not dump publications\n"));
12631266
printf(_(" --no-schema do not dump schema\n"));
12641267
printf(_(" --no-security-labels do not dump security label assignments\n"));
@@ -4035,6 +4038,7 @@ dumpLOs(Archive *fout, const void *arg)
40354038
void
40364039
getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
40374040
{
4041+
DumpOptions *dopt = fout->dopt;
40384042
PQExpBuffer query;
40394043
PQExpBuffer tbloids;
40404044
PGresult *res;
@@ -4056,6 +4060,10 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
40564060
if (fout->remoteVersion < 90500)
40574061
return;
40584062

4063+
/* Skip if --no-policies was specified */
4064+
if (dopt->no_policies)
4065+
return;
4066+
40594067
query = createPQExpBuffer();
40604068
tbloids = createPQExpBuffer();
40614069

‎src/bin/pg_dump/pg_dumpall.c

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -101,6 +101,7 @@ static int no_table_access_method = 0;
101101
static int no_tablespaces = 0;
102102
static int use_setsessauth = 0;
103103
static int no_comments = 0;
104+
static int no_policies = 0;
104105
static int no_publications = 0;
105106
static int no_security_labels = 0;
106107
static int no_data = 0;
@@ -173,6 +174,7 @@ main(int argc, char *argv[])
173174
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
174175
{"no-comments", no_argument, &no_comments, 1},
175176
{"no-data", no_argument, &no_data, 1},
177+
{"no-policies", no_argument, &no_policies, 1},
176178
{"no-publications", no_argument, &no_publications, 1},
177179
{"no-role-passwords", no_argument, &no_role_passwords, 1},
178180
{"no-schema", no_argument, &no_schema, 1},
@@ -457,6 +459,8 @@ main(int argc, char *argv[])
457459
appendPQExpBufferStr(pgdumpopts, " --no-comments");
458460
if (no_data)
459461
appendPQExpBufferStr(pgdumpopts, " --no-data");
462+
if (no_policies)
463+
appendPQExpBufferStr(pgdumpopts, " --no-policies");
460464
if (no_publications)
461465
appendPQExpBufferStr(pgdumpopts, " --no-publications");
462466
if (no_security_labels)
@@ -681,6 +685,7 @@ help(void)
681685
printf(_(" --load-via-partition-root load partitions via the root table\n"));
682686
printf(_(" --no-comments do not dump comment commands\n"));
683687
printf(_(" --no-data do not dump data\n"));
688+
printf(_(" --no-policies do not dump row security policies\n"));
684689
printf(_(" --no-publications do not dump publications\n"));
685690
printf(_(" --no-role-passwords do not dump passwords for roles\n"));
686691
printf(_(" --no-schema do not dump schema\n"));

‎src/bin/pg_dump/pg_restore.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -74,6 +74,7 @@ main(int argc, char **argv)
7474
static int use_setsessauth = 0;
7575
static int no_comments = 0;
7676
static int no_data = 0;
77+
static int no_policies = 0;
7778
static int no_publications = 0;
7879
static int no_schema = 0;
7980
static int no_security_labels = 0;
@@ -129,6 +130,7 @@ main(int argc, char **argv)
129130
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
130131
{"no-comments", no_argument, &no_comments, 1},
131132
{"no-data", no_argument, &no_data, 1},
133+
{"no-policies", no_argument, &no_policies, 1},
132134
{"no-publications", no_argument, &no_publications, 1},
133135
{"no-schema", no_argument, &no_schema, 1},
134136
{"no-security-labels", no_argument, &no_security_labels, 1},
@@ -385,6 +387,7 @@ main(int argc, char **argv)
385387
opts->noTablespace = outputNoTablespaces;
386388
opts->use_setsessauth = use_setsessauth;
387389
opts->no_comments = no_comments;
390+
opts->no_policies = no_policies;
388391
opts->no_publications = no_publications;
389392
opts->no_security_labels = no_security_labels;
390393
opts->no_subscriptions = no_subscriptions;
@@ -505,6 +508,7 @@ usage(const char *progname)
505508
printf(_(" --no-data do not restore data\n"));
506509
printf(_(" --no-data-for-failed-tables do not restore data of tables that could not be\n"
507510
" created\n"));
511+
printf(_(" --no-policies do not restore row level security policies\n"));
508512
printf(_(" --no-publications do not restore publications\n"));
509513
printf(_(" --no-schema do not restore schema\n"));
510514
printf(_(" --no-security-labels do not restore security labels\n"));

‎src/bin/pg_dump/t/002_pg_dump.pl

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -579,6 +579,14 @@
579579
'postgres',
580580
],
581581
},
582+
no_policies => {
583+
dump_cmd => [
584+
'pg_dump', '--no-sync',
585+
'--file' => "$tempdir/no_policies.sql",
586+
'--no-policies',
587+
'postgres',
588+
],
589+
},
582590
no_privs => {
583591
dump_cmd => [
584592
'pg_dump', '--no-sync',
@@ -803,6 +811,7 @@
803811
no_toast_compression => 1,
804812
no_large_objects => 1,
805813
no_owner => 1,
814+
no_policies => 1,
806815
no_privs => 1,
807816
no_statistics => 1,
808817
no_table_access_method => 1,
@@ -1328,6 +1337,7 @@
13281337
unlike => {
13291338
exclude_dump_test_schema => 1,
13301339
exclude_test_table => 1,
1340+
no_policies => 1,
13311341
only_dump_measurement => 1,
13321342
},
13331343
},
@@ -2948,6 +2958,7 @@
29482958
unlike => {
29492959
exclude_dump_test_schema => 1,
29502960
exclude_test_table => 1,
2961+
no_policies => 1,
29512962
only_dump_measurement => 1,
29522963
},
29532964
},
@@ -2969,6 +2980,7 @@
29692980
unlike => {
29702981
exclude_dump_test_schema => 1,
29712982
exclude_test_table => 1,
2983+
no_policies => 1,
29722984
only_dump_measurement => 1,
29732985
},
29742986
},
@@ -2990,6 +3002,7 @@
29903002
unlike => {
29913003
exclude_dump_test_schema => 1,
29923004
exclude_test_table => 1,
3005+
no_policies => 1,
29933006
only_dump_measurement => 1,
29943007
},
29953008
},
@@ -3011,6 +3024,7 @@
30113024
unlike => {
30123025
exclude_dump_test_schema => 1,
30133026
exclude_test_table => 1,
3027+
no_policies => 1,
30143028
only_dump_measurement => 1,
30153029
},
30163030
},
@@ -3032,6 +3046,7 @@
30323046
unlike => {
30333047
exclude_dump_test_schema => 1,
30343048
exclude_test_table => 1,
3049+
no_policies => 1,
30353050
only_dump_measurement => 1,
30363051
},
30373052
},
@@ -3053,6 +3068,7 @@
30533068
unlike => {
30543069
exclude_dump_test_schema => 1,
30553070
exclude_test_table => 1,
3071+
no_policies => 1,
30563072
only_dump_measurement => 1,
30573073
},
30583074
},

0 commit comments

Comments
 (0)