Mark some contrib modules as "trusted".

This allows these modules to be installed into a database without
superuser privileges (assuming that the DBA or sysadmin has installed
the module's files in the expected place).  You only need CREATE
privilege on the current database, which by default would be
available to the database owner.

The following modules are marked trusted:

btree_gin
btree_gist
citext
cube
dict_int
earthdistance
fuzzystrmatch
hstore
hstore_plperl
intarray
isn
jsonb_plperl
lo
ltree
pg_trgm
pgcrypto
seg
tablefunc
tcn
tsm_system_rows
tsm_system_time
unaccent
uuid-ossp

In the future we might mark some more modules trusted, but there
seems to be no debate about these, and on the whole it seems wise
to be conservative with use of this feature to start out with.

Discussion: https://postgr.es/m/32315.1580326876@sss.pgh.pa.us

23 files changed, 151 insertions, 4 deletions

diff --git a/doc/src/sgml/btree-gin.sgml b/doc/src/sgml/btree-gin.sgml
index 314e001fefb..5bc5a054e8d 100644
--- a/doc/src/sgml/btree-gin.sgml
+++ b/doc/src/sgml/btree-gin.sgml
@@ -32,6 +32,12 @@
   two separate indexes that would have to be combined via bitmap ANDing.
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title>Example Usage</title>
 
diff --git a/doc/src/sgml/btree-gist.sgml b/doc/src/sgml/btree-gist.sgml
index 774442feeee..3b61d276a38 100644
--- a/doc/src/sgml/btree-gist.sgml
+++ b/doc/src/sgml/btree-gist.sgml
@@ -52,6 +52,12 @@
   <type>oid</type>, and <type>money</type>.
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title>Example Usage</title>
 
diff --git a/doc/src/sgml/citext.sgml b/doc/src/sgml/citext.sgml
index 85aa339d8ba..667824fb0b8 100644
--- a/doc/src/sgml/citext.sgml
+++ b/doc/src/sgml/citext.sgml
@@ -24,6 +24,12 @@
   </para>
  </tip>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title>Rationale</title>
 
diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml
index b626a345f3e..08bb110b515 100644
--- a/doc/src/sgml/contrib.sgml
+++ b/doc/src/sgml/contrib.sgml
@@ -54,7 +54,7 @@
   Many modules supply new user-defined functions, operators, or types.
   To make use of one of these modules, after you have installed the code
   you need to register the new SQL objects in the database system.
-  In <productname>PostgreSQL</productname> 9.1 and later, this is done by executing
+  This is done by executing
   a <xref linkend="sql-createextension"/> command.  In a fresh database,
   you can simply do
 
@@ -62,15 +62,24 @@
 CREATE EXTENSION <replaceable>module_name</replaceable>;
 </programlisting>
 
-  This command must be run by a database superuser.  This registers the
-  new SQL objects in the current database only, so you need to run this
-  command in each database that you want
+  This command registers the new SQL objects in the current database only,
+  so you need to run it in each database that you want
   the module's facilities to be available in.  Alternatively, run it in
   database <literal>template1</literal> so that the extension will be copied into
   subsequently-created databases by default.
  </para>
 
  <para>
+  For all these modules, <command>CREATE EXTENSION</command> must be run
+  by a database superuser, unless the module is
+  considered <quote>trusted</quote>, in which case it can be run by any
+  user who has <literal>CREATE</literal> privilege on the current
+  database.  Modules that are trusted are identified as such in the
+  sections that follow.  Generally, trusted modules are ones that cannot
+  provide access to outside-the-database functionality.
+ </para>
+
+ <para>
   Many modules allow you to install their objects in a schema of your
   choice.  To do that, add <literal>SCHEMA
   <replaceable>schema_name</replaceable></literal> to the <command>CREATE EXTENSION</command>
diff --git a/doc/src/sgml/cube.sgml b/doc/src/sgml/cube.sgml
index c6e586270aa..71772d799fe 100644
--- a/doc/src/sgml/cube.sgml
+++ b/doc/src/sgml/cube.sgml
@@ -12,6 +12,12 @@
   representing multidimensional cubes.
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title>Syntax</title>
 
diff --git a/doc/src/sgml/dict-int.sgml b/doc/src/sgml/dict-int.sgml
index c15cbd0e4d1..b556f1b4daf 100644
--- a/doc/src/sgml/dict-int.sgml
+++ b/doc/src/sgml/dict-int.sgml
@@ -15,6 +15,12 @@
   unique words, which greatly affects the performance of searching.
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title>Configuration</title>
 
diff --git a/doc/src/sgml/earthdistance.sgml b/doc/src/sgml/earthdistance.sgml
index 670fc9955f7..7ca2c40e37d 100644
--- a/doc/src/sgml/earthdistance.sgml
+++ b/doc/src/sgml/earthdistance.sgml
@@ -23,6 +23,12 @@
   project.)
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title>Cube-Based Earth Distances</title>
 
diff --git a/doc/src/sgml/fuzzystrmatch.sgml b/doc/src/sgml/fuzzystrmatch.sgml
index 373ac4891df..382e54be918 100644
--- a/doc/src/sgml/fuzzystrmatch.sgml
+++ b/doc/src/sgml/fuzzystrmatch.sgml
@@ -20,6 +20,12 @@
   </para>
  </caution>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title>Soundex</title>
 
diff --git a/doc/src/sgml/hstore.sgml b/doc/src/sgml/hstore.sgml
index 94ccd1201e1..64c2477fffc 100644
--- a/doc/src/sgml/hstore.sgml
+++ b/doc/src/sgml/hstore.sgml
@@ -15,6 +15,12 @@
   simply text strings.
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title><type>hstore</type> External Representation</title>
 
@@ -633,6 +639,11 @@ ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || '';
    convention).  If you use them, <type>hstore</type> values are mapped to
    Python dictionaries.
   </para>
+
+  <para>
+   Of these additional extensions, <literal>hstore_plperl</literal> is
+   considered trusted; the rest are not.
+  </para>
  </sect2>
 
  <sect2>
diff --git a/doc/src/sgml/intarray.sgml b/doc/src/sgml/intarray.sgml
index b633cf36778..025cbca616e 100644
--- a/doc/src/sgml/intarray.sgml
+++ b/doc/src/sgml/intarray.sgml
@@ -24,6 +24,12 @@
   treated as though it were a linear array in storage order.
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title><filename>intarray</filename> Functions and Operators</title>
 
diff --git a/doc/src/sgml/isn.sgml b/doc/src/sgml/isn.sgml
index 21174549eb5..6c61f14fdcd 100644
--- a/doc/src/sgml/isn.sgml
+++ b/doc/src/sgml/isn.sgml
@@ -21,6 +21,12 @@
   dropped from a future version of this module.
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title>Data Types</title>
 
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 6ff87518705..1b6aaf0a558 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -622,6 +622,13 @@ SELECT jdoc-&gt;'guid', jdoc-&gt;'name' FROM api WHERE jdoc @&gt; '{"tags": ["qu
    use them, <type>jsonb</type> values are mapped to Python dictionaries,
    lists, and scalars, as appropriate.
   </para>
+
+  <para>
+   Of these extensions, <literal>jsonb_plperl</literal> is
+   considered <quote>trusted</quote>, that is, it can be installed by
+   non-superusers who have <literal>CREATE</literal> privilege on the
+   current database.  The rest require superuser privilege to install.
+  </para>
  </sect2>
 
  <sect2 id="datatype-jsonpath">
diff --git a/doc/src/sgml/lo.sgml b/doc/src/sgml/lo.sgml
index cce37932ec6..0a4f2e4449a 100644
--- a/doc/src/sgml/lo.sgml
+++ b/doc/src/sgml/lo.sgml
@@ -13,6 +13,12 @@
   and a trigger <function>lo_manage</function>.
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title>Rationale</title>
 
diff --git a/doc/src/sgml/ltree.sgml b/doc/src/sgml/ltree.sgml
index 3ddd335b8c9..b4e07f65109 100644
--- a/doc/src/sgml/ltree.sgml
+++ b/doc/src/sgml/ltree.sgml
@@ -13,6 +13,12 @@
   Extensive facilities for searching through label trees are provided.
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title>Definitions</title>
 
diff --git a/doc/src/sgml/pgcrypto.sgml b/doc/src/sgml/pgcrypto.sgml
index 0acd11ed555..cc916ff1d65 100644
--- a/doc/src/sgml/pgcrypto.sgml
+++ b/doc/src/sgml/pgcrypto.sgml
@@ -17,6 +17,12 @@
   <productname>PostgreSQL</productname>.
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title>General Hashing Functions</title>
 
diff --git a/doc/src/sgml/pgtrgm.sgml b/doc/src/sgml/pgtrgm.sgml
index 3e6fd7395fb..049f496869c 100644
--- a/doc/src/sgml/pgtrgm.sgml
+++ b/doc/src/sgml/pgtrgm.sgml
@@ -15,6 +15,12 @@
   strings.
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title>Trigram (or Trigraph) Concepts</title>
 
diff --git a/doc/src/sgml/seg.sgml b/doc/src/sgml/seg.sgml
index d07329f5d17..2492de911ad 100644
--- a/doc/src/sgml/seg.sgml
+++ b/doc/src/sgml/seg.sgml
@@ -14,6 +14,12 @@
   making it especially useful for representing laboratory measurements.
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title>Rationale</title>
 
diff --git a/doc/src/sgml/tablefunc.sgml b/doc/src/sgml/tablefunc.sgml
index 007e9c62f56..ad435d6dc3e 100644
--- a/doc/src/sgml/tablefunc.sgml
+++ b/doc/src/sgml/tablefunc.sgml
@@ -14,6 +14,12 @@
   multiple rows.
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title>Functions Provided</title>
 
diff --git a/doc/src/sgml/tcn.sgml b/doc/src/sgml/tcn.sgml
index aa2fe4f00af..82afe9ada4b 100644
--- a/doc/src/sgml/tcn.sgml
+++ b/doc/src/sgml/tcn.sgml
@@ -18,6 +18,12 @@
  </para>
 
  <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
+ <para>
   Only one parameter may be supplied to the function in a
   <literal>CREATE TRIGGER</literal> statement, and that is optional.  If supplied
   it will be used for the channel name for the notifications.  If omitted
diff --git a/doc/src/sgml/tsm-system-rows.sgml b/doc/src/sgml/tsm-system-rows.sgml
index 3dcd948ff86..071ff301d07 100644
--- a/doc/src/sgml/tsm-system-rows.sgml
+++ b/doc/src/sgml/tsm-system-rows.sgml
@@ -33,6 +33,12 @@
   the <literal>REPEATABLE</literal> clause.
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title>Examples</title>
 
diff --git a/doc/src/sgml/tsm-system-time.sgml b/doc/src/sgml/tsm-system-time.sgml
index fd8e9995443..cd074926d85 100644
--- a/doc/src/sgml/tsm-system-time.sgml
+++ b/doc/src/sgml/tsm-system-time.sgml
@@ -35,6 +35,12 @@
   the <literal>REPEATABLE</literal> clause.
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title>Examples</title>
 
diff --git a/doc/src/sgml/unaccent.sgml b/doc/src/sgml/unaccent.sgml
index 547ac54a71e..5cd716a2aa5 100644
--- a/doc/src/sgml/unaccent.sgml
+++ b/doc/src/sgml/unaccent.sgml
@@ -21,6 +21,12 @@
   normalizing dictionary for the <filename>thesaurus</filename> dictionary.
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title>Configuration</title>
 
diff --git a/doc/src/sgml/uuid-ossp.sgml b/doc/src/sgml/uuid-ossp.sgml
index 0fbabbfda24..54d7813d38e 100644
--- a/doc/src/sgml/uuid-ossp.sgml
+++ b/doc/src/sgml/uuid-ossp.sgml
@@ -16,6 +16,12 @@
   linkend="functions-uuid"/> for built-in ways to generate UUIDs.
  </para>
 
+ <para>
+  This module is considered <quote>trusted</quote>, that is, it can be
+  installed by non-superusers who have <literal>CREATE</literal> privilege
+  on the current database.
+ </para>
+
  <sect2>
   <title><literal>uuid-ossp</literal> Functions</title>