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
