From: | Jong-won Choi <jongwon(at)ticketsquad(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Row level security policy - calling function for right hand side value of 'in' in using_expression |
Date: | 2017-02-03 05:24:39 |
Message-ID: | 241616a9-0156-e833-4eb2-02200d1fd33d@ticketsquad.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-02-03 06:14:19 | Re: Row level security policy - calling function for right hand side value of 'in' in using_expression |
Previous Message | David G. Johnston | 2017-02-03 03:54:01 | Re: Average - Pg 9.2 |