Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kIV3c-0007zX-T4 for pgsql-performance@arkaria.postgresql.org; Wed, 16 Sep 2020 10:53:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kIV3a-0005bH-RJ for pgsql-performance@arkaria.postgresql.org; Wed, 16 Sep 2020 10:52:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kIV3a-0005b9-HO for pgsql-performance@lists.postgresql.org; Wed, 16 Sep 2020 10:52:58 +0000 Received: from mxh01aws49.mxout.inova.com.br ([34.198.242.49] helo=mxhero01.a.inova.com.br) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kIV3V-0004tz-U3 for pgsql-performance@lists.postgresql.org; Wed, 16 Sep 2020 10:52:58 +0000 Received: from ip-10-0-4-78.ec2.internal (localhost [127.0.0.1]) by mxhero01.a.inova.com.br (Postfix) with ESMTP id 7BC561C8D00 for ; Wed, 16 Sep 2020 10:52:50 +0000 (UTC) Received: from corpmoutpolicyd01.a.inova.com.br (puppetmaster.a.inova.com.br [10.0.1.88]) by mxhero01.a.inova.com.br (Postfix) with ESMTP; Wed, 16 Sep 2020 10:52:50 +0000 (UTC) Received: from corpmoutpolicyd01.a.inova.com.br (localhost [127.0.0.1]) by corpmoutpolicyd01.a.inova.com.br (Postfix) with ESMTP id 681142205C4; Wed, 16 Sep 2020 07:52:47 -0300 (BRT) Received: from corpmout01.a.inova.com.br (ip-10-0-1-125.ec2.internal [10.0.1.125]) by corpmoutpolicyd01.a.inova.com.br (Postfix) with ESMTP id 5CDC2B74AC; Wed, 16 Sep 2020 07:52:47 -0300 (BRT) Received: from corpmbox02.a.inova.com.br (ip-10-0-4-158.ec2.internal [10.0.4.158]) by corpmout01.a.inova.com.br (Postfix) with ESMTP; Wed, 16 Sep 2020 07:52:47 -0300 (-03) Date: Wed, 16 Sep 2020 07:52:47 -0300 (BRT) From: luis.roberto@siscobra.com.br To: "Gopisetty, Ramesh" Cc: pgsql-performance Message-ID: <676231578.16817100.1600253567225.JavaMail.zimbra@siscobra.com.br> In-Reply-To: References: Subject: Re: Performance issue when we use policies for Row Level Security along with functions MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_8f2c8f81-2b32-4d74-9328-f63e16e3b982" X-Originating-IP: [10.0.1.247] X-Mailer: Zimbra 8.8.15_GA_3955 (ZimbraWebClient - GC85 (Win)/8.8.15_GA_3953) Thread-Topic: Performance issue when we use policies for Row Level Security along with functions Thread-Index: AQHWi9mRaa7EjFBnVECBFLgQseomYGLPFO6W x-mxHero-Origin-Ip: /127.0.0.1:35360 X-mxHero-AttachmentBlock: rule=48;result=false List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --=_8f2c8f81-2b32-4d74-9328-f63e16e3b982 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit De: "Gopisetty, Ramesh" Para: "pgsql-performance" 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 BQ_BEGIN 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 BQ_END You could try seeting the function as immutable. By default it is volatile. --=_8f2c8f81-2b32-4d74-9328-f63e16e3b982 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
De: "Gopisetty, Ramesh" <rameshg2@illinois.edu&= gt;
Para: "pgsql-performance" <pgsql-performance@lists.postgre= sql.org>
Enviadas: Quarta-feira, 16 de setembro de 2020 0:39:0= 8
Assunto: Performance issue when we use policies for Row Level S= ecurity along with functions

Hi,

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

table test  consists of columns  testkey,oid,category,type,descri= ption... 

Policy

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

Going to a Sequential scan in= stead of index scan.  Hence, performance issue.

pgwfc01q=3D> explain analyze select * from test;
              =                      = ;              QUERY PLAN
-------------------------------------------------= -----------------------------------------------------------
 Seq Scan on test  (cost=3D0.00..25713.12 rows=3D445 width=3D712) = (actual time=3D1849.592..1849.592 rows=3D0 loops=3D1)
&= nbsp;  Filter: ((testkey )::text =3D (f_sel_policy_test(testkey = ))::text)
&= nbsp;  Rows Removed by Filter: 88930
&= nbsp;Planning Time: 0.414 ms
&= nbsp;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;<= /div>
BEGIN

            if vpd_key =3D 'COMMON' then=
                    =     return '''CO= MMON''';
            elsif vpd_key =3D ('COMMON_' || SYS_CONTEXT('ct= x_ng', 'ctx_prod_locale')) then
                    =     return '''CO= MMON_' || SYS_CONTEXT('ctx_ng', 'ctx_prod_locale')||'''';
            elsif vpd_key =3D SYS_CONTEX= T('ctx_ng_vpd', 'ctx_key_fil') then
                    =     return '''co'','''||SYS_CONTEXT('ctx_ng', 'ctx_testkey_fil')|= |'''';
            end if;
            return 'false';    = ;
            exception when undefined_obj= ect then
                    =     return 'failed';
           
END;
$function$
;


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

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

QUERY PLAN

----------------------= ---------------------------------------------------------------------------= -------------------
----------------------= -------------------------------
 Bitmap Heap Scan= on test  (cost=3D396.66..2966.60 rows=3D13396 width=3D712) (actual ti= me=3D0.693..2.318 rows=3D13159 loops=3D1)
   Recheck C= ond: ((testkey=  )::text =3D ANY ((ARRAY['COMMON'::character varying, (current_setting('ctx_vpd.ctx= _key_fil'::text))::character varying])::text[]))
   Heap Bloc= ks: exact=3D373
   -> &nb= sp;Bitmap Index Scan on test_pkey  (cost=3D0.00..393.31 rows=3D13396 w= idth=3D0) (actual time=3D0.653..0.653 rows=3D13159 l
oops=3D1)
      &= nbsp;  Index Cond: ((testkey )::text =3D ANY ((ARRAY['COMMON'::character varying, (current_setting('ctx_vpd.ctx=
_key_fil'::text))::cha= racter 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 choos= es the index.   Not sure how i can implement with the function.&n= bsp;  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 funct= ions in the policy. 

Thank you.


Regards,
Ramesh G

You could try seeting the function as immu= table. By default it is volatile.

<= br>

--=_8f2c8f81-2b32-4d74-9328-f63e16e3b982--