ST_Estimated_Extent assumes postgis is installed in public

[robe]

I just tried to upgrade one of my databases running:

                                                                                                                            postgis_full_version
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="2.1.0SVN r11230" GEOS="3.4.0dev-CAPI-1.8.0 r0" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.8" LIBJSON="UNKNOWN" (core procs from "2.1.0SVN r11197" need upgrade) RASTER (raster procs from "2.1.0SVN r11197" need upgrade)
(1 row)

to the latest using my favorite:

ALTER EXTENSION postgis UPDATE TO "2.1.0SVNnext";

and to my horror, I got this error:

ERROR:  function public.st_estimatedextent(text, text, text) does not exist
LINE 3:     SELECT public.ST_EstimatedExtent($1, $2, $3);
                   ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:   SELECT _postgis_deprecate('ST_Estimated_Extent', 'ST_EstimatedExtent', '2.1.0');
    -- explicit schema for security reason
    SELECT public.ST_EstimatedExtent($1, $2, $3);
---------------------------

I suspect its because this particular database I have has postgis installed in a separate schema called postgis and we've got public.ST_EstimatedExtent hardcoded somewhere.

[robe]

Confirmed, its only an issue if you have postgis installed in non-public.

IF I do this with this database:

ALTER EXTENSION postgis SET SCHEMA public;
ALTER EXTENSION postgis UPDATE TO "2.1.0SVNnext";
ALTER EXTENSION postgis SET SCHEMA postgis;

Works fine but of course no one should be required to go thru hoops like this just because they want to keep the trillions of functions of postgis away from their core data.

[robe]

Ahah — the smoking gun — You have this defined as:

CREATE OR REPLACE FUNCTION ST_estimated_extent(text,text,text) RETURNS box2d AS
  $$ SELECT _postgis_deprecate('ST_Estimated_Extent', 'ST_EstimatedExtent', '2.1.0');
    -- explicit schema for security reason
    SELECT public.ST_EstimatedExtent($1, $2, $3);
  $$
	LANGUAGE 'sql' IMMUTABLE STRICT SECURITY DEFINER;

And evidentally gets called during upgrade for some reason.

We can't assume anything about where PostGIS is installed.

[strk]

Use of the schema is to avoid running arbitrary functions with the permission of the wrapper function definer.

[robe]

strk,

How about we just take off the SECURITY DEFINER off this function and also remove public.

The ST_EstimatedExtent function already has SECURITY DEFINER on it, so anyone running a hacked function will only do as much harm as they are allowed, and the right function will continue to use the elevated privileges of the definer.

[strk]

Oh, interesting idea. If you tested it and it works, I'm all for it.

[robe]

Okay changed at r11249

I didn't go thru the trouble of trying to stab my finger, but it installs now even if my postgis is in a different schema. I added SECURITY INVOKER, but that is just more syntactic deliberance than anything since that is the default behavior anyway.