On Tue, Jul 5, 2022 at 11:12 AM Bryn Llewellyn <bryn@xxxxxxxxxxxx> wrote:
The section "Writing SECURITY DEFINER Functions Safely":
https://www.postgresql.org/docs/current/sql-createfunction.html#id-1.9.3.67.10.2
explains the risk brought if a bad actor creates an object that preemps what the developer intended by putting it in a schema that's ahead of the intended object in the search_path.
You can avoid this risk by always using fully qualified object names. It seems strange that the section doesn't mention this obvious approach. Is it vulnerable to subversion in a way that I haven't spotted?
No, it is probably more that by naming the schema explicitly the vulnerability regarding search_path is by definition a non-factor. This is knowledge the reader was probably assumed to have already so did not need to be repeated here. Also, unless you are going to write: operator(pg_catalog.=) in your function the advice to always use schema qualifications is not going to be taken seriously. You still have to protect the search_path from being unknown.
I suppose that there are use cases where the actual plan is to resolve to the first object that has the right name as the search_path is traversed. (But this seems not to be the common case.)
Due to function caching effects this rarely ends up working well anyway.
This is where setting the search_path as an attribute of a subprogram helps.
I wondered about a self-documenting belt-and-braces approach: use fully qualified object names in the subprograms source code and declare that I want no risk of mis-use of the search_path by setting it to null. But this seems not to be possible. Am I right?
Based upon the advice regarding the temporary schema the correct search_path to set isn't "empty" but "pg_catalog", "pg_temp". While this does violate "DRY" principles it is the solution you are looking for.
I'm admittedly unsure why a non-security-definer doesn't care that the pg_temp schema comes before pg_catalog by default, probably this advice is good for any function.
The main problem is that by doing search_path manipulation on the function you prevent inlining it into the main query. I would much appreciate attention being given to solving that limitation and making the advice "always set search_path to < pg_catalog, pg_temp > on your functions and procedures" be more viable in practice.
Attaching a set search_path clause to a view would be another nice feature. Even if all it did was, at compile time, replace all operator invocations with their operator(schema.op) version without having to force the view author to do so manually.
Being able to write: "SET search_path TO null;" instead of "SET search_path TO '';" doesn't make my list. Now, "SET search_path TO DEFAULT" maybe would work - if it helped solve the inlining limitation. Have it go along with updating postgresql.conf to default to 'pg_catalog, pg_temp'.
David J.
