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

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

Responses

Browse pgsql-general by date

  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