Message-ID: <6cadf9b3-3eb9-4a68-b570-a5e0f5700eea@cloud.gatewaynet.com>
Date: Fri, 25 Apr 2025 15:04:15 +0100
MIME-Version: 1.0
Subject: Re: Clarification on RLS policy
To: 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: Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.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/6cadf9b3-3eb9-4a68-b570-a5e0f5700eea%40cloud.gatewaynet.com>
Precedence: bulk


On 4/25/25 14:01, Vydehi Ganti wrote:
> This is my Scenario:
>
> CREATE OR REPLACE FUNCTION one.get_country(
> powner name,
> ptable_name name)
>     RETURNS character varying

> LANGUAGE 'plpgsql'
>     COST 100
>     STABLE PARALLEL UNSAFE
> AS $BODY$
> DECLARE
>
>     lOSUser         varchar(4000) := UPPER(SUBSTRING(current_user FROM 
> POSITION('\' IN current_user) + 1));
>     lPredicate      varchar(4000) := NULL;
>     lCount          integer;
>
>   i RECORD;
>
> BEGIN
>     IF position('ro' in current_user) = 0 THEN
>         lPredicate := '1=1';
>     ELSE
>         -- Users associated to explicit country_code
>         FOR i IN (SELECT r.country_code AS country_code
>                     FROM one.users  u
>                         where UPPER(SUBSTR(u.Login, INSTR(u.Login, 
> '\', -1) + 1)) = lOSUser )
>                     WHERE u.role_type = 'reader') LOOP
>             lPredicate := lPredicate||''''||i.country_code||''',';
>         END LOOP;
>
>         IF lPredicate IS NOT NULL THEN
>             lPredicate := 'SUBSTR("id",1,3) IN ('||SUBSTR(lPredicate, 
> 1, LENGTH(lPredicate)-1)||')';
>         ELSE
>             lPredicate := '1=1';
>         END IF;
>     END IF;
>
>     RETURN lPredicate;
>
> END;
> $BODY$;
>
> 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'));
side note : it seems ptable_name and powner are not read in your function