hi.
demo:
CREATE TABLE coll_t (c) AS VALUES ('bar'::text);
CREATE POLICY coll_p ON coll_t USING (c < ('foo'::text COLLATE "C"));
+-- coll_t1 row security is not enabled, but we still copy it's policies
+CREATE TABLE coll_t1(LIKE coll_t INCLUDING POLICIES);
+\d coll_t1
+ Table "regress_rls_schema.coll_t1"
+ Column | Type | Collation | Nullable | Default
+--------+------+-----------+----------+---------
+ c | text | | |
+Policies (row security disabled):
+ POLICY "coll_p"
+ USING ((c < ('foo'::text COLLATE "C")))
by default, new table row security will be disabled, policy object comments are
copied if INCLUDING COMMENTS is specified.
ALTER TABLE SET DATA TYPE, changing a column type typically pulls the object and
reconstructs the corresponding CREATE or ALTER command string.
however CREATE TABLE LIKE retrieves the object’s catalog data and adjusts Vars
to build a Create.*Stmt node.
Here, we generate a CreatePolicyStmt from the source relation’s pg_policy
metadata and then pass it to CreatePolicy.
CreatePolicy normally performs parse analysis on CreatePolicyStmt->qual and
CreatePolicyStmt->with_check. However, since the pg_policy entries from the
source relation already have their qual and check_qual parse analyzed, we cannot
re-analyze them. Similar to transformStatsStmt, we therefore need a
transformPolicyStmt.
v1-0001: refactor CreatePolicy, add function transformPolicyStmt
briefly explained in [1].
v1-0002: CREATE TABLE LIKE INCLUDING-POLICIES
[1]
https://postgr.es/m/CACJufxGPcBzdL9T6Qh=OFecN8zqxuU0QXfYF8F3WYV=uzwy...@mail.gmail.com
From d94917c777c3fd454a438b31806e35ffe74ac5f1 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 15 Sep 2025 11:58:56 +0800
Subject: [PATCH v1 2/2] CREATE TABLE LIKE(INCLUDING POLICIES)
We already acquire an AccessShareLock in transformTableLikeClause for the source table.
CREATE/ALTER/DROP POLICY requires an AccessExclusiveLock on the table, there's
no chance that a security policy will be modified while executing CREATE TABLE
LIKE.
discussion: https://postgr.es/m/
---
doc/src/sgml/ref/create_table.sgml | 18 +-
src/backend/commands/policy.c | 24 ++-
src/backend/parser/gram.y | 7 +-
src/backend/parser/parse_utilcmd.c | 194 +++++++++++++++++++++-
src/include/nodes/parsenodes.h | 9 +
src/include/parser/kwlist.h | 1 +
src/test/regress/expected/rowsecurity.out | 105 +++++++++++-
src/test/regress/sql/rowsecurity.sql | 43 +++++
8 files changed, 391 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index dc000e913c1..5f402231dfa 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -88,7 +88,7 @@ class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable cl
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
+{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | POLICIES | STATISTICS | STORAGE | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -672,9 +672,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<term><literal>INCLUDING COMMENTS</literal></term>
<listitem>
<para>
- Comments for the copied columns, constraints, and indexes will be
+ Comments for the copied columns, constraints, policies, and indexes will be
copied. The default behavior is to exclude comments, resulting in
- the copied columns and constraints in the new table having no
+ the copied columns, policies, and constraints in the new table having no
comments.
</para>
</listitem>
@@ -753,6 +753,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry id="sql-createtable-parms-like-opt-policies">
+ <term><literal>INCLUDING POLICIES</literal></term>
+ <listitem>
+ <para>
+ Any row-level security policies are copied to the new table.
+ Note that row-level security is not enabled to the new table,
+ using <command>ALTER TABLE ... ENABLE ROW LEVEL SECURITY</command>
+ in order for created policies to be applied to the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createtable-parms-like-opt-statistics">
<term><literal>INCLUDING STATISTICS</literal></term>
<listitem>
diff --git a/src/backend/commands/policy.c b/src/backend/commands/policy.c
index 799e1e3968a..08a80de1a0f 100644
--- a/src/backend/commands/policy.c
+++ b/src/backend/commands/policy.c
@@ -26,6 +26,7 @@
#include "catalog/pg_authid.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"
@@ -612,8 +613,22 @@ CreatePolicy(CreatePolicyStmt *stmt, const char *queryString)
errmsg("only WITH CHECK expression allowed for INSERT")));
/* Collect role ids */
- role_oids = policy_role_list_to_array(stmt->roles, &nitems);
- role_ids = construct_array_builtin(role_oids, nitems, OIDOID);
+ if (stmt->roles != NIL)
+ {
+ role_oids = policy_role_list_to_array(stmt->roles, &nitems);
+ role_ids = construct_array_builtin(role_oids, nitems, OIDOID);
+ }
+ else
+ {
+ Assert(stmt->rolesId != NIL);
+ nitems = list_length(stmt->rolesId);
+
+ role_oids = (Datum *) palloc(nitems * sizeof(Datum));
+ foreach_oid(roleoid, stmt->rolesId)
+ role_oids[foreach_current_index(roleoid)] = ObjectIdGetDatum(roleoid);
+
+ role_ids = construct_array_builtin(role_oids, nitems, OIDOID);
+ }
/* zero-clear */
memset(values, 0, sizeof(values));
@@ -738,6 +753,11 @@ CreatePolicy(CreatePolicyStmt *stmt, const char *queryString)
relation_close(target_table, NoLock);
table_close(pg_policy_rel, RowExclusiveLock);
+ /* Add any requested comment */
+ if (stmt->policycomment != NULL)
+ CreateComments(policy_id, PolicyRelationId, 0,
+ stmt->policycomment);
+
return myself;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 8016c58b49c..210f1f0ee8e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -756,7 +756,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD PATH
- PERIOD PLACING PLAN PLANS POLICY
+ PERIOD PLACING PLAN PLANS POLICIES POLICY
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
@@ -4214,6 +4214,7 @@ TableLikeOption:
| IDENTITY_P { $$ = CREATE_TABLE_LIKE_IDENTITY; }
| GENERATED { $$ = CREATE_TABLE_LIKE_GENERATED; }
| INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; }
+ | POLICIES { $$ = CREATE_TABLE_LIKE_POLICIES; }
| STATISTICS { $$ = CREATE_TABLE_LIKE_STATISTICS; }
| STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; }
| ALL { $$ = CREATE_TABLE_LIKE_ALL; }
@@ -5949,6 +5950,8 @@ CreatePolicyStmt:
n->qual = $9;
n->with_check = $10;
n->transformed = false;
+ n->policycomment = NULL;
+ n->rolesId = NIL;
$$ = (Node *) n;
}
;
@@ -17937,6 +17940,7 @@ unreserved_keyword:
| PERIOD
| PLAN
| PLANS
+ | POLICIES
| POLICY
| PRECEDING
| PREPARE
@@ -18565,6 +18569,7 @@ bare_label_keyword:
| PLACING
| PLAN
| PLANS
+ | POLICIES
| POLICY
| POSITION
| PRECEDING
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 394a037e817..3f552d6be85 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -37,6 +37,7 @@
#include "catalog/pg_constraint.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
+#include "catalog/pg_policy.h"
#include "catalog/pg_statistic_ext.h"
#include "catalog/pg_type.h"
#include "commands/comment.h"
@@ -61,6 +62,7 @@
#include "rewrite/rewriteManip.h"
#include "utils/acl.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/partcache.h"
#include "utils/rel.h"
@@ -120,6 +122,11 @@ static CreateStatsStmt *generateClonedExtStatsStmt(RangeVar *heapRel,
Oid heapRelid,
Oid source_statsid,
const AttrMap *attmap);
+static CreatePolicyStmt *generateClonedPolicyStmt(RangeVar *heapRel,
+ Relation parent_rel,
+ Relation pg_policy,
+ HeapTuple poltup,
+ const AttrMap *attmap);
static List *get_collation(Oid collation, Oid actual_datatype);
static List *get_opclass(Oid opclass, Oid actual_datatype);
static void transformIndexConstraints(CreateStmtContext *cxt);
@@ -1304,8 +1311,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
}
/*
- * We cannot yet deal with defaults, CHECK constraints, indexes, or
- * statistics, since we don't yet know what column numbers the copied
+ * We cannot yet deal with defaults, CHECK constraints, indexes, statistics
+ * or policies, since we don't yet know what column numbers the copied
* columns will have in the finished table. If any of those options are
* specified, add the LIKE clause to cxt->likeclauses so that
* expandTableLikeClause will be called after we do know that.
@@ -1318,7 +1325,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
CREATE_TABLE_LIKE_GENERATED |
CREATE_TABLE_LIKE_CONSTRAINTS |
CREATE_TABLE_LIKE_INDEXES |
- CREATE_TABLE_LIKE_STATISTICS))
+ CREATE_TABLE_LIKE_STATISTICS |
+ CREATE_TABLE_LIKE_POLICIES))
{
table_like_clause->relationOid = RelationGetRelid(relation);
cxt->likeclauses = lappend(cxt->likeclauses, table_like_clause);
@@ -1332,6 +1340,26 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
table_close(relation, NoLock);
}
+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;
+ }
+}
+
/*
* expandTableLikeClause
*
@@ -1622,6 +1650,56 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
list_free(parent_extstats);
}
+ /*
+ * Process table row level security policies if required.
+ */
+ if (table_like_clause->options & CREATE_TABLE_LIKE_POLICIES)
+ {
+ Relation catalog;
+ ScanKeyData skey;
+ SysScanDesc sscan;
+ HeapTuple tuple;
+
+ /*
+ * Scan pg_policy for any RLS policies defined on source relation. The
+ * order of visiting the policies does not matter, since we are copying
+ * all of them to the new relation.
+ */
+ catalog = table_open(PolicyRelationId, AccessShareLock);
+ ScanKeyInit(&skey,
+ Anum_pg_policy_polrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(relation)));
+ sscan = systable_beginscan(catalog, PolicyPolrelidPolnameIndexId, true,
+ NULL, 1, &skey);
+
+ while (HeapTupleIsValid(tuple = systable_getnext(sscan)))
+ {
+ CreatePolicyStmt *polstmt;
+
+ polstmt = generateClonedPolicyStmt(heapRel, relation, catalog, tuple, attmap);
+
+ /* Copy comment on policies object, if requested */
+ if (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS)
+ {
+ Form_pg_policy policy_form;
+ policy_form = (Form_pg_policy) GETSTRUCT(tuple);
+
+ comment = GetComment(policy_form->oid, PolicyRelationId, 0);
+
+ /*
+ * We make use of CreatePolicyStmt's policycomment option, so as
+ * not to need to know now what name the policies will have.
+ */
+ polstmt->policycomment = comment;
+ }
+ result = lappend(result, polstmt);
+ }
+
+ systable_endscan(sscan);
+ table_close(catalog, AccessShareLock);
+ }
+
/* Done with child rel */
table_close(childrel, NoLock);
@@ -2163,6 +2241,116 @@ generateClonedExtStatsStmt(RangeVar *heapRel, Oid heapRelid,
return stats;
}
+/*
+ * Generate a CreatePolicyStmt node using information from an already existing
+ * pg_policy tuple "poltup", which is owned by parent_rel.
+ *
+ * Attribute numbers in expression Vars are adjusted according to attmap.
+ */
+static CreatePolicyStmt *
+generateClonedPolicyStmt(RangeVar *heapRel, Relation parent_rel, Relation pg_policy,
+ HeapTuple poltup, const AttrMap *attmap)
+{
+ Datum datum;
+ bool isnull;
+ char *str_value;
+ Oid *rawarr;
+ ArrayType *arr;
+ int numkeys;
+ bool found_whole_row;
+ CreatePolicyStmt *polstmt;
+ Form_pg_policy policy_form;
+
+ policy_form = (Form_pg_policy) GETSTRUCT(poltup);
+
+ polstmt = makeNode(CreatePolicyStmt);
+ polstmt->policy_name = pstrdup(NameStr(policy_form->polname));
+ polstmt->table = copyObject(heapRel);
+ polstmt->cmd_name = get_policy_applied_command(policy_form->polcmd);
+ polstmt->permissive = policy_form->polpermissive;
+ polstmt->roles = NIL;
+ polstmt->rolesId = NIL;
+
+ /* Get policy roles */
+ datum = heap_getattr(poltup, Anum_pg_policy_polroles,
+ RelationGetDescr(pg_policy), &isnull);
+ /* shouldn't be null, but let's check for luck */
+ if (isnull)
+ elog(ERROR, "unexpected null value in pg_policy.polroles");
+
+ arr = DatumGetArrayTypeP(datum);
+ if (ARR_NDIM(arr) != 1 ||
+ ARR_HASNULL(arr) ||
+ ARR_ELEMTYPE(arr) != OIDOID)
+ elog(ERROR, "policy roles is not a 1-D Oid array");
+ rawarr = (Oid *) ARR_DATA_PTR(arr);
+ numkeys = ARR_DIMS(arr)[0];
+
+ /* stash a List of the role Oids in our CreatePolicyStmt node */
+ for (int i = 0; i < numkeys; i++)
+ polstmt->rolesId = lappend_oid(polstmt->rolesId, rawarr[i]);
+
+ /* Get policy qual */
+ datum = heap_getattr(poltup, Anum_pg_policy_polqual,
+ RelationGetDescr(pg_policy), &isnull);
+ if (!isnull)
+ {
+ str_value = TextDatumGetCString(datum);
+ polstmt->qual = stringToNode(str_value);
+
+ /* Adjust Vars to match new table's column numbering */
+ polstmt->qual = map_variable_attnos(polstmt->qual,
+ 1, 0,
+ attmap,
+ InvalidOid,
+ &found_whole_row);
+ /*
+ * Prevent this for the same reason as for constraints above.
+ */
+ if (found_whole_row)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert whole-row table reference"),
+ errdetail("Security policy \"%s\" contains a whole-row reference to table \"%s\".",
+ NameStr(policy_form->polname),
+ RelationGetRelationName(parent_rel)));
+ pfree(str_value);
+ }
+
+ /* Get WITH CHECK qual */
+ datum = heap_getattr(poltup, Anum_pg_policy_polwithcheck,
+ RelationGetDescr(pg_policy), &isnull);
+ if (!isnull)
+ {
+ str_value = TextDatumGetCString(datum);
+ polstmt->with_check = stringToNode(str_value);
+
+ /* Adjust Vars to match new table's column numbering */
+ polstmt->with_check = map_variable_attnos(polstmt->with_check,
+ 1, 0,
+ attmap,
+ InvalidOid,
+ &found_whole_row);
+ if (found_whole_row)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert whole-row table reference"),
+ errdetail("Security policy \"%s\" contains a whole-row reference to table \"%s\".",
+ NameStr(policy_form->polname),
+ RelationGetRelationName(parent_rel)));
+ pfree(str_value);
+ }
+
+ /*
+ * The policy qual and check qual from the source table are already
+ * transformed. We’ve copied them and adjusted the Vars, so no need to run
+ * parse analysis again.
+ */
+ polstmt->transformed = true;
+
+ return polstmt;
+}
+
/*
* get_collation - fetch qualified name of a collation
*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index a8cc660d5e6..7b9124f11bf 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -793,6 +793,7 @@ typedef enum TableLikeOption
CREATE_TABLE_LIKE_INDEXES = 1 << 6,
CREATE_TABLE_LIKE_STATISTICS = 1 << 7,
CREATE_TABLE_LIKE_STORAGE = 1 << 8,
+ CREATE_TABLE_LIKE_POLICIES = 1 << 9,
CREATE_TABLE_LIKE_ALL = PG_INT32_MAX
} TableLikeOption;
@@ -3069,6 +3070,14 @@ typedef struct CreatePolicyStmt
Node *qual; /* the policy's condition */
Node *with_check; /* the policy's WITH CHECK condition. */
bool transformed; /* true when transformPolicyStmt is finished */
+ char *policycomment; /* comment to apply to policies, or NULL */
+
+ /*
+ * List of roles oids associated with the policy. either this is NIL or
+ * "roles" is NIL.
+ * Currently used only in CREATE TABLE LIKE INCLUDING POLICIES.
+ */
+ List *rolesId;
} CreatePolicyStmt;
/*----------------------
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index a4af3f717a1..3db1671d986 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -344,6 +344,7 @@ PG_KEYWORD("period", PERIOD, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("plan", PLAN, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("policies", POLICIES, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("position", POSITION, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("preceding", PRECEDING, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 1dc8e5c8f42..02b3d6947d0 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -147,6 +147,85 @@ SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename
regress_rls_schema | document | p2r | RESTRICTIVE | {regress_rls_dave} | ALL | ((cid <> 44) AND (cid < 50)) |
(3 rows)
+--whole-row on qual, CREATE TABLE LIKE should fail.
+CREATE POLICY p2 ON document AS PERMISSIVE USING (document IS NOT NULL);
+CREATE TABLE document0(LIKE document INCLUDING ALL); --error
+ERROR: cannot convert whole-row table reference
+DETAIL: Security policy "p2" contains a whole-row reference to table "document".
+DROP POLICY p2 ON document;
+--whole-row on check qual, CREATE TABLE LIKE should fail.
+CREATE POLICY p3 ON document FOR INSERT WITH CHECK (document IS NOT NULL);
+CREATE TABLE document0(LIKE document INCLUDING ALL); --error
+ERROR: cannot convert whole-row table reference
+DETAIL: Security policy "p3" contains a whole-row reference to table "document".
+DROP POLICY p3 ON document;
+--a contrived complicated policy for testing expression node deparse
+CREATE POLICY p4 ON document AS PERMISSIVE USING (document.cid IS NOT NULL AND
+ (WITH cte AS (SELECT uaccount IS NOT NULL FROM uaccount)
+ SELECT * FROM cte WHERE EXISTS
+ (SELECT category FROM category WHERE EXISTS (SELECT uaccount FROM uaccount WHERE uaccount IS NULL))))
+ WITH CHECK (cid = (SELECT cid FROM document));
+COMMENT ON POLICY p1 ON document IS 'security policy comments';
+CREATE TABLE document1(LIKE document INCLUDING ALL EXCLUDING POLICIES);
+CREATE TABLE document2(prefix text, LIKE document INCLUDING COMMENTS INCLUDING POLICIES);
+--expect zero row
+SELECT 1 FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename = 'document1';
+ ?column?
+----------
+(0 rows)
+
+SELECT tablename, policyname, permissive, roles, cmd, qual,with_check
+FROM pg_policies
+WHERE schemaname = 'regress_rls_schema' AND (tablename = 'document2' or tablename = 'document')
+ORDER BY policyname, tablename;
+ tablename | policyname | permissive | roles | cmd | qual | with_check
+-----------+------------+-------------+--------------------+-----+-------------------------------------------------------------------+--------------------------------
+ document | p1 | PERMISSIVE | {public} | ALL | (dlevel <= ( SELECT uaccount.seclv +|
+ | | | | | FROM uaccount +|
+ | | | | | WHERE (uaccount.pguser = CURRENT_USER))) |
+ document2 | p1 | PERMISSIVE | {public} | ALL | (dlevel <= ( SELECT uaccount.seclv +|
+ | | | | | FROM uaccount +|
+ | | | | | WHERE (uaccount.pguser = CURRENT_USER))) |
+ document | p1r | RESTRICTIVE | {regress_rls_dave} | ALL | (cid <> 44) |
+ document2 | p1r | RESTRICTIVE | {regress_rls_dave} | ALL | (cid <> 44) |
+ document | p2r | RESTRICTIVE | {regress_rls_dave} | ALL | ((cid <> 44) AND (cid < 50)) |
+ document2 | p2r | RESTRICTIVE | {regress_rls_dave} | ALL | ((cid <> 44) AND (cid < 50)) |
+ document | p4 | PERMISSIVE | {public} | ALL | ((cid IS NOT NULL) AND ( WITH cte AS ( +| (cid = ( SELECT document_1.cid+
+ | | | | | SELECT (uaccount.* IS NOT NULL) AS "?column?" +| FROM document document_1))
+ | | | | | FROM uaccount +|
+ | | | | | ) +|
+ | | | | | SELECT cte."?column?" +|
+ | | | | | FROM cte +|
+ | | | | | WHERE (EXISTS ( SELECT category.*::category AS category +|
+ | | | | | FROM category +|
+ | | | | | WHERE (EXISTS ( SELECT uaccount.*::uaccount AS uaccount+|
+ | | | | | FROM uaccount +|
+ | | | | | WHERE (uaccount.* IS NULL))))))) |
+ document2 | p4 | PERMISSIVE | {public} | ALL | ((cid IS NOT NULL) AND ( WITH cte AS ( +| (cid = ( SELECT document.cid +
+ | | | | | SELECT (uaccount.* IS NOT NULL) AS "?column?" +| FROM document))
+ | | | | | FROM uaccount +|
+ | | | | | ) +|
+ | | | | | SELECT cte."?column?" +|
+ | | | | | FROM cte +|
+ | | | | | WHERE (EXISTS ( SELECT category.*::category AS category +|
+ | | | | | FROM category +|
+ | | | | | WHERE (EXISTS ( SELECT uaccount.*::uaccount AS uaccount+|
+ | | | | | FROM uaccount +|
+ | | | | | WHERE (uaccount.* IS NULL))))))) |
+(8 rows)
+
+SELECT pd.description, pc.relname
+FROM pg_description pd JOIN pg_policy pp ON pp.oid = pd.objoid AND pp.tableoid = pd.classoid
+JOIN pg_class pc ON pc.oid = pp.polrelid
+WHERE relname IN ('document', 'document1', 'document2')
+ORDER BY relname COLLATE "C";
+ description | relname
+--------------------------+-----------
+ security policy comments | document
+ security policy comments | document2
+(2 rows)
+
+DROP POLICY p4 ON document;
-- viewpoint from regress_rls_bob
SET SESSION AUTHORIZATION regress_rls_bob;
SET row_security TO ON;
@@ -970,6 +1049,16 @@ SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename
regress_rls_schema | part_document | pp1r | RESTRICTIVE | {regress_rls_dave} | ALL | (cid < 55) |
(2 rows)
+CREATE TABLE part_document_copy(LIKE part_document INCLUDING POLICIES);
+SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename = 'part_document_copy' ORDER BY policyname;
+ schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check
+--------------------+--------------------+------------+-------------+--------------------+-----+--------------------------------------------+------------
+ regress_rls_schema | part_document_copy | pp1 | PERMISSIVE | {public} | ALL | (dlevel <= ( SELECT uaccount.seclv +|
+ | | | | | | FROM uaccount +|
+ | | | | | | WHERE (uaccount.pguser = CURRENT_USER))) |
+ regress_rls_schema | part_document_copy | pp1r | RESTRICTIVE | {regress_rls_dave} | ALL | (cid < 55) |
+(2 rows)
+
-- viewpoint from regress_rls_bob
SET SESSION AUTHORIZATION regress_rls_bob;
SET row_security TO ON;
@@ -4006,6 +4095,17 @@ SELECT attname, most_common_vals FROM pg_stats
BEGIN;
CREATE TABLE coll_t (c) AS VALUES ('bar'::text);
CREATE POLICY coll_p ON coll_t USING (c < ('foo'::text COLLATE "C"));
+-- coll_t1 row security is not enabled, but we still copy it's policies
+CREATE TABLE coll_t1(LIKE coll_t INCLUDING POLICIES);
+\d coll_t1
+ Table "regress_rls_schema.coll_t1"
+ Column | Type | Collation | Nullable | Default
+--------+------+-----------+----------+---------
+ c | text | | |
+Policies (row security disabled):
+ POLICY "coll_p"
+ USING ((c < ('foo'::text COLLATE "C")))
+
ALTER TABLE coll_t ENABLE ROW LEVEL SECURITY;
GRANT SELECT ON coll_t TO regress_rls_alice;
SELECT (string_to_array(polqual, ':'))[7] AS inputcollid FROM pg_policy WHERE polrelid = 'coll_t'::regclass;
@@ -4824,12 +4924,15 @@ drop table rls_t, test_t;
--
RESET SESSION AUTHORIZATION;
DROP SCHEMA regress_rls_schema CASCADE;
-NOTICE: drop cascades to 30 other objects
+NOTICE: drop cascades to 33 other objects
DETAIL: drop cascades to function f_leak(text)
drop cascades to table uaccount
drop cascades to table category
drop cascades to table document
+drop cascades to table document1
+drop cascades to table document2
drop cascades to table part_document
+drop cascades to table part_document_copy
drop cascades to table dependent
drop cascades to table rec1
drop cascades to table rec2
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 21ac0ca51ee..0eba61bbce6 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -111,6 +111,42 @@ CREATE POLICY p1r ON document AS RESTRICTIVE TO regress_rls_dave
\d document
SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename = 'document' ORDER BY policyname;
+--whole-row on qual, CREATE TABLE LIKE should fail.
+CREATE POLICY p2 ON document AS PERMISSIVE USING (document IS NOT NULL);
+CREATE TABLE document0(LIKE document INCLUDING ALL); --error
+DROP POLICY p2 ON document;
+
+--whole-row on check qual, CREATE TABLE LIKE should fail.
+CREATE POLICY p3 ON document FOR INSERT WITH CHECK (document IS NOT NULL);
+CREATE TABLE document0(LIKE document INCLUDING ALL); --error
+DROP POLICY p3 ON document;
+
+--a contrived complicated policy for testing expression node deparse
+CREATE POLICY p4 ON document AS PERMISSIVE USING (document.cid IS NOT NULL AND
+ (WITH cte AS (SELECT uaccount IS NOT NULL FROM uaccount)
+ SELECT * FROM cte WHERE EXISTS
+ (SELECT category FROM category WHERE EXISTS (SELECT uaccount FROM uaccount WHERE uaccount IS NULL))))
+ WITH CHECK (cid = (SELECT cid FROM document));
+
+COMMENT ON POLICY p1 ON document IS 'security policy comments';
+CREATE TABLE document1(LIKE document INCLUDING ALL EXCLUDING POLICIES);
+CREATE TABLE document2(prefix text, LIKE document INCLUDING COMMENTS INCLUDING POLICIES);
+
+--expect zero row
+SELECT 1 FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename = 'document1';
+
+SELECT tablename, policyname, permissive, roles, cmd, qual,with_check
+FROM pg_policies
+WHERE schemaname = 'regress_rls_schema' AND (tablename = 'document2' or tablename = 'document')
+ORDER BY policyname, tablename;
+
+SELECT pd.description, pc.relname
+FROM pg_description pd JOIN pg_policy pp ON pp.oid = pd.objoid AND pp.tableoid = pd.classoid
+JOIN pg_class pc ON pc.oid = pp.polrelid
+WHERE relname IN ('document', 'document1', 'document2')
+ORDER BY relname COLLATE "C";
+DROP POLICY p4 ON document;
+
-- viewpoint from regress_rls_bob
SET SESSION AUTHORIZATION regress_rls_bob;
SET row_security TO ON;
@@ -357,6 +393,8 @@ CREATE POLICY pp1r ON part_document AS RESTRICTIVE TO regress_rls_dave
\d+ part_document
SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename like '%part_document%' ORDER BY policyname;
+CREATE TABLE part_document_copy(LIKE part_document INCLUDING POLICIES);
+SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename = 'part_document_copy' ORDER BY policyname;
-- viewpoint from regress_rls_bob
SET SESSION AUTHORIZATION regress_rls_bob;
@@ -1770,6 +1808,11 @@ SELECT attname, most_common_vals FROM pg_stats
BEGIN;
CREATE TABLE coll_t (c) AS VALUES ('bar'::text);
CREATE POLICY coll_p ON coll_t USING (c < ('foo'::text COLLATE "C"));
+
+-- coll_t1 row security is not enabled, but we still copy it's policies
+CREATE TABLE coll_t1(LIKE coll_t INCLUDING POLICIES);
+\d coll_t1
+
ALTER TABLE coll_t ENABLE ROW LEVEL SECURITY;
GRANT SELECT ON coll_t TO regress_rls_alice;
SELECT (string_to_array(polqual, ':'))[7] AS inputcollid FROM pg_policy WHERE polrelid = 'coll_t'::regclass;
--
2.34.1
From 3d50beeef17915e715aed4d1dab2e0c250c1387d Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 15 Sep 2025 11:59:43 +0800
Subject: [PATCH v1 1/2] refactor CreatePolicy
discussion: https://postgr.es/m/
---
src/backend/commands/policy.c | 53 +++++++-------------
src/backend/parser/gram.y | 1 +
src/backend/parser/parse_utilcmd.c | 59 +++++++++++++++++++++++
src/backend/tcop/utility.c | 2 +-
src/include/commands/policy.h | 2 +-
src/include/nodes/parsenodes.h | 1 +
src/include/parser/parse_utilcmd.h | 2 +
src/test/regress/expected/rowsecurity.out | 2 +
8 files changed, 85 insertions(+), 37 deletions(-)
diff --git a/src/backend/commands/policy.c b/src/backend/commands/policy.c
index 83056960fe4..799e1e3968a 100644
--- a/src/backend/commands/policy.c
+++ b/src/backend/commands/policy.c
@@ -33,6 +33,7 @@
#include "parser/parse_collate.h"
#include "parser/parse_node.h"
#include "parser/parse_relation.h"
+#include "parser/parse_utilcmd.h"
#include "rewrite/rewriteManip.h"
#include "rewrite/rowsecurity.h"
#include "utils/acl.h"
@@ -566,7 +567,7 @@ RemoveRoleFromObjectPolicy(Oid roleid, Oid classid, Oid policy_id)
* stmt - the CreatePolicyStmt that describes the policy to create.
*/
ObjectAddress
-CreatePolicy(CreatePolicyStmt *stmt)
+CreatePolicy(CreatePolicyStmt *stmt, const char *queryString)
{
Relation pg_policy_rel;
Oid policy_id;
@@ -576,8 +577,7 @@ CreatePolicy(CreatePolicyStmt *stmt)
Datum *role_oids;
int nitems = 0;
ArrayType *role_ids;
- ParseState *qual_pstate;
- ParseState *with_check_pstate;
+ ParseState *pstate;
ParseNamespaceItem *nsitem;
Node *qual;
Node *with_check_qual;
@@ -615,10 +615,6 @@ CreatePolicy(CreatePolicyStmt *stmt)
role_oids = policy_role_list_to_array(stmt->roles, &nitems);
role_ids = construct_array_builtin(role_oids, nitems, OIDOID);
- /* Parse the supplied clause */
- qual_pstate = make_parsestate(NULL);
- with_check_pstate = make_parsestate(NULL);
-
/* zero-clear */
memset(values, 0, sizeof(values));
memset(isnull, 0, sizeof(isnull));
@@ -628,35 +624,23 @@ CreatePolicy(CreatePolicyStmt *stmt)
0,
RangeVarCallbackForPolicy,
stmt);
+ if (!stmt->transformed)
+ stmt = transformPolicyStmt(table_id, stmt, queryString);
- /* Open target_table to build quals. No additional lock is necessary. */
+ qual = stmt->qual;
+ with_check_qual = stmt->with_check;
+
+ /* we'll need the pstate->rtable for recordDependencyOnExpr */
+ pstate = make_parsestate(NULL);
+ pstate->p_sourcetext = queryString;
+
+ /* No additional lock is necessary. */
target_table = relation_open(table_id, NoLock);
- /* Add for the regular security quals */
- nsitem = addRangeTableEntryForRelation(qual_pstate, target_table,
+ nsitem = addRangeTableEntryForRelation(pstate, target_table,
AccessShareLock,
NULL, false, false);
- addNSItemToQuery(qual_pstate, nsitem, false, true, true);
-
- /* Add for the with-check quals */
- nsitem = addRangeTableEntryForRelation(with_check_pstate, target_table,
- AccessShareLock,
- NULL, false, false);
- addNSItemToQuery(with_check_pstate, nsitem, false, true, true);
-
- qual = transformWhereClause(qual_pstate,
- stmt->qual,
- EXPR_KIND_POLICY,
- "POLICY");
-
- with_check_qual = transformWhereClause(with_check_pstate,
- stmt->with_check,
- EXPR_KIND_POLICY,
- "POLICY");
-
- /* Fix up collation information */
- assign_expr_collations(qual_pstate, qual);
- assign_expr_collations(with_check_pstate, with_check_qual);
+ addNSItemToQuery(pstate, nsitem, false, true, true);
/* Open pg_policy catalog */
pg_policy_rel = table_open(PolicyRelationId, RowExclusiveLock);
@@ -724,11 +708,11 @@ CreatePolicy(CreatePolicyStmt *stmt)
recordDependencyOn(&myself, &target, DEPENDENCY_AUTO);
- recordDependencyOnExpr(&myself, qual, qual_pstate->p_rtable,
+ recordDependencyOnExpr(&myself, qual, pstate->p_rtable,
DEPENDENCY_NORMAL);
recordDependencyOnExpr(&myself, with_check_qual,
- with_check_pstate->p_rtable, DEPENDENCY_NORMAL);
+ pstate->p_rtable, DEPENDENCY_NORMAL);
/* Register role dependencies */
target.classId = AuthIdRelationId;
@@ -749,8 +733,7 @@ CreatePolicy(CreatePolicyStmt *stmt)
/* Clean up. */
heap_freetuple(policy_tuple);
- free_parsestate(qual_pstate);
- free_parsestate(with_check_pstate);
+ free_parsestate(pstate);
systable_endscan(sscan);
relation_close(target_table, NoLock);
table_close(pg_policy_rel, RowExclusiveLock);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9fd48acb1f8..8016c58b49c 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -5948,6 +5948,7 @@ CreatePolicyStmt:
n->roles = $8;
n->qual = $9;
n->with_check = $10;
+ n->transformed = false;
$$ = (Node *) n;
}
;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index e96b38a59d5..394a037e817 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -3205,6 +3205,65 @@ transformStatsStmt(Oid relid, CreateStatsStmt *stmt, const char *queryString)
return stmt;
}
+/*
+ * transformPolicyStmt - parse analysis for CREATE POLICY
+ * mainly parse analysis for qual and check qual of the policy.
+ *
+ * To avoid race conditions, it's important that this function relies only on
+ * the passed-in relid (and not on stmt->table) to determine the target
+ * relation.
+ */
+CreatePolicyStmt *
+transformPolicyStmt(Oid relid, CreatePolicyStmt *stmt, const char *queryString)
+{
+ ParseState *pstate;
+ ParseNamespaceItem *nsitem;
+ Relation rel;
+
+ /* Nothing to do if statement already transformed. */
+ if (stmt->transformed)
+ return stmt;
+
+ /* Set up pstate */
+ pstate = make_parsestate(NULL);
+ pstate->p_sourcetext = queryString;
+
+ /*
+ * Put the parent table into the rtable so that the expressions can refer
+ * to its fields without qualification. Caller is responsible for locking
+ * relation, but we still need to open it.
+ */
+ rel = relation_open(relid, NoLock);
+ nsitem = addRangeTableEntryForRelation(pstate, rel,
+ AccessShareLock,
+ NULL, false, true);
+
+ /* no to join list, yes to namespaces */
+ addNSItemToQuery(pstate, nsitem, false, true, true);
+
+ stmt->qual = transformWhereClause(pstate,
+ stmt->qual,
+ EXPR_KIND_POLICY,
+ "POLICY");
+
+ stmt->with_check = transformWhereClause(pstate,
+ stmt->with_check,
+ EXPR_KIND_POLICY,
+ "POLICY");
+ /* Fix up collation information */
+ assign_expr_collations(pstate, stmt->qual);
+ assign_expr_collations(pstate, stmt->with_check);
+
+ free_parsestate(pstate);
+
+ /* Close relation */
+ table_close(rel, NoLock);
+
+ /* Mark statement as successfully transformed */
+ stmt->transformed = true;
+
+ return stmt;
+}
/*
* transformRuleStmt -
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 5f442bc3bd4..e8b3deca825 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1818,7 +1818,7 @@ ProcessUtilitySlow(ParseState *pstate,
break;
case T_CreatePolicyStmt: /* CREATE POLICY */
- address = CreatePolicy((CreatePolicyStmt *) parsetree);
+ address = CreatePolicy((CreatePolicyStmt *) parsetree, queryString);
break;
case T_AlterPolicyStmt: /* ALTER POLICY */
diff --git a/src/include/commands/policy.h b/src/include/commands/policy.h
index f06aa1df439..dab4030c38d 100644
--- a/src/include/commands/policy.h
+++ b/src/include/commands/policy.h
@@ -25,7 +25,7 @@ extern void RemovePolicyById(Oid policy_id);
extern bool RemoveRoleFromObjectPolicy(Oid roleid, Oid classid, Oid policy_id);
-extern ObjectAddress CreatePolicy(CreatePolicyStmt *stmt);
+extern ObjectAddress CreatePolicy(CreatePolicyStmt *stmt, const char *queryString);
extern ObjectAddress AlterPolicy(AlterPolicyStmt *stmt);
extern Oid get_relation_policy_oid(Oid relid, const char *policy_name,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 86a236bd58b..a8cc660d5e6 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3068,6 +3068,7 @@ typedef struct CreatePolicyStmt
List *roles; /* the roles associated with the policy */
Node *qual; /* the policy's condition */
Node *with_check; /* the policy's WITH CHECK condition. */
+ bool transformed; /* true when transformPolicyStmt is finished */
} CreatePolicyStmt;
/*----------------------
diff --git a/src/include/parser/parse_utilcmd.h b/src/include/parser/parse_utilcmd.h
index 9f2b58de797..7a3562b88c2 100644
--- a/src/include/parser/parse_utilcmd.h
+++ b/src/include/parser/parse_utilcmd.h
@@ -28,6 +28,8 @@ extern IndexStmt *transformIndexStmt(Oid relid, IndexStmt *stmt,
const char *queryString);
extern CreateStatsStmt *transformStatsStmt(Oid relid, CreateStatsStmt *stmt,
const char *queryString);
+extern CreatePolicyStmt *transformPolicyStmt(Oid relid, CreatePolicyStmt *stmt,
+ const char *queryString);
extern void transformRuleStmt(RuleStmt *stmt, const char *queryString,
List **actions, Node **whereClause);
extern List *transformCreateSchemaStmtElements(List *schemaElts,
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 8c879509313..1dc8e5c8f42 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -4084,6 +4084,8 @@ BEGIN;
CREATE TABLE t (c) AS VALUES ('bar'::text);
CREATE POLICY p ON t USING (max(c)); -- fails: aggregate functions are not allowed in policy expressions
ERROR: aggregate functions are not allowed in policy expressions
+LINE 1: CREATE POLICY p ON t USING (max(c));
+ ^
ROLLBACK;
--
-- Non-target relations are only subject to SELECT policies
--
2.34.1
