Hi hackers,
We got trusted extensions since version 13, and some of them are relocatable:
postgres=> select distinct name from pg_available_extension_versions
where trusted and relocatable;
name
-----------------
tsm_system_time
pgcrypto
dict_int
tablefunc
uuid-ossp
seg
hstore
ltree
intarray
tcn
unaccent
btree_gist
citext
pg_trgm
isn
btree_gin
tsm_system_rows
cube
fuzzystrmatch
lo
(20 rows)
But, when a non-superuser is trying to use ALTER EXTENSION ... SET SCHEMA, it always fails.
Example:
test=> create schema s1;
CREATE SCHEMA
test=> create schema s2;
CREATE SCHEMA
test=> create extension seg with schema s1;
CREATE EXTENSION
test=> alter extension seg set schema s2;
ERROR: must be owner of type s1.seg
Is it an expected behaviour or oversight?
Another thing is that we allow CREATE EXTENSION ... WITH SCHEMA even if an unprivileged user doesn't have CREATE permissions on it.
For example, the following works:
test=> create extension seg with schema pg_catalog;
CREATE EXTENSION
But, at the same time relocation to pg_catalog will fail with the error that the user doesn't have permissions:
test=> create extension seg;
CREATE EXTENSION
test=> alter extension seg set schema pg_catalog;
ERROR: permission denied for schema pg_catalog
This looks like inconsistent behaviour.
Should it be fixed?
There are two main points:
1. Either CREATE EXTENSION ... WITH SCHEMA should also check that the user has CREATE permissions on a schema, or we need to remove this check from ALTER EXTENSION ... SET SCHEMA.
2. Independently of that we need to switch to a BOOTSTRAP_SUPERUSERID in the AlterExtensionNamespace()
What do you think?
Regards,
--
Alexander Kukushkin
