Skip site navigation (1) Skip section navigation (2)

Security definer "generated column" function used in index

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Security definer "generated column" function used in index
Date: 2011-12-09 18:49:06
Message-ID: 4EE203C20200002500043B7D@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-bugs
PostgreSQL version 9.0.4, 64 bit.
Linux version 2.6.16.60-0.39.3-smp (geeko(at)buildhost)
  (gcc version 4.1.2 20070115 (SUSE Linux))
  #1 SMP Mon May 11 11:46:34 UTC 2009
SUSE Linux Enterprise Server 10 (x86_64)
VERSION = 10
PATCHLEVEL = 2
 
We flagged some functions as SECURITY DEFINER and had queries which
had been in use for months suddenly fail to complete.  We set them
back to SECURITY INVOKER and things returned to normal.  I took
stack traces of the four connections with queries which seemed to be
"stuck".  They all had this sequence of calls in the middle:
 
#13 0x000000000054a5b6
  in fmgr_sql (fcinfo=0x7fff9eec0b30)
  at functions.c:441
#14 0x00000000006a2f05
  in fmgr_security_definer (fcinfo=0x30006)
  at fmgr.c:957
#15 0x0000000000544047
  in ExecMakeFunctionResult (fcache=0x512ac70, econtext=0x512aa80,
                             isNull=0x512b8a8 "", isDone=0x512b9c0)
  at execQual.c:1827
#16 0x0000000000540fb7
  in ExecProject (projInfo=<value optimized out>,
                  isDone=0x7fff9eec101c)
  at execQual.c:5089
#17 0x0000000000555403
  in ExecResult (node=0x512a970)
  at nodeResult.c:155
#18 0x00000000005409a6
  in ExecProcNode (node=0x512a970)
  at execProcnode.c:355
#19 0x000000000053f891
  in standard_ExecutorRun (queryDesc=0x2b4121b11280,
                           direction=-1628699568, count=1)
  at execMain.c:1188
#20 0x000000000054a656
  in fmgr_sql (fcinfo=0x7fff9eec1310)
  at functions.c:475
#21 0x00000000006a2f05
  in fmgr_security_definer (fcinfo=0x30006)
  at fmgr.c:957
#22 0x0000000000545ef0
  in ExecMakeFunctionResultNoSets (fcache=0x2b4121aa2b98,
                                   econtext=0x2b4121aa1798,
                                   isNull=0x7fff9eec1a90 "",
                                   isDone=<value optimized out>)
  at execQual.c:1894
#23 0x0000000000545e6c
  in ExecMakeFunctionResultNoSets (fcache=0x2b4121aa2358,
                                   econtext=0x2b4121aa1798,
                                   isNull=0x7fff9eec1b8f "",
                                   isDone=<value optimized out>)
  at execQual.c:1866
#24 0x0000000000545f8f
  in ExecQual (qual=<value optimized out>, econtext=0x2b4121aa1798,
               resultForNull=0 '\0')
  at execQual.c:4991
#25 0x00000000005476ef
  in ExecScan (node=0x2b4121aa1688, accessMtd=0x5511a0 <IndexNext>,
               recheckMtd=0x551150 <IndexRecheck>)
  at execScan.c:192
#26 0x00000000005409ea
  in ExecProcNode (node=0x2b4121aa1688)
  at execProcnode.c:382
#27 0x0000000000554935
  in ExecNestLoop (node=0x2b4121a9dea0)
  at nodeNestloop.c:154
#28 0x0000000000540a6a
  in ExecProcNode (node=0x2b4121a9dea0)
  at execProcnode.c:419
 
Full (unedited) stack traces for all four attached.
 
Notice the recursive calls to fmgr_security_definer().  I wonder
whether that might be a problem, since the comment for that function
says:
 
| This is not re-entrant, but then the fcinfo itself can't be used
| re-entrantly anyway.
 
All of these queries are similar, and involved searches using a LIKE
clause against a "searchName" "generated column" -- a function
taking the record type of the table as its parameter.  That function
then calls a function which takes several parameters,  Both
functions were changed to SECURITY DEFINER when the problems
started.
 
The functions are:
 
CREATE OR REPLACE FUNCTION "searchName"(rec "Party")
 RETURNS "SearchNameT"
 LANGUAGE sql
 IMMUTABLE
AS $$
  select "searchName"($1."nameL", $1."nameF", $1."nameM",
                      $1."suffix");
$$;

CREATE OR REPLACE FUNCTION "searchName"("nameL" "LastNameT",
                                        "nameF" "FirstNameT",
                                        "nameM" "MiddleNameT",
                                        "suffix" "NameSuffixT")
 RETURNS "SearchNameT"
 LANGUAGE sql
 IMMUTABLE
AS $$
select regexp_replace(upper(
  $1
  || case when $2 is not null or $3 is not null or $4 is not null
       then ',' || coalesce($2, '') || coalesce($3, '')
         || coalesce($4, '')
       else ''
     end),
     '[^A-Z0-9\,]', '', 'g')::"SearchNameT"
$$
 
And there is an index on "Party":
 
  "Party_SearchName" btree ("searchName"("Party".*))
 
First off, is there much chance that this is fixed between 9.0.4 and
9.0.6?  If not, what do people feel would be the most useful
information for diagnosing the problem?
 
-Kevin


Attachment: bt4.txt
Description: text/plain (5.8 KB)
Attachment: bt3.txt
Description: text/plain (4.6 KB)
Attachment: bt2.txt
Description: text/plain (5.4 KB)
Attachment: bt1.txt
Description: text/plain (8.2 KB)

Responses

pgsql-bugs by date

Next:From: John LumbyDate: 2011-12-09 19:38:34
Subject: LIKE predicate and ERROR: 42P22: could not determine which collation to use for string comparison - HINT: Use the COLLATE clause ...
Previous:From: Cihan ŞENGÜLDate: 2011-12-09 15:07:53
Subject: About Wince App

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group