0001-invent-trusted-extensions-5.patch

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 85ac79f..a10b665 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -8519,7 +8519,15 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
      <row>
       <entry><structfield>superuser</structfield></entry>
       <entry><type>bool</type></entry>
-      <entry>True if only superusers are allowed to install this extension</entry>
+      <entry>True if only superusers are allowed to install this extension
+       (but see <structfield>trusted</structfield>)</entry>
+     </row>
+
+     <row>
+      <entry><structfield>trusted</structfield></entry>
+      <entry><type>bool</type></entry>
+      <entry>True if the extension can be installed by non-superusers
+       with appropriate privileges</entry>
      </row>
 
      <row>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 3546e39..8d3a0d1 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1742,6 +1742,7 @@ REVOKE ALL ON accounts FROM PUBLIC;
      <listitem>
       <para>
        For databases, allows new schemas and publications to be created within
+       the database, and allows trusted extensions to be installed within
        the database.
       </para>
       <para>
@@ -1753,8 +1754,11 @@ REVOKE ALL ON accounts FROM PUBLIC;
       <para>
        For tablespaces, allows tables, indexes, and temporary files to be
        created within the tablespace, and allows databases to be created that
-       have the tablespace as their default tablespace.  (Note that revoking
-       this privilege will not alter the placement of existing objects.)
+       have the tablespace as their default tablespace.
+      </para>
+      <para>
+       Note that revoking this privilege will not alter the existence or
+       location of existing objects.
       </para>
      </listitem>
     </varlistentry>
diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index a3046f2..ffe068b 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -576,6 +576,31 @@
         version.  If it is set to <literal>false</literal>, just the privileges
         required to execute the commands in the installation or update script
         are required.
+        This should normally be set to <literal>true</literal> if any of the
+        script commands require superuser privileges.  (Such commands would
+        fail anyway, but it's more user-friendly to give the error up front.)
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><varname>trusted</varname> (<type>boolean</type>)</term>
+      <listitem>
+       <para>
+        This parameter, if set to <literal>true</literal> (which is not the
+        default), allows some non-superusers to install an extension that
+        has <varname>superuser</varname> set to <literal>true</literal>.
+        Specifically, installation will be permitted for anyone who has
+        <literal>CREATE</literal> privilege on the current database.
+        When the user executing <command>CREATE EXTENSION</command> is not
+        a superuser but is allowed to install by virtue of this parameter,
+        then the installation or update script is run as the bootstrap
+        superuser, not as the calling user.
+        This parameter is irrelevant if <varname>superuser</varname> is
+        <literal>false</literal>.
+        Generally, this should not be set true for extensions that could
+        allow access to otherwise-superuser-only abilities, such as
+        filesystem access.
        </para>
       </listitem>
      </varlistentry>
@@ -642,6 +667,18 @@
     </para>
 
     <para>
+     If the extension script contains the
+     string <literal>@extowner@</literal>, that string is replaced with the
+     (suitably quoted) name of the user calling <command>CREATE
+     EXTENSION</command> or <command>ALTER EXTENSION</command>.  Typically
+     this feature is used by extensions that are marked trusted to assign
+     ownership of selected objects to the calling user rather than the
+     bootstrap superuser.  (One should be careful about doing so, however.
+     For example, assigning ownership of a C-language function to a
+     non-superuser would create a privilege escalation path for that user.)
+    </para>
+
+    <para>
      While the script files can contain any characters allowed by the specified
      encoding, control files should contain only plain ASCII, because there
      is no way for <productname>PostgreSQL</productname> to know what encoding a
diff --git a/doc/src/sgml/ref/create_extension.sgml b/doc/src/sgml/ref/create_extension.sgml
index 36837f9..d76ac3e 100644
--- a/doc/src/sgml/ref/create_extension.sgml
+++ b/doc/src/sgml/ref/create_extension.sgml
@@ -47,14 +47,25 @@ CREATE EXTENSION [ IF NOT EXISTS ] <replaceable class="parameter">extension_name
   </para>
 
   <para>
-   Loading an extension requires the same privileges that would be
-   required to create its component objects.  For most extensions this
-   means superuser or database owner privileges are needed.
    The user who runs <command>CREATE EXTENSION</command> becomes the
    owner of the extension for purposes of later privilege checks, as well
    as the owner of any objects created by the extension's script.
   </para>
 
+  <para>
+   Loading an extension ordinarily requires the same privileges that would
+   be required to create its component objects.  For many extensions this
+   means superuser privileges are needed.
+   However, if the extension is marked <firstterm>trusted</firstterm> in
+   its control file, then it can be installed by any user who has
+   <literal>CREATE</literal> privilege on the current database.
+   In this case the extension object itself will be owned by the calling
+   user, but the contained objects will be owned by the bootstrap superuser
+   (unless the extension's script explicitly assigns them to the calling
+   user).  This configuration gives the calling user the right to drop the
+   extension, but not to modify individual objects within it.
+  </para>
+
  </refsect1>
 
  <refsect1>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index c9e75f4..c9e6060 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -317,7 +317,8 @@ CREATE VIEW pg_available_extensions AS
 
 CREATE VIEW pg_available_extension_versions AS
     SELECT E.name, E.version, (X.extname IS NOT NULL) AS installed,
-           E.superuser, E.relocatable, E.schema, E.requires, E.comment
+           E.superuser, E.trusted, E.relocatable,
+           E.schema, E.requires, E.comment
       FROM pg_available_extension_versions() AS E
            LEFT JOIN pg_extension AS X
              ON E.name = X.extname AND E.version = X.extversion;
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 01de398..ddd46f4 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -40,6 +40,7 @@
 #include "catalog/indexing.h"
 #include "catalog/namespace.h"
 #include "catalog/objectaccess.h"
+#include "catalog/pg_authid.h"
 #include "catalog/pg_collation.h"
 #include "catalog/pg_depend.h"
 #include "catalog/pg_extension.h"
@@ -84,6 +85,7 @@ typedef struct ExtensionControlFile
 	char	   *schema;			/* target schema (allowed if !relocatable) */
 	bool		relocatable;	/* is ALTER EXTENSION SET SCHEMA supported? */
 	bool		superuser;		/* must be superuser to install? */
+	bool		trusted;		/* allow becoming superuser on the fly? */
 	int			encoding;		/* encoding of the script file, or -1 */
 	List	   *requires;		/* names of prerequisite extensions */
 } ExtensionControlFile;
@@ -558,6 +560,14 @@ parse_extension_control_file(ExtensionControlFile *control,
 						 errmsg("parameter \"%s\" requires a Boolean value",
 								item->name)));
 		}
+		else if (strcmp(item->name, "trusted") == 0)
+		{
+			if (!parse_bool(item->value, &control->trusted))
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+						 errmsg("parameter \"%s\" requires a Boolean value",
+								item->name)));
+		}
 		else if (strcmp(item->name, "encoding") == 0)
 		{
 			control->encoding = pg_valid_server_encoding(item->value);
@@ -614,6 +624,7 @@ read_extension_control_file(const char *extname)
 	control->name = pstrdup(extname);
 	control->relocatable = false;
 	control->superuser = true;
+	control->trusted = false;
 	control->encoding = -1;
 
 	/*
@@ -795,6 +806,27 @@ execute_sql_string(const char *sql)
 }
 
 /*
+ * Policy function: is the given extension trusted for installation by a
+ * non-superuser?
+ *
+ * (Update the errhint logic below if you change this.)
+ */
+static bool
+extension_is_trusted(ExtensionControlFile *control)
+{
+	AclResult	aclresult;
+
+	/* Never trust unless extension's control file says it's okay */
+	if (!control->trusted)
+		return false;
+	/* Allow if user has CREATE privilege on current database */
+	aclresult = pg_database_aclcheck(MyDatabaseId, GetUserId(), ACL_CREATE);
+	if (aclresult == ACLCHECK_OK)
+		return true;
+	return false;
+}
+
+/*
  * Execute the appropriate script file for installing or updating the extension
  *
  * If from_version isn't NULL, it's an update
@@ -806,35 +838,56 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 						 List *requiredSchemas,
 						 const char *schemaName, Oid schemaOid)
 {
+	bool		switch_to_superuser = false;
 	char	   *filename;
+	Oid			save_userid = 0;
+	int			save_sec_context = 0;
 	int			save_nestlevel;
 	StringInfoData pathbuf;
 	ListCell   *lc;
 
 	/*
-	 * Enforce superuser-ness if appropriate.  We postpone this check until
-	 * here so that the flag is correctly associated with the right script(s)
-	 * if it's set in secondary control files.
+	 * Enforce superuser-ness if appropriate.  We postpone these checks until
+	 * here so that the control flags are correctly associated with the right
+	 * script(s) if they happen to be set in secondary control files.
 	 */
 	if (control->superuser && !superuser())
 	{
-		if (from_version == NULL)
+		if (extension_is_trusted(control))
+			switch_to_superuser = true;
+		else if (from_version == NULL)
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("permission denied to create extension \"%s\"",
 							control->name),
-					 errhint("Must be superuser to create this extension.")));
+					 control->trusted
+					 ? errhint("Must have CREATE privilege on current database to create this extension.")
+					 : errhint("Must be superuser to create this extension.")));
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("permission denied to update extension \"%s\"",
 							control->name),
-					 errhint("Must be superuser to update this extension.")));
+					 control->trusted
+					 ? errhint("Must have CREATE privilege on current database to update this extension.")
+					 : errhint("Must be superuser to update this extension.")));
 	}
 
 	filename = get_extension_script_filename(control, from_version, version);
 
 	/*
+	 * If installing a trusted extension on behalf of a non-superuser, become
+	 * the bootstrap superuser.  (This switch will be cleaned up automatically
+	 * if the transaction aborts, as will the GUC changes below.)
+	 */
+	if (switch_to_superuser)
+	{
+		GetUserIdAndSecContext(&save_userid, &save_sec_context);
+		SetUserIdAndSecContext(BOOTSTRAP_SUPERUSERID,
+							   save_sec_context | SECURITY_LOCAL_USERID_CHANGE);
+	}
+
+	/*
 	 * Force client_min_messages and log_min_messages to be at least WARNING,
 	 * so that we won't spam the user with useless NOTICE messages from common
 	 * script actions like creating shell types.
@@ -907,6 +960,22 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 										CStringGetTextDatum("ng"));
 
 		/*
+		 * If the script uses @extowner@, substitute the calling username.
+		 */
+		if (strstr(c_sql, "@extowner@"))
+		{
+			Oid			uid = switch_to_superuser ? save_userid : GetUserId();
+			const char *userName = GetUserNameFromId(uid, false);
+			const char *qUserName = quote_identifier(userName);
+
+			t_sql = DirectFunctionCall3Coll(replace_text,
+											C_COLLATION_OID,
+											t_sql,
+											CStringGetTextDatum("@extowner@"),
+											CStringGetTextDatum(qUserName));
+		}
+
+		/*
 		 * If it's not relocatable, substitute the target schema name for
 		 * occurrences of @extschema@.
 		 *
@@ -953,6 +1022,12 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 	 * Restore the GUC variables we set above.
 	 */
 	AtEOXact_GUC(true, save_nestlevel);
+
+	/*
+	 * Restore authentication state if needed.
+	 */
+	if (switch_to_superuser)
+		SetUserIdAndSecContext(save_userid, save_sec_context);
 }
 
 /*
@@ -2111,8 +2186,8 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 	{
 		ExtensionVersionInfo *evi = (ExtensionVersionInfo *) lfirst(lc);
 		ExtensionControlFile *control;
-		Datum		values[7];
-		bool		nulls[7];
+		Datum		values[8];
+		bool		nulls[8];
 		ListCell   *lc2;
 
 		if (!evi->installable)
@@ -2133,24 +2208,26 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 		values[1] = CStringGetTextDatum(evi->name);
 		/* superuser */
 		values[2] = BoolGetDatum(control->superuser);
+		/* trusted */
+		values[3] = BoolGetDatum(control->trusted);
 		/* relocatable */
-		values[3] = BoolGetDatum(control->relocatable);
+		values[4] = BoolGetDatum(control->relocatable);
 		/* schema */
 		if (control->schema == NULL)
-			nulls[4] = true;
+			nulls[5] = true;
 		else
-			values[4] = DirectFunctionCall1(namein,
+			values[5] = DirectFunctionCall1(namein,
 											CStringGetDatum(control->schema));
 		/* requires */
 		if (control->requires == NIL)
-			nulls[5] = true;
+			nulls[6] = true;
 		else
-			values[5] = convert_requires_to_datum(control->requires);
+			values[6] = convert_requires_to_datum(control->requires);
 		/* comment */
 		if (control->comment == NULL)
-			nulls[6] = true;
+			nulls[7] = true;
 		else
-			values[6] = CStringGetTextDatum(control->comment);
+			values[7] = CStringGetTextDatum(control->comment);
 
 		tuplestore_putvalues(tupstore, tupdesc, values, nulls);
 
@@ -2178,16 +2255,18 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 				values[1] = CStringGetTextDatum(evi2->name);
 				/* superuser */
 				values[2] = BoolGetDatum(control->superuser);
+				/* trusted */
+				values[3] = BoolGetDatum(control->trusted);
 				/* relocatable */
-				values[3] = BoolGetDatum(control->relocatable);
+				values[4] = BoolGetDatum(control->relocatable);
 				/* schema stays the same */
 				/* requires */
 				if (control->requires == NIL)
-					nulls[5] = true;
+					nulls[6] = true;
 				else
 				{
-					values[5] = convert_requires_to_datum(control->requires);
-					nulls[5] = false;
+					values[6] = convert_requires_to_datum(control->requires);
+					nulls[6] = false;
 				}
 				/* comment stays the same */
 
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index bef50c7..2228256 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9496,9 +9496,9 @@
   proname => 'pg_available_extension_versions', procost => '10',
   prorows => '100', proretset => 't', provolatile => 's',
   prorettype => 'record', proargtypes => '',
-  proallargtypes => '{name,text,bool,bool,name,_name,text}',
-  proargmodes => '{o,o,o,o,o,o,o}',
-  proargnames => '{name,version,superuser,relocatable,schema,requires,comment}',
+  proallargtypes => '{name,text,bool,bool,bool,name,_name,text}',
+  proargmodes => '{o,o,o,o,o,o,o,o}',
+  proargnames => '{name,version,superuser,trusted,relocatable,schema,requires,comment}',
   prosrc => 'pg_available_extension_versions' },
 { oid => '3084', descr => 'list an extension\'s version update paths',
   proname => 'pg_extension_update_paths', procost => '10', prorows => '100',
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 70e1e2f..2ab2115 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1311,11 +1311,12 @@ pg_available_extension_versions| SELECT e.name,
     e.version,
     (x.extname IS NOT NULL) AS installed,
     e.superuser,
+    e.trusted,
     e.relocatable,
     e.schema,
     e.requires,
     e.comment
-   FROM (pg_available_extension_versions() e(name, version, superuser, relocatable, schema, requires, comment)
+   FROM (pg_available_extension_versions() e(name, version, superuser, trusted, relocatable, schema, requires, comment)
      LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion))));
 pg_available_extensions| SELECT e.name,
     e.default_version,