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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
