Обсуждение: [GENERAL] Row level security policy - calling function for right hand sidevalue of 'in' in using_expression

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

Jong-won Choi <jongwon@ticketsquad.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

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@ticketsquad.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