Re: Clarification on RLS policy

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