public inbox for [email protected]
help / color / mirror / Atom feedcreate policy statement USING clause
3+ messages / 2 participants
[nested] [flat]
* create policy statement USING clause
@ 2024-11-11 23:10 Mark Phillips <[email protected]>
2024-11-12 08:48 ` Re: create policy statement USING clause Laurenz Albe <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Mark Phillips @ 2024-11-11 23:10 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: create policy statement USING clause
2024-11-11 23:10 create policy statement USING clause Mark Phillips <[email protected]>
@ 2024-11-12 08:48 ` Laurenz Albe <[email protected]>
2024-11-12 18:54 ` Re: create policy statement USING clause Mark Phillips <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2024-11-12 08:48 UTC (permalink / raw)
To: Mark Phillips <[email protected]>; pgsql-general
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: create policy statement USING clause
2024-11-11 23:10 create policy statement USING clause Mark Phillips <[email protected]>
2024-11-12 08:48 ` Re: create policy statement USING clause Laurenz Albe <[email protected]>
@ 2024-11-12 18:54 ` Mark Phillips <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Mark Phillips @ 2024-11-12 18:54 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: pgsql-general
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-11-12 18:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-11 23:10 create policy statement USING clause Mark Phillips <[email protected]>
2024-11-12 08:48 ` Laurenz Albe <[email protected]>
2024-11-12 18:54 ` Mark Phillips <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox