Обсуждение: Changing function from SECURITY DEFINER to SECURITY INVOKER changes query plan?

Поиск
Список
Период
Сортировка

Changing function from SECURITY DEFINER to SECURITY INVOKER changes query plan?

От
Joe Van Dyk
Дата:
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?

От
Tom Lane
Дата:
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?

От
Joe Van Dyk
Дата:
On Thu, Nov 21, 2013 at 6:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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

Thanks. Is that documented somewhere? I looked, couldn't find anything.