[postgis-tickets] [PostGIS] #5194: SELECT postgis_extensions_upgrade() fails with pgextwlist

[PostGIS]

#5194: SELECT postgis_extensions_upgrade() fails with pgextwlist
------------------------------------+---------------------------
  Reporter:  robe                   |      Owner:  strk
      Type:  defect                 |     Status:  new
  Priority:  blocker                |  Milestone:  PostGIS 3.3.0
 Component:  build/upgrade/install  |    Version:  master
Resolution:                         |   Keywords:
------------------------------------+---------------------------
Comment (by robe):

 Replying to [comment:6 gdt]:
 > Can you clarify the permission issue, as I don't really follow "cloud"?
 Is it exactly "someone who is db owner of FOO, but not superuser, and has
 postgis installed in FOO, cannot upgrade postgis"?   And that they were
 able to install, and would be able to upgrade if some particular thing
 were not true?
 >
 > Are we working around "user doesn't have the documented/expected
 permissions for postgis in the first place"?  Something else?
 >

 The way many cloud DbaaS work, they never give super user rights to any
 user.  So they have kind of a pseudo super user  that is allowed to run
 `CREATE EXTENSION` and `ALTER EXTENSION`.

 While these commands are in place, the user is briefly elevated to super
 user status.  Kind of like sudo.

 However our postgis_extensions_upgrade() runs in the context of the
 regular user, so can only do things like updating system catalogs when it
 is run by a real super user, or is running within the `CREATE EXTENSION`
 and `ALTER EXTENSION` commands we call in that function.

 Sandro introduced a commit to update the pg_extension table, to mark the
 postgis and family of extensions to `ANY` (essentially forcing the ALTER
 EXTENSION to run the ANY--3.3.0.sql instead of one of the symlinks (or
 copies of files).  It was an attempt to not have to ship useless files.


 That would have been fine, if it only ran if it could not find an
 extension upgrade path.  But as it stands, I think it breaks all cloud
 installs.  Given that a good number of users are now deploying on DbaaS,
 this is going to break for a lot of people.

 I think it's still useful to have it there and run, if it can't find the
 install path, as it would save some people who decide to delete all these
 files, and perhaps later we can even have an option in config not to
 install them especially for users who run their own system.  It should
 however not do that if it can find the install -- there are functions to
 query for if the path is available or not.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5194#comment:7>
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.