Performance issue when we use policies for Row Level Security along with functions

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Performance issue when we use policies for Row Level Security along with functions

Gopisetty, Ramesh
Hi,

I'm seeing a strange behavior when we implement policies (for RLS - Row level security)  using functions. 

table test  consists of columns  testkey,oid,category,type,description... 

Policy

create policy  policy_sel on test FOR SELECT to ram1 USING  (  testkey in (f_sel_policy_test(testkey))  );

Going to a Sequential scan instead of index scan.  Hence, performance issue.

pgwfc01q=> explain analyze select * from test;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..25713.12 rows=445 width=712) (actual time=1849.592..1849.592 rows=0 loops=1)
   Filter: ((testkey )::text = (f_sel_policy_test(testkey ))::text)
   Rows Removed by Filter: 88930
 Planning Time: 0.414 ms
 Execution Time: 1849.614 ms
(5 rows)


The function is 

CREATE OR REPLACE FUNCTION vpd_sec_usr.f_sel_policy_test(testkey character varying)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
Declare
            v_status character varying;
BEGIN

            if vpd_key = 'COMMON' then
                        return '''COMMON''';
            elsif vpd_key = ('COMMON_' || SYS_CONTEXT('ctx_ng', 'ctx_prod_locale')) then
                        return '''COMMON_' || SYS_CONTEXT('ctx_ng', 'ctx_prod_locale')||'''';
            elsif vpd_key = SYS_CONTEXT('ctx_ng_vpd', 'ctx_key_fil') then
                        return '''co'','''||SYS_CONTEXT('ctx_ng', 'ctx_testkey_fil')||'''';
            end if;
            return 'false';    
            exception when undefined_object then
                        return 'failed';
           
END;
$function$
;


If i replace the policy with stright forward without function then it chooses the index.   Not sure how i can implement with the function.

create policy  policy_sel on test FOR SELECT to ram1 USING  ( testkey in ('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil')));

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------
 Bitmap Heap Scan on test  (cost=396.66..2966.60 rows=13396 width=712) (actual time=0.693..2.318 rows=13159 loops=1)
   Recheck Cond: ((testkey )::text = ANY ((ARRAY['COMMON'::character varying, (current_setting('ctx_vpd.ctx_key_fil'::text))::character varying])::text[]))
   Heap Blocks: exact=373
   ->  Bitmap Index Scan on test_pkey  (cost=0.00..393.31 rows=13396 width=0) (actual time=0.653..0.653 rows=13159 l
oops=1)
         Index Cond: ((testkey )::text = ANY ((ARRAY['COMMON'::character varying, (current_setting('ctx_vpd.ctx
_key_fil'::text))::character varying])::text[]))
 Planning Time: 0.136 ms
 Execution Time: 2.843 ms
(7 rows)


If i replace the policy with stright forward without function then it chooses the index.   Not sure how i can implement with the function.   I thought of creating the policy with a lot of business logic in the function.  If i have the function then i notice going for full table scan instead of index. 

Please help me if i miss anything in writing a function or how to use functions in the policy. 

Thank you.


Regards,
Ramesh G

Reply | Threaded
Open this post in threaded view
|

Re: Performance issue when we use policies for Row Level Security along with functions

luis.roberto
De: "Gopisetty, Ramesh" <[hidden email]>
Para: "pgsql-performance" <[hidden email]>
Enviadas: Quarta-feira, 16 de setembro de 2020 0:39:08
Assunto: Performance issue when we use policies for Row Level Security along with functions

Hi,

I'm seeing a strange behavior when we implement policies (for RLS - Row level security)  using functions. 

table test  consists of columns  testkey,oid,category,type,description... 

Policy

create policy  policy_sel on test FOR SELECT to ram1 USING  (  testkey in (f_sel_policy_test(testkey))  );

Going to a Sequential scan instead of index scan.  Hence, performance issue.

pgwfc01q=> explain analyze select * from test;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..25713.12 rows=445 width=712) (actual time=1849.592..1849.592 rows=0 loops=1)
   Filter: ((testkey )::text = (f_sel_policy_test(testkey ))::text)
   Rows Removed by Filter: 88930
 Planning Time: 0.414 ms
 Execution Time: 1849.614 ms
(5 rows)


The function is 

CREATE OR REPLACE FUNCTION vpd_sec_usr.f_sel_policy_test(testkey character varying)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
Declare
            v_status character varying;
BEGIN

            if vpd_key = 'COMMON' then
                        return '''COMMON''';
            elsif vpd_key = ('COMMON_' || SYS_CONTEXT('ctx_ng', 'ctx_prod_locale')) then
                        return '''COMMON_' || SYS_CONTEXT('ctx_ng', 'ctx_prod_locale')||'''';
            elsif vpd_key = SYS_CONTEXT('ctx_ng_vpd', 'ctx_key_fil') then
                        return '''co'','''||SYS_CONTEXT('ctx_ng', 'ctx_testkey_fil')||'''';
            end if;
            return 'false';    
            exception when undefined_object then
                        return 'failed';
           
END;
$function$
;


If i replace the policy with stright forward without function then it chooses the index.   Not sure how i can implement with the function.

create policy  policy_sel on test FOR SELECT to ram1 USING  ( testkey in ('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil')));

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------
 Bitmap Heap Scan on test  (cost=396.66..2966.60 rows=13396 width=712) (actual time=0.693..2.318 rows=13159 loops=1)
   Recheck Cond: ((testkey )::text = ANY ((ARRAY['COMMON'::character varying, (current_setting('ctx_vpd.ctx_key_fil'::text))::character varying])::text[]))
   Heap Blocks: exact=373
   ->  Bitmap Index Scan on test_pkey  (cost=0.00..393.31 rows=13396 width=0) (actual time=0.653..0.653 rows=13159 l
oops=1)
         Index Cond: ((testkey )::text = ANY ((ARRAY['COMMON'::character varying, (current_setting('ctx_vpd.ctx
_key_fil'::text))::character varying])::text[]))
 Planning Time: 0.136 ms
 Execution Time: 2.843 ms
(7 rows)


If i replace the policy with stright forward without function then it chooses the index.   Not sure how i can implement with the function.   I thought of creating the policy with a lot of business logic in the function.  If i have the function then i notice going for full table scan instead of index. 

Please help me if i miss anything in writing a function or how to use functions in the policy. 

Thank you.


Regards,
Ramesh G


You could try seeting the function as immutable. By default it is volatile.



Reply | Threaded
Open this post in threaded view
|

Re: Performance issue when we use policies for Row Level Security along with functions

Tom Lane-2
In reply to this post by Gopisetty, Ramesh
"Gopisetty, Ramesh" <[hidden email]> writes:
> Policy
> create policy  policy_sel on test FOR SELECT to ram1 USING  (  testkey in (f_sel_policy_test(testkey))  );
> Going to a Sequential scan instead of index scan.  Hence, performance issue.

> If i replace the policy with stright forward without function then it chooses the index.   Not sure how i can implement with the function.
> create policy  policy_sel on test FOR SELECT to ram1 USING  ( testkey in ('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil')));

" testkey in ('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil')) "
is an indexable condition on testkey, because it compares testkey to
a constant (or at least, a value that's fixed for the life of the query).

" testkey in (f_sel_policy_test(testkey)) "
is not an indexable condition on anything, because there are variables
on both sides of the condition.  So there's no fixed value that the
index can search on.

If you intend f_sel_policy_test() to be equivalent to the other condition,
why are you passing it an argument it doesn't need?

As Luis noted, there's also the problem that an indexable condition
can't be volatile.  I gather that SYS_CONTEXT ends up being a probe
of some GUC setting, which means that marking the function IMMUTABLE
would be a lie, but you ought to be able to mark it STABLE.

                        regards, tom lane