create policy statement USING clause

From: Mark Phillips <mphillips(at)mophilly(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: create policy statement USING clause
Date: 2024-11-11 23:10:24
Message-ID: 054FBFBF-C87C-4C3C-B6F5-D5C09E6A7F1A@mophilly.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message JOLAPARA Urvi (SAFRAN) 2024-11-12 06:09:21 RE: postgresql-17.0-1 Application - silent installation Issue
Previous Message Michel Pelletier 2024-11-11 21:34:06 Re: Using Expanded Objects other than Arrays from plpgsql