Re: Docs and tests for RLS policies applied by command type

On Thu, Oct 23, 2025 at 11:15 PM Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> Attached is a new version with more comments in the tests, focusing on
> what is expected from each test.
>
> > The 0001 regess tests define several functions: sel_using_fn,
> > ins_check_fn, upd_using_fn,
> > upd_check_fn, and del_using_fn.
> > IMHO, these could be simplified (we probably only need two functions).
>
> Good point. Actually it can be done with just one function, further
> reducing the amount of test code.
>

hi.

v2-0001 looks good to me.

> A recent commit reminded me that COPY ... TO also applies RLS SELECT
> policies (and so does TABLE, though I doubt many people use that), so
> I think it's worth testing and documenting those too. Updated patches
> attached.
>

other Utility commands will also invoke the SELECT/UPDATE policy.
The below several commands will invoke SELECT or UPDATE policy,
if rls_test_src have SELECT or UPDATE policy on it.

create table sss as SELECT * FROM rls_test_src FOR UPDATE;
explain analyze SELECT * FROM rls_test_src FOR UPDATE;
PREPARE q1 AS SELECT * FROM rls_test_src FOR UPDATE;
EXECUTE q1;
create MATERIALIZED view mv as SELECT * FROM rls_test_src FOR UPDATE
with no data;
REFRESH MATERIALIZED VIEW mv;
create MATERIALIZED view mv1 as SELECT * FROM rls_test_src FOR UPDATE;
DECLARE curs1 CURSOR WITH HOLD FOR SELECT * FROM rls_test_src;

While at it, I found out that
table "Policies Applied by Command Type" was missing SELECT FOR NO KEY UPDATE
and SELECT FOR KEY SHARE.


While at it create_policy.sgml, I am not sure the below sentence is
not fully accurate.
""
If an INSERT or UPDATE command attempts to add rows to the table that do not
pass the ALL policy's WITH CHECK expression, the entire command will be aborted.
""
The above sentence fails to mention the case when the WITH CHECK
expression does not exist.
for example:

create table tts(x int);
CREATE POLICY p1 ON tts FOR all using (x = 1);
grant select, insert on tts to alice;
alter table tts ENABLE ROW LEVEL SECURITY;
set role alice;
insert into tts values (2);