hi. to avoid repeated name lookup issue, I added a RangeTblEntry field to CreatePolicyStmt. see v2-0001-add-RangeTblEntry-to-CreatePolicyStmt.patch commit message too.
v2-0001, v2-0002 will be used in thread [1] too. Please also check polished v2-0003. The main logic is for each source table POLICY, using pg_policy catalog information, produces a CreatePolicyStmt node. The source relation is already locked in AccessShareLock, but POLICY qual, with check qual may reference other unrelated relations, we also need to lock them in AccessShareLock too. [1]: https://postgr.es/m/cacjufxe42vysvedemaobgmgylztcguawh_h-c9dcsldtd5j...@mail.gmail.com -- jian https://www.enterprisedb.com
From 75d8d77852af358e76710d27b4019da6ec376973 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Tue, 23 Dec 2025 09:58:44 +0800 Subject: [PATCH v2 2/3] refactoring CreatePolicy: split policy qual, check qual Currently CreatePolicy is only directly called through parser. But imagine copying policies from one table to another table (CREATE TABLE LIKE INCLUDING POLICIES) or changing a column's data type (ALTER COLUMN SET DATA TYPE). In these case, CreatePolicy will be called indirectly, either via the parser (indirectly), or by constructing a CreatePolicyStmt node from pg_policy catalog. As mentioned above, if we directly use pg_policy catalog info for constructing a new CreatePolicyStmt node, then the policy qual, with_check qual in the new node doesn't need to run parse analysis again, in fact, if we do parse analysis for transformed node again, it will fail. Another reason for refactoring is we may need to do parse analysis earlier because we can only use CreatePolicyStmt.rte, not CreatePolicyStmt.table. Overall splitting CreatePolicy qual, check qual parse analysis into a separate function will make CreatePolicy more neat. If you follow the call chain transformWhereClause -> transformExpr -> transformExprRecurse -> transformSubLink -> parse_sub_analyze, you can see that ParseState.p_rtable is not modified during transformWhereClause. Therefore, it is safe to pass a single RTE list to recordDependencyOnExpr during CreatePolicy. discussion: https://postgr.es/m/cacjufxe42vysvedemaobgmgylztcguawh_h-c9dcsldtd5j...@mail.gmail.com discussion: https://postgr.es/m/CACJufxFuEOB-i2z2qhyCG=dGwDf7g6Fs_o8cz=bui76uuuf...@mail.gmail.com --- src/backend/commands/policy.c | 54 +++++++----------------- src/backend/parser/gram.y | 1 + src/backend/parser/parse_utilcmd.c | 68 ++++++++++++++++++++++++++++++ src/backend/tcop/utility.c | 2 +- src/include/commands/policy.h | 2 +- src/include/nodes/parsenodes.h | 1 + src/include/parser/parse_utilcmd.h | 1 + 7 files changed, 88 insertions(+), 41 deletions(-) diff --git a/src/backend/commands/policy.c b/src/backend/commands/policy.c index a47744962e9..a595cd937d5 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" @@ -564,9 +565,10 @@ RemoveRoleFromObjectPolicy(Oid roleid, Oid classid, Oid policy_id) * handles the execution of the CREATE POLICY command. * * stmt - the CreatePolicyStmt that describes the policy to create. + * queryString - the source text of the CREATE POLICY command. */ ObjectAddress -CreatePolicy(CreatePolicyStmt *stmt) +CreatePolicy(CreatePolicyStmt *stmt, const char *queryString) { Relation pg_policy_rel; Oid policy_id; @@ -576,9 +578,7 @@ CreatePolicy(CreatePolicyStmt *stmt) Datum *role_oids; int nitems = 0; ArrayType *role_ids; - ParseState *qual_pstate; - ParseState *with_check_pstate; - ParseNamespaceItem *nsitem; + List *rtable; Node *qual; Node *with_check_qual; ScanKeyData skey[2]; @@ -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)); @@ -640,35 +636,17 @@ CreatePolicy(CreatePolicyStmt *stmt) stmt->rte->rellockmode = AccessExclusiveLock; } - /* Open target_table to build quals. No additional lock is necessary. */ + rtable = list_make1(stmt->rte); + + /* do parse analysis for qual and check qual */ + transformPolicyStmt(stmt, NULL); + + qual = stmt->qual; + with_check_qual = stmt->with_check; + + /* Open target_table, No additional lock is necessary. */ target_table = relation_open(table_id, NoLock); - /* Add for the regular security quals */ - nsitem = addRangeTableEntryForRelation(qual_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); - /* Open pg_policy catalog */ pg_policy_rel = table_open(PolicyRelationId, RowExclusiveLock); @@ -735,11 +713,11 @@ CreatePolicy(CreatePolicyStmt *stmt) recordDependencyOn(&myself, &target, DEPENDENCY_AUTO); - recordDependencyOnExpr(&myself, qual, qual_pstate->p_rtable, + recordDependencyOnExpr(&myself, qual, rtable, DEPENDENCY_NORMAL); recordDependencyOnExpr(&myself, with_check_qual, - with_check_pstate->p_rtable, DEPENDENCY_NORMAL); + rtable, DEPENDENCY_NORMAL); /* Register role dependencies */ target.classId = AuthIdRelationId; @@ -760,8 +738,6 @@ CreatePolicy(CreatePolicyStmt *stmt) /* Clean up. */ heap_freetuple(policy_tuple); - free_parsestate(qual_pstate); - free_parsestate(with_check_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 f6d46f08c22..97900568464 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -6029,6 +6029,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 2b7b084f216..37f140d691d 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -3208,6 +3208,74 @@ transformStatsStmt(Oid relid, CreateStatsStmt *stmt, const char *queryString) return stmt; } +/* + * transformPolicyStmt - parse analysis for CREATE POLICY + * + * To avoid race conditions, it's important that this function relies only on + * the relid on stmt->rte (and not on stmt->table) to determine the target + * relation. + */ +CreatePolicyStmt * +transformPolicyStmt(CreatePolicyStmt *stmt, const char *queryString) +{ + Relation target_table; + ParseNamespaceItem *nsitem; + ParseState *qual_pstate; + ParseState *with_check_pstate; + + /* Nothing to do if statement already transformed. */ + if (stmt->transformed) + { + Assert(stmt->rte != NULL); + return stmt; + } + + target_table = relation_open(stmt->rte->relid, NoLock); + + /* Parse the supplied clause */ + qual_pstate = make_parsestate(NULL); + with_check_pstate = make_parsestate(NULL); + + qual_pstate->p_sourcetext = queryString; + with_check_pstate->p_sourcetext = queryString; + + /* Add for the regular security quals */ + nsitem = addRangeTableEntryForRelation(qual_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); + + stmt->qual = transformWhereClause(qual_pstate, + stmt->qual, + EXPR_KIND_POLICY, + "POLICY"); + + stmt->with_check = transformWhereClause(with_check_pstate, + stmt->with_check, + EXPR_KIND_POLICY, + "POLICY"); + + /* Fix up collation information */ + assign_expr_collations(qual_pstate, stmt->qual); + assign_expr_collations(with_check_pstate, stmt->with_check); + + free_parsestate(qual_pstate); + free_parsestate(with_check_pstate); + + relation_close(target_table, 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 d18a3a60a46..de9dc267ef2 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -1826,7 +1826,7 @@ ProcessUtilitySlow(ParseState *pstate, break; case T_CreatePolicyStmt: /* CREATE POLICY */ - address = CreatePolicy((CreatePolicyStmt *) parsetree); + address = CreatePolicy(castNode(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 7acbd2bf72c..c217ec73be9 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -3109,6 +3109,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 4965fac4495..916df6eabbe 100644 --- a/src/include/parser/parse_utilcmd.h +++ b/src/include/parser/parse_utilcmd.h @@ -28,6 +28,7 @@ extern IndexStmt *transformIndexStmt(Oid relid, IndexStmt *stmt, const char *queryString); extern CreateStatsStmt *transformStatsStmt(Oid relid, CreateStatsStmt *stmt, const char *queryString); +extern CreatePolicyStmt *transformPolicyStmt(CreatePolicyStmt *stmt, const char *queryString); extern void transformRuleStmt(RuleStmt *stmt, const char *queryString, List **actions, Node **whereClause); extern List *transformCreateSchemaStmtElements(List *schemaElts, -- 2.34.1
From cfe1a262bc5d9ea5b8cd8eba6005c52b56e2ebbf Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Tue, 23 Dec 2025 09:56:30 +0800 Subject: [PATCH v2 1/3] add RangeTblEntry to CreatePolicyStmt MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Currently CreatePolicy is only directly called through parser. But imagine copying policies from one table to another table (CREATE TABLE LIKE INCLUDING POLICIES) or changing a column's data type (ALTER COLUMN SET DATA TYPE). In these case, CreatePolicy will be called indirectly, either via the parser, or by constructing a CreatePolicyStmt node from catalog metadata. These indirectly called CreatePolicy may cause repeated lookup CreatePolicyStmt->table RangeVar. (The relation already exists, but lookup up again via RangeVar). Generally we should avoid look up the same less-than-fully-qualified name multiple times, we might get different answers due to concurrent activity, and that might create a security vulnerability, along the lines of CVE-2014-0062. To avoid that, Add add a RangeTblEntry Node to CreatePolicyStmt, So we can record the Relation Oid advance. discussion: https://postgr.es/m/cacjufxe42vysvedemaobgmgylztcguawh_h-c9dcsldtd5j...@mail.gmail.com discussion: https://postgr.es/m/CACJufxFuEOB-i2z2qhyCG=dGwDf7g6Fs_o8cz=bui76uuuf...@mail.gmail.com --- src/backend/commands/policy.c | 21 ++++++++++++++++----- src/backend/parser/gram.y | 1 + src/include/nodes/parsenodes.h | 10 ++++++++++ 3 files changed, 27 insertions(+), 5 deletions(-) diff --git a/src/backend/commands/policy.c b/src/backend/commands/policy.c index 5bd5f8c9968..a47744962e9 100644 --- a/src/backend/commands/policy.c +++ b/src/backend/commands/policy.c @@ -623,11 +623,22 @@ CreatePolicy(CreatePolicyStmt *stmt) memset(values, 0, sizeof(values)); memset(isnull, 0, sizeof(isnull)); - /* Get id of table. Also handles permissions checks. */ - table_id = RangeVarGetRelidExtended(stmt->table, AccessExclusiveLock, - 0, - RangeVarCallbackForPolicy, - stmt); + if (stmt->rte != NULL) + table_id = stmt->rte->relid; + else + { + /* Get id of table. Also handles permissions checks. */ + table_id = RangeVarGetRelidExtended(stmt->table, AccessExclusiveLock, + 0, + RangeVarCallbackForPolicy, + stmt); + + /* Create a range table entry. */ + stmt->rte = makeNode(RangeTblEntry); + stmt->rte->rtekind = RTE_RELATION; + stmt->rte->relid = table_id; + stmt->rte->rellockmode = AccessExclusiveLock; + } /* Open target_table to build quals. No additional lock is necessary. */ target_table = relation_open(table_id, NoLock); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 28f4e11e30f..f6d46f08c22 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -6023,6 +6023,7 @@ CreatePolicyStmt: n->policy_name = $3; n->table = $5; + n->rte = NULL; n->permissive = $6; n->cmd_name = $7; n->roles = $8; diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index bc7adba4a0f..7acbd2bf72c 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -3094,6 +3094,16 @@ typedef struct CreatePolicyStmt NodeTag type; char *policy_name; /* Policy's name */ RangeVar *table; /* the table name the policy applies to */ + + /* + * RangeTblEntry for the table. This is useful for avoid repeated name + * lookups issue. If CreatePolicyStmt.table has been looked up, we should + * not rely on it to resolve the relation again, use this rte field + * instead. This is useful when calling CreatePolicy not directly from + * parser. + */ + RangeTblEntry *rte; + char *cmd_name; /* the command name the policy applies to */ bool permissive; /* restrictive or permissive policy */ List *roles; /* the roles associated with the policy */ -- 2.34.1
From 54a7ad076895336368c75ffea41ae7843e03810a Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Tue, 23 Dec 2025 13:18:06 +0800 Subject: [PATCH v2 3/3] CREATE TABLE LIKE (INCLUDING POLICIES) We already acquired an AccessShareLock before transformTableLikeClause on source table. CREATE/ALTER/DROP POLICY requires AccessExclusiveLock, there's no chance source table's security policy being modified while executing CREATE TABLE LIKE. Policy USING qual or WITH CHECK qual can have subquery, subquery expression may reference other tables. Lock these referenced table in AccessShareLock mode until xact commit. That will prevent someone else from deleting or ALTERing these referenced tables before the child is committed. Since all source relations referenced by this policy will be locked in AccessShareLock, and map_variable_attnos can descend into subquery, it is safe to copy these qual node then modify it. discussion: https://postgr.es/m/CACJufxFuEOB-i2z2qhyCG=dGwDf7g6Fs_o8cz=bui76uuuf...@mail.gmail.com --- doc/src/sgml/ref/create_table.sgml | 18 +- src/backend/commands/policy.c | 93 +++++++++- src/backend/parser/gram.y | 7 +- src/backend/parser/parse_utilcmd.c | 217 +++++++++++++++++++++- src/include/commands/policy.h | 2 + src/include/nodes/parsenodes.h | 9 + src/include/parser/kwlist.h | 1 + src/test/regress/expected/rowsecurity.out | 145 ++++++++++++++- src/test/regress/sql/rowsecurity.sql | 80 ++++++++ 9 files changed, 560 insertions(+), 12 deletions(-) diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 77c5a763d45..eda0bc847fb 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, indexes, and policies 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, constraints, and policies 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> + All row-level security policies are copied to the new table. + Note that by default 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 a595cd937d5..9c2621d10a3 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" @@ -612,8 +614,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)); @@ -742,6 +758,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; } @@ -1264,3 +1285,71 @@ relation_has_policies(Relation rel) return ret; } + +/* + * PolicyGetRelations - + * + * Collect all relations this policy depends on. + * The policy's check qual or qual may reference other relations, we include + * those as well. + */ +List * +PolicyGetRelations(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); + + if (pg_depend->refclassid == RelationRelationId) + result = list_append_unique_oid(result, pg_depend->refobjid); + } + systable_endscan(depScan); + + relation_close(depRel, AccessShareLock); + + Assert(result != NIL); + + return result; +} + +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; + } +} diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 97900568464..8ae0ce1951c 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -766,7 +766,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER PARALLEL PARAMETER PARSER PARTIAL PARTITION PARTITIONS 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 @@ -4294,6 +4294,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; } @@ -6030,6 +6031,8 @@ CreatePolicyStmt: n->qual = $9; n->with_check = $10; n->transformed = false; + n->policycomment = NULL; + n->rolesId = NIL; $$ = (Node *) n; } ; @@ -18099,6 +18102,7 @@ unreserved_keyword: | PERIOD | PLAN | PLANS + | POLICIES | POLICY | PRECEDING | PREPARE @@ -18732,6 +18736,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 37f140d691d..0b09f07504f 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -38,10 +38,12 @@ #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" #include "commands/defrem.h" +#include "commands/policy.h" #include "commands/sequence.h" #include "commands/tablecmds.h" #include "commands/tablespace.h" @@ -64,6 +66,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" @@ -123,6 +126,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); @@ -1122,8 +1130,8 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) * table has been created. * * Some options are ignored. For example, as foreign tables have no storage, - * these INCLUDING options have no effect: STORAGE, COMPRESSION, IDENTITY - * and INDEXES. Similarly, INCLUDING INDEXES is ignored from a view. + * these INCLUDING options have no effect: STORAGE, COMPRESSION, IDENTITY, + * POLICIES, and INDEXES. Similarly, INCLUDING INDEXES is ignored from a view. */ static void transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_clause) @@ -1307,8 +1315,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, policies + * or statistics, 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. @@ -1321,7 +1329,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); @@ -1625,6 +1634,91 @@ 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 && + childrel->rd_rel->relkind != RELKIND_FOREIGN_TABLE) + { + Relation pg_policy; + ScanKeyData skey; + SysScanDesc sscan; + HeapTuple tuple; + Form_pg_policy policy_form; + List *polrels = NIL; + + /* + * 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. + */ + pg_policy = table_open(PolicyRelationId, AccessShareLock); + ScanKeyInit(&skey, + Anum_pg_policy_polrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(RelationGetRelid(relation))); + sscan = systable_beginscan(pg_policy, + PolicyPolrelidPolnameIndexId, true, + NULL, + 1, + &skey); + while (HeapTupleIsValid(tuple = systable_getnext(sscan))) + { + RangeTblEntry *rte; + CreatePolicyStmt *polstmt; + + polstmt = generateClonedPolicyStmt(heapRel, relation, + pg_policy, + tuple, + attmap); + + /* Create a range table entry. */ + rte = makeNode(RangeTblEntry); + rte->rtekind = RTE_RELATION; + rte->relid = RelationGetRelid(childrel); + rte->rellockmode = ShareUpdateExclusiveLock; + polstmt->rte = rte; + + policy_form = (Form_pg_policy) GETSTRUCT(tuple); + + polrels = PolicyGetRelations(policy_form->oid); + + /* + * Policy USING qual or WITH CHECK qual can have subquery, + * subquery may reference other tables. Lock these referenced + * table in AccessShareLock mode until xact commit. That will + * prevent someone else from deleting or ALTERing these referenced + * table before the child is committed. + */ + foreach_oid(refrelid, polrels) + { + if (refrelid != RelationGetRelid(relation)) + { + Relation refrel = table_open(refrelid, AccessShareLock); + + table_close(refrel, NoLock); + } + } + + /* Copy comment on policies object, if requested */ + if (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS) + { + 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(pg_policy, AccessShareLock); + } + /* Done with child rel */ table_close(childrel, NoLock); @@ -2166,6 +2260,119 @@ 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); + + /* As in expandTableLikeClause, reject whole-row variables */ + 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); + + /* As in expandTableLikeClause, reject whole-row variables */ + 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/commands/policy.h b/src/include/commands/policy.h index dab4030c38d..f38dd9213ed 100644 --- a/src/include/commands/policy.h +++ b/src/include/commands/policy.h @@ -34,5 +34,7 @@ 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 *PolicyGetRelations(Oid policyId); +extern char *get_policy_applied_command(char polcmd); #endif /* POLICY_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index c217ec73be9..af4aeecfd11 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -795,6 +795,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; @@ -3110,6 +3111,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 OID associated with the policy. either this is NIL or + * CreatePolicyStmt.roles is NIL. This field is used only for CREATE TABLE + * LIKE INCLUDING POLICIES. + */ + List *rolesId; } CreatePolicyStmt; /*---------------------- diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 9fde58f541c..e322f958f04 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -347,6 +347,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 c958ef4d70a..e76f657eede 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -406,6 +406,125 @@ 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 deliberately complex policy used to test expression node deparsing +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); +--expect zero row +SELECT true +FROM pg_policies +WHERE schemaname = 'regress_rls_schema' AND tablename = 'document1'; + ?column? +---------- +(0 rows) + +BEGIN; +DROP TYPE IF EXISTS lockmodes; +NOTICE: type "lockmodes" does not exist, skipping +CREATE TYPE lockmodes as enum ( + 'SIReadLock' +,'AccessShareLock' +,'RowShareLock' +,'RowExclusiveLock' +,'ShareUpdateExclusiveLock' +,'ShareLock' +,'ShareRowExclusiveLock' +,'ExclusiveLock' +,'AccessExclusiveLock' +); +CREATE OR REPLACE VIEW my_locks AS +SELECT c.relname, MAX(mode::lockmodes) AS max_lockmode +FROM pg_locks l JOIN pg_class c ON l.relation = c.oid + WHERE virtualtransaction = ( + SELECT virtualtransaction + FROM pg_locks + WHERE transactionid = pg_current_xact_id()::xid) +AND locktype = 'relation' +AND relnamespace != (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog') +AND c.relname != 'my_locks' +AND c.relname NOT LIKE 'pg_toast%' +GROUP BY c.relname +ORDER BY c.relname; +CREATE TABLE document2(prefix text, LIKE document INCLUDING COMMENTS INCLUDING POLICIES); +SELECT * FROM my_locks; + relname | max_lockmode +-----------+--------------------- + category | AccessShareLock + document | AccessShareLock + document2 | AccessExclusiveLock + uaccount | AccessShareLock +(4 rows) + +ROLLBACK; +CREATE TABLE document2(prefix text, LIKE document INCLUDING COMMENTS INCLUDING POLICIES); +SELECT tablename, policyname, permissive, roles, cmd, qual, with_check +FROM pg_policies +WHERE schemaname = 'regress_rls_schema' AND tablename IN ('document', 'document2') +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; @@ -1229,6 +1348,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; @@ -4288,6 +4417,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; @@ -5105,12 +5245,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 5d923c5ca3b..50c36cc766c 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -250,6 +250,79 @@ 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 deliberately complex policy used to test expression node deparsing +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); + +--expect zero row +SELECT true +FROM pg_policies +WHERE schemaname = 'regress_rls_schema' AND tablename = 'document1'; + +BEGIN; +DROP TYPE IF EXISTS lockmodes; +CREATE TYPE lockmodes as enum ( + 'SIReadLock' +,'AccessShareLock' +,'RowShareLock' +,'RowExclusiveLock' +,'ShareUpdateExclusiveLock' +,'ShareLock' +,'ShareRowExclusiveLock' +,'ExclusiveLock' +,'AccessExclusiveLock' +); + +CREATE OR REPLACE VIEW my_locks AS +SELECT c.relname, MAX(mode::lockmodes) AS max_lockmode +FROM pg_locks l JOIN pg_class c ON l.relation = c.oid + WHERE virtualtransaction = ( + SELECT virtualtransaction + FROM pg_locks + WHERE transactionid = pg_current_xact_id()::xid) +AND locktype = 'relation' +AND relnamespace != (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog') +AND c.relname != 'my_locks' +AND c.relname NOT LIKE 'pg_toast%' +GROUP BY c.relname +ORDER BY c.relname; + +CREATE TABLE document2(prefix text, LIKE document INCLUDING COMMENTS INCLUDING POLICIES); + +SELECT * FROM my_locks; +ROLLBACK; + +CREATE TABLE document2(prefix text, LIKE document INCLUDING COMMENTS INCLUDING POLICIES); + +SELECT tablename, policyname, permissive, roles, cmd, qual, with_check +FROM pg_policies +WHERE schemaname = 'regress_rls_schema' AND tablename IN ('document', 'document2') +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; @@ -496,6 +569,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; @@ -1912,6 +1987,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
