Re: COPY FROM with RLS

On Thu, 24 Sept 2026 at 00:06, jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> On Fri, Sep 11, 2026 at 1:21 AM Bryan Green <dbryan(dot)green(at)gmail(dot)com> wrote:
> >
> > 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.
> >
>
> + foreach_node(WithCheckOption, wco, resultRelInfo->ri_WithCheckOptions)
> + {
> + if (contain_subplans(wco->qual) ||
> + contain_volatile_functions(wco->qual))
> + {
> + volatile_wco = true;
> + break;
> + }
> + }
> Make volatile expression and sub-select fall back to CIM_SINGLE should be fine.
>
> > 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.
> >
>
> We can use RelationData->rd_rules to check if a relation has rules
> defined on it or not.
>
> > 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?
> >
> I am not sure about "non-violation error from a policy expression".
> Currently check constraint violations produce an error line, but RLS
> violations do not.
>
>
>
> --
> jian
> https://www.enterprisedb.com/

Hi!
Looks like Bryan point about losing line num is still present in v16?

reshke=# -- run as superuser
CREATE ROLE u1 LOGIN;
CREATE TABLE t7 (id int, v text);
CREATE FUNCTION t7_div(id int) RETURNS int LANGUAGE sql STABLE
AS $$ SELECT 1/(id-3) $$;
ALTER TABLE t7 ENABLE ROW LEVEL SECURITY;
CREATE POLICY t7_p ON t7 FOR INSERT WITH CHECK (t7_div(id) IS NOT NULL);
GRANT INSERT ON t7 TO u1;
GRANT EXECUTE ON FUNCTION t7_div(int) TO u1;

SET SESSION AUTHORIZATION u1;
COPY t7 FROM STDIN WITH (DELIMITER ',');

CREATE ROLE
CREATE TABLE
ERROR: function "t7_div" already exists with same argument types
ALTER TABLE
CREATE POLICY
GRANT
GRANT
SET
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 1,a
>> 3,boom
>> \.
ERROR: division by zero
CONTEXT: COPY t7

Line number is missing

Also looks like the rule guard is broader than needed.

RLS table with a single ON DELETE rule -> COPY FROM rejected

CREATE TABLE t (id int);
ALTER TABLE t ENABLE ROW LEVEL SECURITY;
CREATE POLICY p ON t FOR INSERT WITH CHECK (id > 0);
GRANT INSERT ON t TO u1;
CREATE RULE r_del AS ON DELETE TO t DO INSTEAD NOTHING; -- never
applies to COPY

SET SESSION AUTHORIZATION u1;
COPY t FROM STDIN;
1
\.

ERROR: cannot copy to table "t"
DETAIL: COPY FROM is not supported on tables with rules when
row-level security is enabled.
HINT: Use INSERT statements instead.

Without RLS it works

db3=# CREATE TABLE t (id int);
CREATE TABLE
db3=# CREATE RULE r_del AS ON DELETE TO t DO INSTEAD NOTHING;
CREATE RULE
db3=# GRANT INSERT ON t TO u1;
GRANT
db3=# SET SESSION AUTHORIZATION u1;
COPY t FROM STDIN;
SET
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 1
>> \.
COPY 1

Is there reason for such restriction?

--
Best regards,
Kirill Reshke