[PostGIS] #5853: Bug in upgrade scripts for topology and tiger geocoder

[PostGIS]

#5853: Bug in upgrade scripts for topology and tiger geocoder
---------------------+--------------------
 Reporter:  sbrys    |      Owner:  strk
     Type:  defect   |     Status:  new
 Priority:  low      |  Milestone:
Component:  upgrade  |    Version:  master
 Keywords:           |
---------------------+--------------------
 I have discovered a bug in the upgrade scripts of topology and tiger
 geocoder.

 The extension helper function, postgis_extension_remove_objects, had a bug
 introduced with commit
 [https://git.osgeo.org/gitea/postgis/postgis/commit/0b67924d22ec6ef5c095c41660d7cd9788027245
 0b67924]. We incorrectly set var_class to ‘pg_catalog.pg_proc’
 [https://git.osgeo.org/gitea/postgis/postgis/src/branch/master/extensions/postgis_extension_helper.sql.in#L33
 (line 33)], which is compared against the relname column of pg_class
 [https://git.osgeo.org/gitea/postgis/postgis/src/branch/master/extensions/postgis_extension_helper.sql.in#L67
 (line 67)], ultimately failing as ‘pg_catalog.pg_proc’ != ‘pg_proc’,
 leading us to not unpack any of the functions.

 This seems to have gone under the radar, considering it’s been nearly
 three years. I think a possible explanation is that since 2012 the
 PostgreSQL community commit !08dd23c had enabled DROP FUNCTION to work
 inside of an extension script without first dissociating the function from
 the extension.

 Now, the unfortunate part is that by fixing this bug we introduce
 ourselves to the fallout of CVE-2022-2625, as the subsequent CREATE OR
 REPLACE FUNCTION statements fail as the functions are no longer owned by
 the extension (similar to #5209). These scripts relied on those CREATE OR
 REPLACE FUNCTION statements to package them back into the extension, but
 we can no longer do that.

 Please correct me if I’m wrong, but since it seems ALTER EXTENSION DROP is
 no longer needed, could we get rid of postgis_extension_remove_objects,
 and subsequently remove_from_extension.sql.in?
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5853>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.