Re: Security definer "generated column" function used in index

No comments on this? It seems to me that at a minimum this needs
better documentation of a limitation, and the conditions under which
you hit the problem. I'm not sure there isn't an outright bug here.
We would like to flag all of our trigger functions as SECURITY
DEFINER, but there are triggers which do DML which can fire other
triggers, and at this point I'm not sure whether that's safe.

Anyone?

-Kevin

On 2011-12-09 12:49 PM I wrote:

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