Re: Seems to be impossible to set a NULL search_path

On Tue, Jul 5, 2022 at 11:12 AM Bryn Llewellyn <bryn(at)yugabyte(dot)com> 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.