Allow non-superusers to create (some) extensions.

Remove the unconditional superuser permissions check in CREATE EXTENSION,
and instead define a "superuser" extension property, which when false
(not the default) skips the superuser permissions check.  In this case
the calling user only needs enough permissions to execute the commands
in the extension's installation script.  The superuser property is also
enforced in the same way for ALTER EXTENSION UPDATE cases.

In other ALTER EXTENSION cases and DROP EXTENSION, test ownership of
the extension rather than superuserness.  ALTER EXTENSION ADD/DROP needs
to insist on ownership of the target object as well; to do that without
duplicating code, refactor comment.c's big switch for permissions checks
into a separate function in objectaddress.c.

I also removed the superuserness checks in pg_available_extensions and
related functions; there's no strong reason why everybody shouldn't
be able to see that info.

Also invent an IF NOT EXISTS variant of CREATE EXTENSION, and use that
in pg_dump, so that dumps won't fail for installed-by-default extensions.
We don't have any of those yet, but we will soon.

This is all per discussion of wrapping the standard procedural languages
into extensions.  I'll make those changes in a separate commit; this is
just putting the core infrastructure in place.

5 files changed, 46 insertions, 8 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 88eaca0bea0..b14fb72cab8 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -6460,8 +6460,8 @@
 
   <para>
    The <structname>pg_available_extensions</structname> view lists the
-   extensions that are available for installation.  This view can only
-   be read by superusers.  See also the
+   extensions that are available for installation.
+   See also the
    <link linkend="catalog-pg-extension"><structname>pg_extension</structname></link>
    catalog, which shows the extensions currently installed.
   </para>
@@ -6522,8 +6522,8 @@
 
   <para>
    The <structname>pg_available_extension_versions</structname> view lists the
-   specific extension versions that are available for installation.  This view
-   can only be read by superusers.  See also the <link
+   specific extension versions that are available for installation.
+   See also the <link
    linkend="catalog-pg-extension"><structname>pg_extension</structname></link>
    catalog, which shows the extensions currently installed.
   </para>
@@ -6561,6 +6561,12 @@
      </row>
 
      <row>
+      <entry><structfield>superuser</structfield></entry>
+      <entry><type>bool</type></entry>
+      <entry>True if only superusers are allowed to install this extension</entry>
+     </row>
+
+     <row>
       <entry><structfield>relocatable</structfield></entry>
       <entry><type>bool</type></entry>
       <entry>True if extension can be relocated to another schema</entry>
diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index 63a917d3c94..de0dc2663bd 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -464,6 +464,19 @@
      </varlistentry>
 
      <varlistentry>
+      <term><varname>superuser</varname> (<type>boolean</type>)</term>
+      <listitem>
+       <para>
+        If this parameter is <literal>true</> (which is the default),
+        only superusers can create the extension or update it to a new
+        version.  If it is set to <literal>false</>, just the privileges
+        required to execute the commands in the installation or update script
+        are required.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
       <term><varname>relocatable</varname> (<type>boolean</type>)</term>
       <listitem>
        <para>
diff --git a/doc/src/sgml/ref/alter_extension.sgml b/doc/src/sgml/ref/alter_extension.sgml
index d12aee251b5..e8c9608666e 100644
--- a/doc/src/sgml/ref/alter_extension.sgml
+++ b/doc/src/sgml/ref/alter_extension.sgml
@@ -114,7 +114,9 @@ ALTER EXTENSION <replaceable class="PARAMETER">extension_name</replaceable> DROP
   </para>
 
   <para>
-   Only superusers can execute <command>ALTER EXTENSION</command>.
+   You must own the extension to use <command>ALTER EXTENSION</command>.
+   The <literal>ADD</>/<literal>DROP</> forms require ownership of the
+   added/dropped object as well.
   </para>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/create_extension.sgml b/doc/src/sgml/ref/create_extension.sgml
index d3b5fb009b3..818bc0c4b48 100644
--- a/doc/src/sgml/ref/create_extension.sgml
+++ b/doc/src/sgml/ref/create_extension.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-CREATE EXTENSION <replaceable class="parameter">extension_name</replaceable>
+CREATE EXTENSION [ IF NOT EXISTS ] <replaceable class="parameter">extension_name</replaceable>
     [ WITH ] [ SCHEMA <replaceable class="parameter">schema</replaceable> ]
              [ VERSION <replaceable class="parameter">version</replaceable> ]
              [ FROM <replaceable class="parameter">old_version</replaceable> ]
@@ -51,7 +51,12 @@ CREATE EXTENSION <replaceable class="parameter">extension_name</replaceable>
   </para>
 
   <para>
-   Only superusers can execute <command>CREATE EXTENSION</command>.
+   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>
 
  </refsect1>
@@ -61,6 +66,18 @@ CREATE EXTENSION <replaceable class="parameter">extension_name</replaceable>
 
     <variablelist>
      <varlistentry>
+      <term><literal>IF NOT EXISTS</></term>
+      <listitem>
+       <para>
+        Do not throw an error if an extension with the same name already
+        exists.  A notice is issued in this case.  Note that there is no
+        guarantee that the existing extension is anything like the one that
+        would have been created.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
       <term><replaceable class="parameter">extension_name</replaceable></term>
       <listitem>
        <para>
diff --git a/doc/src/sgml/ref/drop_extension.sgml b/doc/src/sgml/ref/drop_extension.sgml
index 1e09ec4c7a7..979a6ebc15e 100644
--- a/doc/src/sgml/ref/drop_extension.sgml
+++ b/doc/src/sgml/ref/drop_extension.sgml
@@ -34,7 +34,7 @@ DROP EXTENSION [ IF EXISTS ] <replaceable class="PARAMETER">extension_name</repl
   </para>
 
   <para>
-   An extension can only be dropped by a superuser.
+   You must own the extension to use <command>DROP EXTENSION</command>.
   </para>
  </refsect1>