COPY FROM with RLS
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
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.
Attachments:
v2-0001-COPY-FROM-with-RLS.patchtext/x-patch; charset=US-ASCII; name=v2-0001-COPY-FROM-with-RLS.patchDownload+276-65
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