public inbox for [email protected]  

help / color / mirror / Atom feed

From: Achilleas Mantzios - cloud <[email protected]>
To: [email protected]
Subject: Re: Clarification on RLS policy
Date: Fri, 25 Apr 2025 15:04:15 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAMH-PzXe7AR2QDCDx3eRZPMGuixfTwm3sLF3kMZQkyNhtu=5qQ@mail.gmail.com>
References: <CAMH-PzUPN7JcLXJx-aJzGnJD2T-aSMo8gaMFkzO_x26urpMqSA@mail.gmail.com>
	<[email protected]>
	<CAFCRh-_fXGfKk6b26xaDD1fg5OhLy92bNofmeDfX2TFjkQLLXA@mail.gmail.com>
	<CAMH-PzXe7AR2QDCDx3eRZPMGuixfTwm3sLF3kMZQkyNhtu=5qQ@mail.gmail.com>


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

view thread (10+ messages)  latest in thread

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]
  Subject: Re: Clarification on RLS policy
  In-Reply-To: <[email protected]>

* 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