[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):

 Replying to [comment:3 strk]:
 > > our function would now make a function they own be part of postgis
 >
 > Woudn't it be much simpler to just FORCE ownership of functions, in the
 upgrade script ?
 > We would check the ownership of a function which is known to have been
 in postgis forever (postgis_version?) and set ownership of all functions,
 after the CREATE OR REPLACE, to that user.
 >
 > Would this block the kind of attack you describe ?
 >
 > The enforcement of all function of the extension could be also done in a
 single final statement, as we can easily get the list of all functions in
 our extension, and could set ownership of those function match ownership
 of the extension itself


 I fear such a step would be flagged by vulnerability scanners as a
 vulnerability.  Cause changing permissions is the kind of thing they are
 looking for.  Although I guess they mostly look for ALTER ROLE so perhaps
 not.  I still think doing CREATE instead of CREATE OR REPLACE for the
 create extension script is trivial enough don't you think?  We don't need
 to change ownership, just change or regex for the postgis-3.3.0.sql file
 to replace CREATE OR REPLACE with CREATE.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5165#comment:4>
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.