From: luis.roberto@siscobra.com.br
To: Gopisetty, Ramesh <rameshg2@illinois.edu>
Cc: pgsql-performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Performance issue when we use policies for Row Level Security along with functions
Date: Wed, 16 Sep 2020 07:52:47 -0300 (BRT)
Message-ID: <676231578.16817100.1600253567225.JavaMail.zimbra@siscobra.com.br> (raw)
In-Reply-To: <BL0PR11MB3153082AF1A7B16DEE74DCE5EE210@BL0PR11MB3153.namprd11.prod.outlook.com>
References: <BL0PR11MB3153082AF1A7B16DEE74DCE5EE210@BL0PR11MB3153.namprd11.prod.outlook.com>
De: "Gopisetty, Ramesh" <rameshg2@illinois.edu>
Para: "pgsql-performance" <pgsql-performance@lists.postgresql.org>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: luis.roberto@siscobra.com.br, rameshg2@illinois.edu, pgsql-performance@lists.postgresql.org
Subject: Re: Performance issue when we use policies for Row Level Security along with functions
In-Reply-To: <676231578.16817100.1600253567225.JavaMail.zimbra@siscobra.com.br>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox