search_path problem with boxrange function

[jogoodma]

Summary Recent versions (9.3+) of PostgreSQL that have been patched for CVE-2018-1058 will throw the following error when a vacuum / analyze is attempted on the featureloc table.

Error

vacuumdb: vacuuming of database "prod" failed: ERROR:  function create_point(integer, integer) does not exist
LINE 1: SELECT box (create_point(0, $1), create_point($2,500000000))
                    ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT box (create_point(0, $1), create_point($2,500000000))
CONTEXT:  SQL function "boxrange" during inlining

Details This problem is caused by the binloc_boxrange and binloc_boxrange_src indices on the featureloc table. These indices are generated by the boxrange function defined in the default Chado schema. Due to changes made for CVE-2018-1058, when a function is used in an index the search_path within that function has the public schema removed, thus any other user defined functions fail to execute because they are not found in the search_path. In our case, calls to create_point from within the boxrange function fail.

Possible solutions

1. Hard code the boxrange function to use the schema name e.g. public.create_point(0, $1).

Hard coding the public schema in a function name is not ideal and would break on any installs that did not run in the public schema.

2. Use the SET SEARCH_PATH FROM CURRENT in the function definition of boxrange.

SET search_path = public;
CREATE OR REPLACE FUNCTION boxrange (bigint, bigint) RETURNS box AS
 'SELECT box (create_point(0, $1), create_point($2,500000000))'
LANGUAGE 'sql' IMMUTABLE SET SEARCH_PATH FROM CURRENT;

This is what the PostgreSQL devs currently recommend. We would need to somehow dynamically set the search_path during build/install time. Talk to @scottcain on how best to do that within the GMOD schema build/install process.

Other functions We should check for other functions that may be impacted by this change as well.

Additional resources

1. https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path
2. https://www.postgresql.org/message-id/87muz3f0xt.fsf%40news-spur.riddles.org.uk