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

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> No comments on this?
>
> If there was a reproducible test case in your original message,
> I didn't see it, so I assumed you intended to investigate further
> on your own.  It wasn't even clear to me that this was a Postgres
> bug rather than some error in your trigger logic.

Sorry if my first post wasn't clear.  It was happening on SELECT
statements; no triggers involved.  (I had *intended* just to get
trigger functions, but had accidentally included some others.)

I wasn't able to create a small, self-contained test case with a few
hours of attempts, so I was hoping someone could suggest (from the
stack traces and other clues) how best to attempt that or what other
information might be useful.  It wasn't even clear to me that it was
OK to have one security definer function call another, based on the
code comment I quoted, so I didn't want to spend more hours on
attempting to create a test case if it simply wasn't supported.

Sad to say, the script which flagged the functions as security
definer didn't cause problems in normal testing, and were deployed
to production (in advance of a software release which will need the
expanded permissions), where the problem surfaced under user load.
The fact that the larger number of concurrent users hit the problem
where my test scripts haven't suggests some race condition, so even
if I create it here, it will probably be something where I need to
know what information to capture while it is happening.

We only need to add the security definer flag on trigger functions
at this point for the upcoming application release, but I'm not yet
confident that this is safe.

-Kevin