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

On Sat, 2023-08-12 at 11:25 -0700, Andres Freund wrote:
>
> I'm not sure that anything based, directly or indirectly, on
> search_path
> really is a realistic way to get there.

Can you explain a little more? I see what you mean generally, that
search_path is an imprecise thing, and that it leaves room for
ambiguity and mistakes.

But I also think we can do a lot better than we're doing today and
still retain the basic concept of search_path, which is good because
it's deeply integrated into postgres, and it's not clear that we're
going to get away from it any time soon.

>
>
> I think that'd be pretty painful from a UX perspective. Having to
> write
> e.g. operators as operator(schema, op) just sucks as an experience.

I'm not suggesting that the user fully-qualify everything; I'm
suggesting that the include a "SET search_path" clause if they depend
on anything other than pg_catalog.

> And with
> extensions plenty of operators will live outside of pg_catalog, so
> there is
> plenty things that will need qualifying.

In my proposal, that would still involve a "SET search_path TO
myextension, pg_catalog, pg_temp".

The main reason that's bad is that adding pg_temp at the end is painful
UX -- just something that the user needs to remember to do with little
obvious reason or observable impact; but it has important security
implications. Perhaps we should just not implicitly include pg_temp for
a function's search_path (at least for the case of CREATE FUNCTION ...
SEARCH SYSTEM)?

>   And because of things like type
> coercion search, which prefers "bettering fitting" coercions over
> search path
> order, you can't just put "less important" things later in search
> path.

I understand this introduces some ambiguity, but you just can't include
schemas in the search_path that you don't trust, for similar reasons as
$PATH. If you have a few objects you'd like to access in another user's
schema, fully-qualify them.

> We can't just store the oids at the time, because that'd end up very
> fragile -
> tables/functions/... might be dropped and recreated etc and thus
> change their
> oid.

Robert suggested something along those lines[1]. I won't rule it out,
but I agree that there are quite a few things left to figure out.

> But we could change the core PLs to rewrite all the queries (*) so
> that
> they schema qualify absolutely everything, including operators and
> implicit
> type casts.

So not quite like "SET search_path FROM CURRENT": you resolve it to a
specific "schemaname.objectname", but stop just short of resolving to a
specific OID?

An interesting compromise, but I'm not sure what the benefit is vs. SET
search_path FROM CURRENT (or some defined search_path).

> That way objects referenced by functions can still be replaced, but
> search
> path can't be used to "inject" objects in different schemas.
> Obviously it
> could lead to errors on some schema changes - e.g. changing a column
> type
> might mean that a relevant cast lives in a different place than with
> the old
> type - but I think that'll be quite rare. Perhaps we could offer a
> ALTER
> FUNCTION ... REFRESH REFERENCES; or such?

Hmm. I feel like that's making things more complicated. I'd find it
more straightforward to use something like Robert's approach of fully
parsing something, and then have the REFRESH command reparse it when
something needs updating. Or perhaps just create all of the dependency
entries more like a view query and then auto-refresh.

> (*) Obviously the one thing that doesn't work for is use of EXECUTE
> in plpgsql
> and similar constructs elsewhere. I'm not sure there's much that can
> be done
> to make that safe, but it's worth thinking about more.

I think it would be really nice to have some better control over the
search_path regardless, because it still helps with cases like this. A
lot of C functions build queries, and I don't think it's reasonable to
constantly worry about the ambiguity of the schema for "=".

Regards,
Jeff Davis

[1]
https://www.postgresql.org/message-id/CA%2BTgmobd%3DeFRGWHhfG4mG2cA%2BdsVuA4jpBvD8N1NS%3DVc9eHFQg%40mail.gmail.com