PostgreSQL 13 is just around the corner and one little, but important feature that was added are trusted extensions. When you want to add an extension to a database you need to be superuser for most of the extension or you need to implement something like this (please also note the comment from Hans at the bottom of the blog). This is where this new feature helps: If extensions are marked as “trusted” you do not need to be superuser anymore to install them into a database.

If an extension is trusted or not is specified in the extension’s control file, so all of these should be fine to install without being superuser (depending on how you installed postgres you need to check where the extension files actually are located):

postgres@centos8pg:/u01/app/postgres/product/DEV/db_1/share/extension/ [pgdev] grep trusted *.control | grep -v comment
bool_plperl.control:trusted = true
btree_gin.control:trusted = true
btree_gist.control:trusted = true
citext.control:trusted = true
cube.control:trusted = true
dict_int.control:trusted = true
fuzzystrmatch.control:trusted = true
hstore.control:trusted = true
intarray.control:trusted = true
isn.control:trusted = true
jsonb_plperl.control:trusted = true
lo.control:trusted = true
ltree.control:trusted = true
pgcrypto.control:trusted = true
pg_trgm.control:trusted = true
plperl.control:trusted = true
plpgsql.control:trusted = true
seg.control:trusted = true
tablefunc.control:trusted = true
tcn.control:trusted = true
tsm_system_rows.control:trusted = true
tsm_system_time.control:trusted = true
unaccent.control:trusted = true

Using seq as an example we should be able to install that as a normal user as it is marked as trusted:

postgres=# create user u with login password 'u';
CREATE ROLE
postgres=# c postgres u
You are now connected to database "postgres" as user "u".
postgres=> create extension seg;
ERROR:  permission denied to create extension "seg"
HINT:  Must have CREATE privilege on current database to create this extension.

Being able to connect to the database is not enough, you need to have the create privilege on the database:

postgres=> c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# grant create on database postgres to u;
GRANT
postgres=# c postgres u
You are now connected to database "postgres" as user "u".
postgres=> create extension seg;
CREATE EXTENSION

That is a huge help if you are using many extensions in many databases as users now can do that on their own.

What you also could do, if you trust your users is to adjust the control file of a specific extension. The bloom extension is not marked as trusted:

postgres@centos8pg:/u01/app/postgres/product/DEV/db_1/share/extension/ [pgdev] cat bloom.control
# bloom extension
comment = 'bloom access method - signature file based index'
default_version = '1.0'
module_pathname = '$libdir/bloom'
relocatable = true

If we modify that to (be aware that you’ll loose that once you patch/re-install PostgreSQL):

postgres@centos8pg:/u01/app/postgres/product/DEV/db_1/share/extension/ [pgdev] cat bloom.control
# bloom extension
comment = 'bloom access method - signature file based index'
default_version = '1.0'
module_pathname = '$libdir/bloom'
relocatable = true
trusted = true

… this one can be installed by a normal user as well:

postgres=# c postgres u
You are now connected to database "postgres" as user "u".
postgres=> create extension bloom;
CREATE EXTENSION
postgres=> 

This is of course not recommended.