Re: Fixes for missing schema qualifications - Mailing list pgsql-hackers

On Wed, Mar 14, 2018 at 10:50:38AM +0900, Michael Paquier wrote:
> On Sat, Mar 10, 2018 at 08:36:34AM +0000, Noah Misch wrote:
> > This qualifies some functions, but it leaves plenty of unqualified operators.
> 
> Yeah, I know that, and i don't have a perfect reply to offer to you.
> There are a couple of methods that we could use to tackle that:
> 1) For functions, enforce search_path with a SET search_path =
> 'pg_catalog' command.  However this has a performance impact.
> 2) Enforce operators qualification with operator(pg_catalog.foo).  This
> has no impact on performance, but repeating that all over the place is
> rather ugly, particularly for psql's describe.c and tab-completion.c.
> 3) Tweak dynamically search_path before running a query:
> - Save the existing search_path value by issuing SHOW search_path.
> - Use ALWAYS_SECURE_SEARCH_PATH_SQL to enforce the path.
> - Set back search_path based on the previous value.
> This logic can happen in a dedicated wrapper, but this impacts
> performance as it requires extra round trips to the server.
> 
> For information_schema.sql, we are talking about tweaking 12 functions.
> So I think that we could live with 2).  To simplify user's life, we
> could also recommend just to users to issue a ALTER FUNCTION SET
> search_path to fix the problem for all functions, that's easier to
> digest.

For information_schema, I'd pick (1).  Performance is not very important
there, and reading or editing code like this is painful:

  (($2 OPERATOR(pg_catalog.-) 4) OPERATOR(pg_catalog.>>) 16) OPERATOR(pg_catalog.&) 65535

(If performance becomes important, one could implement a way to automatically
translate sql-language function source to fully-qualified SQL at CREATE
FUNCTION time or at plan time.)

> For the rest, which basically concerns psql, I have been thinking that
> actually using 2) would be the most painful approach, still something
> which does not impact the user experience, while 3) is easier to
> back-patch by minimizing the code footprint and avoids also any kind of
> future problems.

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.

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.

nm