Getting rid of pg_pltemplate

One of my goals for the extensions feature has been that we should be able
to get rid of the pg_pltemplate system catalog, moving all the information
therein into languages' extension definition files. This would allow
third-party procedural languages to be installed as easily as built-in
ones. We failed to get this done in 9.1, mostly because we couldn't work
out what to do about tmpldbacreate (the feature to allow non-superuser
database owners to create "safe" languages). Here's a proposal for that.

We'll add a new boolean parameter to extension control files, called say
"dba_create" (ideas for better names welcome). If it's missing or set
to false, there's no change in behavior. When it's true, then

(a) you must be superuser or owner of the current database to create the
extension;

(b) the commands within the extension's script will be run as though by a
superuser, even if you aren't one.

My initial thought about how to actually implement (b) was to hot-wire
superuser() so that it would return true while executing such a script.
However, the end result of that would be that the extension's contained
objects would be owned by the non-superuser DBA, and I'm not sure that's a
good idea. I seem to recall some discussions about how the SQL owner of,
say, a C-language function could use ALTER FUNCTION on it in ways that
could open security holes. So it might be a better idea to execute the
script as though we'd temporarily done SET ROLE to the bootstrap
superuser, so that the objects end up owned by that user. (Of course,
we'd only need to do that when the calling user isn't already a superuser,
else he might as well own the objects.)

Presumably, a dba_create extension could also be dropped by a
non-superuser DBA. We could either inspect the extension control file
again when deciding whether to allow DROP EXTENSION, or copy the flag into
a new column in pg_extension so that the installed extension doesn't rely
on having the control file still around. Probably the latter is a better
idea.

The above mechanism could be applied to any sort of extension, not just
procedural language ones, and would be useful for ones involving
C-language functions (which is most). But it's not my purpose at the
moment to open a debate about whether any of our existing contrib modules
ought to get marked as dba_create. For the moment I'm just considering
the procedural languages.

(In essence, if a database administrator allows a dba_create extension
to be installed in his extensions directory, he's certifying that he
trusts that extension enough to allow it to be installed by
non-superusers. This is not just a matter of whether the extension
itself is safe, but whether the installation script could conceivably be
subverted by running it in a malicious SQL environment. I'd just as
soon start out with assuming that only for the PL extensions, which need
do nothing except a couple of CREATE FUNCTION commands and then CREATE
LANGUAGE.)

Once we have the above mechanism, we'd redefine CREATE LANGUAGE thusly:

1. CREATE LANGUAGE with no parameters becomes just a deprecated synonym
for CREATE EXTENSION, ie, we turn it into a request to create the
extension of the same name. This is mostly to allow loading of old dump
files.

2. CREATE LANGUAGE with parameters is expected to be used in extension
definition files. It becomes a superuser-only command (with the
dba_create override of course), and must specify all properties of the
language. The existing weird rules about overriding the specified
parameters with data from pg_pltemplate go away.

3. Likewise, we get rid of the weird rules in pg_dump about when to
provide parameters for CREATE LANGUAGE. If we're dumping a language
definition at all, dump it with full parameters.

Having done that, we'd mark all the standard "trusted" PLs as dba_create,
expand the existing definition scripts for the PL extensions so that they
fully specify the languages and their support functions (transferring all
that knowledge from the current contents of pg_pltemplate), and then
remove pg_pltemplate.

Now, the reason we invented pg_pltemplate in the first place was to
solve problems with updating procedural language definitions from one
release to the next. Essentially, if we do this, we're making a bet
that the extensions feature provides a more complete, better-thought-out
update mechanism than pg_pltemplate itself. I think that's probably
right, especially when thinking about it from the standpoint of a
non-core PL; but it's worth pointing out that we are taking some risk of
having to do more work than before. For example, if we wanted to add
another type of support function to PLs in the future, this approach
would mean having to add an ALTER LANGUAGE command for the PLs' update
scripts to use to add that function to an existing PL. Otherwise we
could not support binary-upgrade scenarios.

Comments?

regards, tom lane