public inbox for [email protected]  

help / color / mirror / Atom feed

From: Mark Phillips <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: [email protected]
Subject: Re: create policy statement USING clause
Date: Tue, 12 Nov 2024 10:54:21 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

Thank you for the reply. I appreciate it very much.

I checked the data for null in the column values, but I didn't any. I started over from the beginning with a fresh clone of the database, and followed the set up in ordered fashion, including a little routine to assure valid data in the column, and it now works fine. The better form of the USING clause certainly helped. I am happy to share my notes if someone would like to see them.

As for pg 12, an update to the current stable release is on the project roadmap.

Cheers,

 - Mark

> On Nov 12, 2024, at 12:48 AM, Laurenz Albe <[email protected]> wrote:
> 
> 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)

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