Re: Removing pg_pltemplate and creating "trustable" extensions

Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Jan 28, 2020 at 3:52 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I continue to think that allowing DB owners to decide this is, if not
>> fundamentally the wrong thing, at least not a feature that anybody has
>> asked for in the past.  The feature *I* want in this area is for the
>> superuser to be able to decide who's got install privilege.  Making
>> it a DB-level privilege doesn't serve that goal, more the opposite.

> I agree.

>> Still, if we can compromise by making this part of DB "CREATE" privilege
>> for the time being, I'm willing to take that compromise.  It's certainly
>> better than failing to get rid of pg_pltemplate.

> Doesn't that have exactly the issue you describe above?
> bob=> grant create on database bob to fred;
> GRANT

Either of them do, in that a DB owner can always grant his whole role;
"grant bob to fred" will give fred install privileges (in bob's DBs)
regardless of which of these choices we adopt.  And that was true before
(with respect to trusted PLs), too.  Attaching the ability to the CREATE
bit would at least allow DB owners to be a bit more selective about how
they give it out.

The reason I'm happier about doing this with CREATE than inventing
a separate INSTALL bit is that once we do the latter, we're more or
less bound to keep supporting that ability forever.  If we extend
the definition of CREATE in v13, and then narrow it again in some
future release, that seems less likely to cause problems than taking
away a named privilege bit would do.

On the other hand, there's the point that lots of people have probably
given out schema-CREATE privilege to users whom they wouldn't necessarily
wish to trust with INSTALL privilege.  Schema-CREATE is a pretty harmless
privilege, INSTALL much less so.