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

Greetings!

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Tue, Jan 7, 2020 at 1:17 PM Stephen Frost <sfrost@snowman.net> wrote:
> > Why would it be trusted if it's $SCARY_EXTENSION ...?  Why are we trying
> > to punt on solving for that question by installing a much more
> > complicated system here than is really necessary, just to avoid having
> > to make that decision?
>
> I'm not convinced that whether or not something is trusted is an
> altogether objective question.

How have we managed to have an answer to that question for all of the
languages that work with PG then..?  I feel like the answer is actually
pretty clear, at least if we view it in that light, and in the specific
case below, we are in agreement on which way it goes.

> For instance, postgres_fdw probably
> doesn't let you become the superuser, unless it has bugs. But it does
> let you make network connections originating from the database host,
> and somebody might reasonably want to restrict that in a
> security-sensitive environment. But the same user might be totally OK
> with a particular database owner installing citext.

Agreement!  Progress!  At least as it relates to, specifically,
postgres_fdw and about how non-superusers should have to be granted
something special to be allowed to make network connections.

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?

We don't currently have anything that prevents objects from an
extension from being modified by their owner, for example, and that
seems like a problem from where I'm sitting when you're talking about
having non-superusers creating objects that previously only superusers
could, and where the ownership-level rights on those objects would allow
that user to do things we generally don't feel an 'untrusted' user
should be able to.

Which basically leads to- in my mental model of this, the 'create
trusted extension' action would be kind of like a 'sudo apt install',
where the result is an extension that's installed as if a superuser did
install it and therefore it's owned by a superuser and the DB owner
can't go monkey around with any of the functions or tables or such
(unless allowed by the extension), beyond granting access (or not) to
the schema that the extension is installed into (which is actually more
than the 'sudo apt install' example above would probably let you do).
Further, that installation doesn't give the DB owner any more rights to
do things on the system than they already had.

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?

> > If these functions were to just be put into core (as many really should
> > be...), instead of being out in contrib, this whole issue also wouldn't
> > exist and everyone would be able to use the functions (at least, those
> > that we decide are safe for users to directly use- and with appropriate
> > privilege access over ones that aren't), without any "the superuser must
> > approve of this explicitly after installation" fuss.
>
> Well, I don't agree with the idea of moving everything into core, but
> I think a good solution to the problem at hand will reduce the fuss
> while allowing superusers to retain some control.

I don't actually mean everything, just to be clear, but a whole lot of
what's in contrib really could be in core with only a relatively modest
increase in the size of our base install/catalog (and, yes, I know some
people would complain about that, but in that case I'd argue that maybe
we should arrange to let them optionally not include those during the
build or something else because they're probably taking other steps to
minimize the size of PG on disk if they care that much..).

Having something like postgres_fdw installed as part of core would also
address the complications we have above regarding who owns it and who
gets to grant out access to it.

Thanks,

Stephen