hi.
in [1],
RememberAllDependentForRebuilding
/*
* A policy can depend on a column because the column is
* specified in the policy's USING or WITH CHECK qual
* expressions. It might be possible to rewrite and recheck
* the policy expression, but punt for now. It's certainly
* easy enough to remove and recreate the policy; still, FIXME
* someday.
*/
After 11 year, I am trying to allow column type changes to cope with
security policy dependencies.
CREATE TABLE s (a int, b int);
CREATE POLICY p2 ON s USING (s.b = 1);
--master branch will result error
ALTER TABLE s ALTER COLUMN b SET DATA TYPE INT8;
ERROR: cannot alter type of a column used in a policy definition
DETAIL: policy p2 on table s depends on column "b"
with the attached patch, ALTER TABLE SET DATA TYPE can cope with columns that
have associated security policy.
The above ALTER TABLE SET DATA TYPE will just work fine.
The code roughly follows how statistics are recreated after a column
data type change.
Currently table rewrite does not recheck the policy expression, for example:
RESET SESSION AUTHORIZATION;
CREATE USER regress_rls_alice NOLOGIN;
GRANT ALL ON SCHEMA public to public;
DROP TABLE IF EXISTS R1;
SET row_security = on;
begin;
set role regress_rls_alice;
CREATE TABLE r1 (a int, b int GENERATED ALWAYS AS (a * 10) STORED);
INSERT INTO r1 VALUES (1), (2), (4);
CREATE POLICY p0 ON r1 USING (true);
CREATE POLICY p1 ON r1 AS RESTRICTIVE USING (b > 10);
ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
commit;
set role regress_rls_alice;
INSERT INTO r1 VALUES (0); -- Should fail p1
ALTER TABLE r1 ALTER COLUMN b SET EXPRESSION AS (-1); --OK
so i guess ALTER TABLE SET DATA TYPE, table rewrite no checking policy
should be fine?
[1]
https://git.postgresql.org/cgit/postgresql.git/commit/?id=143b39c1855f8a22f474f20354ee5ee5d2f4d266
From be887b714a3bc788b6859954fad63137d64d1f61 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Fri, 12 Sep 2025 16:06:53 +0800
Subject: [PATCH v1 1/1] let ALTER COLUMN SET DATA TYPE cope with POLICY
dependency
CREATE TABLE s (a int, b int);
CREATE POLICY p2 ON s USING (s.b = 1);
--no-error, while master branch will result error
ALTER TABLE s ALTER COLUMN b SET DATA TYPE INT8;
discussion: https://postgr.es/m/
---
src/backend/commands/policy.c | 59 ++++++
src/backend/commands/tablecmds.c | 122 ++++++++++--
src/backend/parser/gram.y | 1 +
src/backend/utils/adt/ruleutils.c | 185 ++++++++++++++++++
src/include/commands/policy.h | 1 +
src/include/nodes/parsenodes.h | 2 +
src/include/utils/ruleutils.h | 1 +
.../test_ddl_deparse/test_ddl_deparse.c | 3 +
src/test/regress/expected/rowsecurity.out | 79 ++++++++
src/test/regress/sql/rowsecurity.sql | 41 ++++
10 files changed, 479 insertions(+), 15 deletions(-)
diff --git a/src/backend/commands/policy.c b/src/backend/commands/policy.c
index 83056960fe4..c287c08f155 100644
--- a/src/backend/commands/policy.c
+++ b/src/backend/commands/policy.c
@@ -24,8 +24,10 @@
#include "catalog/namespace.h"
#include "catalog/objectaccess.h"
#include "catalog/pg_authid.h"
+#include "catalog/pg_depend.h"
#include "catalog/pg_policy.h"
#include "catalog/pg_type.h"
+#include "commands/comment.h"
#include "commands/policy.h"
#include "miscadmin.h"
#include "nodes/pg_list.h"
@@ -755,6 +757,11 @@ CreatePolicy(CreatePolicyStmt *stmt)
relation_close(target_table, NoLock);
table_close(pg_policy_rel, RowExclusiveLock);
+ /* Add any requested comment */
+ if (stmt->polcomment != NULL)
+ CreateComments(policy_id, PolicyRelationId, 0,
+ stmt->polcomment);
+
return myself;
}
@@ -1277,3 +1284,55 @@ relation_has_policies(Relation rel)
return ret;
}
+
+/*
+ * PoliciesGetRelations -
+ * Collect all relations that this policy depends on.
+ * Since the policy's check qual or qual may reference other relations, we
+ * include those as well.
+ */
+List *
+PoliciesGetRelations(Oid policyId)
+{
+ List *result = NIL;
+ Relation depRel;
+ ScanKeyData key[2];
+ SysScanDesc depScan;
+ HeapTuple depTup;
+
+ /*
+ * We scan pg_depend to find those things that policy being depended on.
+ */
+ depRel = table_open(DependRelationId, AccessShareLock);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_depend_classid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(PolicyRelationId));
+ ScanKeyInit(&key[1],
+ Anum_pg_depend_objid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(policyId));
+
+ depScan = systable_beginscan(depRel, DependDependerIndexId, true,
+ NULL, 2, key);
+ while (HeapTupleIsValid(depTup = systable_getnext(depScan)))
+ {
+ Form_pg_depend pg_depend = (Form_pg_depend) GETSTRUCT(depTup);
+
+ /* Prepend oid of the relation with this policy. */
+ if (pg_depend->refclassid == RelationRelationId)
+ {
+ if (pg_depend->deptype == DEPENDENCY_AUTO)
+ result = lcons_oid(pg_depend->refobjid, result);
+ else
+ result = lappend_oid(result, pg_depend->refobjid);
+ }
+ }
+ systable_endscan(depScan);
+
+ relation_close(depRel, AccessShareLock);
+
+ Assert(result != NIL);
+ return result;
+}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3be2e051d32..341f8be7299 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -60,6 +60,7 @@
#include "commands/comment.h"
#include "commands/defrem.h"
#include "commands/event_trigger.h"
+#include "commands/policy.h"
#include "commands/sequence.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
@@ -208,6 +209,8 @@ typedef struct AlteredTableInfo
char *clusterOnIndex; /* index to use for CLUSTER */
List *changedStatisticsOids; /* OIDs of statistics to rebuild */
List *changedStatisticsDefs; /* string definitions of same */
+ List *changedPolicyOids; /* OIDs of policy to rebuild */
+ List *changedPolicyDefs; /* string definitions of same */
} AlteredTableInfo;
/* Struct describing one new constraint to check in Phase 3 scan */
@@ -546,6 +549,8 @@ static ObjectAddress ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
IndexStmt *stmt, bool is_rebuild, LOCKMODE lockmode);
static ObjectAddress ATExecAddStatistics(AlteredTableInfo *tab, Relation rel,
CreateStatsStmt *stmt, bool is_rebuild, LOCKMODE lockmode);
+static ObjectAddress ATExecAddPolicies(AlteredTableInfo *tab, Relation rel,
+ CreatePolicyStmt *stmt, bool is_rebuild, LOCKMODE lockmode);
static ObjectAddress ATExecAddConstraint(List **wqueue,
AlteredTableInfo *tab, Relation rel,
Constraint *newConstraint, bool recurse, bool is_readd,
@@ -651,6 +656,7 @@ static void RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableT
static void RememberConstraintForRebuilding(Oid conoid, AlteredTableInfo *tab);
static void RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab);
static void RememberStatisticsForRebuilding(Oid stxoid, AlteredTableInfo *tab);
+static void RememberPolicyForRebuilding(Oid policyId, AlteredTableInfo *tab);
static void ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab,
LOCKMODE lockmode);
static void ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId,
@@ -5449,6 +5455,10 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
address = ATExecAddStatistics(tab, rel, (CreateStatsStmt *) cmd->def,
true, lockmode);
break;
+ case AT_ReAddPolicies: /* ADD POLICIES */
+ address = ATExecAddPolicies(tab, rel, (CreatePolicyStmt *) cmd->def,
+ true, lockmode);
+ break;
case AT_AddConstraint: /* ADD CONSTRAINT */
/* Transform the command only during initial examination */
if (cur_pass == AT_PASS_ADD_CONSTR)
@@ -6716,6 +6726,8 @@ alter_table_type_to_string(AlterTableType cmdtype)
return "ALTER COLUMN ... DROP IDENTITY";
case AT_ReAddStatistics:
return NULL; /* not real grammar */
+ case AT_ReAddPolicies:
+ return NULL; /* not real grammar */
}
return NULL;
@@ -9663,6 +9675,26 @@ ATExecAddStatistics(AlteredTableInfo *tab, Relation rel,
return address;
}
+/*
+ * ALTER TABLE ADD POLICIES
+ *
+ * This is no such command in the grammar, but we use this internally to add
+ * AT_ReAddPolicies subcommands to rebuild policies after a table
+ * column type change.
+ */
+static ObjectAddress
+ATExecAddPolicies(AlteredTableInfo *tab, Relation rel,
+ CreatePolicyStmt *stmt, bool is_rebuild, LOCKMODE lockmode)
+{
+ ObjectAddress address;
+
+ Assert(IsA(stmt, CreatePolicyStmt));
+
+ address = CreatePolicy(stmt);
+
+ return address;
+}
+
/*
* ALTER TABLE ADD CONSTRAINT USING INDEX
*
@@ -15136,22 +15168,8 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
break;
case PolicyRelationId:
-
- /*
- * A policy can depend on a column because the column is
- * specified in the policy's USING or WITH CHECK qual
- * expressions. It might be possible to rewrite and recheck
- * the policy expression, but punt for now. It's certainly
- * easy enough to remove and recreate the policy; still, FIXME
- * someday.
- */
if (subtype == AT_AlterColumnType)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot alter type of a column used in a policy definition"),
- errdetail("%s depends on column \"%s\"",
- getObjectDescription(&foundObject, false),
- colName)));
+ RememberPolicyForRebuilding(foundObject.objectId, tab);
break;
case AttrDefaultRelationId:
@@ -15393,6 +15411,33 @@ RememberStatisticsForRebuilding(Oid stxoid, AlteredTableInfo *tab)
}
}
+/*
+ * Subroutine for ATExecAlterColumnType: remember that a policy object needs to
+ * be rebuilt (which we might already know).
+ */
+static void
+RememberPolicyForRebuilding(Oid policyId, AlteredTableInfo *tab)
+{
+ /*
+ * This de-duplication check is critical for two independent reasons: we
+ * mustn't try to recreate the same policy twice, and if a policy
+ * depends on more than one column whose type is to be altered, we must
+ * capture its definition string before applying any of the column type
+ * changes. ruleutils.c will get confused if we ask again later.
+ */
+ if (!list_member_oid(tab->changedPolicyOids, policyId))
+ {
+ /* OK, capture the policies's existing definition string */
+ char *defstring = pg_get_policy_def_command(policyId);
+
+ tab->changedPolicyOids = lappend_oid(tab->changedPolicyOids,
+ policyId);
+ tab->changedPolicyDefs = lappend(tab->changedPolicyDefs,
+ defstring);
+ }
+}
+
+
/*
* Cleanup after we've finished all the ALTER TYPE or SET EXPRESSION
* operations for a particular relation. We have to drop and recreate all the
@@ -15537,6 +15582,39 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
add_exact_object_address(&obj, objects);
}
+ /* add dependencies for new policies */
+ forboth(oid_item, tab->changedPolicyOids,
+ def_item, tab->changedPolicyDefs)
+ {
+ Oid oldId = lfirst_oid(oid_item);
+ List *relids;
+
+ relids = PoliciesGetRelations(oldId);
+ Assert(relids != NIL);
+
+ /*
+ * As above, make sure we have lock on the relations if it's not the
+ * same table. However, we take AccessExclusiveLock here, aligning with
+ * the lock level used in CreatePolicy and RemovePolicyById.
+ *
+ * CAUTION: this should be done after all cases that grab
+ * AccessExclusiveLock, else we risk causing deadlock due to needing
+ * to promote our table lock.
+ */
+ foreach_oid(relid, relids)
+ {
+ if (relid != tab->relid)
+ LockRelationOid(relid, AccessExclusiveLock);
+ }
+
+ ATPostAlterTypeParse(oldId, tab->relid, InvalidOid,
+ (char *) lfirst(def_item),
+ wqueue, lockmode, tab->rewrite);
+
+ ObjectAddressSet(obj, PolicyRelationId, oldId);
+ add_exact_object_address(&obj, objects);
+ }
+
/*
* Queue up command to restore replica identity index marking
*/
@@ -15788,6 +15866,20 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
tab->subcmds[AT_PASS_MISC] =
lappend(tab->subcmds[AT_PASS_MISC], newcmd);
}
+ else if (IsA(stm, CreatePolicyStmt))
+ {
+ CreatePolicyStmt *stmt = (CreatePolicyStmt *) stm;
+ AlterTableCmd *newcmd;
+
+ /* keep the policies object's comment */
+ stmt->polcomment = GetComment(oldId, PolicyRelationId, 0);
+
+ newcmd = makeNode(AlterTableCmd);
+ newcmd->subtype = AT_ReAddPolicies;
+ newcmd->def = (Node *) stmt;
+ tab->subcmds[AT_PASS_MISC] =
+ lappend(tab->subcmds[AT_PASS_MISC], newcmd);
+ }
else
elog(ERROR, "unexpected statement type: %d",
(int) nodeTag(stm));
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9fd48acb1f8..e795bf171bb 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -5942,6 +5942,7 @@ CreatePolicyStmt:
CreatePolicyStmt *n = makeNode(CreatePolicyStmt);
n->policy_name = $3;
+ n->polcomment = NULL;
n->table = $5;
n->permissive = $6;
n->cmd_name = $7;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 3d6e6bdbfd2..d5685be6770 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -33,6 +33,7 @@
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_partitioned_table.h"
+#include "catalog/pg_policy.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_statistic_ext.h"
#include "catalog/pg_trigger.h"
@@ -57,6 +58,7 @@
#include "rewrite/rewriteHandler.h"
#include "rewrite/rewriteManip.h"
#include "rewrite/rewriteSupport.h"
+#include "utils/acl.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
@@ -367,6 +369,7 @@ static char *pg_get_partkeydef_worker(Oid relid, int prettyFlags,
bool attrsOnly, bool missing_ok);
static char *pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
int prettyFlags, bool missing_ok);
+static char *pg_get_policydef_worker(Oid policyId, int prettyFlags, bool missing_ok);
static text *pg_get_expr_worker(text *expr, Oid relid, int prettyFlags);
static int print_function_arguments(StringInfo buf, HeapTuple proctup,
bool print_table_args, bool print_defaults);
@@ -2611,6 +2614,188 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
return buf.data;
}
+/*
+ * Internal version that returns a full CREATE POLICY command
+ */
+char *
+pg_get_policy_def_command(Oid policyId)
+{
+ return pg_get_policydef_worker(policyId, 0, false);
+}
+
+
+/*
+ * get_policy_applied_command -
+ * Helper function to convert char representation to their full command strings.
+ * Returned valid values are 'all', 'select', 'insert', 'update' and 'delete'.
+ */
+static char *
+get_policy_applied_command(char polcmd)
+{
+ if (polcmd == '*')
+ return pstrdup("all");
+ else if (polcmd == ACL_SELECT_CHR)
+ return pstrdup("select");
+ else if (polcmd == ACL_INSERT_CHR)
+ return pstrdup("insert");
+ else if (polcmd == ACL_UPDATE_CHR)
+ return pstrdup("update");
+ else if (polcmd == ACL_DELETE_CHR)
+ return pstrdup("delete");
+ else
+ {
+ elog(ERROR, "unrecognized policy command");
+ return NULL;
+ }
+}
+
+static char *
+pg_get_policydef_worker(Oid policyId, int prettyFlags, bool missing_ok)
+{
+ HeapTuple tup;
+ Form_pg_policy policy_form;
+ StringInfoData buf;
+ SysScanDesc scandesc;
+ ScanKeyData scankey[1];
+ Snapshot snapshot = RegisterSnapshot(GetTransactionSnapshot());
+ Relation relation = table_open(PolicyRelationId, AccessShareLock);
+ ArrayType *policy_roles;
+ Datum roles_datum;
+ Datum datum;
+ bool isnull;
+ Oid *roles;
+ int num_roles;
+ List *context = NIL;
+ char *str_value;
+ char *exprsrc;
+ char *rolString;
+ char *policy_command;
+ Node *expr;
+
+ ScanKeyInit(&scankey[0],
+ Anum_pg_policy_oid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(policyId));
+
+ scandesc = systable_beginscan(relation,
+ PolicyOidIndexId,
+ true,
+ snapshot,
+ 1,
+ scankey);
+ tup = systable_getnext(scandesc);
+
+ UnregisterSnapshot(snapshot);
+
+ if (!HeapTupleIsValid(tup))
+ {
+ if (missing_ok)
+ {
+ systable_endscan(scandesc);
+ table_close(relation, AccessShareLock);
+ return NULL;
+ }
+ elog(ERROR, "could not find tuple for policy %u", policyId);
+ }
+
+ policy_form = (Form_pg_policy) GETSTRUCT(tup);
+ context = deparse_context_for(get_relation_name(policy_form->polrelid),
+ policy_form->polrelid);
+
+ initStringInfo(&buf);
+ if (OidIsValid(policy_form->oid))
+ appendStringInfo(&buf, "CREATE POLICY %s ON %s ",
+ quote_identifier(NameStr(policy_form->polname)),
+ generate_qualified_relation_name(policy_form->polrelid));
+ else
+ elog(ERROR, "invalid policy: %u", policyId);
+
+ /* Get policy type, permissive or restrictive */
+ if (policy_form->polpermissive)
+ appendStringInfoString(&buf, "AS PERMISSIVE ");
+ else
+ appendStringInfoString(&buf, "AS RESTRICTIVE ");
+
+ appendStringInfoString(&buf, "FOR ");
+
+ /* Get policy applied command type */
+ policy_command = get_policy_applied_command(policy_form->polcmd);
+ if (strcmp(policy_command, "all") == 0)
+ appendStringInfoString(&buf, "ALL ");
+ else if (strcmp(policy_command, "select") == 0)
+ appendStringInfoString(&buf, "SELECT ");
+ else if (strcmp(policy_command, "insert") == 0)
+ appendStringInfoString(&buf, "INSERT ");
+ else if (strcmp(policy_command, "update") == 0)
+ appendStringInfoString(&buf, "UPDATE ");
+ else if (strcmp(policy_command, "delete") == 0)
+ appendStringInfoString(&buf, "DELETE ");
+ else
+ elog(ERROR, "invalid command type %c", policy_form->polcmd);
+
+ appendStringInfoString(&buf, "TO ");
+
+ /* Get the current set of roles */
+ datum = heap_getattr(tup,
+ Anum_pg_policy_polroles,
+ RelationGetDescr(relation),
+ &isnull);
+ Assert(!isnull);
+
+ policy_roles = DatumGetArrayTypePCopy(datum);
+ roles = (Oid *) ARR_DATA_PTR(policy_roles);
+ num_roles = ARR_DIMS(policy_roles)[0];
+ for (int i = 0; i < num_roles; i++)
+ {
+ if (i > 0)
+ appendStringInfoString(&buf, ", ");
+
+ if (OidIsValid(roles[i]))
+ {
+ datum = ObjectIdGetDatum(roles[i]);
+ roles_datum = DirectFunctionCall1(pg_get_userbyid, datum);
+ rolString = DatumGetCString(DirectFunctionCall1(nameout, roles_datum));
+ appendStringInfo(&buf, "%s", rolString);
+ }
+ else
+ appendStringInfoString(&buf, "PUBLIC");
+ }
+
+ /* Get policy qual */
+ datum = heap_getattr(tup, Anum_pg_policy_polqual,
+ RelationGetDescr(relation), &isnull);
+ if (!isnull)
+ {
+ str_value = TextDatumGetCString(datum);
+ expr = (Node *) stringToNode(str_value);
+ pfree(str_value);
+
+ exprsrc = deparse_expression_pretty(expr, context, false, false,
+ prettyFlags, 0);
+ appendStringInfo(&buf, " USING (%s) ", exprsrc);
+ }
+
+ /* Get WITH CHECK qual */
+ datum = heap_getattr(tup, Anum_pg_policy_polwithcheck,
+ RelationGetDescr(relation), &isnull);
+ if (!isnull)
+ {
+ str_value = TextDatumGetCString(datum);
+ expr = (Node *) stringToNode(str_value);
+ pfree(str_value);
+
+ exprsrc = deparse_expression_pretty(expr, context, false, false,
+ prettyFlags, 0);
+ appendStringInfo(&buf, "WITH CHECK (%s)", exprsrc);
+ }
+
+ /* Cleanup */
+ systable_endscan(scandesc);
+ table_close(relation, AccessShareLock);
+
+ return buf.data;
+}
+
/*
* Convert an int16[] Datum into a comma-separated list of column names
diff --git a/src/include/commands/policy.h b/src/include/commands/policy.h
index f06aa1df439..a39cda611cf 100644
--- a/src/include/commands/policy.h
+++ b/src/include/commands/policy.h
@@ -34,5 +34,6 @@ extern Oid get_relation_policy_oid(Oid relid, const char *policy_name,
extern ObjectAddress rename_policy(RenameStmt *stmt);
extern bool relation_has_policies(Relation rel);
+extern List *PoliciesGetRelations(Oid policyId);
#endif /* POLICY_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 86a236bd58b..921c0a61520 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2477,6 +2477,7 @@ typedef enum AlterTableType
AT_SetIdentity, /* SET identity column options */
AT_DropIdentity, /* DROP IDENTITY */
AT_ReAddStatistics, /* internal to commands/tablecmds.c */
+ AT_ReAddPolicies, /* internal to commands/tablecmds.c */
} AlterTableType;
typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */
@@ -3064,6 +3065,7 @@ typedef struct CreatePolicyStmt
char *policy_name; /* Policy's name */
RangeVar *table; /* the table name the policy applies to */
char *cmd_name; /* the command name the policy applies to */
+ char *polcomment; /* comment to apply to policies, or NULL */
bool permissive; /* restrictive or permissive policy */
List *roles; /* the roles associated with the policy */
Node *qual; /* the policy's condition */
diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 5f2ea2e4d0e..a82f83c6c72 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -34,6 +34,7 @@ extern char *pg_get_partkeydef_columns(Oid relid, bool pretty);
extern char *pg_get_partconstrdef_string(Oid partitionId, char *aliasname);
extern char *pg_get_constraintdef_command(Oid constraintId);
+extern char *pg_get_policy_def_command(Oid policyId);
extern char *deparse_expression(Node *expr, List *dpcontext,
bool forceprefix, bool showimplicit);
extern List *deparse_context_for(const char *aliasname, Oid relid);
diff --git a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
index 193669f2bc1..f438936b719 100644
--- a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
+++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
@@ -308,6 +308,9 @@ get_altertable_subcmdinfo(PG_FUNCTION_ARGS)
case AT_ReAddStatistics:
strtype = "(re) ADD STATS";
break;
+ case AT_ReAddPolicies:
+ strtype = "(re) ADD POLICIES";
+ break;
}
if (subcmd->recurse)
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 8c879509313..ac1e8a234ad 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -26,6 +26,45 @@ GRANT regress_rls_group2 TO regress_rls_carol;
CREATE SCHEMA regress_rls_schema;
GRANT ALL ON SCHEMA regress_rls_schema to public;
SET search_path = regress_rls_schema;
+--check alter column set data type also drop whole-row references policy
+CREATE TABLE rls_tbl (a int, b int, c int);
+CREATE POLICY p0 ON rls_tbl
+ FOR UPDATE
+ TO regress_rls_alice, regress_rls_bob, regress_rls_carol
+ USING (a < 20) WITH CHECK (c <> 0 and a is not null);
+CREATE POLICY p1 ON rls_tbl AS RESTRICTIVE
+ FOR ALL
+ TO regress_rls_alice, regress_rls_carol, regress_rls_bob
+ USING (a < 30) WITH CHECK (c > 0 and a is not null);
+CREATE POLICY p2 ON rls_tbl AS RESTRICTIVE
+ FOR UPDATE
+ TO PUBLIC
+ USING (a < 40) WITH CHECK (c > 0 and a is not null);
+COMMENT ON POLICY p0 ON rls_tbl IS 'policy p1';
+COMMENT ON POLICY p1 ON rls_tbl IS 'policy p2';
+COMMENT ON POLICY p2 ON rls_tbl IS 'policy p3';
+ALTER TABLE rls_tbl ALTER COLUMN a SET DATA TYPE INT8, ALTER COLUMN c SET DATA TYPE INT8;
+SELECT polname, description
+FROM pg_description, pg_policy c
+WHERE classoid = 'pg_policy'::regclass
+AND objoid = c.oid AND c.polrelid = 'rls_tbl'::regclass
+ORDER BY polname;
+ polname | description
+---------+-------------
+ p0 | policy p1
+ p1 | policy p2
+ p2 | policy p3
+(3 rows)
+
+SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename = 'rls_tbl' ORDER BY policyname;
+ schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check
+--------------------+-----------+------------+-------------+-------------------------------------------------------+--------+----------+--------------------------------
+ regress_rls_schema | rls_tbl | p0 | PERMISSIVE | {regress_rls_alice,regress_rls_bob,regress_rls_carol} | UPDATE | (a < 20) | ((c <> 0) AND (a IS NOT NULL))
+ regress_rls_schema | rls_tbl | p1 | RESTRICTIVE | {regress_rls_alice,regress_rls_bob,regress_rls_carol} | ALL | (a < 30) | ((c > 0) AND (a IS NOT NULL))
+ regress_rls_schema | rls_tbl | p2 | RESTRICTIVE | {public} | UPDATE | (a < 40) | ((c > 0) AND (a IS NOT NULL))
+(3 rows)
+
+DROP TABLE rls_tbl;
-- setup of malicious function
CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool
COST 0.0000001 LANGUAGE plpgsql
@@ -439,6 +478,46 @@ EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dt
Index Cond: (cid = document.cid)
(5 rows)
+SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename = 'document' ORDER BY policyname;
+ schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check
+--------------------+-----------+------------+-------------+--------------------+-----+------------------------------+------------
+ regress_rls_schema | document | p1 | PERMISSIVE | {public} | ALL | (dauthor = CURRENT_USER) |
+ regress_rls_schema | document | p1r | RESTRICTIVE | {regress_rls_dave} | ALL | (cid <> 44) |
+ regress_rls_schema | document | p2r | RESTRICTIVE | {regress_rls_dave} | ALL | ((cid <> 44) AND (cid < 50)) |
+(3 rows)
+
+RESET SESSION AUTHORIZATION;
+ALTER TABLE document ALTER COLUMN cid SET DATA TYPE INT8;
+SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename = 'document' ORDER BY policyname;
+ schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check
+--------------------+-----------+------------+-------------+--------------------+-----+------------------------------+------------
+ regress_rls_schema | document | p1 | PERMISSIVE | {public} | ALL | (dauthor = CURRENT_USER) |
+ regress_rls_schema | document | p1r | RESTRICTIVE | {regress_rls_dave} | ALL | (cid <> 44) |
+ regress_rls_schema | document | p2r | RESTRICTIVE | {regress_rls_dave} | ALL | ((cid <> 44) AND (cid < 50)) |
+(3 rows)
+
+-- viewpoint from rls_regres_carol again
+SET SESSION AUTHORIZATION regress_rls_carol;
+EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
+ QUERY PLAN
+---------------------------------------------------------
+ Seq Scan on document
+ Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
+ QUERY PLAN
+---------------------------------------------------------------
+ Nested Loop
+ -> Seq Scan on document
+ Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
+ -> Index Scan using category_pkey on category
+ Index Cond: (cid = document.cid)
+(5 rows)
+
+--change data type back
+RESET SESSION AUTHORIZATION;
+ALTER TABLE document ALTER COLUMN cid SET DATA TYPE INT4;
-- interaction of FK/PK constraints
SET SESSION AUTHORIZATION regress_rls_alice;
CREATE POLICY p2 ON category
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 21ac0ca51ee..4d930dcae2e 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -35,6 +35,35 @@ CREATE SCHEMA regress_rls_schema;
GRANT ALL ON SCHEMA regress_rls_schema to public;
SET search_path = regress_rls_schema;
+--check alter column set data type also drop whole-row references policy
+CREATE TABLE rls_tbl (a int, b int, c int);
+CREATE POLICY p0 ON rls_tbl
+ FOR UPDATE
+ TO regress_rls_alice, regress_rls_bob, regress_rls_carol
+ USING (a < 20) WITH CHECK (c <> 0 and a is not null);
+CREATE POLICY p1 ON rls_tbl AS RESTRICTIVE
+ FOR ALL
+ TO regress_rls_alice, regress_rls_carol, regress_rls_bob
+ USING (a < 30) WITH CHECK (c > 0 and a is not null);
+CREATE POLICY p2 ON rls_tbl AS RESTRICTIVE
+ FOR UPDATE
+ TO PUBLIC
+ USING (a < 40) WITH CHECK (c > 0 and a is not null);
+COMMENT ON POLICY p0 ON rls_tbl IS 'policy p1';
+COMMENT ON POLICY p1 ON rls_tbl IS 'policy p2';
+COMMENT ON POLICY p2 ON rls_tbl IS 'policy p3';
+
+ALTER TABLE rls_tbl ALTER COLUMN a SET DATA TYPE INT8, ALTER COLUMN c SET DATA TYPE INT8;
+
+SELECT polname, description
+FROM pg_description, pg_policy c
+WHERE classoid = 'pg_policy'::regclass
+AND objoid = c.oid AND c.polrelid = 'rls_tbl'::regclass
+ORDER BY polname;
+
+SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename = 'rls_tbl' ORDER BY policyname;
+DROP TABLE rls_tbl;
+
-- setup of malicious function
CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool
COST 0.0000001 LANGUAGE plpgsql
@@ -167,6 +196,18 @@ SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did;
EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
+SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename = 'document' ORDER BY policyname;
+RESET SESSION AUTHORIZATION;
+ALTER TABLE document ALTER COLUMN cid SET DATA TYPE INT8;
+SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename = 'document' ORDER BY policyname;
+-- viewpoint from rls_regres_carol again
+SET SESSION AUTHORIZATION regress_rls_carol;
+EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
+EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
+--change data type back
+RESET SESSION AUTHORIZATION;
+ALTER TABLE document ALTER COLUMN cid SET DATA TYPE INT4;
+
-- interaction of FK/PK constraints
SET SESSION AUTHORIZATION regress_rls_alice;
CREATE POLICY p2 ON category
--
2.34.1
