pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedCOPY FROM with RLS
11+ messages / 5 participants
[nested] [flat]
* COPY FROM with RLS
@ 2025-10-28 12:12 jian he <jian.universality@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: jian he @ 2025-10-28 12:12 UTC (permalink / raw)
To: pgsql-hackers
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....).
If we don’t need to handle policies containing subquery cases, then the
implementation seems pretty straightforward:
1. get_row_security_policies to get all these WCO
2. initialize these WCO exprstate and pass WCO, WCO exprstate to
resultRelInfo->ri_WithCheckOptions and
resultRelInfo->ri_WithCheckOptionExprs
3. call ExecWithCheckOptions in CopyFrom
to get get_row_security_policies work, I need to invent a Query and
RangeTblEntry node.
similar to ExecInsert, i placed ExecWithCheckOptions right above
ExecConstraints.
To support partitioned tables, I needed to produce a ModifyTable node. so that
ExecInitPartitionInfo can populate each leaf partition’s ResultRelInfo with the
relevant ri_WithCheckOptions and ri_WithCheckOptionExprs.
(this part feels like a hack, but since we only care about
ModifyTable.withCheckOptionLists so the implication is limited, i think).
anyway, please check the attached patch for COPY FROM with RLS.
Attachments:
[text/x-patch] v1-0001-COPY-FROM-with-RLS.patch (15.2K, ../../CACJufxFbmnoa5O-vL43DPTCGt6oagY4dXgKxy=rcD9-e9g0zEg@mail.gmail.com/2-v1-0001-COPY-FROM-with-RLS.patch)
download | inline diff:
From 5222c8c413a6741f4d7dedffe021f8f779992bd6 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 28 Oct 2025 20:07:37 +0800
Subject: [PATCH v1 1/1] COPY FROM with RLS
mainly because get_row_security_policies will do all the work.
discussion: https://postgr.es/m/
---
doc/src/sgml/ref/copy.sgml | 8 +-
src/backend/commands/copy.c | 8 +-
src/backend/commands/copyfrom.c | 114 ++++++++++++++++++++++
src/test/regress/expected/rowsecurity.out | 56 ++++++++++-
src/test/regress/sql/rowsecurity.sql | 66 ++++++++++++-
5 files changed, 238 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index fdc24b36bb8..8a453589ccf 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -569,9 +569,11 @@ 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.
+ <command>COPY FROM</command> is supported for tables with row-level security.
+ However if any row-level security policy’s <literal>USING</literal> or
+ <literal>WITH CHECK</literal> expression contains a subquery, then
+ <command>COPY FROM</command> is not supported. In that case, Use equivalent
+ <command>INSERT</command> statements instead.
</para>
<para>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 44020d0ae80..0bb4e9df4ea 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -179,7 +179,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;
@@ -187,12 +187,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 12781963b4f..8705942483a 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -39,16 +39,20 @@
#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 "parser/parse_relation.h"
#include "pgstat.h"
#include "rewrite/rewriteHandler.h"
+#include "rewrite/rowsecurity.h"
#include "storage/fd.h"
#include "tcop/tcopprot.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/portal.h"
#include "utils/rel.h"
+#include "utils/rls.h"
#include "utils/snapmgr.h"
/*
@@ -957,6 +961,109 @@ CopyFrom(CopyFromState cstate)
Assert(resultRelInfo->ri_BatchSize >= 1);
+ if (check_enable_rls(RelationGetRelid(cstate->rel), InvalidOid, false) == RLS_ENABLED)
+ {
+ List *securityQuals = NIL;
+ List *withCheckOptions = NIL;
+ List *newWithCheckOptions = NIL;
+ List *wcoExprs = NIL;
+ char *refname;
+
+ Query *root = makeNode(Query);
+ ModifyTable *node = makeNode(ModifyTable);
+ RangeTblEntry *rte = makeNode(RangeTblEntry);
+ bool hasRowSecurity = false;
+ bool hasSubLinks = false;
+ RTEPermissionInfo *perminfo;
+
+ /*
+ * We use the Query and RTE nodes to retrieve the COPY FROM relation's
+ * security policies (get_row_security_policies) and transform them into
+ * WithCheckOption nodes. Later, we initialize these WCO node exprstate
+ * and pass these initialized WCOs to the resultRelInfo.
+ */
+ rte->alias = NULL;
+ refname = RelationGetRelationName(cstate->rel);
+ rte->eref = makeAlias(refname, NIL);
+ rte->rtekind = RTE_RELATION;
+ rte->relid = RelationGetRelid(cstate->rel);
+ rte->inh = false;
+ rte->relkind = cstate->rel->rd_rel->relkind;
+ rte->rellockmode = RowExclusiveLock;
+ rte->lateral = false;
+ rte->inFromCl = false;
+
+ perminfo = addRTEPermissionInfo(&root->rteperminfos, rte);
+ perminfo->requiredPerms = ACL_INSERT;
+ perminfo->checkAsUser = InvalidOid;
+
+ /* we already did the permission check on DoCopy */
+ foreach_int(cur, cstate->attnumlist)
+ {
+ int attno;
+ Bitmapset **bms;
+
+ attno = cur - FirstLowInvalidHeapAttributeNumber;
+ bms = &perminfo->insertedCols;
+
+ *bms = bms_add_member(*bms, attno);
+ }
+
+ root->resultRelation = 1;
+ root->rtable = list_make1(rte);
+ root->commandType = CMD_INSERT;
+ root->stmt_location = -1;
+
+ get_row_security_policies(root, rte, 1,
+ &securityQuals,
+ &withCheckOptions,
+ &hasRowSecurity,
+ &hasSubLinks);
+
+ /* policy contain subquery, maybe doable? */
+ if (hasSubLinks)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("COPY FROM not supported with row-level security policy contain subquery"),
+ errhint("Use INSERT statements instead."));
+
+ foreach_node(WithCheckOption, wco, withCheckOptions)
+ {
+ wco->qual = eval_const_expressions(NULL, wco->qual);
+ wco->qual = (Node *) canonicalize_qual((Expr *) wco->qual, false);
+ wco->qual = (Node *) make_ands_implicit((Expr *) wco->qual);
+
+ if (wco->qual != NULL)
+ newWithCheckOptions = lappend(newWithCheckOptions, wco);
+ }
+
+ foreach_node(WithCheckOption, wco, newWithCheckOptions)
+ {
+ ExprState *wcoExpr = ExecInitQual(castNode(List, wco->qual),
+ &mtstate->ps);
+
+ wcoExprs = lappend(wcoExprs, wcoExpr);
+ }
+
+ resultRelInfo->ri_WithCheckOptions = newWithCheckOptions;
+ resultRelInfo->ri_WithCheckOptionExprs = wcoExprs;
+
+ /* see make_modifytable */
+ node->operation = CMD_INSERT;
+
+ /*
+ * INSERT applies to a single relation only, so rootRelation is always 0
+ */
+ node->rootRelation = 0;
+ node->returningOldAlias = NULL;
+ node->returningNewAlias = NULL;
+ node->resultRelations = list_make1_int(1);
+ node->onConflictAction = ONCONFLICT_NONE;
+ node->withCheckOptionLists = list_make1(list_copy(newWithCheckOptions));
+
+ mtstate->ps.plan = (Plan *) node;
+ }
+
/* Prepare to catch AFTER triggers. */
AfterTriggerBeginQuery();
@@ -1349,6 +1456,13 @@ 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.
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 42b78a24603..140f5602df7 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -356,6 +356,10 @@ 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"
+-- fail, COPY FROM, security policy with subquery not supported
+COPY document FROM STDIN;
+ERROR: COPY FROM not supported with row-level security policy contain subquery
+HINT: Use INSERT statements instead.
-- 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"
@@ -1092,6 +1096,10 @@ 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"
+-- fail, COPY FROM, security policy with subquery not supported
+COPY part_document FROM STDIN;
+ERROR: COPY FROM not supported with row-level security policy contain subquery
+HINT: Use INSERT statements instead.
-- 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"
@@ -1376,6 +1384,49 @@ EXPLAIN (COSTS OFF) SELECT * FROM dependent; -- After drop, should be unqualifie
Seq Scan on dependent
(1 row)
+--COPY FROM with RLS
+RESET SESSION AUTHORIZATION;
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (10);
+CREATE TABLE pp_2 PARTITION OF pp FOR VALUES FROM (10) TO (20);
+ALTER TABLE pp ENABLE ROW LEVEL SECURITY;
+ALTER TABLE pp_1 ENABLE ROW LEVEL SECURITY;
+ALTER TABLE pp_2 ENABLE ROW LEVEL SECURITY;
+CREATE POLICY pp_1_p1 ON pp_1 FOR INSERT WITH CHECK (id = 6);
+CREATE POLICY p2_pp ON pp FOR ALL USING(id = 1 or id = 2);
+GRANT SELECT, INSERT ON pp TO regress_rls_alice;
+GRANT SELECT, INSERT ON pp_1 TO regress_rls_alice;
+GRANT SELECT, INSERT ON pp_2 TO regress_rls_alice;
+SET SESSION AUTHORIZATION regress_rls_alice;
+INSERT INTO pp_1 VALUES (13, 2); --error
+ERROR: new row violates row-level security policy for table "pp_1"
+INSERT INTO pp_1 VALUES (16, 6); --ok
+COPY pp_1 FROM STDIN WITH DELIMITER ','; --second record not ok
+ERROR: new row violates row-level security policy for table "pp_1"
+CONTEXT: COPY pp_1, line 2: "13,2"
+INSERT INTO pp VALUES (1,11), (2,12);
+INSERT INTO pp values (5,11); --error
+ERROR: new row violates row-level security policy for table "pp"
+INSERT INTO pp values (6,11); --error
+ERROR: new row violates row-level security policy for table "pp"
+COPY pp FROM STDIN WITH DELIMITER ','; --error, second record not ok
+ERROR: new row violates row-level security policy for table "pp"
+CONTEXT: COPY pp, line 2: "5,11"
+COPY pp FROM STDIN WITH DELIMITER ','; --error, second record not ok
+ERROR: new row violates row-level security policy for table "pp"
+CONTEXT: COPY pp, line 2: "6,11"
+RESET SESSION AUTHORIZATION;
+CREATE POLICY p1_pp ON pp FOR INSERT WITH CHECK(id > 4);
+SET SESSION AUTHORIZATION regress_rls_alice;
+INSERT INTO pp VALUES (5, 15), (6, 16); --ok
+INSERT INTO pp VALUES (4, 14); --error
+ERROR: new row violates row-level security policy for table "pp"
+COPY pp FROM STDIN WITH DELIMITER ','; --third record will result error
+ERROR: new row violates row-level security policy for table "pp"
+CONTEXT: COPY pp, line 3: "4,14"
+RESET SESSION AUTHORIZATION;
+DROP TABLE PP;
----- RECURSION ----
--
-- Simple recursion
@@ -3820,9 +3871,7 @@ 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; --no error
-- Check COPY FROM as user with permissions and BYPASSRLS
SET SESSION AUTHORIZATION regress_rls_exempt_user;
SET row_security TO ON;
@@ -4338,6 +4387,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;
-- No error, but no rows
TABLE r1;
a
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 2d1be543391..11458b0d74a 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -144,6 +144,11 @@ 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
+
+-- fail, COPY FROM, security policy with subquery not supported
+COPY document FROM STDIN;
+\.
+
-- Just to see a p2r error
INSERT INTO document VALUES (100, 55, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail
@@ -379,6 +384,10 @@ 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
+-- fail, COPY FROM, security policy with subquery not supported
+COPY part_document FROM STDIN;
+\.
+
-- pp1r ERROR
INSERT INTO part_document VALUES (100, 99, 1, 'regress_rls_dave', 'testing pp1r'); -- fail
@@ -479,6 +488,56 @@ DROP TABLE dependee CASCADE;
EXPLAIN (COSTS OFF) SELECT * FROM dependent; -- After drop, should be unqualified
+--COPY FROM with RLS
+RESET SESSION AUTHORIZATION;
+CREATE TABLE pp (id int,val int) PARTITION BY RANGE (id);
+CREATE TABLE pp_1 (val int, id int);
+ALTER TABLE pp ATTACH PARTITION pp_1 FOR VALUES FROM (1) TO (10);
+CREATE TABLE pp_2 PARTITION OF pp FOR VALUES FROM (10) TO (20);
+ALTER TABLE pp ENABLE ROW LEVEL SECURITY;
+ALTER TABLE pp_1 ENABLE ROW LEVEL SECURITY;
+ALTER TABLE pp_2 ENABLE ROW LEVEL SECURITY;
+CREATE POLICY pp_1_p1 ON pp_1 FOR INSERT WITH CHECK (id = 6);
+CREATE POLICY p2_pp ON pp FOR ALL USING(id = 1 or id = 2);
+GRANT SELECT, INSERT ON pp TO regress_rls_alice;
+GRANT SELECT, INSERT ON pp_1 TO regress_rls_alice;
+GRANT SELECT, INSERT ON pp_2 TO regress_rls_alice;
+
+SET SESSION AUTHORIZATION regress_rls_alice;
+INSERT INTO pp_1 VALUES (13, 2); --error
+INSERT INTO pp_1 VALUES (16, 6); --ok
+COPY pp_1 FROM STDIN WITH DELIMITER ','; --second record not ok
+16,6
+13,2
+\.
+
+INSERT INTO pp VALUES (1,11), (2,12);
+INSERT INTO pp values (5,11); --error
+INSERT INTO pp values (6,11); --error
+COPY pp FROM STDIN WITH DELIMITER ','; --error, second record not ok
+1,11
+5,11
+\.
+
+COPY pp FROM STDIN WITH DELIMITER ','; --error, second record not ok
+2,12
+6,11
+\.
+
+RESET SESSION AUTHORIZATION;
+CREATE POLICY p1_pp ON pp FOR INSERT WITH CHECK(id > 4);
+SET SESSION AUTHORIZATION regress_rls_alice;
+INSERT INTO pp VALUES (5, 15), (6, 16); --ok
+INSERT INTO pp VALUES (4, 14); --error
+COPY pp FROM STDIN WITH DELIMITER ','; --third record will result error
+5,15
+6,16
+4,14
+\.
+
+RESET SESSION AUTHORIZATION;
+DROP TABLE PP;
+
----- RECURSION ----
--
@@ -1656,8 +1715,10 @@ 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; --no error
+\.
-- Check COPY FROM as user with permissions and BYPASSRLS
SET SESSION AUTHORIZATION regress_rls_exempt_user;
@@ -2015,6 +2076,9 @@ ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
-- Works fine
INSERT INTO r1 VALUES (10), (20);
+COPY r1 FROM STDIN;
+10
+\.
-- No error, but no rows
TABLE r1;
--
2.34.1
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: COPY FROM with RLS
@ 2026-05-10 14:23 jian he <jian.universality@gmail.com>
parent: jian he <jian.universality@gmail.com>
0 siblings, 3 replies; 11+ messages in thread
From: jian he @ 2026-05-10 14:23 UTC (permalink / raw)
To: pgsql-hackers
On Tue, Oct 28, 2025 at 8:12 PM jian he <jian.universality@gmail.com> 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/
Attachments:
[text/x-patch] v2-0001-COPY-FROM-with-RLS.patch (29.2K, ../../CACJufxFdt0+R6C5ZByAK3DpMEXbb=3Hc0XyRTPvbjpc9sPt=gQ@mail.gmail.com/2-v2-0001-COPY-FROM-with-RLS.patch)
download | inline diff:
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: COPY FROM with RLS
@ 2026-05-25 04:50 solai v <solai.cdac@gmail.com>
parent: jian he <jian.universality@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: solai v @ 2026-05-25 04:50 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: pgsql-hackers
Hi jian,
I tested the v2 patch for COPY FROM with RLS.
Before applying the patch, I was able to reproduce the issue where
\copy bypassed the RLS INSERT policy and allowed invalid rows to be
inserted.
After applying the patch and retesting, rows violating the policy were
correctly rejected during \copy, while valid rows were inserted
successfully. The behavior is now consistent with normal INSERT.
The error context reporting during COPY was also clear and helpful
while testing.
Regards,
Solai
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re:Re: COPY FROM with RLS
@ 2026-06-29 10:13 Yilin Zhang <jiezhilove@126.com>
parent: jian he <jian.universality@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: Yilin Zhang @ 2026-06-29 10:13 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: pgsql-hackers
At 2026-05-10 14:23:26, "jian he" <jian.universality@gmail.com> wrote:
>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.
Hi, I've gone through your v2 patch and there’s an issue I’d like to point out.
In src/test/regress/expected/rowsecurity.out:
+-- 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"
This "missing data for column c" error is actually a COPY formatting error, not an RLS policy violation.
Best regards,
--
Yilin Zhang
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re:Re: COPY FROM with RLS
@ 2026-06-30 01:20 Yilin Zhang <jiezhilove@126.com>
parent: jian he <jian.universality@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: Yilin Zhang @ 2026-06-30 01:20 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: pgsql-hackers
At 2026-05-10 14:23:26, "jian he" <jian.universality@gmail.com> wrote:
>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.
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Re: COPY FROM with RLS
@ 2026-07-13 08:13 jian he <jian.universality@gmail.com>
parent: Yilin Zhang <jiezhilove@126.com>
0 siblings, 1 reply; 11+ messages in thread
From: jian he @ 2026-07-13 08:13 UTC (permalink / raw)
To: Yilin Zhang <jiezhilove@126.com>; +Cc: pgsql-hackers
On Tue, Jun 30, 2026 at 9:20 AM Yilin Zhang <jiezhilove@126.com> 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/
Attachments:
[text/x-patch] v3-0001-COPY-FROM-with-RLS.patch (33.2K, ../../CACJufxHU3tetCM+Qymxpj7-OLP8b6x3HJAnARadSrEAt64MHkg@mail.gmail.com/2-v3-0001-COPY-FROM-with-RLS.patch)
download | inline diff:
From b85199517a541918d311e81d3946fc9b9b17f965 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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-e9g0zEg@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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: COPY FROM with RLS
@ 2026-07-28 09:15 Yilin Zhang <jiezhilove@126.com>
parent: jian he <jian.universality@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Yilin Zhang @ 2026-07-28 09:15 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: pgsql-hackers
At 2026-07-13 16:13:58, "jian he" <jian.universality@gmail.com> wrote:
>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.
> */
Hi,
I have reviewed the latest version of the patch, LGTM.
Best regards,
--
Yilin Zhang
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: COPY FROM with RLS
@ 2026-08-27 01:04 dbryan.green@gmail.com
parent: Yilin Zhang <jiezhilove@126.com>
0 siblings, 1 reply; 11+ messages in thread
From: dbryan.green@gmail.com @ 2026-08-27 01:04 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org
On 7/28/26 4:15 AM, Yilin Zhang <jiezhilove@126.com> wrote:
> At 2026-07-13 16:13:58, "jian he" <jian.universality@gmail.com> wrote:
> > 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.
> > */
>
>
>
> Hi,
> I have reviewed the latest version of the patch, LGTM.
>
>
> Best regards,
>
> --
>
> Yilin Zhang
>
The RLS enforcement in v3 looks right. The WITH CHECK runs in the per-row
path for both the single and batched inserts, and row_security=off against a
forced policy errors the way INSERT does. The patch has two other problems:
it can crash the backend, and it changes how COPY treats rules.
CopyFrom pushes the error-context callback under a guard and pops it without
one. In master both are unconditional. The patch wraps the push at
copyfrom.c:1211 in "if (queryDesc == NULL)" but leaves the pop at 1574,
"error_context_stack = errcallback.previous". On the RLS path queryDesc is
set, so errcallback is never initialized, and the pop stores a garbage stack
value into error_context_stack. The next ereport in CopyFrom walks it. COPY
with ON_ERROR ignore and one bad row gets there through the "rows skipped"
NOTICE at 1580:
CREATE ROLE alice LOGIN;
CREATE TABLE t (id int, v text);
ALTER TABLE t ENABLE ROW LEVEL SECURITY;
CREATE POLICY p ON t USING (true) WITH CHECK (true);
GRANT INSERT ON t TO alice;
-- as alice, with a file whose second line has a non-integer id:
COPY t FROM '...' WITH (ON_ERROR ignore);
That crashes a non-assert backend and takes the cluster into recovery, so any
role with INSERT on the table its policy applies to can crash the server. The
same guard also drops the line-number context for every error on an RLS table,
not just policy violations. Registering the callback unconditionally and
scoping relname_only to the ExecWithCheckOptions call balances the push and
pop and limits the suppression to the policy violation.
Separately, getting the WITH CHECK expressions from a full rewrite of a dummy
INSERT runs the target's ON INSERT rules on the RLS path, so the same COPY
behaves differently depending on whether the table has RLS enabled:
COPY into non-RLS table COPY into RLS table
DO ALSO loads target, rule ignored ERROR (unsupported)
DO INSTEAD (uncond.) loads the named table loads the redirect target
DO INSTEAD (cond.) loads target, rule ignored ERROR (unsupported)
The length check rejects the rewrites that fan out past one query, but a
single unconditional DO INSTEAD passes it, so COPY into an RLS table lands the
rows in the rule's redirect target rather than the named table.
The remaining point is cosmetic. The query_string is only sourceText, but it
is built with raw %s and no quote-doubling, so it is malformed for a name like
foo"bar. quote_qualified_identifier() would build it correctly.
bg
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: COPY FROM with RLS
@ 2026-09-01 07:13 jian he <jian.universality@gmail.com>
parent: dbryan.green@gmail.com
0 siblings, 1 reply; 11+ messages in thread
From: jian he @ 2026-09-01 07:13 UTC (permalink / raw)
To: dbryan.green@gmail.com; +Cc: pgsql-hackers@lists.postgresql.org
On Thu, Aug 27, 2026 at 9:04 AM <dbryan.green@gmail.com> wrote:
>
> Separately, getting the WITH CHECK expressions from a full rewrite of a dummy
> INSERT runs the target's ON INSERT rules on the RLS path, so the same COPY
> behaves differently depending on whether the table has RLS enabled:
>
> COPY into non-RLS table COPY into RLS table
> DO ALSO loads target, rule ignored ERROR (unsupported)
> DO INSTEAD (uncond.) loads the named table loads the redirect target
> DO INSTEAD (cond.) loads target, rule ignored ERROR (unsupported)
>
> The length check rejects the rewrites that fan out past one query, but a
> single unconditional DO INSTEAD passes it, so COPY into an RLS table lands the
> rows in the rule's redirect target rather than the named table.
>
Portion of RLS node processing is inside the query rewriter (pg_rewrite_query),
pg_rewrite_query cannot be skipped.
So the only option is to disallow RLS on tables that have rules, IMHO.
I also made some other miscellaneous changes and addressed the other
issue you mentioned.
In the copy.sgml, I changed it as
<para>
<command>COPY FROM</command> will invoke any triggers and check
constraints on the destination table. However, it will not invoke rules.
+ If row-level security is enabled for the table, rules on the table are
+ not supported.
</para>
Below is the commit message:
Subject: [PATCH v15 1/1] COPY FROM with RLS
Previously, COPY FROM on a table with row-level security enabled failed with
"COPY FROM not supported with row-level security". Now it is fully supported:
the table's INSERT policies are enforced against each copied row, the same as
for INSERT.
To achieve this, CopyFrom() builds a dummy "INSERT INTO rel DEFAULT VALUES"
statement, runs it through parse analysis, the rewriter, and the planner, and
calls ExecutorStart() on the result. The plan is never executed; it only serves
to initialize the executor state, in particular the WITH CHECK OPTIONs derived
from the table's policies, which COPY then verifies against every row with
ExecWithCheckOptions().
Portion part of RLS node processing is inside the query rewriter, so this path
cannot skip query rewriting. Hence, unlike plain COPY FROM, which ignores rules
on the target table, COPY FROM with row-level security raises an error if the
table has any rule that would apply to an INSERT.
discussion: https://postgr.es/m/CACJufxFbmnoa5O-vL43DPTCGt6oagY4dXgKxy=rcD9-e9g0zEg@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6178
---------------------------
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v15-0001-COPY-FROM-with-RLS.patch (34.4K, ../../CACJufxEPnQ_wx=-6P6h=haxCO3CHeAijV7eeq3keUZ_Tve-1yw@mail.gmail.com/2-v15-0001-COPY-FROM-with-RLS.patch)
download | inline diff:
From 5de6a8fe8eef0a9c59dfcdd61b3e5a8c3023a5a4 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 1 Sep 2026 15:05:27 +0800
Subject: [PATCH v15 1/1] COPY FROM with RLS
Previously, COPY FROM on a table with row-level security enabled failed with
"COPY FROM not supported with row-level security". Now it is fully supported:
the table's INSERT policies are enforced against each copied row, the same as
for INSERT.
To achieve this, CopyFrom() builds a dummy "INSERT INTO rel DEFAULT VALUES"
statement, runs it through parse analysis, the rewriter, and the planner, and
calls ExecutorStart() on the result. The plan is never executed; it only serves
to initialize the executor state, in particular the WITH CHECK OPTIONs derived
from the table's policies, which COPY then verifies against every row with
ExecWithCheckOptions().
Portion part of RLS node processing is inside the query rewriter, so this path
cannot skip query rewriting. Hence, unlike plain COPY FROM, which ignores rules
on the target table, COPY FROM with row-level security raises an error if the
table has any rule that would apply to an INSERT.
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 | 9 +-
doc/src/sgml/ref/create_policy.sgml | 2 +-
src/backend/commands/copy.c | 10 +-
src/backend/commands/copyfrom.c | 221 +++++++++++++++++-----
src/test/regress/expected/rowsecurity.out | 80 +++++++-
src/test/regress/sql/rowsecurity.sql | 99 +++++++++-
6 files changed, 355 insertions(+), 66 deletions(-)
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index b23433b2c41..4f91fa358ec 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>
@@ -670,6 +669,8 @@ COPY <replaceable class="parameter">count</replaceable>
<para>
<command>COPY FROM</command> will invoke any triggers and check
constraints on the destination table. However, it will not invoke rules.
+ If row-level security is enabled for the table, rules on the table are
+ not supported.
</para>
<para>
diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml
index 0a1699185c9..3602bad8596 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 3782db171be..5d6d5b856a8 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -40,16 +40,19 @@
#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"
#include "rewrite/rewriteHandler.h"
#include "storage/fd.h"
#include "tcop/tcopprot.h"
+#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/portal.h"
#include "utils/rel.h"
+#include "utils/rls.h"
#include "utils/snapmgr.h"
#include "utils/typcache.h"
@@ -783,7 +786,7 @@ CopyFrom(CopyFromState cstate)
ResultRelInfo *resultRelInfo;
ResultRelInfo *target_resultRelInfo;
ResultRelInfo *prevResultRelInfo = NULL;
- EState *estate = CreateExecutorState(); /* for ExecConstraints() */
+ EState *estate;
ModifyTableState *mtstate;
ExprContext *econtext;
TupleTableSlot *singleslot = NULL;
@@ -801,6 +804,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 +914,121 @@ 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;
+ char *namespace;
+ namespace = get_namespace_name(RelationGetNamespace(cstate->rel));
+
+ from = makeRangeVar(namespace,
+ pstrdup(RelationGetRelationName(cstate->rel)),
+ -1);
+ from->inh = false;
+
+ 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 DEFAULT VALUES",
+ quote_qualified_identifier(namespace,
+ RelationGetRelationName(cstate->rel)));
+
+ /*
+ * Run parse analysis and rewrite. RLS is applied during query
+ * rewrite, so it cannot be skipped here. Note this also acquires
+ * sufficient locks on the table(s) involved.
+ */
+ rewritten = pg_analyze_and_rewrite_fixedparams(raw_query,
+ query_string,
+ NULL,
+ 0,
+ NULL);
+ if (rewritten == NIL)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DO INSTEAD NOTHING rules are not supported for COPY FROM with row-level security"));
+ else if (list_length(rewritten) > 1)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("rules with additional commands are not supported for COPY FROM with row-level security"));
+
+ query = linitial_node(Query, rewritten);
+
+ if (query->querySource != QSRC_ORIGINAL)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DO INSTEAD rules are not supported for COPY FROM with row-level security"));
+
+ /* 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,
+ None_Receiver,
+ NULL,
+ NULL,
+ 0);
+
+ /*
+ * Call ExecutorStart to build the executor state; the plan is never
+ * run, as COPY supplies the rows itself. This initializes the
+ * ResultRelInfo, including the ri_WithCheckOptions needed by
+ * ExecWithCheckOptions() to check RLS policies against copied rows.
+ */
+ ExecutorStart(queryDesc, 0);
+
+ estate = queryDesc->estate;
+ mtstate = (ModifyTableState *) queryDesc->planstate;
+ resultRelInfo = target_resultRelInfo = mtstate->resultRelInfo;
+ }
+ else
+ {
+ estate = CreateExecutorState(); /* for ExecConstraints() */
+
+ /* 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 +1051,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 +1061,20 @@ 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);
+ else
+ cstate->transition_capture = mtstate->mt_transition_capture;
/*
* 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 +1446,20 @@ CopyFrom(CopyFromState cstate)
ExecComputeStoredGenerated(resultRelInfo, estate, myslot,
CMD_INSERT);
+ /*
+ * We suppress error context information other than the
+ * relation name, if row level security policy check fails.
+ */
+ Assert(!cstate->relname_only);
+ cstate->relname_only = true;
+
+ if (resultRelInfo->ri_WithCheckOptions != NIL)
+ ExecWithCheckOptions(WCO_RLS_INSERT_CHECK, resultRelInfo,
+ myslot, estate);
+
+ /* reset relname_only */
+ cstate->relname_only = false;
+
/*
* If the target is a plain table, check the constraints of
* the tuple.
@@ -1490,30 +1599,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 356f118a366..19ff0e9519f 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -138,6 +138,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 *;
@@ -708,9 +712,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
-- 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
-- only owner can change policies
ALTER POLICY p1 ON document USING (true); --fail
ERROR: must be owner of table document
@@ -829,6 +839,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, COPY is equivalent to INSERT
+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
-----+-----+--------+---------+--------
@@ -837,6 +850,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
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
@@ -1445,15 +1461,28 @@ 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"
+CONTEXT: COPY 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"
+CONTEXT: COPY 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"
+CONTEXT: COPY 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
@@ -1489,6 +1518,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
-- 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
@@ -1709,6 +1741,14 @@ 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
+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;
@@ -1788,6 +1828,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));
@@ -1799,6 +1840,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);
@@ -2302,6 +2345,7 @@ NOTICE: f_leak => 4a44dc15364204a80fe80e9039455cc1
10 | 4a44dc15364204a80fe80e9039455cc1
(5 rows)
+-- COPY FROM is not supported for views, so no COPY FROM tests are needed 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
@@ -4221,9 +4265,10 @@ 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"
+CONTEXT: COPY copy_t
-- Check COPY FROM as user with permissions and BYPASSRLS
SET SESSION AUTHORIZATION regress_rls_exempt_user;
SET row_security TO ON;
@@ -4240,6 +4285,22 @@ 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"
+CONTEXT: COPY copy_t
+COPY copy_t FROM STDIN WITH (DELIMITER ',', ON_ERROR IGNORE); -- ok
+NOTICE: 1 row was skipped due to data type incompatibility
+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);
@@ -4543,6 +4604,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
UPDATE r2 SET a = 2 RETURNING *; -- Updates nothing
a
---
@@ -4611,6 +4675,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
-- No error (unable to see any rows to update)
UPDATE r1 SET a = 1;
TABLE r1;
@@ -4739,6 +4806,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
@@ -4820,9 +4888,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
-- 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
-- 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 99eaba28f5a..415f1f54c46 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -117,6 +117,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;
@@ -334,8 +339,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
@@ -379,10 +391,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
@@ -569,14 +587,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
@@ -590,6 +626,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
@@ -651,6 +690,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;
@@ -733,6 +783,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%'));
@@ -746,6 +797,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;
@@ -877,6 +931,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 is not supported for views, so no COPY FROM tests are needed 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
@@ -1888,7 +1943,11 @@ 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
@@ -1914,6 +1973,28 @@ 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 ',', ON_ERROR IGNORE); -- ok
+14,abc
+13,abc
+a,abc
+\.
+RESET SESSION AUTHORIZATION;
+DROP TABLE copy_t;
+
+
-- Check WHERE CURRENT OF
SET SESSION AUTHORIZATION regress_rls_alice;
@@ -2092,6 +2173,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
@@ -2123,6 +2207,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;
@@ -2249,6 +2337,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;
@@ -2326,9 +2417,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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: COPY FROM with RLS
@ 2026-09-10 17:21 Bryan Green <dbryan.green@gmail.com>
parent: jian he <jian.universality@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Bryan Green @ 2026-09-10 17:21 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
On 9/1/2026 2:13 AM, jian he wrote:
> On Thu, Aug 27, 2026 at 9:04 AM <dbryan.green@gmail.com> wrote:
>>
>> Separately, getting the WITH CHECK expressions from a full rewrite of a dummy
>> INSERT runs the target's ON INSERT rules on the RLS path, so the same COPY
>> behaves differently depending on whether the table has RLS enabled:
>>
>> COPY into non-RLS table COPY into RLS table
>> DO ALSO loads target, rule ignored ERROR (unsupported)
>> DO INSTEAD (uncond.) loads the named table loads the redirect target
>> DO INSTEAD (cond.) loads target, rule ignored ERROR (unsupported)
>>
>> The length check rejects the rewrites that fan out past one query, but a
>> single unconditional DO INSTEAD passes it, so COPY into an RLS table lands the
>> rows in the rule's redirect target rather than the named table.
>>
>
> Portion of RLS node processing is inside the query rewriter (pg_rewrite_query),
> pg_rewrite_query cannot be skipped.
> So the only option is to disallow RLS on tables that have rules, IMHO.
>
> I also made some other miscellaneous changes and addressed the other
> issue you mentioned.
>
> In the copy.sgml, I changed it as
> <para>
> <command>COPY FROM</command> will invoke any triggers and check
> constraints on the destination table. However, it will not invoke rules.
> + If row-level security is enabled for the table, rules on the table are
> + not supported.
> </para>
>
> Below is the commit message:
>
> Subject: [PATCH v15 1/1] COPY FROM with RLS
>
> Previously, COPY FROM on a table with row-level security enabled failed with
> "COPY FROM not supported with row-level security". Now it is fully supported:
> the table's INSERT policies are enforced against each copied row, the same as
> for INSERT.
>
> To achieve this, CopyFrom() builds a dummy "INSERT INTO rel DEFAULT VALUES"
> statement, runs it through parse analysis, the rewriter, and the planner, and
> calls ExecutorStart() on the result. The plan is never executed; it only serves
> to initialize the executor state, in particular the WITH CHECK OPTIONs derived
> from the table's policies, which COPY then verifies against every row with
> ExecWithCheckOptions().
>
> Portion part of RLS node processing is inside the query rewriter, so this path
> cannot skip query rewriting. Hence, unlike plain COPY FROM, which ignores rules
> on the target table, COPY FROM with row-level security raises an error if the
> table has any rule that would apply to an INSERT.
>
> discussion: https://postgr.es/m/CACJufxFbmnoa5O-vL43DPTCGt6oagY4dXgKxy=rcD9-e9g0zEg@mail.gmail.com
> commitfest: https://commitfest.postgresql.org/patch/6178
> ---------------------------
>
>
>
> --
> jian
> https://www.enterprisedb.com/
v15 checks out on the v3 items. Crash is gone on both an assert and a
non-assert build, context is right, the rule cases error instead of
redirecting, and quoting is fixed.
The new one is the result of a forgotten guard. The WCO quals aren't in
the volatile test that forces CIM_SINGLE, so under multi-insert a
self-referential policy doesn't see the still-buffered rows and accepts
ones a plain INSERT would reject.
CREATE FUNCTION under_quota(o text) RETURNS bool LANGUAGE plpgsql VOLATILE
AS $$ BEGIN RETURN (SELECT count(*) FROM q WHERE owner = o) < 3; END $$;
CREATE POLICY ins ON q FOR INSERT WITH CHECK (under_quota(owner));
INSERT of ten rows stops at the fourth. COPY takes all ten. A no-op
BEFORE ROW trigger makes COPY stop at four as well, so it's the
batching. Probably wants ri_WithCheckOptions in that CIM_SINGLE test,
or just forced under RLS.
The wording "any rule that would apply to an INSERT" isn't quite right
either. A conditional DO INSTEAD NOTHING stays one QSRC_ORIGINAL query
and slips the three screens, so COPY inserts the row that would have
dropped under INSERT with the rule.
Also, relname_only wraps the whole ExecWithCheckOptions call, so a
non-violation error from a policy expression loses the "line x". This
is because relname_only is true for anything the call raises, including
errors that are not due to policy rejection. Maybe set the flag only
around the violation report instead of the whole evaluation?
--
Bryan Green
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: COPY FROM with RLS
@ 2026-09-23 19:05 jian he <jian.universality@gmail.com>
parent: Bryan Green <dbryan.green@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: jian he @ 2026-09-23 19:05 UTC (permalink / raw)
To: Bryan Green <dbryan.green@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
On Fri, Sep 11, 2026 at 1:21 AM Bryan Green <dbryan.green@gmail.com> wrote:
>
> v15 checks out on the v3 items. Crash is gone on both an assert and a
> non-assert build, context is right, the rule cases error instead of
> redirecting, and quoting is fixed.
>
> The new one is the result of a forgotten guard. The WCO quals aren't in
> the volatile test that forces CIM_SINGLE, so under multi-insert a
> self-referential policy doesn't see the still-buffered rows and accepts
> ones a plain INSERT would reject.
>
> CREATE FUNCTION under_quota(o text) RETURNS bool LANGUAGE plpgsql VOLATILE
> AS $$ BEGIN RETURN (SELECT count(*) FROM q WHERE owner = o) < 3; END $$;
> CREATE POLICY ins ON q FOR INSERT WITH CHECK (under_quota(owner));
>
> INSERT of ten rows stops at the fourth. COPY takes all ten. A no-op
> BEFORE ROW trigger makes COPY stop at four as well, so it's the
> batching. Probably wants ri_WithCheckOptions in that CIM_SINGLE test,
> or just forced under RLS.
>
+ foreach_node(WithCheckOption, wco, resultRelInfo->ri_WithCheckOptions)
+ {
+ if (contain_subplans(wco->qual) ||
+ contain_volatile_functions(wco->qual))
+ {
+ volatile_wco = true;
+ break;
+ }
+ }
Make volatile expression and sub-select fall back to CIM_SINGLE should be fine.
> The wording "any rule that would apply to an INSERT" isn't quite right
> either. A conditional DO INSTEAD NOTHING stays one QSRC_ORIGINAL query
> and slips the three screens, so COPY inserts the row that would have
> dropped under INSERT with the rule.
>
We can use RelationData->rd_rules to check if a relation has rules
defined on it or not.
> Also, relname_only wraps the whole ExecWithCheckOptions call, so a
> non-violation error from a policy expression loses the "line x". This
> is because relname_only is true for anything the call raises, including
> errors that are not due to policy rejection. Maybe set the flag only
> around the violation report instead of the whole evaluation?
>
I am not sure about "non-violation error from a policy expression".
Currently check constraint violations produce an error line, but RLS
violations do not.
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v16-0001-COPY-FROM-with-RLS.patch (35.3K, ../../CACJufxEX3GD9XaG63M76fr-_QTt9ezVeSS3XvW=ZGhgr7K9LDg@mail.gmail.com/2-v16-0001-COPY-FROM-with-RLS.patch)
download | inline diff:
From a7d2b7562242ae32dd46389da71a944af25e0c00 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 24 Sep 2026 02:45:19 +0800
Subject: [PATCH v16 1/1] COPY FROM with RLS
Previously, COPY FROM on a table with row-level security enabled failed with
"COPY FROM not supported with row-level security". Now it is fully supported:
the table's INSERT policies are enforced against each copied row, the same as
for INSERT.
To achieve this, CopyFrom() builds a dummy "INSERT INTO rel DEFAULT VALUES"
statement, runs it through parse analysis, rewriter, planner, and
calls ExecutorStart() on the result. The plan is never executed; it only serves
to initialize the executor state, in particular the WITH CHECK OPTIONs derived
from the table's policies, which COPY then verifies against every row with
ExecWithCheckOptions().
Portion part of RLS node processing is inside the query rewriter, so this path
cannot skip query rewriting. Hence, unlike plain COPY FROM, which ignores rules
on the target table, COPY FROM with row-level security raises an error if the
table has any rule that would apply to an INSERT.
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 | 9 +-
doc/src/sgml/ref/create_policy.sgml | 2 +-
src/backend/commands/copy.c | 10 +-
src/backend/commands/copyfrom.c | 241 +++++++++++++++++-----
src/test/regress/expected/rowsecurity.out | 81 +++++++-
src/test/regress/sql/rowsecurity.sql | 97 ++++++++-
6 files changed, 374 insertions(+), 66 deletions(-)
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index b23433b2c41..4f91fa358ec 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>
@@ -670,6 +669,8 @@ COPY <replaceable class="parameter">count</replaceable>
<para>
<command>COPY FROM</command> will invoke any triggers and check
constraints on the destination table. However, it will not invoke rules.
+ If row-level security is enabled for the table, rules on the table are
+ not supported.
</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 746b4fdf6c3..339a9e8dd95 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -40,16 +40,20 @@
#include "foreign/fdwapi.h"
#include "mb/pg_wchar.h"
#include "miscadmin.h"
+#include "nodes/makefuncs.h"
#include "nodes/miscnodes.h"
+#include "optimizer/clauses.h"
#include "optimizer/optimizer.h"
#include "pgstat.h"
#include "rewrite/rewriteHandler.h"
#include "storage/fd.h"
#include "tcop/tcopprot.h"
+#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/portal.h"
#include "utils/rel.h"
+#include "utils/rls.h"
#include "utils/snapmgr.h"
#include "utils/typcache.h"
@@ -783,7 +787,7 @@ CopyFrom(CopyFromState cstate)
ResultRelInfo *resultRelInfo;
ResultRelInfo *target_resultRelInfo;
ResultRelInfo *prevResultRelInfo = NULL;
- EState *estate = CreateExecutorState(); /* for ExecConstraints() */
+ EState *estate;
ModifyTableState *mtstate;
ExprContext *econtext;
TupleTableSlot *singleslot = NULL;
@@ -801,6 +805,8 @@ CopyFrom(CopyFromState cstate)
bool has_before_insert_row_trig;
bool has_instead_insert_row_trig;
bool leafpart_use_multi_insert = false;
+ QueryDesc *queryDesc = NULL;
+ bool volatile_wco = false;
Assert(cstate->rel);
Assert(list_length(cstate->range_table) == 1);
@@ -910,33 +916,119 @@ 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;
+ char *namespace;
+
+ if (cstate->rel->rd_rules != NULL)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot copy to table \"%s\"",
+ RelationGetRelationName(cstate->rel)),
+ errdetail("COPY FROM is not supported on tables with rules when row-level security is enabled."),
+ errhint("Use INSERT statements instead."));
+
+ namespace = get_namespace_name(RelationGetNamespace(cstate->rel));
+
+ from = makeRangeVar(namespace,
+ pstrdup(RelationGetRelationName(cstate->rel)),
+ -1);
+ from->inh = false;
+
+ 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 DEFAULT VALUES",
+ quote_qualified_identifier(namespace,
+ RelationGetRelationName(cstate->rel)));
+
+ /*
+ * Run parse analysis and rewrite. RLS is also processed during query
+ * rewrite.
+ */
+ rewritten = pg_analyze_and_rewrite_fixedparams(raw_query,
+ query_string,
+ NULL,
+ 0,
+ NULL);
+ Assert(list_length(rewritten) == 1);
+
+ query = linitial_node(Query, rewritten);
+
+ Assert(query->querySource == QSRC_ORIGINAL);
+
+ /* 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,
+ None_Receiver,
+ NULL,
+ NULL,
+ 0);
+
+ /*
+ * Call ExecutorStart to build the executor state; the plan is never
+ * run, as COPY supplies the rows itself. This initializes the
+ * ResultRelInfo, including the ri_WithCheckOptions needed by
+ * ExecWithCheckOptions() to check RLS policies against copied rows.
+ */
+ ExecutorStart(queryDesc, 0);
+
+ estate = queryDesc->estate;
+ mtstate = (ModifyTableState *) queryDesc->planstate;
+ resultRelInfo = target_resultRelInfo = mtstate->resultRelInfo;
+ }
+ else
+ {
+ estate = CreateExecutorState(); /* for ExecConstraints() */
+
+ /* 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,8 +1051,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,22 +1061,36 @@ 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);
+ else
+ cstate->transition_capture = mtstate->mt_transition_capture;
/*
* 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)
cstate->qualexpr = ExecInitQual(castNode(List, cstate->whereClause),
&mtstate->ps);
+ foreach_node(WithCheckOption, wco, resultRelInfo->ri_WithCheckOptions)
+ {
+ if (contain_subplans(wco->qual) ||
+ contain_volatile_functions(wco->qual))
+ {
+ volatile_wco = true;
+ break;
+ }
+ }
+
/*
* It's generally more efficient to prepare a bunch of tuples for
* insertion, and insert them in one
@@ -1052,6 +1157,16 @@ CopyFrom(CopyFromState cstate)
*/
insertMethod = CIM_SINGLE;
}
+ else if (queryDesc != NULL && volatile_wco)
+ {
+ /*
+ * Can't support multi-inserts if the RLS quals contain any volatile
+ * function expressions or any sub-selects. Similarly to the trigger
+ * case above, such quals may query the table we're inserting into. A
+ * sub-select can do that even without volatile functions.
+ */
+ insertMethod = CIM_SINGLE;
+ }
else
{
/*
@@ -1351,6 +1466,20 @@ CopyFrom(CopyFromState cstate)
ExecComputeStoredGenerated(resultRelInfo, estate, myslot,
CMD_INSERT);
+ /*
+ * We suppress error context information other than the
+ * relation name, if row level security policy check fails.
+ */
+ Assert(!cstate->relname_only);
+ cstate->relname_only = true;
+
+ if (resultRelInfo->ri_WithCheckOptions != NIL)
+ ExecWithCheckOptions(WCO_RLS_INSERT_CHECK, resultRelInfo,
+ myslot, estate);
+
+ /* reset relname_only */
+ cstate->relname_only = false;
+
/*
* If the target is a plain table, check the constraints of
* the tuple.
@@ -1490,30 +1619,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 00b11544589..240ba5389a5 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 *;
@@ -671,9 +675,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
-- 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
-- only owner can change policies
ALTER POLICY p1 ON document USING (true); --fail
ERROR: must be owner of table document
@@ -792,6 +802,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, COPY is equivalent to INSERT
+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
-----+-----+--------+---------+--------
@@ -800,6 +813,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
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
@@ -1408,15 +1424,28 @@ 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"
+CONTEXT: COPY 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"
+CONTEXT: COPY 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"
+CONTEXT: COPY 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
@@ -1452,6 +1481,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
-- 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
@@ -1672,6 +1704,14 @@ 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
+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;
@@ -1751,6 +1791,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));
@@ -1762,6 +1803,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);
@@ -2265,6 +2308,7 @@ NOTICE: f_leak => 4a44dc15364204a80fe80e9039455cc1
10 | 4a44dc15364204a80fe80e9039455cc1
(5 rows)
+-- COPY FROM is not supported for views, so no COPY FROM tests are needed 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
@@ -4184,9 +4228,10 @@ 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"
+CONTEXT: COPY copy_t
-- Check COPY FROM as user with permissions and BYPASSRLS
SET SESSION AUTHORIZATION regress_rls_exempt_user;
SET row_security TO ON;
@@ -4203,6 +4248,23 @@ RESET SESSION AUTHORIZATION;
DROP TABLE copy_t;
DROP TABLE copy_rel_to CASCADE;
NOTICE: drop cascades to table copy_rel_to_child
+SET SESSION AUTHORIZATION regress_rls_alice;
+CREATE TABLE copy_t (owner text);
+CREATE FUNCTION under_quota(o text) RETURNS bool LANGUAGE plpgsql VOLATILE
+AS $$ BEGIN RETURN (SELECT count(*) FROM copy_t WHERE owner = o) < 2 AND; END $$;
+CREATE POLICY p1 ON copy_t FOR SELECT USING (true);
+CREATE POLICY p2 ON copy_t FOR INSERT WITH CHECK (under_quota(owner));
+ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY;
+ALTER TABLE copy_t FORCE ROW LEVEL SECURITY;
+GRANT SELECT, INSERT ON copy_t to regress_rls_bob;
+SET SESSION AUTHORIZATION regress_rls_bob;
+COPY copy_t FROM STDIN; -- error
+ERROR: new row violates row-level security policy for table "copy_t"
+CONTEXT: COPY copy_t
+SET SESSION AUTHORIZATION regress_rls_alice;
+DROP TABLE copy_t;
+DROP FUNCTION under_quota(text);
+RESET SESSION AUTHORIZATION;
-- Check WHERE CURRENT OF
SET SESSION AUTHORIZATION regress_rls_alice;
CREATE TABLE current_check (currentid int, payload text, rlsuser text);
@@ -4506,6 +4568,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
UPDATE r2 SET a = 2 RETURNING *; -- Updates nothing
a
---
@@ -4574,6 +4639,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
-- No error (unable to see any rows to update)
UPDATE r1 SET a = 1;
TABLE r1;
@@ -4702,6 +4770,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
@@ -4783,9 +4852,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
-- 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
-- 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 9e987b4d927..0f943c35d37 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;
@@ -303,8 +308,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
@@ -348,10 +360,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
@@ -538,14 +556,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
@@ -559,6 +595,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
@@ -620,6 +659,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;
@@ -702,6 +752,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%'));
@@ -715,6 +766,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;
@@ -846,6 +900,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 is not supported for views, so no COPY FROM tests are needed 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
@@ -1857,7 +1912,11 @@ 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
@@ -1883,6 +1942,26 @@ RESET SESSION AUTHORIZATION;
DROP TABLE copy_t;
DROP TABLE copy_rel_to CASCADE;
+SET SESSION AUTHORIZATION regress_rls_alice;
+CREATE TABLE copy_t (owner text);
+CREATE FUNCTION under_quota(o text) RETURNS bool LANGUAGE plpgsql VOLATILE
+AS $$ BEGIN RETURN (SELECT count(*) FROM copy_t WHERE owner = o) < 2 AND; END $$;
+CREATE POLICY p1 ON copy_t FOR SELECT USING (true);
+CREATE POLICY p2 ON copy_t FOR INSERT WITH CHECK (under_quota(owner));
+ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY;
+ALTER TABLE copy_t FORCE ROW LEVEL SECURITY;
+GRANT SELECT, INSERT ON copy_t to regress_rls_bob;
+SET SESSION AUTHORIZATION regress_rls_bob;
+COPY copy_t FROM STDIN; -- error
+regress_rls_bob
+regress_rls_bob
+regress_rls_bob
+\.
+SET SESSION AUTHORIZATION regress_rls_alice;
+DROP TABLE copy_t;
+DROP FUNCTION under_quota(text);
+RESET SESSION AUTHORIZATION;
+
-- Check WHERE CURRENT OF
SET SESSION AUTHORIZATION regress_rls_alice;
@@ -2061,6 +2140,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
@@ -2092,6 +2174,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;
@@ -2218,6 +2304,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;
@@ -2295,9 +2384,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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2026-09-23 19:05 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-28 12:12 COPY FROM with RLS jian he <jian.universality@gmail.com>
2026-05-10 14:23 ` jian he <jian.universality@gmail.com>
2026-05-25 04:50 ` solai v <solai.cdac@gmail.com>
2026-06-29 10:13 ` Yilin Zhang <jiezhilove@126.com>
2026-06-30 01:20 ` Yilin Zhang <jiezhilove@126.com>
2026-07-13 08:13 ` jian he <jian.universality@gmail.com>
2026-07-28 09:15 ` Yilin Zhang <jiezhilove@126.com>
2026-08-27 01:04 ` dbryan.green@gmail.com
2026-09-01 07:13 ` jian he <jian.universality@gmail.com>
2026-09-10 17:21 ` Bryan Green <dbryan.green@gmail.com>
2026-09-23 19:05 ` jian he <jian.universality@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox