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.

1 files changed, 15 insertions, 1 deletions

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 7c0c64cc7ad..ea1818b1d57 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -45,6 +45,7 @@
 
 #include "access/attnum.h"
 #include "access/sysattr.h"
+#include "access/transam.h"
 #include "catalog/pg_cast.h"
 #include "catalog/pg_class.h"
 #include "catalog/pg_default_acl.h"
@@ -7041,6 +7042,19 @@ dumpExtension(Archive *fout, ExtensionInfo *extinfo)
 	if (!extinfo->dobj.dump || dataOnly)
 		return;
 
+	/*
+	 * In a regular dump, we use IF NOT EXISTS so that there isn't a problem
+	 * if the extension already exists in the target database; this is
+	 * essential for installed-by-default extensions such as plpgsql.
+	 *
+	 * In binary-upgrade mode, that doesn't work well, so instead we skip
+	 * extensions with OIDs less than FirstNormalObjectId; those were
+	 * presumably installed by initdb, and we assume they'll exist in the
+	 * target installation too.
+	 */
+	if (binary_upgrade && extinfo->dobj.catId.oid < (Oid) FirstNormalObjectId)
+		return;
+
 	q = createPQExpBuffer();
 	delq = createPQExpBuffer();
 	labelq = createPQExpBuffer();
@@ -7051,7 +7065,7 @@ dumpExtension(Archive *fout, ExtensionInfo *extinfo)
 
 	if (!binary_upgrade)
 	{
-		appendPQExpBuffer(q, "CREATE EXTENSION %s WITH SCHEMA %s;\n",
+		appendPQExpBuffer(q, "CREATE EXTENSION IF NOT EXISTS %s WITH SCHEMA %s;\n",
 						  qextname, fmtId(extinfo->namespace));
 	}
 	else