Search Postgresql Archives

Re: create policy statement USING clause

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux