Message-ID: <e281aa4941402a4b63f570ddf58f81721432f58e.camel@cybertec.at>
Subject: Re: create policy statement USING clause
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Mark Phillips <mphillips@mophilly.com>, pgsql-general@postgresql.org
Date: Tue, 12 Nov 2024 09:48:31 +0100
In-Reply-To: <054FBFBF-C87C-4C3C-B6F5-D5C09E6A7F1A@mophilly.com>
References: <054FBFBF-C87C-4C3C-B6F5-D5C09E6A7F1A@mophilly.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-2.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/e281aa4941402a4b63f570ddf58f81721432f58e.camel%40cybertec.at>
Precedence: bulk

On Mon, 2024-11-11 at 15:10 -0800, Mark Phillips wrote:
> PostgreSQL 12

Upgrade now!

> 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 row=
s with deadfiled =3D true.
> Row so marked should be excluded from views and queries in all other case=
s 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 dead=
filed 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 articl=
es 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 broa=
dly about
> this attempted application of RLS.

It seems that your problem is that "deadfiled" is NULL in some rows, any yo=
u 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