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
