v2-0001-COPY-FROM-with-RLS.patch
text/x-patch
Filename: v2-0001-COPY-FROM-with-RLS.patch
Type: text/x-patch
Part: 0
Message:
Re: COPY FROM with RLS
From dfeca5ec3488d105595fc886b8dfe5e40d6856f3 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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-e9g0zEg@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