Re: COPY FROM with RLS

On Thu, Aug 27, 2026 at 9:04 AM <dbryan(dot)green(at)gmail(dot)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/