public inbox for [email protected]help / color / mirror / Atom feed
create policy statement USING clause 3+ messages / 2 participants [nested] [flat]
* create policy statement USING clause @ 2024-11-11 23:10 Mark Phillips <[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-12 08:48 Laurenz Albe <[email protected]> parent: 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-12 18:54 Mark Phillips <[email protected]> parent: Laurenz Albe <[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