search_path related bug in sql wrapper function?

[Lonepsycho]

Hi,

version() PostgreSQL 9.6.8 on x86_64-pc-linux-gnu (Debian 9.6.8-1.pgdg80+1), compiled by gcc (Debian 4.9.2-10+deb8u1) 4.9.2, 64-bit

postgis_full_version() POSTGIS="2.3.3 r15473" GEOS="3.4.2-CAPI-1.8.2 r3921" SFCGAL="1.3.0" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="0.11.99" RASTER

After migrating to PostgreSQL 9.6.8 the fun begun. There is a problem with wrapper functions (i believe c functions have been fixed) related to postgres security patch that targets default public schema usage;

in our case we have some functional indexes, that fail to pass test SET SEARCH_PATH TO pg_catalog; ANALYZE table;

the function in question is CREATE OR REPLACE FUNCTION public.st_buffer (

public.geography, double precision, text

) RETURNS public.geography AS $body$ SELECT geography(ST_Transform(ST_Buffer(ST_Transform(geometry($1), public._ST_BestSRID($1)), $2, $3), 4326)) $body$ LANGUAGE 'sql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER COST 100;

COMMENT ON FUNCTION public.st_buffer(public.geography, double precision, text) IS 'args: g1, radius_of_buffer, buffer_style_parameters - (T)Returns a geometry covering all points within a given distancefrom the input geometry.';

pg_restore fails:

ERROR: function geometry(public.geography) does not exist at character 54 HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT geography(ST_Transform(ST_Buffer(ST_Transform(geometry($1), public._ST_BestSRID($1)), $2), 4326)) CONTEXT: SQL function "st_buffer" during inlining CREATE INDEX idx_tg_geocode_cache_box ON gpt_geodata.tg_geocode_cache USING gist (((public.st_buffer((public.st_setsrid(public.st_makepoint(lon, lat), 4326))::public.geography, (3)::double precision))::public.geometry));

ANALYZE TABLE ... also fails. Guess same problem persists in all SQL, wrapper, postgis functions that target other postgis functions.

Marius

[Lonepsycho]

sorry, double checked postgis_full_version() is:

POSTGIS="2.4.3 r16312" PGSQL="96" GEOS="3.4.2-CAPI-1.8.2 r3921" SFCGAL="1.3.0" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="0.11.99" RASTER

[robe]

In 16481:

schema qualify geography functions (including ST_Buffer)
References #4052 for PostGIS 2.3.7

[robe]

In 16482:

schema qualify geography functions (including ST_Buffer)
References #4052 for PostGIS 2.4.4

[robe]

In 16483:

schema qualify geography functions (including ST_Buffer)
Closes #4052 for PostGIS 2.5 (trunk)