Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u8EX7-00GSRA-C2 for pgsql-general@arkaria.postgresql.org; Fri, 25 Apr 2025 08:35:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u8EX2-008LYs-Ma for pgsql-general@arkaria.postgresql.org; Fri, 25 Apr 2025 08:35:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u8EX2-008LYj-BE for pgsql-general@lists.postgresql.org; Fri, 25 Apr 2025 08:35:37 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1u8EWz-001rK1-2C for pgsql-general@lists.postgresql.org; Fri, 25 Apr 2025 08:35:36 +0000 Content-Type: multipart/alternative; boundary="------------Ehf0eDUhHkGc1rS5JWInPndJ" Message-ID: Date: Fri, 25 Apr 2025 09:35:30 +0100 MIME-Version: 1.0 Subject: Re: Clarification on RLS policy To: pgsql-general@lists.postgresql.org References: Content-Language: en-US From: Achilleas Mantzios - cloud In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------Ehf0eDUhHkGc1rS5JWInPndJ Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 4/25/25 08:08, Vydehi Ganti wrote: > Hi Team, > > 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? Just show the output of \d and particularly the Policies: section. Then also show the source of your function and anything else involved. > > Rg > Vydehi. --------------Ehf0eDUhHkGc1rS5JWInPndJ Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 4/25/25 08:08, Vydehi Ganti wrote:
Hi Team,

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?

Just show the output of

\d <your_table>

and particularly the Policies: section. Then also show the source of your function and anything else involved.


Rg
Vydehi.
--------------Ehf0eDUhHkGc1rS5JWInPndJ--