From: | Bryn Llewellyn <bryn(at)yugabyte(dot)com> |
---|---|
To: | pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Seems to be impossible to set a NULL search_path |
Date: | 2022-07-05 18:12:48 |
Message-ID: | 4B5F24E6-2FF9-4964-BF07-C609DC38E377@yugabyte.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
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.) 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?
I've confirmed that even a superuser cannot create objects in a "special" schema like "pg_catalog" or "pg_toast". So this gives me a workaround to the limitation that I cannot force the use of fully qualified names by setting a null search_path: I could set the attribute of my subprogram to "pg_catalog".
Apart from the fact that, as I suppose, this would be a rare and therefore possibly puzzling pattern (so clear doc about the purpose would be needed), are there any risks that I haven't spotted?
Finally, what do you think of a possible future enhancement to allow setting a null search_path?
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2022-07-05 18:14:07 | Re: How to upgrade postgres version 8 to 13 |
Previous Message | Adrian Klaver | 2022-07-05 18:05:04 | Re: How to upgrade postgres version 8 to 13 |