Thread: Changing function from SECURITY DEFINER to SECURITY INVOKER changes query plan?
Changing function from SECURITY DEFINER to SECURITY INVOKER changes query plan?
From
Joe Van Dyk
Date:
I had a function that was set to SECURITY INVOKER. I needed to give access to a view that uses this function to a role, so I made the function SECURITY DEFINER.
The function is STABLE and is usually inlined and takes 2 ms to run.
Immediately, the function quit being inlined and took 1500ms to run.
Changing the function back to SECURITY DEFINER let the function be inlined again.
On postgresql 9.3.1.
Is this expected behavior?
Re: Changing function from SECURITY DEFINER to SECURITY INVOKER changes query plan?
From
Tom Lane
Date:
Joe Van Dyk <joe@tanga.com> writes:
> I had a function that was set to SECURITY INVOKER. I needed to give access
> to a view that uses this function to a role, so I made the function
> SECURITY DEFINER.
> The function is STABLE and is usually inlined and takes 2 ms to run.
> Immediately, the function quit being inlined and took 1500ms to run.
> Changing the function back to SECURITY DEFINER let the function be inlined
> again.
> On postgresql 9.3.1.
> Is this expected behavior?
Yes. SECURITY DEFINER functions can't be inlined --- there would be
noplace to effect the change of user ID.
regards, tom lane
Re: Changing function from SECURITY DEFINER to SECURITY INVOKER changes query plan?
From
Joe Van Dyk
Date:
On Thu, Nov 21, 2013 at 6:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Yes. SECURITY DEFINER functions can't be inlined --- there would beJoe Van Dyk <joe@tanga.com> writes:
> I had a function that was set to SECURITY INVOKER. I needed to give access
> to a view that uses this function to a role, so I made the function
> SECURITY DEFINER.
> The function is STABLE and is usually inlined and takes 2 ms to run.
> Immediately, the function quit being inlined and took 1500ms to run.
> Changing the function back to SECURITY DEFINER let the function be inlined
> again.
> On postgresql 9.3.1.
> Is this expected behavior?
noplace to effect the change of user ID.
regards, tom lane
Thanks. Is that documented somewhere? I looked, couldn't find anything.