Removing pg_pltemplate and creating "trustable" extensions

We've repeatedly kicked around the idea of getting rid of the
pg_pltemplate catalog in favor of keeping that information directly in
the languages' extension files [1][2][3][4]. The primary abstract
argument for that is that it removes a way in which our in-tree PLs
are special compared to out-of-tree PLs, which can't have entries in
pg_pltemplate. A concrete argument for it is that it might simplify
fixing the python-2-vs-python-3 mess, since one of the issues there
is that pg_pltemplate has hard-wired knowledge that "plpythonu" is
Python 2. Accordingly, attached is a patch series that ends by
removing that catalog.

As I noted in [2], the main stumbling block to doing this is that
the code associated with pg_pltemplate provides a privilege override
mechanism that allows non-superuser database owners to install trusted
PLs. For backwards compatibility if nothing else, we probably want to
keep that ability, though it'd be nice if it weren't such a hard-wired
behavior.

Patch 0001 below addresses this problem by inventing a concept of
"trustable" (not necessarily trusted) extensions. An extension that
would normally require superuser permissions (e.g., because it creates
C functions) can now be installed by a non-superuser if (a) it is
marked trustable in the extension's control file, AND (b) it is
listed as trusted in one of two new GUCs, trusted_extensions_dba and
trusted_extensions_anyone. (These names could stand a visit to the
bikeshed, no doubt.) Extensions matching trusted_extensions_dba can
be installed by a database owner, while extensions matching
trusted_extensions_anyone can be installed by anybody. The default
settings of these GUCs provide backwards-compatible behavior, but
they can be adjusted to provide more or less ability to install
extensions. (This design is basically what Andres advocated in [2].)

In this patch series, I've only marked the trusted-PL extensions as
trustable, but we should probably make most of the contrib extensions
trustable --- not, say, adminpack, but surely most of the datatype
and transform modules could be marked trustable. (Maybe we could
make the default GUC settings more permissive, too.)

As coded, the two GUCs are not lists of extension names but rather
regexes. You could use them as lists, eg "^plperl$|^plpgsql$|^pltcl$"
but that's a bit tedious, especially if someone wants to trust most
or all of contrib. I am a tad worried about user-friendliness of
this notation, but I think we need something with wild-cards, and
that's the only wild-card-capable matching engine we have available
at a low level.

You might wonder why bother with the trustable flag rather than just
relying on the GUCs. The answer is mostly paranoia: I'm worried about
somebody writing e.g. "plperl" with no anchors and not realizing that
that will match "plperlu" as well. Anyway, since we're talking about
potential escalation-to-superuser security problems, I think having
both belt and suspenders protection on untrusted languages is wise.

There are no regression tests for this functionality in 0001,
but I added one in 0002.

Patch 0002 converts all the in-tree PLs to use fully specified
CREATE LANGUAGE and not rely on pg_pltemplate.

I had a better idea about how to manage permissions than what was
discussed in [3]; we can just give ownership of the language
object to the user calling CREATE EXTENSION. Doing it that way
means that we end up with exactly the same catalog state as we
do in existing releases. And that should mean that we don't have
to treat this as an extension version upgrade. So I just modified
the 1.0 scripts in-place instead of adding 1.0--1.1 scripts. It
looks to me like there's no need to touch the from-unpackaged
scripts, either. And by the same token this isn't really an issue
for pg_upgrade.

(I noticed while testing this that pg_upgrade fails to preserve
ownership on extensions, but that's not new; this patch is not
making that situation any better or worse than it was. Still,
maybe we oughta try to fix that sometime soon too.)

Patch 0003 removes CREATE LANGUAGE's reliance on pg_pltemplate.
CREATE LANGUAGE without parameters is now interpreted as
CREATE EXTENSION, thus providing a forward compatibility path
for old dump files.

Note: this won't help for *really* old dump files, ie those containing
CREATE LANGUAGE commands that do have parameters but the parameters are
wrong according to modern usage. This is a hazard for dumps coming
from 8.0 or older servers; we invented pg_pltemplate in 8.1 primarily
as a way of cleaning up such dumps [5]. I think that that's far enough
back that we don't have to worry about how convenient it will be to go
from 8.0-or-older to v13-or-newer in one jump.

Finally, patch 0004 removes the now-unused catalog and cleans up some
incidental comments referring to it.

Once this is in, we could start thinking about whether we actually
want to change anything about plpython in the near future.

regards, tom lane

[1] https://www.postgresql.org/message-id/flat/763f2fe4-743f-d530-8831-20811edd3d6a%402ndquadrant.com
[2] https://www.postgresql.org/message-id/flat/7495.1524861244%40sss.pgh.pa.us
[3] https://www.postgresql.org/message-id/flat/5351890.TdMePpdHBD%40nb.usersys.redhat.com
[4] https://www.postgresql.org/message-id/flat/CAKmB1PGDAy9mXxSTqUchYEi4iJAA6NKVj4P5BtAzvQ9wSDUwJw(at)mail(dot)gmail(dot)com
[5] https://www.postgresql.org/message-id/flat/5088(dot)1125525412(at)sss(dot)pgh(dot)pa(dot)us