Re: Removing pg_pltemplate and creating "trustable" extensions

On Mon, Jan 6, 2020 at 1:27 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> So, is that actually an objection to the current proposal, or just
> an unrelated rant?

Well, you brought up the topic of remaining bits in the context of the
proposal, so I guess it's related. And I said pretty clearly that it
wasn't necessarily an objection.

But regarding your proposal:

> 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.

All of those sound reasonably legitimate; against that, you can always
argue that permissions should be more finely grained, and it's not
always worth the implementation effort to make it possible. On #1, I
tend to think that *most* people would be happy rather than sad about
DB owners being able to install extensions; after all, evil extensions
can be restricted by removing them from the disk (or marking them
untrusted), and most people who set up a database are hoping it's
going to get used for something. But somebody might not like it,
especially if e.g. it turns out that one of our "trusted" extensions
has a horrible security vulnerability. On #2, I can certainly imagine
large providers having a view about which extensions they think are
safe enough for users to install that differs from ours, and if that
horrible security vulnerability materializes it sure would be nice to
be able to easily disable access to just that one. #3 seems less
likely to be an issue, but it's not unthinkable.

"GRANT INSTALL ON mydb" seems like it would solve #1 and #3. You could
grant a particular DB owner permission to install extensions, or not.
If you have them that power WITH GRANT OPTION, then they could
sub-delegate. It wouldn't do anything about #2; that would require
some more complex scheme.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company