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

On Mon, Sep 25, 2023 at 12:00 PM Joe Conway <mail(at)joeconway(dot)com> wrote:
> Should there be a way to have a separate "execution" search_path?

I have heard that idea proposed before, and I don't think it's a
terrible idea, but I also don't think it fixes anything terribly
fundamental. I think it's pretty normal for people to define functions
and procedures and then call them from other functions and procedures,
and if you do that, then you need that schema in your execution search
path. Of course, if somebody doesn't do that, or schema-qualifies all
such references, then this becomes useful for defense in depth. But I
find it hard to see it as anything more than defense in depth because
I think a lot of people will need to have use cases where they need to
put non-system schemas into the execution search path, and such people
wouldn't really benefit from the existence of this feature.

Slightly off-topic, but I wonder whether, if we do this, we ought to
do it by adding some kind of a marker to the existing search_path,
rather than by creating a new GUC. For example, maybe putting & before
a schema name means that it can be searched, but only for
non-executable things. Then you could set search_path = &jconway,
pg_catalog or something of that kind. It could potentially be more
powerful to have it be a completely separate setting, but if we do
that, everyone who currently needs to secure search_path properly
starts needing to also secure execution_search_path properly. This is
one of those cases where two is not better than one.

--
Robert Haas
EDB: http://www.enterprisedb.com