Re: Removing pg_pltemplate and creating "trustable" extensions

Greetings,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Fri, Jan 10, 2020 at 2:40 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Well, the other direction we could go here, which I guess is what
> > you are arguing for, is to forget the new default role and just
> > say that marking an extension trusted allows it to be installed by
> > DB owners, full stop. That's nice and simple and creates no
> > backwards-compatibility issues. If we later decide that we want
> > a default role, or any other rules about who-can-install, we might
> > feel like this was a mistake --- but the backwards-compatibility issues
> > we'd incur by changing it later are exactly the same as what we'd have
> > today if we do something different from this. The only difference
> > is that there'd be more extensions affected later (assuming we mark
> > more things trusted).
>
> I agree with your analysis, but I'm still inclined to feel that the
> new pre-defined roll is a win.
>
> Generally, decoupled permissions are better. Being able to grant
> someone either A or B or both or neither is usually superior to having
> to grant either both permissions or neither.

Right- I like the idea of decoupled permissions too.

To be clear, I was advocating for a NEW DB-level privilege ('INSTALL' or
'CREATE EXTENSION' if we could make that work), so that we have it be
distinct from CREATE (which, today, really means 'CREATE SCHEMA').

I'd be willing to accept making this part of DB-level 'CREATE' rights if
there is a huge amount of push-back about burning a privilege bit for
it, but, as discussed up-thread, I don't think we should really be
stressing ourselves about that.

I do like the idea of having it be decoupled from explicit DB ownership,
so that a DB owner (or superuser) could say "I want this role to be able
to install extensions, but NOT run ALTER DATABASE", and optionally even
include ADMIN so that it could be further delegated (and also because
then it'd be just like the rest of our GRANT privilege system, and I
like that..).

Thanks,

Stephen