Re: policies with security definer option for allowing inline optimization

On Fri, Apr 02, 2021 at 02:24:59PM -0700, Dan Lynch wrote:
> Does anyone know details of, or where to find more information about the
> implications of the optimizer on the quals/checks for the policies being
> functions vs inline?

Roughly, the PostgreSQL optimizer treats LANGUAGE SQL functions like a C
compiler treats "extern inline" functions. Other PostgreSQL functions behave
like C functions in a shared library. Non-SQL functions can do arbitrary
things, and the optimizer knows only facts like their volatility and the value
given in CREATE FUNCTION ... COST.

> I suppose if the
> get_group_ids_of_current_user() function is marked as STABLE, would the
> optimizer cache this value for every row in a SELECT that returned
> multiple rows?

While there was a patch to implement caching, it never finished. The
optimizer is allowed to, and sometimes does, choose plan shapes that reduce
the number of function calls.

> Is it possible that if the function is sql vs plpgsql it
> makes a difference?

Yes; see inline_function() in the PostgreSQL source. The hard part of
$SUBJECT is creating the infrastructure to inline across a SECURITY DEFINER
boundary. Currently, a single optimizable statement operates under just one
user identity. Somehow, the optimizer would need to translate the SECURITY
DEFINER call into a list of moments where the executor shall switch user ID,
then maintain that list across further optimization steps. security_barrier
views are the most-similar thing, but as Joe Conway mentioned, views differ
from SECURITY DEFINER in crucial ways.