[postgis-tickets] [PostGIS] #5165: CREATE EXTENSION scripts should use CREATE instead of CREATE OR REPLACE

[PostGIS]

#5165: CREATE EXTENSION scripts should use CREATE instead of CREATE OR REPLACE
------------------------------------+---------------------------
  Reporter:  robe                   |      Owner:  strk
      Type:  defect                 |     Status:  new
  Priority:  medium                 |  Milestone:  PostGIS 3.3.0
 Component:  build/upgrade/install  |    Version:  master
Resolution:                         |   Keywords:
------------------------------------+---------------------------
Comment (by robe):

 I was thinking that for 3.3.0 (and earlier) we do it just for the ``CREATE
 EXTENSION ..`` call.

 I can't think of a situation where someone should be allowed to run CREATE
 EXTENSION and actually have any postgis functions installed.

 For the upgrade CREATE OR REPLACE function is safe as long as we know it
 would have been installed by CREATE EXTENSION.  Cause there is no chance
 of someone other than a super user having created that function since non-
 super users can't overwrite other people's functions.

 The danger for ALTER EXTENSION here is if a regular untrusted user knows a
 function exists in postgis in a newer version not yet installed.  They go
 create a function with that signature with the future expectation that
 postgis will be upgraded.  Then when ALTER EXTENSION UPDATE is done, our
 function would now make a function they own be part of postgis, and they
 could then change that function putting malicious things in there.  If a
 super user then runs this coopted function, they could accidentally
 elevate the privileges of said user (cause it would be running under super
 user rights).

 In practice people can easily avoid this by preventing untrusted users
 from creating things in a schema where postgis is installed.  We could
 also force ownership of all postgis packaged functions at end to be owned
 by the person running create extension.  I think the force is not a good
 idea because I suspect DbaaS are looking for that kind of stuff and would
 treat it as a threat and bale out.  It probably would be seen as such too
 by many vulnerability scanners.  So our attempt to mitigate such a thing
 would look like we're trying cause such a thing to happen.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5165#comment:2>
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.