Re: Removing pg_pltemplate and creating "trustable" extensions

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Jan 6, 2020 at 1:27 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.

> If somebody were to complain about this, what could they complain
> about? Potential complaints:

> 1. I'm the superuser and I don't want my DB owners to be able to
> install extensions other than trusted PLs.
> 2. Or I want to control which specific ones they can install.
> 3. I'm a non-superuser DB owner and I want to delegate permissions to
> install trusted extensions to some other user who is not a DB owner.

Sure, but all of these seem to be desires for features that could be
added later. As for #1, we could have that just by not taking the
next step of marking anything but the PLs trusted (something that is
going to happen anyway for v13, if this patch doesn't move faster).
#2 is not a feature that exists now, either; actually, the patch *adds*
it, to the extent that the superuser is willing to adjust extension
control files. Likewise, #3 is not a feature that exists now. Also,
the patch adds something that looks partly like #3, in that the
superuser could grant pg_install_trusted_extension with admin option
to database users who should be allowed to delegate it. Perhaps that's
inadequate, but I don't see why we can't wait for complaints before
trying to design something that satisfies hypothetical use cases.

The facts that I'm worried about are that this is already the January
'fest, and if we don't want to ship v13 with python 2 as still the
preferred python, we need to not only get this patch committed but do
some less-than-trivial additional work (that hasn't even been started).
So I'm getting very resistant to requests for more features in this patch.
I think everything you're suggesting above could be tackled later,
when and if there's actual field demand for it.

> "GRANT INSTALL ON mydb" seems like it would solve #1 and #3.

It's not apparent to me that that's better, and it seems possible that
it's worse. The fact that a DB owner could grant that privilege to
himself means that you might as well just have it on all the time.
Like a table owner's DML rights, it would only be useful to prevent
accidentally shooting yourself in the foot ... but who accidentally
issues CREATE EXTENSION? And if they do (for an extension that
deserves to be marked trusted) what harm is done really? Worst
case is you drop it again.

regards, tom lane