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

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