Goodbye pg_pltemplate, hello trusted extensions

Over in the thread about python versions there was some mention of
getting rid of the pg_pltemplate system catalog in favor of putting
all the info about a procedural language into its extension script.
This has been discussed before, and it'd be a very good thing to do
because it would allow third-party PLs to be fully on par with
the PLs provided by the core distribution. Right now, the existence
of entries in the default contents of pg_pltemplate makes the core
PLs much easier to work with than third-party PLs.

I spent a bit of time poking at this today, and soon found that the
key thing that pg_pltemplate is doing for us is providing a privilege
escalation mechanism, in that it allows database owners to create trusted
PLs, which is something that'd otherwise require superuser privileges.
We could imagine hacking the behavior of CREATE LANGUAGE a bit in hopes
of getting around that, but a standalone PL extension script would also
need to contain "CREATE FUNCTION ... LANGUAGE C" command(s) for the
language's support functions, and we surely do not want to allow that
to be done with less than superuser privileges. Moreover, the end state
has to be that those functions are *owned* by a superuser; allowing a
non-superuser to have ALTER FUNCTION privileges on a C-language function
is also a security hole.

So I thought for awhile about how to deal with that, and eventually
decided that really what we need to do is solve this as part of the
extension mechanism, not CREATE LANGUAGE per se. What I'm imagining
is that we add new option(s) to extension control files that allow
specifying that the extension's script is run as a different user
than the user invoking CREATE EXTENSION. The extension object itself
remains owned by the calling user (who can drop it), but the contained
objects are then owned by the other user, so that the extension owner
doesn't have privilege to modify or drop them individually.

In the particular case of trusted PLs, we'd want to say that anyone
with the privileges of the database owner can do CREATE EXTENSION,
but the script is run as the bootstrap superuser, who ends up owning
the C functions as well as the language object proper. It's not
very hard to imagine people using this facility for other extensions
containing C functions, though, and some of them might want different
rules like "this extension can be installed by anybody". So I don't
have a concrete proposal for what the extension options should look like
exactly. It's tempting to suggest that the options should include ways
to list specific role names who can install the extension, or a specific
role name to run the script as. But I'm not sure we want to go there:
that would make the control files pretty installation-specific, which
seems like a bad idea for distribution purposes. We definitely need
ways to say "allow the database owner to run this" and "run this as the
bootstrap superuser" without getting involved in specific role names.
Beyond that I'm uncertain what's needed.

The ultimate security assumption behind this is that the contents of the
extension script directory are superuser-approved, and so we can trust
the contents of any script extension file to be OK to run as superuser
if its control file says so. That'd be replacing the existing assumption
that the contents of pg_pltemplate are superuser-approved. Perhaps
there's a hole in that, but I don't see what; if an attacker can scribble
on the extension script directory, it seems like it's already game over
for database security.

Another issue is that with the existing code for CREATE LANGUAGE,
if a database owner creates a trusted PL, she ends up as the owner
of the language object, though not the underlying C functions.
This solution would not replicate that situation, which is a problem
because it'd take away the DB owner's ability to grant/revoke usage on
the PL. I'm imagining that we could fix that by having the PL's
extension script do something like

GRANT ALL ON LANGUAGE plfoo TO @extowner@ WITH GRANT OPTION;

where @extowner@ is a macro expanded by CREATE EXTENSION in the
same way as it already does for @extschema(at)(dot) This idea isn't fully
baked though: we'd need the state to be that the language's default
PUBLIC USAGE grant comes from the DB owner not directly from the
superuser, and this isn't enough to arrange that. Maybe it could
read

REVOKE ALL ON LANGUAGE plfoo FROM PUBLIC;
GRANT ALL ON LANGUAGE plfoo TO @extowner@ WITH GRANT OPTION;
SET ROLE @extowner@;
GRANT USAGE ON LANGUAGE plfoo TO PUBLIC;
RESET ROLE;

That seems pretty hacky; maybe it's time to implement the SQL standard's
GRANTED BY <grantor> option so that the last three lines could be
simplified to

GRANT USAGE ON LANGUAGE plfoo TO PUBLIC GRANTED BY @extowner@;

Anyway, this is all pretty sketchy, but I'd be willing to work towards
making it happen in v12. Thoughts?

regards, tom lane