hi. inspired by this message [1], now, v3 logic is more like other CREATE TABLE LIKE INCLUDING commands.
Since policy queries can include SubLinks, we first acquire AccessShareLock lock on all the referenced relations. Only then we copy the USING and WITH CHECK clauses from the source relation. [1] https://postgr.es/m/ca+tgmoy8v44y8j--b7e72kuvupzdtmezf6fvjrfeqsg4gun...@mail.gmail.com -- jian https://www.enterprisedb.com
From eb3c2b6e7a0246b3dfdf330b81dc2bea31a0ed73 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Wed, 21 Jan 2026 12:56:19 +0800 Subject: [PATCH v3 1/1] CREATE TABLE LIKE (INCLUDING POLICIES) This will copy all source table's RLS policies to the new table, however this does not apply to CREATE FOREIGN TABLE LIKE statements. The command will fail if the source table's USING or WITH CHECK qual contain whole-row references. Additionally, if INCLUDING COMMENTS is specified, the comments associated with the RLS policies will also be copied. 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. Acquire AccessShareLock on tables referenced within policy USING and WITH CHECK subqueries to prevent someone else from deleting or ALTERing these referenced tables before the child is committed. Since map_variable_attnos handles subquery recursion and all source relations are locked, USING and WITH CHECK qual nodes can be safely copied and transformed. discussion: https://postgr.es/m/CACJufxFuEOB-i2z2qhyCG=dGwDf7g6Fs_o8cz=bui76uuuf...@mail.gmail.com commitfest: https://commitfest.postgresql.org/patch/6064 --- doc/src/sgml/ref/create_table.sgml | 18 +- src/backend/commands/policy.c | 129 +++++++++-- src/backend/parser/gram.y | 8 +- src/backend/parser/parse_utilcmd.c | 215 +++++++++++++++++- src/include/commands/policy.h | 2 + src/include/nodes/parsenodes.h | 11 + src/include/parser/kwlist.h | 1 + .../regress/expected/create_table_like.out | 7 +- src/test/regress/expected/rowsecurity.out | 144 +++++++++++- src/test/regress/sql/create_table_like.sql | 4 +- src/test/regress/sql/rowsecurity.sql | 79 +++++++ 11 files changed, 593 insertions(+), 25 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 21b8eebe32d..940fa7ed8a9 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) 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); + } /* Parse the supplied clause */ qual_pstate = make_parsestate(NULL); @@ -644,19 +660,27 @@ CreatePolicy(CreatePolicyStmt *stmt) NULL, false, false); addNSItemToQuery(with_check_pstate, nsitem, false, true, true); - qual = transformWhereClause(qual_pstate, - stmt->qual, - EXPR_KIND_POLICY, - "POLICY"); + if (stmt->transformed) + { + qual = stmt->qual; + with_check_qual = stmt->with_check; + } + else + { + qual = transformWhereClause(qual_pstate, + stmt->qual, + EXPR_KIND_POLICY, + "POLICY"); - with_check_qual = transformWhereClause(with_check_pstate, - stmt->with_check, - 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); + /* 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); @@ -755,6 +779,11 @@ CreatePolicy(CreatePolicyStmt *stmt) 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; } @@ -1277,3 +1306,77 @@ relation_has_policies(Relation rel) return ret; } + +/* + * PolicyGetRelations - + * + * Collect all relations that this policy depends on. + * + * The policy's USING or WITH CHECK qualifiers may reference other relations, + * such relations are included 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 && + !list_member_oid(result, pg_depend->refobjid)) + { + result = lappend_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 713ee5c10a2..eb04de3604a 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; } @@ -6028,6 +6029,9 @@ CreatePolicyStmt: n->roles = $8; n->qual = $9; n->with_check = $10; + n->transformed = false; + n->policycomment = NULL; + n->rolesId = NIL; $$ = (Node *) n; } ; @@ -18102,6 +18106,7 @@ unreserved_keyword: | PERIOD | PLAN | PLANS + | POLICIES | POLICY | PRECEDING | PREPARE @@ -18735,6 +18740,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 b5f4c72459d..86a88bde1ef 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" @@ -62,8 +64,10 @@ #include "partitioning/partbounds.h" #include "partitioning/partdesc.h" #include "rewrite/rewriteManip.h" +#include "storage/lmgr.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 +127,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 +1131,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 +1316,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 +1330,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); @@ -1587,6 +1597,83 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause) } } + /* + * Process table row level security policies if required. + */ + if (table_like_clause->options & CREATE_TABLE_LIKE_POLICIES && + childrel->rd_rel->relkind != RELKIND_FOREIGN_TABLE) + { + List *polrels = NIL; + ScanKeyData skey; + SysScanDesc sscan; + HeapTuple tuple; + CreatePolicyStmt *polstmt; + + /* + * Scan pg_policy for any RLS policies defined on the source relation. + * The order of visiting the policies does not matter, since we are + * copying all of them to the new target relation. + */ + 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))) + { + Form_pg_policy policy_form = (Form_pg_policy) GETSTRUCT(tuple); + + polrels = PolicyGetRelations(policy_form->oid); + + /* + * Acquire AccessShareLock on all tables referenced in the + * policy's USING or WITH CHECK qualifiers. These locks are held + * until transaction commit to prevent concurrent DROP or ALTER + * operations on the referenced tables before the child is + * committed. + * + * The source table is being locked already, so no need for it. + */ + foreach_oid(refrelid, polrels) + { + if (refrelid != RelationGetRelid(relation)) + LockRelationOid(refrelid, AccessShareLock); + } + + polstmt = generateClonedPolicyStmt(heapRel, + relation, + pg_policy, + tuple, + attmap); + + /* 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); + } + /* * Process extended statistics if required. */ @@ -2042,6 +2129,124 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx, return index; } +/* + * 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; + + Form_pg_policy policy_form = (Form_pg_policy) GETSTRUCT(poltup); + + CreatePolicyStmt *polstmt = makeNode(CreatePolicyStmt); + + if (heapRel->schemaname == NULL) + elog(ERROR, "table name \"%s\" must be schema-qualified", + heapRel->relname); + + polstmt->policy_name = pstrdup(NameStr(policy_form->polname)); + polstmt->table = 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 USING 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 policy 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); + } + + /* + * Avoid re-running parse analysis: the source policy quals have already + * been processed, copied, and mapped to the target relation context. + */ + polstmt->transformed = true; + + return polstmt; +} + /* * Generate a CreateStatsStmt node using information from an already existing * extended statistic "source_statsid", for the rel identified by heapRel and diff --git a/src/include/commands/policy.h b/src/include/commands/policy.h index 63c38100fd2..bbc8fc4746f 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 646d6ced763..3fc9ede322f 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; @@ -3100,6 +3101,16 @@ 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 means parsing analysis of the policy's + * USING, WITH CHECK qual has finished */ + char *policycomment; /* comment to apply to policies, or NULL */ + + /* + * List of roles OID associated with this policy. Either this is NIL or + * CreatePolicyStmt->roles is NIL. This field is used only for command + * CREATE TABLE LIKE INCLUDING POLICIES. + */ + List *rolesId; } CreatePolicyStmt; /*---------------------- diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index f7753c5c8a8..48f519a136c 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/create_table_like.out b/src/test/regress/expected/create_table_like.out index d3c35c14847..910b1460a56 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -608,6 +608,8 @@ CREATE TABLE ctl_table(a int PRIMARY KEY, e int DEFAULT 1); CREATE INDEX ctl_table_a_key ON ctl_table(a); COMMENT ON COLUMN ctl_table.b IS 'Column b'; +ALTER TABLE ctl_table ENABLE ROW LEVEL SECURITY; +CREATE POLICY p1 ON ctl_table AS PERMISSIVE USING (c =2); CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table; ALTER TABLE ctl_table ADD CONSTRAINT foo CHECK (b = 'text'); ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE MAIN; @@ -625,6 +627,9 @@ Indexes: "ctl_table_a_key" btree (a) Check constraints: "foo" CHECK (b::text = 'text'::text) +Policies: + POLICY "p1" + USING ((c = 2)) Statistics objects: "public.ctl_table_stat" ON a, b FROM ctl_table Not-null constraints: @@ -661,7 +666,7 @@ SELECT attname, attcompression FROM pg_attribute (5 rows) -- Test INCLUDING ALL --- INDEXES, IDENTITY, COMPRESSION, STORAGE are not copied. +-- INDEXES, IDENTITY, COMPRESSION, POLICIES, STORAGE are not copied. CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0; \d+ ctl_foreign_table2 Foreign table "public.ctl_foreign_table2" diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index c958ef4d70a..b2e047e3a2e 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -406,6 +406,124 @@ 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 policy USING 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: 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 policy WITH CHECK qual, CREATE TABLE LIKE should fail +CREATE POLICY p2 ON document FOR INSERT WITH CHECK (document IS NOT NULL); +CREATE TABLE document0(LIKE document INCLUDING ALL); +ERROR: cannot convert whole-row table reference +DETAIL: Security policy "p2" contains a whole-row reference to table "document". +DROP POLICY p2 ON document; +-- A deliberately complex policy used to test expression node mapping +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 p1 on table document'; +CREATE TABLE document1(a int, 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 p1 on table document | document + security policy p1 on table document | 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 +1347,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 +4416,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 policy +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 +5244,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/create_table_like.sql b/src/test/regress/sql/create_table_like.sql index 93389b57dbf..c6a2f2fb5fb 100644 --- a/src/test/regress/sql/create_table_like.sql +++ b/src/test/regress/sql/create_table_like.sql @@ -253,6 +253,8 @@ CREATE TABLE ctl_table(a int PRIMARY KEY, CREATE INDEX ctl_table_a_key ON ctl_table(a); COMMENT ON COLUMN ctl_table.b IS 'Column b'; +ALTER TABLE ctl_table ENABLE ROW LEVEL SECURITY; +CREATE POLICY p1 ON ctl_table AS PERMISSIVE USING (c =2); CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table; ALTER TABLE ctl_table ADD CONSTRAINT foo CHECK (b = 'text'); ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE MAIN; @@ -268,7 +270,7 @@ SELECT attname, attcompression FROM pg_attribute WHERE attrelid = 'ctl_foreign_table1'::regclass and attnum > 0 ORDER BY attnum; -- Test INCLUDING ALL --- INDEXES, IDENTITY, COMPRESSION, STORAGE are not copied. +-- INDEXES, IDENTITY, COMPRESSION, POLICIES, STORAGE are not copied. CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0; \d+ ctl_foreign_table2 -- \d+ does not report the value of attcompression for a foreign table, so diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql index 5d923c5ca3b..3bd3600e436 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -250,6 +250,78 @@ 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 policy USING 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); +DROP POLICY p2 ON document; + +--whole-row on policy WITH CHECK qual, CREATE TABLE LIKE should fail +CREATE POLICY p2 ON document FOR INSERT WITH CHECK (document IS NOT NULL); +CREATE TABLE document0(LIKE document INCLUDING ALL); +DROP POLICY p2 ON document; + +-- A deliberately complex policy used to test expression node mapping +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 p1 on table document'; +CREATE TABLE document1(a int, 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 +568,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 +1986,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 policy +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
