On Thu, Mar 15, 2018 at 01:42:08AM -0700, Noah Misch wrote:
> Dozens of psql queries call pg_*_is_visible functions, which need the
> search_path pertinent for user-entered queries. By itself, (3) doesn't work
> for such queries. Even if you implemented (2), using psql with a hostile
> search_path would remain approximately hopeless. It's too hard for psql users
> to write safe input. Thus, I'd be -1 on accepting (2) or a similarly-ugly
> change in psql. Any proposal for schema qualification in psql faces stiff
> competition from the alternative of doing nothing.
Good point. One thing that could happen here is to extend
pg_*_is_visible with an extra parameter which allows the caller to
enforce the value of search_path. This actually brings more value to
approach 3), because by fetching first the value of search_path, you
could enforce the visibility functions to scan this given namespace for
the time of their execution, but still make the whole query run using a
safe search_path.
> For src/test, I would change nothing. If tests malfunction in a hostile
> database, that is not important. Keeping tests easy to add, modify and review
> is more important.
OK. I would still suggest to fix the schema qualification for
pg_backend_pid though. This is a one-liner, and simple to address.
This applies as well to psql for array_remove().
So based on the feedback here is what we could at least do now as a
minimal fix set, in the shape of:
- Patch functions in information_schema.sql, using either operator() or
SET search_path.
- Patch function qualifications I found here and there. Those are
mainly one-liners, and gives readers better references for their own
queries.
--
Michael