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 clone of the database, and followed the set up in ordered fashion, including a little routine to assure valid data in the column, and it now works fine. The better form of the USING clause certainly helped. I am happy to share my notes if someone would 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.a...@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
