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
