On Tue, Oct 28, 2025 at 8:12 PM jian he <[email protected]> wrote: > > hi. > > While working on other COPY related patches, I wonder > whether it's doable to implement RLS for COPY FROM. > > COPY FROM is more simple that INSERT. we don't need to consider > INSERT RETURNING and INSERT ON CONFLICT DO UPDATE, > also no need to worry about MERGE ... INSERT. > we also don't need to consider SELECT privilege. > security policy only applies to table and partitioned tables, so don't > need to worry about > VIEW INSTEAD OF TRIGGER and foreign tables. > > However, there's another complication that makes implementation difficult — a > policy can contain a subquery. > I'm not sure how to handle that properly, so error out whenever a policy > contains a sublink/subquery. (maybe it's doable, seems hard....). >
Hi. I implemented a hack to make it work with RLS policies that contain subqueries; I'm not sure how valid it is. IF (check_enable_rls(RelationGetRelid(cstate->rel), InvalidOid, false) == RLS_ENABLED) is true, then we construct a dummy query string: INSERT INTO "%s"."%s" DEFAULT VALUES (where %s.%s is replaced by the schema-qualified relation name) and then run it through the following pipeline: pg_analyze_and_rewrite_fixedparams() pg_plan_query() CreateQueryDesc() ExecutorStart() Obviously, ExecutorRun() cannot be used because the data source for COPY FROM is an external file rather than a normal query execution. The main hack is to bypass ExecutorRun() completely and only use ExecutorStart() to initialize the required executor structures (EState, PlanState, ResultRelInfo, etc.) for ExecWithCheckOptions (which is part of ExecutorRun normally). In DoCopy we did converting COPY (REL) TO,to a SelectStmt when REL have RLS. -- jian https://www.enterprisedb.com/
From dfeca5ec3488d105595fc886b8dfe5e40d6856f3 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Sun, 10 May 2026 19:03:08 +0800 Subject: [PATCH v2 1/1] COPY FROM with RLS 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 | 5 +- src/backend/commands/copy.c | 8 +- src/backend/commands/copyfrom.c | 213 +++++++++++++++++----- src/test/regress/expected/rowsecurity.out | 54 +++++- src/test/regress/sql/rowsecurity.sql | 60 +++++- 5 files changed, 276 insertions(+), 64 deletions(-) diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index 4706c9a4410..c654b15501d 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -616,9 +616,8 @@ COPY <replaceable class="parameter">count</replaceable> 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. + While <command>COPY FROM</command> is supported for tables with row-level + security, it is not supported if the table also has rules. </para> <para> diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 003b70852bb..66e4835a194 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -239,7 +239,7 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, * If RLS is not enabled for this, then just fall through to the * normal non-filtering relation handling. */ - 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 +247,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 64ac3063c61..2cd83a22f0e 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,127 @@ 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); + 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) + { + ListCell *lc; + + /* examine queries to determine which error message to issue */ + foreach(lc, rewritten) + { + Query *q = lfirst_node(Query, lc); + + 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 here. 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); + + /* + * 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,9 +1056,6 @@ CopyFrom(CopyFromState cstate) Assert(resultRelInfo->ri_BatchSize >= 1); - /* Prepare to catch AFTER triggers. */ - AfterTriggerBeginQuery(); - /* * If there are any triggers with transition tables on the named relation, * we need to be prepared to capture transition tuples. @@ -970,16 +1064,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) @@ -1351,6 +1447,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 +1591,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..6f223a5cf01 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") +-- COPY FROM should also apply INSERT CHECK policy clause +COPY rls_test_tgt FROM STDIN WITH (DELIMITER ','); +ERROR: missing data for column "c" +CONTEXT: COPY rls_test_tgt, line 1: "1,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,15 @@ 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" +CONTEXT: COPY document, line 1: "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 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" +CONTEXT: COPY document, line 1: "100,55,1,regress_rls_dave,testing sorting of policies" -- only owner can change policies ALTER POLICY p1 ON document USING (true); --fail ERROR: must be owner of table document @@ -781,6 +791,9 @@ 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 +ERROR: duplicate key value violates unique constraint "document_pkey" +CONTEXT: COPY document, line 1 SELECT * FROM document WHERE did = 8; -- and confirm we can't see it did | cid | dlevel | dauthor | dtitle -----+-----+--------+---------+-------- @@ -789,6 +802,9 @@ 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" +CONTEXT: COPY document, line 1: "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 ERROR: new row violates row-level security policy for table "document" -- database superuser does bypass RLS policy when enabled @@ -1397,13 +1413,22 @@ 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 FROM STDIN WITH (DELIMITER ','); -- fail, COPY FROM is equivalent to INSERT +ERROR: new row violates row-level security policy for table "part_document" +CONTEXT: COPY part_document, line 1: "100,11,5,regress_rls_dave,testing pp1" -- 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 FROM STDIN WITH (DELIMITER ','); -- fail, COPY FROM is equivalent to INSERT +ERROR: new row violates row-level security policy "pp1r" for table "part_document" +CONTEXT: COPY part_document, line 1: "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 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" +CONTEXT: COPY part_document, line 1: "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 -- We still cannot see the row using the parent @@ -1441,6 +1466,9 @@ 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" +CONTEXT: COPY part_document_satire, line 1: "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; did | cid | dlevel | dauthor | dtitle @@ -1661,6 +1689,9 @@ 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" +CONTEXT: COPY part_document, line 1: "100,11,5,regress_rls_carol,testing pp3" ----- 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 ','); -- no error +ERROR: new row violates row-level security policy for table "copy_t" +CONTEXT: COPY copy_t, line 2: "1,abc" -- Check COPY FROM as user with permissions and BYPASSRLS SET SESSION AUTHORIZATION regress_rls_exempt_user; SET row_security TO ON; @@ -4495,6 +4530,9 @@ 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" +CONTEXT: COPY r2, line 1: "2" UPDATE r2 SET a = 2 RETURNING *; -- Updates nothing a --- @@ -4563,6 +4601,9 @@ 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" +CONTEXT: COPY r1, line 1: "1" -- No error (unable to see any rows to update) UPDATE r1 SET a = 1; TABLE r1; @@ -4691,6 +4732,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 +4814,15 @@ 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" +CONTEXT: COPY r1, line 1: "0" -- 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" +CONTEXT: COPY r1, line 1: "4" -- 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..db90181ed97 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -101,6 +101,10 @@ 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'); +-- 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 +303,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 +355,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 +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,12 +551,22 @@ 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 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 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 -- We still cannot see the row using the parent @@ -555,6 +582,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 +646,9 @@ 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 +\. ----- Dependencies ----- SET SESSION AUTHORIZATION regress_rls_alice; @@ -698,6 +731,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 +745,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 +879,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 +1889,12 @@ 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 ','); -- no error +2,abc +1,abc +\. -- Check COPY FROM as user with permissions and BYPASSRLS SET SESSION AUTHORIZATION regress_rls_exempt_user; @@ -2053,6 +2095,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 +2129,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 +2259,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 +2339,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
