Re: CREATE FUNCTION ... SEARCH { DEFAULT | SYSTEM | SESSION }

On Fri, 2023-08-18 at 14:25 +0200, Peter Eisentraut wrote:
>
> Not specifying SEARCH would have the same issue?

Not specifying SEARCH is equivalent to SEARCH DEFAULT, and that gives
us some control over what happens. In the proposed patch, a GUC
determines whether it behaves like SEARCH SESSION (the default for
compatibility reasons) or SEARCH SYSTEM (safer).

> > 2. There's no way to explicitly request that you'd actually like to
> > use
> > the session's search_path, so it makes it very hard to ever change
> > the
> > default.
>
> That sounds like something that should be fixed independently.  I
> could
> see this being useful for other GUC settings, like I want to run a
> function explicitly with the session's work_mem.

I'm confused about how this would work. It doesn't make sense to set a
GUC to be the session value in postgresql.conf, because there's no
session yet. And it doesn't really make sense in a top-level session,
because it would just be a no-op (right?). It maybe makes sense in a
function, but I'm still not totally clear on what that would mean.

>
> True, but is that specific to functions?  Maybe I want a safe
> search_path just in general, for a session or something.

I agree this is a somewhat orthogonal problem and we should have a way
to keep pg_temp out of the search_path entirely. We just need to agree
on a string representation of a search path that omits pg_temp. One
idea would be to have special identifiers "!pg_temp" and "!pg_catalog"
that would cause those to be excluded entirely.

>
> I'm not sure I follow that.  When you say a function should be
> context-insensitive, you could also say, a function should be
> context-sensitive, but have a separate context.  Which is kind of how
> it
> works now.  Maybe not well enough.

For functions called from index expressions or constraints, you want
the function's result to only depend on its arguments; otherwise you
can easily violate a constraint or cause an index to return wrong
results.

You're right that there is some other context, like the database
default collation, but (a) that's mostly nailed down; and (b) if it
changes unexpectedly that also causes problems.

> > I'm open to suggestion about other ways to improve it, but SEARCH
> > is
> > what I came up with.
>
> Some extensions of the current mechanism, like search_path = safe,
> search_path = session, search_path = inherit, etc. might work.

I had considered some new special names like this in search path, but I
didn't come up with a specific proposal that I liked. Do you have some
more details about how this would help get us to a safe default?

Regards,
Jeff Davis