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

Thanks Tom!

I actually considered what you suggested earlier, but somehow I thought
the function get called as many as number of rows, then guessed
returning a list might be cached and used internally (but I confess that
I do not know the internals of RLS).

Anyway, I rewrote it as you suggested:

CREATE OR REPLACE FUNCTION valid_promoter_p(promoter_id BIGINT)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
ts_promoter_id BIGINT =
substring(current_setting('ts.promoter',TRUE),26)::BIGINT;
BEGIN
RETURN ((promoter_id = ts_promoter_id) AND
NOT EXISTS(SELECT TRUE FROM Agency WHERE
Agency.promoter = ts_promoter_id))
OR
EXISTS(SELECT TRUE FROM Agency WHERE
Agency.promoter = ts_promoter_id AND promoter_id = ANY(customers));
END
$$;

Have a great weekend!

- Jong-won

On 03/02/17 17:14, Tom Lane wrote:
> Jong-won Choi <jongwon(at)ticketsquad(dot)com> writes:
>> 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());
> That's failing basic SQL expression syntax: the RHS of "IN" has
> to be a parenthesized sub-select or array value. You'd have better
> luck with (promoter in (select * from build_valid_promoter_list()))
> ... syntax-wise, at least. I'm not sure if we allow sub-selects
> in RLS conditions.
>
> Personally I'd write that more like
> USING (check_valid_promoter(promoter))
> with that function being defined in the obvious way. There's little
> reason to enumerate the entire set of valid promoters if you only
> need to find out whether one specific value is one.
>
> regards, tom lane