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.