Thread

1. COPY FROM with RLS

   jian he <jian.universality@gmail.com> — 2025-10-28T12:12:30Z

   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.
   

2. Re: COPY FROM with RLS

   jian he <jian.universality@gmail.com> — 2026-05-10T14:23:26Z

   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/
   

3. Re: COPY FROM with RLS

   solai v <solai.cdac@gmail.com> — 2026-05-25T04:50:37Z

   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
   
   
   
   

4. Re:Re: COPY FROM with RLS

   Yilin Zhang <jiezhilove@126.com> — 2026-06-29T10:13:02Z

   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
   
   
   
   

5. Re:Re: COPY FROM with RLS

   Yilin Zhang <jiezhilove@126.com> — 2026-06-30T01:20:20Z

   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