Re: Removing pg_pltemplate and creating "trustable" extensions

Greetings,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Tue, Jan 7, 2020 at 4:36 PM Stephen Frost <sfrost@snowman.net> wrote:
> > Here's the thing though..  creating the extension isn't *really* (in our
> > permissions model anyway) what lets you create outbound connections-
> > it's creating a 'SERVER', and to be able to do that you need to have
> > USAGE rights on the FDW, which, normally, only a superuser can create.
> > The crux here is that the FDW is created as part of the extension
> > though.  As long as only superusers can create extensions, that's fine,
> > but when we allow others to do so, we come to an interesting question:
> >
> > No matter how we end up allowing a non-superuser to create a trusted
> > extension, who should end up owning it and being able to modify it
> > and/or grant access to objects within it?
>
> Hmm.  Good question. But it's addressed in the documentation for the
> patch Tom wrote, so I don't know why we need to discuss it de novo.
> His answer seems pretty sensible and also happens to, I think, match
> what you've written here.

I would disagree about it matching what I wrote, but only because it
goes farther and lets the extension choose, which is even better.
Thanks for pointing that out, I had missed how that was addressed.

> > Of course, there's the other option, which is to just agree that,
> > because of the way postgres_fdw works, it's gotta be marked as
> > untrusted.  I would ask though- are we really sure that we aren't ever
> > going to have any issues with functions in untrusted languages (or any
> > other objects) created by extensions being owned by non-superusers?
>
> But I don't see what the question of "who owns the objects?" has to do
> with whether a superuser might want to allow some extensions to be
> installed but not others. I think someone might want that, and if I
> understand correctly, Tom thought so too when he wrote v1 of the
> patch, because it had some capabilities along these lines. All I'm
> doing is arguing that his first instinct was correct. And I'm not even
> sure that you're disagreeing, since you seem to think that the
> question of whether postgres_fdw ought to be marked trusted is
> debatable. I'm really not sure what we're arguing about here.

Here's the thing though- I *am* disagreeing, and that Tom had addressed
the ownership issue solidifies my feeling that the justification that's
been proposed for why a superuser might want to allow some extensions to
be installed but not others (beyond the "trustable" question that we are
proposing to address) isn't valid.

You raised the point regarding postgres_fdw and a DB owner being able to
run 'create extension postgres_fdw;' and to then make network
connections, but that's proven to be invalid because, assuming we make
postgres_fdw trustable, we will surely make the FDW itself that's
created be owned by the bootstrap superuser and therefore the DB owner
*couldn't* create such network connections- at least, now without an
additional step being taken by a superuser.  Further, it's pretty clear
to everyone *why* that additional step has to be taken for postgres_fdw.

So I come back to the questions that were raised up-thread but either
weren't answered or were done so with invalid points, as explained above
regarding postgres_fdw:

What's the security issue from installing a trusted extension?

Why would a $SCARY_EXTENSION be marked as trusted?

If there's no security issue, and no $SCARY_EXTENSION that's marked as
trusted, then why wouldn't a superuser be comfortable allowing a DB
owner to install a trusted extension into the DB they own?  The DB where
they can create any other trusted object from functions in trusted
languages to operators to schemas to tables and indexes and views and
all the others?  What is the superuser concerned about?  What do they
need to check before allowing this?  What's dangerous about allowing it?

Maybe it would help to say that I'm seeing the pattern here being
something along the lines of:

0) DBA owns prod database, but is not a superuser.
1) DBA decides they want $trusted_extension but it isn't installed
2) DBA submits a ticket to the infra team and says "please install this
   RPM on to this database server"
3) infra reviews that request and decides if they're ok with the RPM
4) infra resolves the ticket and installs the RPM,
5) DBA then runs 'create extension $trusted_extension;' and go about
   doing whatever it is they want to do with that extension.

The approach you're advocating for, assuming I've understood it
correctly, requires the infra team to also log in to the database as the
postgres superuser and then grant this role to the DBA, and I just don't
see the justification for that additional step, and I'm sure they're
going to be asking themselves "why do I need to do this..?  what power
is this granting?  why is this dangerous?  What about this isn't to be
trusted?"

To the question of "how do we know if the extension is trusted?" I
answer- how do we know a PL is trusted?  We have to evaluate it,
consider what it allows the user to do, see if it has appropriate checks
to make sure the user can't circumvent our privilege system and can't
break out of the 'box', as it were, and then decide on if it's trusted
or not.  Sure, if we mark it as trusted then we have to deal with fixing
any bugs that come up with it, including security issues, but that's
just the same as we do with the core code (and, for the most part, what
we do with the core extensions also anyway...  I can't think of a single
case off-hand where we have said "well, you have to actually install
that extension, so we aren't going to address this security issue that
you found in it"), so I don't see it as being that difficult for us to
also do that for extensions.

Thanks,

Stephen