Re: Removing pg_pltemplate and creating "trustable" extensions - Mailing list pgsql-hackers

Greetings,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Wed, Jan 8, 2020 at 6:09 PM Stephen Frost <sfrost@snowman.net> wrote:
> > > To me, this seems more accidental than the natural fallout of good design.
> >
> > I disagree that the privilege design for FDWs was accidental.
>
> That seems like a stronger statement than the one I made...

Perhaps I misunderstood what you were referring to then.

> > What I see differently is that the purview of those decisions should be
> > that of the DB owner and not the superuser.
>
> Yeah, I don't agree with that at all. If I'm a hosting provider, I
> want to tell my customers what they're allowed to run and what they're
> not allowed to run, but I don't want them to have to call me when they
> want to run an extension I've decided is OK.

Now I'm really floored because I've also been contemplating exactly the
situation of a hosting provider.  In my experience, there's a few pretty
common themes among the ones that I've played with:

- Users definitely don't get true PG superuser
- They certainly like users to be able to install trusted extensions
- They'd generally prefer to minimize the size of their fork

I've been feeling like the solution I'm pushing for would, in the end,
*reduce* the amount of code they have that's different from PG, which I
believe they'd see as an entirely good thing.  The approach proposed in
this thread seems likely to either increase the size of the fork or, at
best, be about the same size.

Now the question boils down to "what's trusted?" and if I'm a hosting
provider, it'd sure be nice to foist off the responsibility of figuring
that out on a community that I can trust that will do so at no cost to
me, and who will address any bugs or security holes in those trusted
extensions for me.

Realistically, we pretty much already do that for contrib and that's
really all that's relevant here- anything else would need to be
physically installed on the system in order for a user to be able to
install it, and I expect hosting providers to be pretty happy with a
solution that boils down to just having to install RPMs, or not (or the
equivilant with regard to putting files into the right places and such).

Now, if we're talking about defining repositories or allowing users to
upload their *own* C code from their systems in to PG and install that
as an extension, then, sure, that's gotta be limited to a superuser (and
wouldn't be allowed by hosting providers).  If the extension is
installed on the system though, and it's marked as trusted, then why do
we need a superuser to take some additional action to allow it to be
installed..?

When it comes to the hosting provider case, I'd argue that what we're
missing here is a way for them to give their "not-quite-a-superuser
role" the ability to have certain capabilities (at install time, of
course)- a good example of that would be "allowed to make outbound
network connections", with a way to then be able to delegate out that
ability to others.  Then they'd actually be able to use things like
postgres_fdw and dblink after they're installed and without having to
get a superuser to grant them that ability post-install (and that order
of operations issue is a pretty key one- it's far simpler to set things
up and hand them over to the user than to have some operation that has
to happen as an actual superuser after the installation is done).

Even if we marked postgres_fdw as trusted, and used this default-role
based approach, we're almost certainly going to have the FDW itself be
owned by the bootstrap superuser and therefore whatever non-superuser
role that installs it wouldn't be able to actually use it without some
other changes.  I'd love to get to a point where you could have an
initially set up system with a not-quite-superuser role that would be
able to actually install AND use postgres_fdw, without having to fork
PG.

> > As it relates to things in contrib that could be classified as 'a pile
> > of crap' or 'too experimental'- that's *our* failing, and one which we
> > should accept and address instead of punting on it.
>
> I don't think changing what's in contrib helps much. Even if we rm
> -rf'd it, there's the same problem with out-of-core extensions. Joe
> Extensionman may think his extension ought to be trusted, and package
> it as such, but Paula Skepticaldba is entitled to think Joe's view of
> the security risks originating from his code is overly rosy.

Out of core extensions have to get installed on to the system though,
they don't just show up magically, and lots and lots of folks out there
from corporate infrastructure groups to hosting providers have got lots
of experience with deciding what they'll allow to be installed on a
system and what they won't, what repositories of code they'll trust and
which they won't.

Of course, when it comes to contrib extensions, if we don't feel
comfortable with them and we don't want to spend the time to vet them,
we can certainly just leave them marked as 'untrusted' and tell anyone
who wants to make them trusted that they need to put in the effort to
review that extension and get everyone comfortable with it.

Thanks,

Stephen