Re: Removing pg_pltemplate and creating "trustable" extensions

I wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> Really? Why do you think that DB ownership shouldn't be enough for
>> this, for trusted extensions?

> DB owners have never been particularly highly privileged in the past.
> I think that suddenly saying they can install extensions is moving
> the understanding of that privilege level quite a bit. Although
> admittedly, the precedent of trusted PLs would point to allowing them
> to install trusted extensions without further ado. So maybe a
> different take on this is "allow installing trusted extensions if you
> are DB owner *or* have the pg_install_trusted_extensions role"?

After sleeping on it, I'm liking that idea; it's simple, and it
preserves the existing behavior that DB owners can install trusted PLs
without any extra permissions. Now, if we follow this up by marking
most of contrib as trusted, we'd be expanding that existing privilege.
But I think that's all right: I don't recall anybody ever complaining
that they wanted to prevent DB owners from installing trusted PLs, and
I do recall people wishing that it didn't take superuser to install
the other stuff.

Accordingly, here's a patchset that does it like that.

I decided after looking at the existing default role names that
"pg_install_trusted_extension" (no plural) was more consistent
with the existing names than adding an "s". I don't find that
precedent particularly charming, but it's what we've got.

I also renamed the extension property from "trustable" to "trusted".
There are at least a couple of reasons to be dissatisfied with that:

(1) There's potential for confusion between the notion of a trusted
extension and that of a trusted PL; those properties do roughly
similar things, but they're not exactly equivalent. I didn't think
this was enough of a problem to justify choosing a different name,
but somebody else might think differently.

(2) If we were starting this design from scratch, we'd probably not
have two interrelated boolean properties "superuser" and "trusted",
but one three-state enum property. The enum approach would likely
be a lot easier to extend if we eventually grow more privilege
levels for extension installation. I'm not sure whether it's worth
breaking backwards compatibility now to keep our options open for
that, though. We could preserve extension control file
compatibility easily enough by keeping "superuser = true" and
"superuser = false" as allowed legacy spellings for two values of
an enum property. But the pg_available_extension_versions view is
a tougher nut. On the other hand, maybe replacing its "superuser"
column with something else wouldn't really cause many problems.

Other than getting rid of the GUCs in favor of this design,
it's mostly the same patchset as before. 0003 and 0004 haven't
changed at all, and 0002 only differs by adjusting the test case.

regards, tom lane