Re: Removing pg_pltemplate and creating "trustable" extensions

Greetings,

On Tue, Jan 28, 2020 at 16:17 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Tue, Jan 28, 2020 at 3:52 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

Right.

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.

I would like to segregate these privileges more than just “install” vs
“other stuff” in the future anyway, so mixing it with the existing CREATE
isn’t that big of a deal in my view.

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.

CREATE doesn’t just control the ability to create schemas these days- it
was extended to cover publications also not that long ago. We never said
we wouldn’t extend CREATE to cover more objects and we’ve already extended
it recently, without anyone being up in arms about it that I can recall, so
this doesn’t feel like a huge issue or concern to me. Note that, again,
these are trusted extensions which means, in their regular install, they
shouldn’t be able to “break outside the box” any more than a plpgsql
function is able to.

Thanks,

Stephen