public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Mark Phillips <[email protected]>
To: [email protected]
Subject: Re: create policy statement USING clause
Date: Tue, 12 Nov 2024 09:48:31 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

On Mon, 2024-11-11 at 15:10 -0800, Mark Phillips wrote:
> 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".
> 
> 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.

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

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], [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