Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.200.121\))
Subject: Re: create policy statement USING clause
From: Mark Phillips <mphillips@mophilly.com>
In-Reply-To: <e281aa4941402a4b63f570ddf58f81721432f58e.camel@cybertec.at>
Date: Tue, 12 Nov 2024 10:54:21 -0800
Cc: pgsql-general@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <45E37776-2020-4FD2-BEB4-E02477B630EC@mophilly.com>
References: <054FBFBF-C87C-4C3C-B6F5-D5C09E6A7F1A@mophilly.com>
 <e281aa4941402a4b63f570ddf58f81721432f58e.camel@cybertec.at>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Archived-At: <https://www.postgresql.org/message-id/45E37776-2020-4FD2-BEB4-E02477B630EC%40mophilly.com>
Precedence: bulk

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=E2=80=AFAM, Laurenz Albe =
<laurenz.albe@cybertec.at> wrote:
>=20
> On Mon, 2024-11-11 at 15:10 -0800, Mark Phillips wrote:
>> PostgreSQL 12
>=20
> Upgrade now!
>=20
>> Given a table =E2=80=9Ccustomer=E2=80=9D with a column =
=E2=80=9Cdeadfiled=E2=80=9D of the type boolean. The column
>> deadfiled is used to indicate that a row is =E2=80=9Cin the trash =
bin=E2=80=9D. The app has a window
>> that lists the contents of the =E2=80=9Ctrash bin=E2=80=9D, which any =
rows with deadfiled =3D true.
>> Row so marked should be excluded from views and queries in all other =
cases when the
>> current user has the role =E2=80=9Capp_user".
>>=20
>> I thought I could use row level security (RLS) to filter out all the =
deadfiled rows.=20
>>=20
>> ALTER TABLE customer ENABLE ROW LEVEL SECURITY;
>> CREATE POLICY filter_customer_deadfiled
>> 	ON public.customer
>> 	FOR SELECT
>> 	TO app_staff
>> 	USING ( NOT deadfiled );
>>=20
>> However, that did not work as desired. I have read through a dozen =
articles and posts
>> online but haven=E2=80=99t 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)=3Dfalse);
>>=20
>> So my question is specifically about the USING clause, but also more =
broadly about
>> this attempted application of RLS.
>=20
> It seems that your problem is that "deadfiled" is NULL in some rows, =
any you want
> such rows to be considered live.
>=20
> Since NOT NULL is not TRUE, you'd have to use a USING clause like
>=20
>  USING (deadfiled IS NOT TRUE)
>=20
> Yours,
> Laurenz Albe