Re: create policy statement USING clause

On Mon, 2024-11-11 at 15:10 -0800, Mark Phillips wrote:
> PostgreSQL 12

Upgrade now!

> Given a table “customer” with a column “deadfiled” of the type boolean. The column
> deadfiled is used to indicate that a row is “in the trash bin”. The app has a window
> that lists the contents of the “trash bin”, which any rows with deadfiled = true.
> Row so marked should be excluded from views and queries in all other cases when the
> current user has the role “app_user".
>
> I thought I could use row level security (RLS) to filter out all the deadfiled rows.
>
> ALTER TABLE customer ENABLE ROW LEVEL SECURITY;
> CREATE POLICY filter_customer_deadfiled
> ON public.customer
> FOR SELECT
> TO app_staff
> USING ( NOT deadfiled );
>
> However, that did not work as desired. I have read through a dozen articles and posts
> online but haven’t figured out the USING clause. To my surprise, this worked:
> CREATE POLICY customer_deadfiled
> ON public.customer
> AS PERMISSIVE
> FOR SELECT
> TO prm_staff
> USING (coalesce(deadfiled,false)=false);
>
> So my question is specifically about the USING clause, but also more broadly about
> this attempted application of RLS.

It seems that your problem is that "deadfiled" is NULL in some rows, any you want
such rows to be considered live.

Since NOT NULL is not TRUE, you'd have to use a USING clause like

USING (deadfiled IS NOT TRUE)

Yours,
Laurenz Albe