From 54a7ad076895336368c75ffea41ae7843e03810a Mon Sep 17 00:00:00 2001 From: jian he 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=BUi76UuUFSOA@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 ] [ ON UPDATE and like_option is: -{ 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 } and partition_bound_spec is: @@ -672,9 +672,9 @@ WITH ( MODULUS numeric_literal, REM INCLUDING COMMENTS - 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. @@ -753,6 +753,18 @@ WITH ( MODULUS numeric_literal, REM + + INCLUDING POLICIES + + + 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 ALTER TABLE ... ENABLE ROW LEVEL SECURITY + in order for created policies to be applied to the new table. + + + + INCLUDING STATISTICS 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