Invent "trusted" extensions, and remove the pg_pltemplate catalog.

This patch creates a new extension property, "trusted".  An extension
that's marked that way in its control file can be installed by a
non-superuser who has the CREATE privilege on the current database,
even if the extension contains objects that normally would have to be
created by a superuser.  The objects within the extension will (by
default) be owned by the bootstrap superuser, but the extension itself
will be owned by the calling user.  This allows replicating the old
behavior around trusted procedural languages, without all the
special-case logic in CREATE LANGUAGE.  We have, however, chosen to
loosen the rules slightly: formerly, only a database owner could take
advantage of the special case that allowed installation of a trusted
language, but now anyone who has CREATE privilege can do so.

Having done that, we can delete the pg_pltemplate catalog, moving the
knowledge it contained into the extension script files for the various
PLs.  This ends up being no change at all for the in-core PLs, but it is
a large step forward for external PLs: they can now have the same ease
of installation as core PLs do.  The old "trusted PL" behavior was only
available to PLs that had entries in pg_pltemplate, but now any
extension can be marked trusted if appropriate.

This also removes one of the stumbling blocks for our Python 2 -> 3
migration, since the association of "plpythonu" with Python 2 is no
longer hard-wired into pg_pltemplate's initial contents.  Exactly where
we go from here on that front remains to be settled, but one problem
is fixed.

Patch by me, reviewed by Peter Eisentraut, Stephen Frost, and others.

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

6 files changed, 163 insertions, 15 deletions

diff --git a/src/pl/plperl/GNUmakefile b/src/pl/plperl/GNUmakefile
index 9b1c5141014..e4d0a0bf32e 100644
--- a/src/pl/plperl/GNUmakefile
+++ b/src/pl/plperl/GNUmakefile
@@ -55,8 +55,10 @@ endif # win32
 
 SHLIB_LINK = $(perl_embed_ldflags)
 
-REGRESS_OPTS = --dbname=$(PL_TESTDB) --load-extension=plperl  --load-extension=plperlu
-REGRESS = plperl plperl_lc plperl_trigger plperl_shared plperl_elog plperl_util plperl_init plperlu plperl_array plperl_call plperl_transaction
+REGRESS_OPTS = --dbname=$(PL_TESTDB)
+REGRESS = plperl_setup plperl plperl_lc plperl_trigger plperl_shared \
+	plperl_elog plperl_util plperl_init plperlu plperl_array \
+	plperl_call plperl_transaction
 # if Perl can support two interpreters in one backend,
 # test plperl-and-plperlu cases
 ifneq ($(PERL),)
diff --git a/src/pl/plperl/expected/plperl_setup.out b/src/pl/plperl/expected/plperl_setup.out
new file mode 100644
index 00000000000..faeb645c5f8
--- /dev/null
+++ b/src/pl/plperl/expected/plperl_setup.out
@@ -0,0 +1,66 @@
+--
+-- Install the plperl and plperlu extensions
+--
+-- Before going ahead with the to-be-tested installations, verify that
+-- a non-superuser is allowed to install plperl (but not plperlu) when
+-- suitable permissions have been granted.
+CREATE USER regress_user1;
+CREATE USER regress_user2;
+SET ROLE regress_user1;
+CREATE EXTENSION plperl;  -- fail
+ERROR:  permission denied to create extension "plperl"
+HINT:  Must have CREATE privilege on current database to create this extension.
+CREATE EXTENSION plperlu;  -- fail
+ERROR:  permission denied to create extension "plperlu"
+HINT:  Must be superuser to create this extension.
+RESET ROLE;
+DO $$
+begin
+  execute format('grant create on database %I to regress_user1',
+                 current_database());
+end;
+$$;
+SET ROLE regress_user1;
+CREATE EXTENSION plperl;
+CREATE EXTENSION plperlu;  -- fail
+ERROR:  permission denied to create extension "plperlu"
+HINT:  Must be superuser to create this extension.
+CREATE FUNCTION foo1() returns int language plperl as '1;';
+SELECT foo1();
+ foo1 
+------
+    1
+(1 row)
+
+-- Should be able to change privileges on the language
+revoke all on language plperl from public;
+SET ROLE regress_user2;
+CREATE FUNCTION foo2() returns int language plperl as '2;';  -- fail
+ERROR:  permission denied for language plperl
+SET ROLE regress_user1;
+grant usage on language plperl to regress_user2;
+SET ROLE regress_user2;
+CREATE FUNCTION foo2() returns int language plperl as '2;';
+SELECT foo2();
+ foo2 
+------
+    2
+(1 row)
+
+SET ROLE regress_user1;
+-- Should be able to drop the extension, but not the language per se
+DROP LANGUAGE plperl CASCADE;
+ERROR:  cannot drop language plperl because extension plperl requires it
+HINT:  You can drop extension plperl instead.
+DROP EXTENSION plperl CASCADE;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to function foo1()
+drop cascades to function foo2()
+-- Clean up
+RESET ROLE;
+DROP OWNED BY regress_user1;
+DROP USER regress_user1;
+DROP USER regress_user2;
+-- Now install the versions that will be used by subsequent test scripts.
+CREATE EXTENSION plperl;
+CREATE EXTENSION plperlu;
diff --git a/src/pl/plperl/plperl--1.0.sql b/src/pl/plperl/plperl--1.0.sql
index f716ba1c563..5ff31e74e55 100644
--- a/src/pl/plperl/plperl--1.0.sql
+++ b/src/pl/plperl/plperl--1.0.sql
@@ -1,11 +1,20 @@
 /* src/pl/plperl/plperl--1.0.sql */
 
-/*
- * Currently, all the interesting stuff is done by CREATE LANGUAGE.
- * Later we will probably "dumb down" that command and put more of the
- * knowledge into this script.
- */
+CREATE FUNCTION plperl_call_handler() RETURNS language_handler
+  LANGUAGE c AS 'MODULE_PATHNAME';
 
-CREATE LANGUAGE plperl;
+CREATE FUNCTION plperl_inline_handler(internal) RETURNS void
+  STRICT LANGUAGE c AS 'MODULE_PATHNAME';
+
+CREATE FUNCTION plperl_validator(oid) RETURNS void
+  STRICT LANGUAGE c AS 'MODULE_PATHNAME';
+
+CREATE TRUSTED LANGUAGE plperl
+  HANDLER plperl_call_handler
+  INLINE plperl_inline_handler
+  VALIDATOR plperl_validator;
+
+-- The language object, but not the functions, can be owned by a non-superuser.
+ALTER LANGUAGE plperl OWNER TO @extowner@;
 
 COMMENT ON LANGUAGE plperl IS 'PL/Perl procedural language';
diff --git a/src/pl/plperl/plperl.control b/src/pl/plperl/plperl.control
index 6faace12fa5..3a2230a151d 100644
--- a/src/pl/plperl/plperl.control
+++ b/src/pl/plperl/plperl.control
@@ -4,4 +4,5 @@ default_version = '1.0'
 module_pathname = '$libdir/plperl'
 relocatable = false
 schema = pg_catalog
-superuser = false
+superuser = true
+trusted = true
diff --git a/src/pl/plperl/plperlu--1.0.sql b/src/pl/plperl/plperlu--1.0.sql
index 7efb4fbc5bf..10d75945a16 100644
--- a/src/pl/plperl/plperlu--1.0.sql
+++ b/src/pl/plperl/plperlu--1.0.sql
@@ -1,11 +1,17 @@
 /* src/pl/plperl/plperlu--1.0.sql */
 
-/*
- * Currently, all the interesting stuff is done by CREATE LANGUAGE.
- * Later we will probably "dumb down" that command and put more of the
- * knowledge into this script.
- */
+CREATE FUNCTION plperlu_call_handler() RETURNS language_handler
+  LANGUAGE c AS 'MODULE_PATHNAME';
 
-CREATE LANGUAGE plperlu;
+CREATE FUNCTION plperlu_inline_handler(internal) RETURNS void
+  STRICT LANGUAGE c AS 'MODULE_PATHNAME';
+
+CREATE FUNCTION plperlu_validator(oid) RETURNS void
+  STRICT LANGUAGE c AS 'MODULE_PATHNAME';
+
+CREATE LANGUAGE plperlu
+  HANDLER plperlu_call_handler
+  INLINE plperlu_inline_handler
+  VALIDATOR plperlu_validator;
 
 COMMENT ON LANGUAGE plperlu IS 'PL/PerlU untrusted procedural language';
diff --git a/src/pl/plperl/sql/plperl_setup.sql b/src/pl/plperl/sql/plperl_setup.sql
new file mode 100644
index 00000000000..ae48feae863
--- /dev/null
+++ b/src/pl/plperl/sql/plperl_setup.sql
@@ -0,0 +1,64 @@
+--
+-- Install the plperl and plperlu extensions
+--
+
+-- Before going ahead with the to-be-tested installations, verify that
+-- a non-superuser is allowed to install plperl (but not plperlu) when
+-- suitable permissions have been granted.
+
+CREATE USER regress_user1;
+CREATE USER regress_user2;
+
+SET ROLE regress_user1;
+
+CREATE EXTENSION plperl;  -- fail
+CREATE EXTENSION plperlu;  -- fail
+
+RESET ROLE;
+
+DO $$
+begin
+  execute format('grant create on database %I to regress_user1',
+                 current_database());
+end;
+$$;
+
+SET ROLE regress_user1;
+
+CREATE EXTENSION plperl;
+CREATE EXTENSION plperlu;  -- fail
+
+CREATE FUNCTION foo1() returns int language plperl as '1;';
+SELECT foo1();
+
+-- Should be able to change privileges on the language
+revoke all on language plperl from public;
+
+SET ROLE regress_user2;
+
+CREATE FUNCTION foo2() returns int language plperl as '2;';  -- fail
+
+SET ROLE regress_user1;
+
+grant usage on language plperl to regress_user2;
+
+SET ROLE regress_user2;
+
+CREATE FUNCTION foo2() returns int language plperl as '2;';
+SELECT foo2();
+
+SET ROLE regress_user1;
+
+-- Should be able to drop the extension, but not the language per se
+DROP LANGUAGE plperl CASCADE;
+DROP EXTENSION plperl CASCADE;
+
+-- Clean up
+RESET ROLE;
+DROP OWNED BY regress_user1;
+DROP USER regress_user1;
+DROP USER regress_user2;
+
+-- Now install the versions that will be used by subsequent test scripts.
+CREATE EXTENSION plperl;
+CREATE EXTENSION plperlu;