From: Mark Phillips <mphillips@mophilly.com>
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.200.121\))
Subject: create policy statement USING clause 
Message-Id: <054FBFBF-C87C-4C3C-B6F5-D5C09E6A7F1A@mophilly.com>
Date: Mon, 11 Nov 2024 15:10:24 -0800
To: pgsql-general@postgresql.org
Archived-At: <https://www.postgresql.org/message-id/054FBFBF-C87C-4C3C-B6F5-D5C09E6A7F1A%40mophilly.com>
Precedence: bulk

PostgreSQL 12
Given a table =E2=80=9Ccustomer=E2=80=9D with a column =E2=80=9Cdeadfiled=E2=
=80=9D of the type boolean. The column deadfiled is used to indicate =
that a row is =E2=80=9Cin the trash bin=E2=80=9D. The app has a window =
that lists the contents of the =E2=80=9Ctrash bin=E2=80=9D, which any =
rows with deadfiled =3D true. Row so marked should be excluded from =
views and queries in all other cases when the current user has the role =
=E2=80=9Capp_user".

I thought I could use row level security (RLS) to filter out all the =
deadfiled rows.=20

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=E2=80=99t 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)=3Dfalse);

So my question is specifically about the USING clause, but also more =
broadly about this attempted application of RLS.=20

Links and advice accepted with gratitude.

Mark=