public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dominique Devienne <[email protected]>
To: Vydehi Ganti <[email protected]>
Cc: [email protected]
Subject: Re: Clarification on RLS policy
Date: Fri, 25 Apr 2025 11:07:48 +0200
Message-ID: <CAFCRh--FkPkFQeP7ucr2seyXGPd0jTjcdWLS43s+4WYqk5weNw@mail.gmail.com> (raw)
In-Reply-To: <CAMH-PzUPN7JcLXJx-aJzGnJD2T-aSMo8gaMFkzO_x26urpMqSA@mail.gmail.com>
References: <CAMH-PzUPN7JcLXJx-aJzGnJD2T-aSMo8gaMFkzO_x26urpMqSA@mail.gmail.com>

On Fri, Apr 25, 2025 at 9:09 AM Vydehi Ganti <[email protected]> wrote:
> 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 Function
> 2.The function would return a character varying string which should be appended to the select as a filter.
> Ex: Select * from employee would be appended with where 1=1;
> 3.When we try to implement it says the below error.
> ERROR: invalid input syntax for type boolean: "1=1" CONTEXT: PL/pgSQL function function name(name,name) while casting return value to function's return 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=1 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)` policy.
    // Note that we use ALL, and not just SELECT, because we used RESTRICTIVE
    // 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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Clarification on RLS policy
  In-Reply-To: <CAFCRh--FkPkFQeP7ucr2seyXGPd0jTjcdWLS43s+4WYqk5weNw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox