Re: COPY FROM with RLS

Hi Jian,

I reviewed v16 and ran some tests. I tried to look at areas that were not
covered in the thread yet.

Setup: master at 952bab8b74, Debian bookworm container, built with
--enable-cassert --enable-debug. v16 applies with small offsets, the build
has no warnings, and make check passes (243 tests). I ran the same probe
script on master and on the patched build. As expected, on master every
non-owner COPY FROM below fails with "COPY FROM not supported with row-level
security".

Things that work the same as INSERT:
- Triggers: with BEFORE/AFTER ROW, BEFORE STATEMENT and an AFTER STATEMENT
trigger with REFERENCING NEW TABLE, a 3-row COPY fires each row trigger 3
times, each statement trigger once, and the transition table has 3 rows.
INSERT gives the same counts.
- A BEFORE ROW trigger that changes the owner column so the row fails the
policy: COPY fails with the RLS error, same as INSERT. So the check runs
on the row after the triggers.
- Partitioned table with a permissive policy on the parent and a stricter
INSERT policy on the leaf: COPY into the parent succeeds and COPY into the
leaf fails, the same as INSERT through the parent and the leaf.
- FORCE ROW LEVEL SECURITY: the owner's COPY is checked. A BYPASSRLS role is
not checked.
- Column privileges: with only INSERT (a), COPY t (a) works and COPY t (a,
secret) fails with "permission denied", same as master. So the DEFAULT
VALUES statement does not need extra privileges.
- COPY ... WHERE: a row removed by WHERE that would fail the policy does not
raise an error.
- A deferred constraint trigger fires at commit.

1. The internal INSERT is visible to monitoring extensions

With pg_stat_statements.track = 'all', one COPY FROM on an RLS table leaves
two entries:

toplevel | calls | rows | query
----------+-------+------+-------------------------------------
t | 1 | 3 | COPY t (a) FROM STDIN
f | 1 | 0 | INSERT INTO public.t DEFAULT VALUES

With auto_explain.log_nested_statements = on, the server log also gets a
plan for it:

LOG: duration: 0.000 ms plan:
Query Text: INSERT INTO public.t DEFAULT VALUES
Insert on t (cost=0.00..0.01 rows=0 width=0)
-> Result (cost=0.00..0.01 rows=1 width=36)

This comes from running the query through
ExecutorStart/ExecutorFinish/ExecutorEnd, which call the executor hooks.
With track = 'top' only the COPY entry is there. It is not wrong, but a DBA
who sees "INSERT INTO public.t DEFAULT VALUES" with rows = 0 may think some
application ran it. Maybe this is fine and only needs a comment in the code.
Or maybe the query should not get a queryId, so pg_stat_statements skips it.
I am not sure which is better, so I wanted to mention it.

2. ON_ERROR ignore and the missing line number

COPY ... WITH (ON_ERROR ignore) skips a bad input value, but an RLS
violation still aborts the whole COPY. I think that is right, because
ON_ERROR only covers soft input errors. But the error only shows "CONTEXT:
COPY t", without the line number, so with a large file there is no easy way
to find the row that failed. This is the same point Kirill raised for errors
inside a policy function. I think it applies to the violation error too, and
it is probably the most common error users will see from this feature.

3. Performance

1M rows from a file, fsync = off, a simple stable policy (owner =
current_user), times for one COPY:
- table owner, RLS not applied: 329 ms
- non-owner, RLS applied: 440 ms
- non-owner, INSERT ... SELECT of the same rows: 1170 ms

So multi-insert batching still works with a non-volatile policy, and COPY
stays much faster than the INSERT workaround.

Regards,
Joao Rito