[postgis-tickets] [PostGIS] #5442: Database search_path does not what it intends to do

[PostGIS]

#5442: Database search_path does not what it intends to do
----------------------------------------+---------------------------
 Reporter:  JelteF                      |      Owner:  pramsey
     Type:  patch                       |     Status:  new
 Priority:  medium                      |  Milestone:  PostGIS 3.3.4
Component:  postgis                     |    Version:  master
 Keywords:  search_path topology tiger  |
----------------------------------------+---------------------------
 So there's two functions that run ALTER DATABASE ... SET to include a
 postgis extension schema in the default search_path. One that's used by
 the topology extension and one that's used by the tiger extension.

 The topology extension has the most bugs, because it was not kept up to
 date with the tiger one. The tiger one also has bugs though:

 1. It uses reset_val instead of boot_val to build the search_path. This is
 problematic if the extension is being created for a user that has a
 specific search_path configured. In our case we do ALTER USER postgres SET
 search_path = pg_catalog. This is done for security reasons to avoid
 executing functions from the public schema with the superuser. By using
 reset_val as the base for the database its search_path, the new
 search_path will remove public for all users.
 2. At the end of the function it sets the search_path to the original
 default search_path using:

 {{{
 EXECUTE 'SET search_path = ' || var_cur_search_path;
 }}}

 This is incorrect because it removes any changes from the search_path that
 were done in the session. This is problematic for us, because Postgres
 includes the schemas of dependent extensions in the search_path of the
 session. But this removes them again.

 Attached is a patch that fixes the tiger one, and changes the topology one
 with the same new code.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5442>
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.