On Tue, Jun 30, 2026 at 9:20 AM Yilin Zhang <[email protected]> wrote: > > Hi, I found another issue with the v2 patch. > When running COPY FROM against a partitioned table with RLS enabled, > ExecSetupPartitionTupleRouting gets invoked twice, > which creates two separate sets of PartitionTupleRouting structures. > One of these sets never gets cleaned up, and PostgreSQL’s ResourceOwner > mechanism catches this and emits the following warnings: > > WARNING: resource was not closed: relation "rls_part_1" > WARNING: resource was not closed: relation "rls_part_2" > > Best regards, > -- > Yilin Zhang >
Hi. While v2 has extensive failure test cases, it doesn't have a single successful test case, that's why this issue wasn't found. The 2 issues you reported are fixed in the attached v3. Also, previously in v2: +COPY r1 FROM STDIN WITH (DELIMITER ','); -- fail +ERROR: new row violates row-level security policy "p2" for table "r1" +CONTEXT: COPY r1, line 1: "4" it's better to remove the CONTEXT line. because ExecWithCheckOptions has comments like: /* * For WITH CHECK OPTIONs coming from views, we might be * able to provide the details on the row, depending on * the permissions on the relation (that is, if the user * could view it directly anyway). For RLS violations, we * don't include the data since we don't know if the user * should be able to view the tuple as that depends on the * USING policy. */ -- jian https://www.enterprisedb.com/
From b85199517a541918d311e81d3946fc9b9b17f965 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Mon, 13 Jul 2026 16:02:59 +0800 Subject: [PATCH v3 1/1] COPY FROM with RLS Previously, using COPY FROM on a table with RLS enabled would throw an error (ERROR: COPY FROM not supported with row-level security), with a HINT: ``HINT: Use INSERT statements instead.`` Now it's fully supported, it will be just equivalent to INSERT. COPY FROM typically prints context information detailing the exact line where an error occurred, we have opted to suppress this CONTEXT line for RLS policy violations. This change ensures that our error reporting remains consistent with how RLS errors are handled elsewhere in the system. discussion: https://postgr.es/m/CACJufxFbmnoa5O-vL43DPTCGt6oagY4dXgKxy=rcd9-e9g0...@mail.gmail.com commitfest: https://commitfest.postgresql.org/patch/6178 --- doc/src/sgml/ref/copy.sgml | 7 +- doc/src/sgml/ref/create_policy.sgml | 2 +- src/backend/commands/copy.c | 10 +- src/backend/commands/copyfrom.c | 226 +++++++++++++++++----- src/test/regress/expected/rowsecurity.out | 64 +++++- src/test/regress/sql/rowsecurity.sql | 100 +++++++++- 6 files changed, 340 insertions(+), 69 deletions(-) diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index b23433b2c41..49b57e8ab7f 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -616,10 +616,9 @@ COPY <replaceable class="parameter">count</replaceable> <para> If row-level security is enabled for the table, the relevant <command>SELECT</command> policies will apply to <literal>COPY - <replaceable class="parameter">table</replaceable> TO</literal> statements. - Currently, <command>COPY FROM</command> is not supported for tables - with row-level security. Use equivalent <command>INSERT</command> - statements instead. + <replaceable class="parameter">table</replaceable> TO</literal> statements; + the relevant <command>INSERT</command> policies will apply to <literal>COPY + <replaceable class="parameter">table</replaceable> FROM</literal> statements. </para> <para> diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index d8a036739c0..8bd9c90d61c 100644 --- a/doc/src/sgml/ref/create_policy.sgml +++ b/doc/src/sgml/ref/create_policy.sgml @@ -504,7 +504,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable <entry>—</entry> </row> <row> - <entry><command>INSERT</command></entry> + <entry><command>INSERT</command> / <command>COPY ... FROM</command></entry> <entry> Check new row <footnote id="rls-select-priv"> <para> diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 003b70852bb..9d018a7c7f0 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -238,8 +238,10 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, * * If RLS is not enabled for this, then just fall through to the * normal non-filtering relation handling. + * + * COPY FROM with row-level security is handled in function CopyFrom. */ - if (check_enable_rls(relid, InvalidOid, false) == RLS_ENABLED) + if (!is_from && check_enable_rls(relid, InvalidOid, false) == RLS_ENABLED) { SelectStmt *select; ColumnRef *cr; @@ -247,12 +249,6 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, RangeVar *from; List *targetList = NIL; - if (is_from) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("COPY FROM not supported with row-level security"), - errhint("Use INSERT statements instead."))); - /* * Build target list * diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c index 80a527ed4c6..81390528f6e 100644 --- a/src/backend/commands/copyfrom.c +++ b/src/backend/commands/copyfrom.c @@ -40,6 +40,7 @@ #include "foreign/fdwapi.h" #include "mb/pg_wchar.h" #include "miscadmin.h" +#include "nodes/makefuncs.h" #include "nodes/miscnodes.h" #include "optimizer/optimizer.h" #include "pgstat.h" @@ -50,6 +51,7 @@ #include "utils/memutils.h" #include "utils/portal.h" #include "utils/rel.h" +#include "utils/rls.h" #include "utils/snapmgr.h" #include "utils/typcache.h" @@ -801,6 +803,7 @@ CopyFrom(CopyFromState cstate) bool has_before_insert_row_trig; bool has_instead_insert_row_trig; bool leafpart_use_multi_insert = false; + QueryDesc *queryDesc = NULL; Assert(cstate->rel); Assert(list_length(cstate->range_table) == 1); @@ -910,33 +913,129 @@ CopyFrom(CopyFromState cstate) ti_options |= TABLE_INSERT_FROZEN; } - /* - * We need a ResultRelInfo so we can use the regular executor's - * index-entry-making machinery. (There used to be a huge amount of code - * here that basically duplicated execUtils.c ...) - */ - ExecInitRangeTable(estate, cstate->range_table, cstate->rteperminfos, - bms_make_singleton(1)); - resultRelInfo = target_resultRelInfo = makeNode(ResultRelInfo); - ExecInitResultRelation(estate, resultRelInfo, 1); - - /* Verify the named relation is a valid target for INSERT */ - CheckValidResultRel(resultRelInfo, CMD_INSERT, ONCONFLICT_NONE, NIL, NULL); + if (check_enable_rls(RelationGetRelid(cstate->rel), InvalidOid, false) == RLS_ENABLED) + { + Query *query; + RawStmt *raw_query; + char *query_string; + PlannedStmt *plan; + RangeVar *from; + InsertStmt *insertstmt; + List *rewritten; + from = makeRangeVar(get_namespace_name(RelationGetNamespace(cstate->rel)), + pstrdup(RelationGetRelationName(cstate->rel)), + -1); + from->inh = (cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE); + + insertstmt = makeNode(InsertStmt); + insertstmt->relation = from; + + raw_query = makeNode(RawStmt); + raw_query->stmt = (Node *) insertstmt; + raw_query->stmt_location = -1; + raw_query->stmt_len = 0; + + query_string = psprintf("INSERT INTO \"%s\".\"%s\" DEFAULT VALUES", + get_namespace_name(RelationGetNamespace(cstate->rel)), + pstrdup(RelationGetRelationName(cstate->rel))); + + /* + * Run parse analysis and rewrite. Note this also acquires sufficient + * locks on the source table(s). + */ + rewritten = pg_analyze_and_rewrite_fixedparams(raw_query, + query_string, + NULL, + 0, + NULL); + + /* check that we got back something we can work with */ + if (rewritten == NIL) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("DO INSTEAD NOTHING rules are not supported for COPY")); + else if (list_length(rewritten) > 1) + { + /* examine queries to determine which error message to issue */ + foreach_node(Query, q, rewritten) + { + if (q->querySource == QSRC_QUAL_INSTEAD_RULE) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("conditional DO INSTEAD rules are not supported for COPY")); + + if (q->querySource == QSRC_NON_INSTEAD_RULE) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("DO ALSO rules are not supported for COPY")); + } + + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("multi-statement DO INSTEAD rules are not supported for COPY")); + } + + query = linitial_node(Query, rewritten); + + /* plan the query */ + plan = pg_plan_query(query, query_string, 0, NULL, NULL); + + /* Create a QueryDesc requesting no output */ + queryDesc = CreateQueryDesc(plan, + query_string, + GetActiveSnapshot(), + InvalidSnapshot, + NULL, + NULL, + NULL, + 0); + + /* + * Call ExecutorStart to prepare the plan for execution. We do not + * actually execute the plan, just let ExecutorStart populates all the + * necessary information in EState, PlanState, and the ResultRelInfo, + * subfield of PlanState. These structures are required for later + * ExecWithCheckOptions(). + */ + ExecutorStart(queryDesc, 0); + + estate = queryDesc->estate; + mtstate = (ModifyTableState *) queryDesc->planstate; + resultRelInfo = target_resultRelInfo = mtstate->resultRelInfo; + } + else + { + /* Prepare to catch AFTER triggers. */ + AfterTriggerBeginQuery(); + + /* + * We need a ResultRelInfo so we can use the regular executor's + * index-entry-making machinery. (There used to be a huge amount of + * code here that basically duplicated execUtils.c ...) + */ + ExecInitRangeTable(estate, cstate->range_table, cstate->rteperminfos, + bms_make_singleton(1)); + resultRelInfo = target_resultRelInfo = makeNode(ResultRelInfo); + ExecInitResultRelation(estate, resultRelInfo, 1); + + /* Verify the named relation is a valid target for INSERT */ + CheckValidResultRel(resultRelInfo, CMD_INSERT, ONCONFLICT_NONE, NIL, NULL); + + /* + * Set up a ModifyTableState so we can let FDW(s) init themselves for + * foreign-table result relation(s). + */ + mtstate = makeNode(ModifyTableState); + mtstate->ps.plan = NULL; + mtstate->ps.state = estate; + mtstate->operation = CMD_INSERT; + mtstate->mt_nrels = 1; + mtstate->resultRelInfo = resultRelInfo; + mtstate->rootResultRelInfo = resultRelInfo; + } ExecOpenIndices(resultRelInfo, false); - /* - * Set up a ModifyTableState so we can let FDW(s) init themselves for - * foreign-table result relation(s). - */ - mtstate = makeNode(ModifyTableState); - mtstate->ps.plan = NULL; - mtstate->ps.state = estate; - mtstate->operation = CMD_INSERT; - mtstate->mt_nrels = 1; - mtstate->resultRelInfo = resultRelInfo; - mtstate->rootResultRelInfo = resultRelInfo; - if (resultRelInfo->ri_FdwRoutine != NULL && resultRelInfo->ri_FdwRoutine->BeginForeignInsert != NULL) resultRelInfo->ri_FdwRoutine->BeginForeignInsert(mtstate, @@ -959,8 +1058,7 @@ CopyFrom(CopyFromState cstate) Assert(resultRelInfo->ri_BatchSize >= 1); - /* Prepare to catch AFTER triggers. */ - AfterTriggerBeginQuery(); + proute = mtstate->mt_partition_tuple_routing; /* * If there are any triggers with transition tables on the named relation, @@ -970,16 +1068,18 @@ CopyFrom(CopyFromState cstate) * transition capture is active, we also set it in mtstate, which is * passed to ExecFindPartition() below. */ - cstate->transition_capture = mtstate->mt_transition_capture = - MakeTransitionCaptureState(cstate->rel->trigdesc, - RelationGetRelid(cstate->rel), - CMD_INSERT); + if (mtstate->mt_transition_capture == NULL) + cstate->transition_capture = mtstate->mt_transition_capture = + MakeTransitionCaptureState(cstate->rel->trigdesc, + RelationGetRelid(cstate->rel), + CMD_INSERT); /* * If the named relation is a partitioned table, initialize state for * CopyFrom tuple routing. */ - if (cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) + if (cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE && + proute == NULL) proute = ExecSetupPartitionTupleRouting(estate, cstate->rel); if (cstate->whereClause) @@ -1106,10 +1206,13 @@ CopyFrom(CopyFromState cstate) econtext = GetPerTupleExprContext(estate); /* Set up callback to identify error line number */ - errcallback.callback = CopyFromErrorCallback; - errcallback.arg = cstate; - errcallback.previous = error_context_stack; - error_context_stack = &errcallback; + if (queryDesc == NULL) + { + errcallback.callback = CopyFromErrorCallback; + errcallback.arg = cstate; + errcallback.previous = error_context_stack; + error_context_stack = &errcallback; + } for (;;) { @@ -1351,6 +1454,11 @@ CopyFrom(CopyFromState cstate) ExecComputeStoredGenerated(resultRelInfo, estate, myslot, CMD_INSERT); + /* do row level security policy check */ + if (resultRelInfo->ri_WithCheckOptions != NIL) + ExecWithCheckOptions(WCO_RLS_INSERT_CHECK, resultRelInfo, + myslot, estate); + /* * If the target is a plain table, check the constraints of * the tuple. @@ -1490,30 +1598,42 @@ CopyFrom(CopyFromState cstate) /* Execute AFTER STATEMENT insertion triggers */ ExecASInsertTriggers(estate, target_resultRelInfo, cstate->transition_capture); - /* Handle queued AFTER triggers */ - AfterTriggerEndQuery(estate); - - ExecResetTupleTable(estate->es_tupleTable, false); - - /* Allow the FDW to shut down */ - if (target_resultRelInfo->ri_FdwRoutine != NULL && - target_resultRelInfo->ri_FdwRoutine->EndForeignInsert != NULL) - target_resultRelInfo->ri_FdwRoutine->EndForeignInsert(estate, - target_resultRelInfo); - /* Tear down the multi-insert buffer data */ if (insertMethod != CIM_SINGLE) CopyMultiInsertInfoCleanup(&multiInsertInfo); - /* Close all the partitioned tables, leaf partitions, and their indices */ - if (proute) - ExecCleanupTupleRouting(mtstate, proute); + if (queryDesc == NULL) + { + /* Handle queued AFTER triggers */ + AfterTriggerEndQuery(estate); - /* Close the result relations, including any trigger target relations */ - ExecCloseResultRelations(estate); - ExecCloseRangeTableRelations(estate); + ExecResetTupleTable(estate->es_tupleTable, false); - FreeExecutorState(estate); + /* Allow the FDW to shut down */ + if (target_resultRelInfo->ri_FdwRoutine != NULL && + target_resultRelInfo->ri_FdwRoutine->EndForeignInsert != NULL) + target_resultRelInfo->ri_FdwRoutine->EndForeignInsert(estate, + target_resultRelInfo); + + /* + * Close all the partitioned tables, leaf partitions, and their + * indices + */ + if (proute) + ExecCleanupTupleRouting(mtstate, proute); + + /* Close the result relations, including any trigger target relations */ + ExecCloseResultRelations(estate); + ExecCloseRangeTableRelations(estate); + + FreeExecutorState(estate); + } + else + { + ExecutorFinish(queryDesc); + ExecutorEnd(queryDesc); + FreeQueryDesc(queryDesc); + } return processed; } diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 3a5e82c35bd..06e03622d58 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -124,6 +124,10 @@ NOTICE: SELECT USING on rls_test_src.(1,"src a") -- plain INSERT should apply INSERT CHECK policy clause INSERT INTO rls_test_tgt VALUES (1, 'tgt a'); NOTICE: INSERT CHECK on rls_test_tgt.(1,"tgt a","TGT A") +TRUNCATE rls_test_tgt; +-- COPY FROM should also apply INSERT CHECK policy clause +COPY rls_test_tgt FROM STDIN WITH (DELIMITER ','); +NOTICE: INSERT CHECK on rls_test_tgt.(1,"tgt a","TGT A") -- INSERT ... RETURNING should also apply SELECT USING policy clause TRUNCATE rls_test_tgt; INSERT INTO rls_test_tgt VALUES (1, 'tgt a') RETURNING *; @@ -660,9 +664,13 @@ EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dt -- back from p1r for this because it sorts first INSERT INTO document VALUES (100, 44, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail ERROR: new row violates row-level security policy "p1r" for table "document" +COPY document FROM STDIN WITH (DELIMITER ','); -- fail +ERROR: new row violates row-level security policy "p1r" for table "document" -- Just to see a p2r error INSERT INTO document VALUES (100, 55, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail ERROR: new row violates row-level security policy "p2r" for table "document" +COPY document FROM STDIN WITH (DELIMITER ','); -- fail +ERROR: new row violates row-level security policy "p2r" for table "document" -- only owner can change policies ALTER POLICY p1 ON document USING (true); --fail ERROR: must be owner of table document @@ -781,6 +789,8 @@ INSERT INTO document VALUES (11, 33, 1, current_user, 'hoge'); SET SESSION AUTHORIZATION regress_rls_bob; INSERT INTO document VALUES (8, 44, 1, 'regress_rls_bob', 'my third manga'); -- Must fail with unique violation, revealing presence of did we can't see ERROR: duplicate key value violates unique constraint "document_pkey" +COPY document FROM STDIN WITH (DELIMITER ','); -- fail, COPY is equivalent to INSERT +ERROR: duplicate key value violates unique constraint "document_pkey" SELECT * FROM document WHERE did = 8; -- and confirm we can't see it did | cid | dlevel | dauthor | dtitle -----+-----+--------+---------+-------- @@ -789,6 +799,8 @@ SELECT * FROM document WHERE did = 8; -- and confirm we can't see it -- RLS policies are checked before constraints INSERT INTO document VALUES (8, 44, 1, 'regress_rls_carol', 'my third manga'); -- Should fail with RLS check violation, not duplicate key violation ERROR: new row violates row-level security policy for table "document" +COPY document FROM STDIN WITH (DELIMITER ','); -- fail, COPY is equivalent to INSERT +ERROR: new row violates row-level security policy for table "document" UPDATE document SET did = 8, dauthor = 'regress_rls_carol' WHERE did = 5; -- Should fail with RLS check violation, not duplicate key violation ERROR: new row violates row-level security policy for table "document" -- database superuser does bypass RLS policy when enabled @@ -1397,15 +1409,25 @@ EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); -- pp1 ERROR INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_dave', 'testing pp1'); -- fail ERROR: new row violates row-level security policy for table "part_document" +COPY part_document(did, cid, dlevel, dauthor, dtitle) FROM STDIN WITH (DELIMITER ','); -- fail, COPY FROM is equivalent to INSERT +ERROR: new row violates row-level security policy for table "part_document" -- pp1r ERROR INSERT INTO part_document VALUES (100, 99, 1, 'regress_rls_dave', 'testing pp1r'); -- fail ERROR: new row violates row-level security policy "pp1r" for table "part_document" +COPY part_document(did, cid, dlevel, dauthor, dtitle) FROM STDIN WITH (DELIMITER ','); -- fail, COPY FROM is equivalent to INSERT +ERROR: new row violates row-level security policy "pp1r" for table "part_document" -- Show that RLS policy does not apply for direct inserts to children -- This should fail with RLS POLICY pp1r violation. INSERT INTO part_document VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail ERROR: new row violates row-level security policy "pp1r" for table "part_document" +COPY part_document FROM STDIN WITH (DELIMITER ','); -- fail, COPY FROM is equivalent to INSERT +ERROR: new row violates row-level security policy "pp1r" for table "part_document" -- But this should succeed. INSERT INTO part_document_satire VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- success +-- COPY FROM should also succeed. +BEGIN; +COPY part_document_satire FROM STDIN WITH (DELIMITER ','); +ROLLBACK; -- We still cannot see the row using the parent SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; NOTICE: f_leak => my first novel @@ -1441,6 +1463,8 @@ CREATE POLICY pp3 ON part_document_satire AS RESTRICTIVE SET SESSION AUTHORIZATION regress_rls_dave; INSERT INTO part_document_satire VALUES (101, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail ERROR: new row violates row-level security policy for table "part_document_satire" +COPY part_document_satire FROM STDIN WITH (DELIMITER ','); -- fail, COPY FROM is equivalent to INSERT +ERROR: new row violates row-level security policy for table "part_document_satire" -- And now we cannot see directly into the partition either, due to RLS SELECT * FROM part_document_satire WHERE f_leak(dtitle) ORDER BY did; did | cid | dlevel | dauthor | dtitle @@ -1661,6 +1685,13 @@ CREATE POLICY pp3 ON part_document AS RESTRICTIVE SET SESSION AUTHORIZATION regress_rls_carol; INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_carol', 'testing pp3'); -- fail ERROR: new row violates row-level security policy "pp3" for table "part_document" +COPY part_document FROM STDIN WITH (DELIMITER ','); -- fail, COPY FROM is equivalent to INSERT +ERROR: new row violates row-level security policy "pp3" for table "part_document" +SET SESSION AUTHORIZATION regress_rls_bob; +BEGIN; +INSERT INTO part_document VALUES (1, 11, 1, 'regress_rls_bob', 'my second novel'); -- ok +COPY part_document(did, cid, dlevel, dauthor, dtitle) FROM STDIN WITH (DELIMITER ','); -- ok +ROLLBACK; ----- Dependencies ----- SET SESSION AUTHORIZATION regress_rls_alice; SET row_security TO ON; @@ -1740,6 +1771,7 @@ INSERT INTO s1 (SELECT x, public.fipshash(x::text) FROM generate_series(-10,10) CREATE TABLE s2 (x int, y text); INSERT INTO s2 (SELECT x, public.fipshash(x::text) FROM generate_series(-6,6) x); GRANT SELECT ON s1, s2 TO regress_rls_bob; +GRANT INSERT ON s1 TO regress_rls_bob; CREATE POLICY p1 ON s1 USING (a in (select x from s2 where y like '%2f%')); CREATE POLICY p2 ON s2 USING (x in (select a from s1 where b like '%22%')); CREATE POLICY p3 ON s1 FOR INSERT WITH CHECK (a = (SELECT a FROM s1)); @@ -1751,6 +1783,8 @@ SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion) ERROR: infinite recursion detected in policy for relation "s1" INSERT INTO s1 VALUES (1, 'foo'); -- fail (infinite recursion) ERROR: infinite recursion detected in policy for relation "s1" +COPY s1 FROM STDIN WITH (DELIMITER ','); -- fail, COPY FROM is equivalent to INSERT +ERROR: infinite recursion detected in policy for relation "s1" SET SESSION AUTHORIZATION regress_rls_alice; DROP POLICY p3 on s1; ALTER POLICY p2 ON s2 USING (x % 2 = 0); @@ -2254,6 +2288,7 @@ NOTICE: f_leak => 4a44dc15364204a80fe80e9039455cc1 10 | 4a44dc15364204a80fe80e9039455cc1 (5 rows) +-- COPY FROM does not support for view, not need COPY FROM tests here INSERT INTO bv1 VALUES (-1, 'xxx'); -- should fail view WCO ERROR: new row violates row-level security policy for table "b1" INSERT INTO bv1 VALUES (11, 'xxx'); -- should fail RLS check @@ -4173,9 +4208,9 @@ SET row_security TO OFF; COPY copy_t FROM STDIN; --fail - would be affected by RLS. ERROR: query would be affected by row-level security policy for table "copy_t" SET row_security TO ON; -COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS. -ERROR: COPY FROM not supported with row-level security -HINT: Use INSERT statements instead. +COPY copy_t FROM STDIN WITH (DELIMITER ','); -- ok +COPY copy_t FROM STDIN WITH (DELIMITER ','); -- error +ERROR: new row violates row-level security policy for table "copy_t" -- Check COPY FROM as user with permissions and BYPASSRLS SET SESSION AUTHORIZATION regress_rls_exempt_user; SET row_security TO ON; @@ -4192,6 +4227,20 @@ RESET SESSION AUTHORIZATION; DROP TABLE copy_t; DROP TABLE copy_rel_to CASCADE; NOTICE: drop cascades to table copy_rel_to_child +-- COPY FROM with multiple policies +RESET SESSION AUTHORIZATION; +CREATE TABLE copy_t (a integer, b text); +CREATE POLICY p1 ON copy_t USING (a % 2 = 0) WITH CHECK (a > 8); +CREATE POLICY p2 ON copy_t WITH CHECK (a > 10); +ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY; +SET row_security TO ON; +GRANT ALL ON copy_t TO regress_rls_bob, regress_rls_exempt_user; +SET SESSION AUTHORIZATION regress_rls_bob; +COPY copy_t FROM STDIN WITH (DELIMITER ','); -- fail +ERROR: new row violates row-level security policy for table "copy_t" +COPY copy_t FROM STDIN WITH (DELIMITER ','); -- ok +RESET SESSION AUTHORIZATION; +DROP TABLE copy_t; -- Check WHERE CURRENT OF SET SESSION AUTHORIZATION regress_rls_alice; CREATE TABLE current_check (currentid int, payload text, rlsuser text); @@ -4495,6 +4544,8 @@ SELECT * FROM r2; -- r2 is read-only INSERT INTO r2 VALUES (2); -- Not allowed ERROR: new row violates row-level security policy for table "r2" +COPY r2 FROM STDIN WITH (DELIMITER ','); -- fail +ERROR: new row violates row-level security policy for table "r2" UPDATE r2 SET a = 2 RETURNING *; -- Updates nothing a --- @@ -4563,6 +4614,8 @@ TABLE r1; -- RLS error INSERT INTO r1 VALUES (1); ERROR: new row violates row-level security policy for table "r1" +COPY r1 FROM STDIN WITH (DELIMITER ','); -- fail +ERROR: new row violates row-level security policy for table "r1" -- No error (unable to see any rows to update) UPDATE r1 SET a = 1; TABLE r1; @@ -4691,6 +4744,7 @@ ALTER TABLE r1 ENABLE ROW LEVEL SECURITY; ALTER TABLE r1 FORCE ROW LEVEL SECURITY; -- Works fine INSERT INTO r1 VALUES (10), (20); +COPY r1 FROM STDIN; -- ok -- No error, but no rows TABLE r1; a @@ -4772,9 +4826,13 @@ ALTER TABLE r1 FORCE ROW LEVEL SECURITY; -- Should fail p1 INSERT INTO r1 VALUES (0); ERROR: new row violates row-level security policy "p1" for table "r1" +COPY r1 FROM STDIN WITH (DELIMITER ','); -- fail +ERROR: new row violates row-level security policy "p1" for table "r1" -- Should fail p2 INSERT INTO r1 VALUES (4); ERROR: new row violates row-level security policy "p2" for table "r1" +COPY r1 FROM STDIN WITH (DELIMITER ','); -- fail +ERROR: new row violates row-level security policy "p2" for table "r1" -- OK INSERT INTO r1 VALUES (3); SELECT * FROM r1; diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql index 6b3566271df..1f054d08fc0 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -101,6 +101,11 @@ SELECT * FROM rls_test_src FOR KEY SHARE; -- plain INSERT should apply INSERT CHECK policy clause INSERT INTO rls_test_tgt VALUES (1, 'tgt a'); +TRUNCATE rls_test_tgt; +-- COPY FROM should also apply INSERT CHECK policy clause +COPY rls_test_tgt FROM STDIN WITH (DELIMITER ','); +1,tgt a, +\. -- INSERT ... RETURNING should also apply SELECT USING policy clause TRUNCATE rls_test_tgt; @@ -299,8 +304,15 @@ EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dt -- 44 would technically fail for both p2r and p1r, but we should get an error -- back from p1r for this because it sorts first INSERT INTO document VALUES (100, 44, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail +COPY document FROM STDIN WITH (DELIMITER ','); -- fail +100,44,1,regress_rls_dave,testing sorting of policies +\. + -- Just to see a p2r error INSERT INTO document VALUES (100, 55, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail +COPY document FROM STDIN WITH (DELIMITER ','); -- fail +100,55,1,regress_rls_dave,testing sorting of policies +\. -- only owner can change policies ALTER POLICY p1 ON document USING (true); --fail @@ -344,10 +356,16 @@ INSERT INTO document VALUES (11, 33, 1, current_user, 'hoge'); -- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row SET SESSION AUTHORIZATION regress_rls_bob; INSERT INTO document VALUES (8, 44, 1, 'regress_rls_bob', 'my third manga'); -- Must fail with unique violation, revealing presence of did we can't see +COPY document FROM STDIN WITH (DELIMITER ','); -- fail, COPY is equivalent to INSERT +8,44,1,regress_rls_bob,my third manga +\. SELECT * FROM document WHERE did = 8; -- and confirm we can't see it -- RLS policies are checked before constraints INSERT INTO document VALUES (8, 44, 1, 'regress_rls_carol', 'my third manga'); -- Should fail with RLS check violation, not duplicate key violation +COPY document FROM STDIN WITH (DELIMITER ','); -- fail, COPY is equivalent to INSERT +8,44,1,regress_rls_carol,my third manga +\. UPDATE document SET did = 8, dauthor = 'regress_rls_carol' WHERE did = 5; -- Should fail with RLS check violation, not duplicate key violation -- database superuser does bypass RLS policy when enabled @@ -534,14 +552,32 @@ EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); -- pp1 ERROR INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_dave', 'testing pp1'); -- fail +COPY part_document(did, cid, dlevel, dauthor, dtitle) FROM STDIN WITH (DELIMITER ','); -- fail, COPY FROM is equivalent to INSERT +100,11,5,regress_rls_dave,testing pp1 +\. -- pp1r ERROR INSERT INTO part_document VALUES (100, 99, 1, 'regress_rls_dave', 'testing pp1r'); -- fail +COPY part_document(did, cid, dlevel, dauthor, dtitle) FROM STDIN WITH (DELIMITER ','); -- fail, COPY FROM is equivalent to INSERT +100,99,1,regress_rls_dave,testing pp1r +\. -- Show that RLS policy does not apply for direct inserts to children -- This should fail with RLS POLICY pp1r violation. INSERT INTO part_document VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail +COPY part_document FROM STDIN WITH (DELIMITER ','); -- fail, COPY FROM is equivalent to INSERT +100,55,1,regress_rls_dave,testing RLS with partitions +\. + -- But this should succeed. INSERT INTO part_document_satire VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- success + +-- COPY FROM should also succeed. +BEGIN; +COPY part_document_satire FROM STDIN WITH (DELIMITER ','); +100, 55, 1, regress_rls_dave, testing RLS with partitions +\. +ROLLBACK; + -- We still cannot see the row using the parent SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; -- But we can if we look directly @@ -555,6 +591,9 @@ CREATE POLICY pp3 ON part_document_satire AS RESTRICTIVE -- This should fail with RLS violation now. SET SESSION AUTHORIZATION regress_rls_dave; INSERT INTO part_document_satire VALUES (101, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail +COPY part_document_satire FROM STDIN WITH (DELIMITER ','); -- fail, COPY FROM is equivalent to INSERT +101,55,1,regress_rls_dave,testing RLS with partitions +\. -- And now we cannot see directly into the partition either, due to RLS SELECT * FROM part_document_satire WHERE f_leak(dtitle) ORDER BY did; -- The parent looks same as before @@ -616,6 +655,17 @@ CREATE POLICY pp3 ON part_document AS RESTRICTIVE SET SESSION AUTHORIZATION regress_rls_carol; INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_carol', 'testing pp3'); -- fail +COPY part_document FROM STDIN WITH (DELIMITER ','); -- fail, COPY FROM is equivalent to INSERT +100,11,5,regress_rls_carol,testing pp3 +\. + +SET SESSION AUTHORIZATION regress_rls_bob; +BEGIN; +INSERT INTO part_document VALUES (1, 11, 1, 'regress_rls_bob', 'my second novel'); -- ok +COPY part_document(did, cid, dlevel, dauthor, dtitle) FROM STDIN WITH (DELIMITER ','); -- ok +1,11,1,regress_rls_bob,my second novel +\. +ROLLBACK; ----- Dependencies ----- SET SESSION AUTHORIZATION regress_rls_alice; @@ -698,6 +748,7 @@ CREATE TABLE s2 (x int, y text); INSERT INTO s2 (SELECT x, public.fipshash(x::text) FROM generate_series(-6,6) x); GRANT SELECT ON s1, s2 TO regress_rls_bob; +GRANT INSERT ON s1 TO regress_rls_bob; CREATE POLICY p1 ON s1 USING (a in (select x from s2 where y like '%2f%')); CREATE POLICY p2 ON s2 USING (x in (select a from s1 where b like '%22%')); @@ -711,6 +762,9 @@ CREATE VIEW v2 AS SELECT * FROM s2 WHERE y like '%af%'; SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion) INSERT INTO s1 VALUES (1, 'foo'); -- fail (infinite recursion) +COPY s1 FROM STDIN WITH (DELIMITER ','); -- fail, COPY FROM is equivalent to INSERT +1,foo +\. SET SESSION AUTHORIZATION regress_rls_alice; DROP POLICY p3 on s1; @@ -842,6 +896,7 @@ SET SESSION AUTHORIZATION regress_rls_carol; EXPLAIN (COSTS OFF) SELECT * FROM bv1 WHERE f_leak(b); SELECT * FROM bv1 WHERE f_leak(b); +-- COPY FROM does not support for view, not need COPY FROM tests here INSERT INTO bv1 VALUES (-1, 'xxx'); -- should fail view WCO INSERT INTO bv1 VALUES (11, 'xxx'); -- should fail RLS check INSERT INTO bv1 VALUES (12, 'xxx'); -- ok @@ -1851,8 +1906,14 @@ COPY copy_t FROM STDIN; --ok SET SESSION AUTHORIZATION regress_rls_bob; SET row_security TO OFF; COPY copy_t FROM STDIN; --fail - would be affected by RLS. +\. SET row_security TO ON; -COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS. +COPY copy_t FROM STDIN WITH (DELIMITER ','); -- ok +2,abc +\. +COPY copy_t FROM STDIN WITH (DELIMITER ','); -- error +1,abc +\. -- Check COPY FROM as user with permissions and BYPASSRLS SET SESSION AUTHORIZATION regress_rls_exempt_user; @@ -1875,6 +1936,27 @@ RESET SESSION AUTHORIZATION; DROP TABLE copy_t; DROP TABLE copy_rel_to CASCADE; +-- COPY FROM with multiple policies +RESET SESSION AUTHORIZATION; +CREATE TABLE copy_t (a integer, b text); +CREATE POLICY p1 ON copy_t USING (a % 2 = 0) WITH CHECK (a > 8); +CREATE POLICY p2 ON copy_t WITH CHECK (a > 10); +ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY; +SET row_security TO ON; +GRANT ALL ON copy_t TO regress_rls_bob, regress_rls_exempt_user; +SET SESSION AUTHORIZATION regress_rls_bob; + +COPY copy_t FROM STDIN WITH (DELIMITER ','); -- fail +6,abc +\. +COPY copy_t FROM STDIN WITH (DELIMITER ','); -- ok +14,abc +13,abc +\. +RESET SESSION AUTHORIZATION; +DROP TABLE copy_t; + + -- Check WHERE CURRENT OF SET SESSION AUTHORIZATION regress_rls_alice; @@ -2053,6 +2135,9 @@ SELECT * FROM r2; -- r2 is read-only INSERT INTO r2 VALUES (2); -- Not allowed +COPY r2 FROM STDIN WITH (DELIMITER ','); -- fail +2 +\. UPDATE r2 SET a = 2 RETURNING *; -- Updates nothing DELETE FROM r2 RETURNING *; -- Deletes nothing @@ -2084,6 +2169,10 @@ TABLE r1; -- RLS error INSERT INTO r1 VALUES (1); +COPY r1 FROM STDIN WITH (DELIMITER ','); -- fail +1 +\. + -- No error (unable to see any rows to update) UPDATE r1 SET a = 1; @@ -2210,6 +2299,9 @@ ALTER TABLE r1 FORCE ROW LEVEL SECURITY; -- Works fine INSERT INTO r1 VALUES (10), (20); +COPY r1 FROM STDIN; -- ok +10 +\. -- No error, but no rows TABLE r1; @@ -2287,9 +2379,15 @@ ALTER TABLE r1 FORCE ROW LEVEL SECURITY; -- Should fail p1 INSERT INTO r1 VALUES (0); +COPY r1 FROM STDIN WITH (DELIMITER ','); -- fail +0 +\. -- Should fail p2 INSERT INTO r1 VALUES (4); +COPY r1 FROM STDIN WITH (DELIMITER ','); -- fail +4 +\. -- OK INSERT INTO r1 VALUES (3); -- 2.34.1
