> On Friday, January 3, 2025, Jan Behrens <jbe-mlist@magnetkern.de> wrote: > > > > I would like to know if the above example is correct. It seems overall > > bulky, but I haven't found a better way, assuming that it can be > > unknown where a particular extension has been installed to. In > > particular I feel a bit insecure about where I have to fully qualify, > > and where not. See the comments in the code above. > > > Short answer, you cannot looking at a definition and know the answer - > whether the code is going to be executed in a sanitized search_path is what > matters.
I don't understand. Do you mean my last example is wrong / insecure? If so, why?
It is at risk because it depends on the session search_path. That is all. Whether that risk turns into a failure to execute depends on how/when it is executed. I'm not that comfortable talking about security risks in this context though the current design goal is to mitigate such security issues by setting things up so the function execution fails rather than is executed insecurely. This is presently mainly done by setting the search_path to just effectively pg_catalog before executing the query, breaking any code depending on other schemas existing in the search_path.
> Anything that would be executed during pg_restore has to be made > safe. Therefore, code that is only ever executed by applications directly > can use swarch_path.
Why should the function be executed during pg_restore?
If the function is used in building an index, or a materialized view, are the common cases. Trigger functions too.
Note, this is talking about evaluating functions generally, not the one provided here specifically.
I could do that, but I would like to understand if that is really necessary as it makes the interface more complicated, and I would like to avoid unnecessary complexity in my interface.
Is it really impossible to have functions without SET search_path in the definition of a PL/pgSQL function if I fully-qualify all types in the DECLARE section and if all other non-qualified identifiers occur after set_config('search_path', ...)?
If you add a set_config to the body of the function then you indeed avoid the problem. It is basically equivalent to adding a SET clause to the create function command. In this case even when the function is executed in a sanitized search_path environment (such as the one established by pg_restore) you are not relying on it. That non-reliance is all that really matters.
