public inbox for [email protected]  

help / color / mirror / Atom feed

From: Mark Phillips <[email protected]>
To: [email protected]
Subject: create policy statement USING clause
Date: Mon, 11 Nov 2024 15:10:24 -0800
Message-ID: <[email protected]> (raw)

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

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: create policy statement USING clause
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox