First, as a long time user of the pgextwlist extension, I'm happy to see this functionality appearing in core. However, as a long term user of that extension, I can say that ability to create an extension as a non-super user is pretty limited in itself in a lot of cases. Many extensions both in contrib and external ones (like PostGIS for example) don't give appropriate permissions to actually use the extension.
Taking postgresql_fdw as an example.
I modify the control file to allow it to be trusted (I'd suggest how to do this be documented as well)
cat /usr/pgsql-13/share/extension/postgres_fdw.control
# postgres_fdw extension
comment = 'foreign-data wrapper for remote PostgreSQL servers'
default_version = '1.0'
module_pathname = '$libdir/postgres_fdw'
relocatable = true
trusted = true
My current, non-super user:
\du admin
List of roles
Role name | Attributes | Member of
-----------+------------------------+-----------
admin | Create role, Create DB | {}
Create the extension:
test2=> create extension postgres_fdw ;
CREATE EXTENSION
Actually try and use it:
test2=> CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');
ERROR: permission denied for foreign-data wrapper postgres_fdw
To make it work, you need a superuser to run GRANT usage ... on the foreign data wrapper to the user that creates it. And while a superuser can apply the needed grants, it's an awkard feature to allow a non-superuser to create an extension, then require a superuser to grant things to allow it to actually be used.
There are lots of other cases of such behavior. pg_stats_statements_reset() for example. Big external extensions like PostGIS also suffer from permissions issues.
Brad.
