Mark PostGIS extensions as trusted ( PG13+ )

[strk]

PostgreSQL 13 introduced the concept of "trusted" extensions: ​https://www.postgresql.org/docs/13/extend-extensions.html

I guess PostGIS could be marked as trusted, effectively allowing unprivileged users to install the extension.

It may also simplify sandboxed environment testing (or fix related bugs) - see #5566

[robe]

No don't do it.

[robe]

@strk,

I haven't read thru all the ramifications of what that allows or does not allow. So I'd rather we not do it. May never make sense to do it.

I think we should test with #5566 before we venture down that path.

[Sandro Santilli <strk@…>]

In 5739cacd/git:

run_test.pl: add POSTGIS_REGRESS_DB_OWNER env support

Allows specifying a DB role to be given ownership of the regress
database and be used to create extension of POSTGIS_REGRESS_EXT_CREATOR
is not also provided to override that.

References #5212
References #5566
References #5567

[pramsey]

We are so not a trusted extension.

[robe]

@strk before you commit this, you need to ask permission of the PSC.

[rouault]

​https://www.postgresql.org/docs/13/extend-extensions.html mentions "Generally, this should not be set true for extensions that could allow access to otherwise-superuser-only abilities, such as file system access." So obviously you don't want postgis_raster to be trusted (at least when out_db support is enabled). But even regular postgis can access files like using ST_Transform() with a PROJ.4 string using +nadgrids=/some/path . ST_TransformPipeline() can also use various PROJ operations that load grids or JSON files.

[robe]

Replying to rouault:

​https://www.postgresql.org/docs/13/extend-extensions.html mentions "Generally, this should not be set true for extensions that could allow access to otherwise-superuser-only abilities, such as file system access." So obviously you don't want postgis_raster to be trusted (at least when out_db support is enabled). But even regular postgis can access files like using ST_Transform() with a PROJ.4 string using +nadgrids=/some/path . ST_TransformPipeline() can also use various PROJ operations that load grids or JSON files.

Thanks for the input. Yah given all that I think we should definitely stay untrusted. If a sysadmin really wants to mark our extensions trusted, they can do so by changing the extension file.