Row level security policy - calling function for right hand side value of 'in' in using_expression

[Jong-won Choi <jongwon@xxxxxxxxxxxxxxx>]

I have a RLS policy definition like:

        CREATE POLICY promoter_policy ON Agency
        USING (promoter in build_valid_promoter_list())
        WITH CHECK (promoter in build_valid_promoter_list());


The build_valid_promoter_list function definition is:

        CREATE OR REPLACE FUNCTION build_valid_promoter_list() RETURNS 
TABLE(id BIGINT)
          LANGUAGE plpgsql
        AS $$
           DECLARE
                   promoter_id BIGINT;
                   acct_role TEXT;
           BEGIN
             SELECT promoter, role INTO promoter_id, acct_role FROM 
PromoterAccount
             WHERE oid = substring(current_setting('ts.promoter',true), 
26)::BIGINT;

             IF acct_role = 'agency' THEN
                RETURN QUERY SELECT UNNEST(customers) FROM Agency WHERE 
promoter = promoter_id;
             ELSE
                RETURN QUERY SELECT promoter_id;
             END IF;
           END
        $$;



And I have one problem and one concern;

- When I try to create the policy using the first code fragment, I got 
'ERROR:  syntax error at or near "build_valid_promoter_list"'. I am 
wondering the reason and how to fix it.

- Ideally, it would be great if the function build_valid_promoter_list() 
get called once and the RLS internal uses it as a constant value. Is 
this possible?



I am very new to RLS, any hints, opinions, and fixes will be greatly 
appreciated.



Thanks

- Jong-won


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general