Feedback-ID: i76984098:Fastmail
Message-ID: <8b745873-f008-41ec-b58e-4b167e266b29@aklaver.com>
Date: Fri, 25 Apr 2025 08:13:49 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Clarification on RLS policy
To: Vydehi Ganti <rayudugs@gmail.com>,
 Dominique Devienne <ddevienne@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>,
 pgsql-general@lists.postgresql.org
References: <CAMH-PzUPN7JcLXJx-aJzGnJD2T-aSMo8gaMFkzO_x26urpMqSA@mail.gmail.com>
 <88804c921b425d37a3072b5698b558a763d80d63.camel@cybertec.at>
 <CAFCRh-_fXGfKk6b26xaDD1fg5OhLy92bNofmeDfX2TFjkQLLXA@mail.gmail.com>
 <CAMH-PzXe7AR2QDCDx3eRZPMGuixfTwm3sLF3kMZQkyNhtu=5qQ@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAMH-PzXe7AR2QDCDx3eRZPMGuixfTwm3sLF3kMZQkyNhtu=5qQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/8b745873-f008-41ec-b58e-4b167e266b29%40aklaver.com>
Precedence: bulk

On 4/25/25 06:01, Vydehi Ganti wrote:
> This is my Scenario:
> 

> *Can you please guide how to achieve this?*
> 
> CREATE OR REPLACE FUNCTION one.get_country(
> powner name,
> ptable_name name)
>      RETURNS character varying
> LANGUAGE 'plpgsql'

> 
> For the below policy statement it created the policy but i cant call that
> CREATE POLICY "Codebase_Filter"
>      ON one.activity
>      FOR SELECT
>      TO one
>      USING (one.get_country('one','activity'));

The core of the issue you are getting is that this from the function:

RETURNS character varying

is not going to work here:

USING (one.get_country('one','activity'))

as what USING will see is a string not the evaluation of 1=1 hence the 
error you get:

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com