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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Joe Van Dyk <joe(at)tanga(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Changing function from SECURITY DEFINER to SECURITY INVOKER changes query plan?
Date: 2013-11-22 02:11:44
Message-ID: 15466.1385086304@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Joe Van Dyk <joe(at)tanga(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joe Van Dyk 2013-11-22 02:14:26 Re: Changing function from SECURITY DEFINER to SECURITY INVOKER changes query plan?
Previous Message Joe Van Dyk 2013-11-22 02:08:40 Changing function from SECURITY DEFINER to SECURITY INVOKER changes query plan?