COPY FROM with RLS

Started by jian heabout 1 year ago13 messageshackers
Jump to latest
#1jian he
jian.universality@gmail.com

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:

v1-0001-COPY-FROM-with-RLS.patchtext/x-patch; charset=UTF-8; name=v1-0001-COPY-FROM-with-RLS.patchDownload+238-15
#2jian he
jian.universality@gmail.com
In reply to: jian he (#1)
Re: COPY FROM with RLS

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:

t52551_2
v2-0001-COPY-FROM-with-RLS.patchtext/x-patch; charset=US-ASCII; name=v2-0001-COPY-FROM-with-RLS.patchDownload+276-65
#3solai v
solai.cdac@gmail.com
In reply to: jian he (#2)
Re: COPY FROM with RLS

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

#4Yilin Zhang
jiezhilove@126.com
In reply to: jian he (#2)
Re:Re: COPY FROM with RLS

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

#5Yilin Zhang
jiezhilove@126.com
In reply to: jian he (#2)
Re:Re: COPY FROM with RLS

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

#6jian he
jian.universality@gmail.com
In reply to: Yilin Zhang (#5)
Re: Re: COPY FROM with RLS

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:

t52551_6
v3-0001-COPY-FROM-with-RLS.patchtext/x-patch; charset=US-ASCII; name=v3-0001-COPY-FROM-with-RLS.patchDownload+340-70
#7Yilin Zhang
jiezhilove@126.com
In reply to: jian he (#6)
Re: COPY FROM with RLS

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

#8Bryan Green
dbryan.green@gmail.com
In reply to: Yilin Zhang (#7)
Re: COPY FROM with RLS

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

#9jian he
jian.universality@gmail.com
In reply to: Bryan Green (#8)
Re: COPY FROM with RLS

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: /messages/by-id/CACJufxFbmnoa5O-vL43DPTCGt6oagY4dXgKxy=rcD9-e9g0zEg@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6178
---------------------------

--
jian
https://www.enterprisedb.com/

Attachments:

t52551_9
v15-0001-COPY-FROM-with-RLS.patchtext/x-patch; charset=US-ASCII; name=v15-0001-COPY-FROM-with-RLS.patchDownload+355-67
#10Bryan Green
dbryan.green@gmail.com
In reply to: jian he (#9)
Re: COPY FROM with RLS

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: /messages/by-id/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

#11jian he
jian.universality@gmail.com
In reply to: Bryan Green (#10)
Re: COPY FROM with RLS

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:

t52551_11
v16-0001-COPY-FROM-with-RLS.patchtext/x-patch; charset=US-ASCII; name=v16-0001-COPY-FROM-with-RLS.patchDownload+374-67
#12Kirill Reshke
reshkekirill@gmail.com
In reply to: jian he (#11)
Re: COPY FROM with RLS

On Thu, 24 Sept 2026 at 00:06, jian he <jian.universality@gmail.com> wrote:

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/

Hi!
Looks like Bryan point about losing line num is still present in v16?

reshke=# -- run as superuser
CREATE ROLE u1 LOGIN;
CREATE TABLE t7 (id int, v text);
CREATE FUNCTION t7_div(id int) RETURNS int LANGUAGE sql STABLE
AS $$ SELECT 1/(id-3) $$;
ALTER TABLE t7 ENABLE ROW LEVEL SECURITY;
CREATE POLICY t7_p ON t7 FOR INSERT WITH CHECK (t7_div(id) IS NOT NULL);
GRANT INSERT ON t7 TO u1;
GRANT EXECUTE ON FUNCTION t7_div(int) TO u1;

SET SESSION AUTHORIZATION u1;
COPY t7 FROM STDIN WITH (DELIMITER ',');

CREATE ROLE
CREATE TABLE
ERROR: function "t7_div" already exists with same argument types
ALTER TABLE
CREATE POLICY
GRANT
GRANT
SET
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.

1,a
3,boom
\.

ERROR: division by zero
CONTEXT: COPY t7

Line number is missing

Also looks like the rule guard is broader than needed.

RLS table with a single ON DELETE rule -> COPY FROM rejected

CREATE TABLE t (id int);
ALTER TABLE t ENABLE ROW LEVEL SECURITY;
CREATE POLICY p ON t FOR INSERT WITH CHECK (id > 0);
GRANT INSERT ON t TO u1;
CREATE RULE r_del AS ON DELETE TO t DO INSTEAD NOTHING; -- never
applies to COPY

SET SESSION AUTHORIZATION u1;
COPY t FROM STDIN;
1
\.

ERROR: cannot copy to table "t"
DETAIL: COPY FROM is not supported on tables with rules when
row-level security is enabled.
HINT: Use INSERT statements instead.

Without RLS it works

db3=# CREATE TABLE t (id int);
CREATE TABLE
db3=# CREATE RULE r_del AS ON DELETE TO t DO INSTEAD NOTHING;
CREATE RULE
db3=# GRANT INSERT ON t TO u1;
GRANT
db3=# SET SESSION AUTHORIZATION u1;
COPY t FROM STDIN;
SET
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.

1
\.

COPY 1

Is there reason for such restriction?

--
Best regards,
Kirill Reshke

#13Joao Rito
joaovictor.rito@gmail.com
In reply to: Kirill Reshke (#12)
Re: COPY FROM with RLS

Hi Jian,

I reviewed v16 and ran some tests. I tried to look at areas that were not
covered in the thread yet.

Setup: master at 952bab8b74, Debian bookworm container, built with
--enable-cassert --enable-debug. v16 applies with small offsets, the build
has no warnings, and make check passes (243 tests). I ran the same probe
script on master and on the patched build. As expected, on master every
non-owner COPY FROM below fails with "COPY FROM not supported with row-level
security".

Things that work the same as INSERT:
- Triggers: with BEFORE/AFTER ROW, BEFORE STATEMENT and an AFTER STATEMENT
trigger with REFERENCING NEW TABLE, a 3-row COPY fires each row trigger 3
times, each statement trigger once, and the transition table has 3 rows.
INSERT gives the same counts.
- A BEFORE ROW trigger that changes the owner column so the row fails the
policy: COPY fails with the RLS error, same as INSERT. So the check runs
on the row after the triggers.
- Partitioned table with a permissive policy on the parent and a stricter
INSERT policy on the leaf: COPY into the parent succeeds and COPY into the
leaf fails, the same as INSERT through the parent and the leaf.
- FORCE ROW LEVEL SECURITY: the owner's COPY is checked. A BYPASSRLS role is
not checked.
- Column privileges: with only INSERT (a), COPY t (a) works and COPY t (a,
secret) fails with "permission denied", same as master. So the DEFAULT
VALUES statement does not need extra privileges.
- COPY ... WHERE: a row removed by WHERE that would fail the policy does not
raise an error.
- A deferred constraint trigger fires at commit.

1. The internal INSERT is visible to monitoring extensions

With pg_stat_statements.track = 'all', one COPY FROM on an RLS table leaves
two entries:

toplevel | calls | rows | query
----------+-------+------+-------------------------------------
t | 1 | 3 | COPY t (a) FROM STDIN
f | 1 | 0 | INSERT INTO public.t DEFAULT VALUES

With auto_explain.log_nested_statements = on, the server log also gets a
plan for it:

LOG: duration: 0.000 ms plan:
Query Text: INSERT INTO public.t DEFAULT VALUES
Insert on t (cost=0.00..0.01 rows=0 width=0)
-> Result (cost=0.00..0.01 rows=1 width=36)

This comes from running the query through
ExecutorStart/ExecutorFinish/ExecutorEnd, which call the executor hooks.
With track = 'top' only the COPY entry is there. It is not wrong, but a DBA
who sees "INSERT INTO public.t DEFAULT VALUES" with rows = 0 may think some
application ran it. Maybe this is fine and only needs a comment in the code.
Or maybe the query should not get a queryId, so pg_stat_statements skips it.
I am not sure which is better, so I wanted to mention it.

2. ON_ERROR ignore and the missing line number

COPY ... WITH (ON_ERROR ignore) skips a bad input value, but an RLS
violation still aborts the whole COPY. I think that is right, because
ON_ERROR only covers soft input errors. But the error only shows "CONTEXT:
COPY t", without the line number, so with a large file there is no easy way
to find the row that failed. This is the same point Kirill raised for errors
inside a policy function. I think it applies to the violation error too, and
it is probably the most common error users will see from this feature.

3. Performance

1M rows from a file, fsync = off, a simple stable policy (owner =
current_user), times for one COPY:
- table owner, RLS not applied: 329 ms
- non-owner, RLS applied: 440 ms
- non-owner, INSERT ... SELECT of the same rows: 1170 ms

So multi-insert batching still works with a non-volatile policy, and COPY
stays much faster than the INSERT workaround.

Regards,
Joao Rito