MIME-Version: 1.0
References: <CAMH-PzUPN7JcLXJx-aJzGnJD2T-aSMo8gaMFkzO_x26urpMqSA@mail.gmail.com>
In-Reply-To: <CAMH-PzUPN7JcLXJx-aJzGnJD2T-aSMo8gaMFkzO_x26urpMqSA@mail.gmail.com>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Fri, 25 Apr 2025 11:07:48 +0200
Message-ID: <CAFCRh--FkPkFQeP7ucr2seyXGPd0jTjcdWLS43s+4WYqk5weNw@mail.gmail.com>
Subject: Re: Clarification on RLS policy
To: Vydehi Ganti <rayudugs@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh--FkPkFQeP7ucr2seyXGPd0jTjcdWLS43s%2B4WYqk5weNw%40mail.gmail.com>
Precedence: bulk

On Fri, Apr 25, 2025 at 9:09=E2=80=AFAM Vydehi Ganti <rayudugs@gmail.com> w=
rote:
> We are presently using Postgresql:PostgreSQL 15.12 on x86_64-pc-linux-gnu=
, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-23), 64-bit
> I have a scenario where
> 1.I need to enforce RLS policy on a table for Select by calling a Functio=
n
> 2.The function would return a character varying string which should be ap=
pended to the select as a filter.
> Ex: Select * from employee would be appended with where 1=3D1;
> 3.When we try to implement it says the below error.
> ERROR: invalid input syntax for type boolean: "1=3D1" CONTEXT: PL/pgSQL f=
unction function name(name,name) while casting return value to function's r=
eturn type
> 4.It works fine on Oracle. Can you please suggest how to fix this issue?

In PostgreSQL, you won't have to resort to the 1=3D1 trick like on Oracle.

    // One RLS is active, it is an implicit DENY on all DML types.
    // So we must explicitly allow SELECTs, using a dummy `USING (true)` po=
licy.
    // Note that we use ALL, and not just SELECT, because we used RESTRICTI=
VE
    // on the UPDATE policy (needs at last one PERMISSIVE policy)
    CREATE POLICY rls_pass_thru ON {} FOR ALL USING (true)

(replace {} with a table name). --DD