COPY FROM with RLS

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: COPY FROM with RLS
Date: 2025-10-28 12:12:30
Message-ID: CACJufxFbmnoa5O-vL43DPTCGt6oagY4dXgKxy=rcD9-e9g0zEg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Attachment Content-Type Size
v1-0001-COPY-FROM-with-RLS.patch text/x-patch 15.2 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Jelte Fennema-Nio 2025-10-28 12:24:49 Re: Add uuid_to_base32hex() and base32hex_to_uuid() built-in functions
Previous Message Shinya Kato 2025-10-28 12:05:13 Re: Enhance statistics reset functions to return reset timestamp