Thread: create policy statement USING clause

PostgreSQL 12
Given a table “customer” with a column “deadfiled” of the type boolean. The column deadfiled is used to indicate that a
rowis “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
therole “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
theUSING 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.

Links and advice accepted with gratitude.

Mark

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

Thank you for the reply. I appreciate it very much.

I checked the data for null in the column values, but I didn't any. I started over from the beginning with a fresh
cloneof the database, and followed the set up in ordered fashion, including a little routine to assure valid data in
thecolumn, and it now works fine. The better form of the USING clause certainly helped. I am happy to share my notes if
someonewould like to see them. 

As for pg 12, an update to the current stable release is on the project roadmap.

Cheers,

 - Mark

> On Nov 12, 2024, at 12:48 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> 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