Message-ID: <ab913841-b458-4931-bb89-cee7fba9eb24@gmail.com>
Date: Thu, 10 Sep 2026 12:21:12 -0500
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: COPY FROM with RLS
To: jian he <jian.universality@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
References: 
 <CACJufxFbmnoa5O-vL43DPTCGt6oagY4dXgKxy=rcD9-e9g0zEg@mail.gmail.com>
 <CACJufxFdt0+R6C5ZByAK3DpMEXbb=3Hc0XyRTPvbjpc9sPt=gQ@mail.gmail.com>
 <70d4ee07.f25.19f161ce6b2.Coremail.jiezhilove@126.com>
 <CACJufxHU3tetCM+Qymxpj7-OLP8b6x3HJAnARadSrEAt64MHkg@mail.gmail.com>
 <1266b6dc.6689.19fa801caa0.Coremail.jiezhilove@126.com>
 <2e3ed175-8618-46a9-847b-b7e54597aa83@gmail.com>
 <CACJufxEPnQ_wx=-6P6h=haxCO3CHeAijV7eeq3keUZ_Tve-1yw@mail.gmail.com>
Content-Language: en-US
From: Bryan Green <dbryan.green@gmail.com>
In-Reply-To: 
 <CACJufxEPnQ_wx=-6P6h=haxCO3CHeAijV7eeq3keUZ_Tve-1yw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/ab913841-b458-4931-bb89-cee7fba9eb24%40gmail.com>
Precedence: bulk

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