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
