Subject: create policy statement USING clause

From: Mark Phillips <[email-masked]>
Date: Mon, 11 Nov 2024 15:10:24 -0800

PostgreSQL 12
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. 

Links and advice accepted with gratitude.

Mark

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 12 Nov 2024 09:48:31 +0100

> 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".
> 
> 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:

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

---

From: Mark Phillips <[email-masked]>
Date: Tue, 12 Nov 2024 10:54:21 -0800

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 <[email-masked]> wrote:
>