From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephen Frost <sfrost@snowman.net>
cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>,
        pgsql-hackers@lists.postgresql.org
Subject: Re: Removing pg_pltemplate and creating "trustable" extensions
In-reply-to: <20191107191320.GZ6962@tamriel.snowman.net>
References: <5889.1566415762@sss.pgh.pa.us>
 <c15c0f58-79a9-4051-1f43-8fc06564df69@2ndquadrant.com>
 <27727.1573052802@sss.pgh.pa.us> <20191107183856.GY6962@tamriel.snowman.net>
 <31658.1573152852@sss.pgh.pa.us> <20191107191320.GZ6962@tamriel.snowman.net>
Comments: In-reply-to Stephen Frost <sfrost@snowman.net>
	message dated "Thu, 07 Nov 2019 14:13:20 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2100.1573155932.1@sss.pgh.pa.us>
Date: Thu, 07 Nov 2019 14:45:32 -0500
Message-ID: <2101.1573155932@sss.pgh.pa.us>
Precedence: bulk

Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> Stephen Frost <sfrost@snowman.net> writes:
>>> Two things- first, this doesn't actually cover everything that the
>>> proposed GUCs do- specifically, the proposed GUCs give you a way to
>>> limit what specific extensions are allowed to be installed, and by whom.
>>> Moving to a GRANT-based system removes the extension specificity and
>>> leaves with just "is user X allowed to install extensions".

>> True.  But do we care?  We did not have that flexibility before, either.

> I'm not 100% sure that we do, but I wanted to mention it as a
> difference.  Certainly there have previously been suggestions of having
> a 'whitelist' similar to what you initially proposed, that are
> extensions which non-superusers are allowed to install.

Right, but I'm not sure that we need multiple layers of that.  Flags
in the extension control files are a clear and understandable mechanism
for that.  I didn't especially like the idea of a GUC-based whitelist
even when I proposed it, and Peter's points against it are compelling
too, so I don't really want to go down that path anymore.  Do you have
another mechanism in mind?

> At a high level, I agree with the idea of an extension being able to be
> marked as one that's "trusted" or not, but we would also need to come up
> with exactly what that means for it to really have value, and I don't
> think we've really done that yet.

Agreed, we'd need to have a policy for what we'd mark.  The policy that
I more or less had in mind was to mark a contrib module as trusted if it
does not provide a mechanism for privilege escalation (such as access to
the filesystem, in the case of adminpack).  Some people might feel that
"contrib module X shouldn't be trusted because I'm not convinced it hasn't
got bugs", but I fear if we start trying to make decisions on that basis,
we'll be spending a whole lot of time arguing hypotheticals.

>> That doesn't seem like a big objection from here.  We could fix it
>> by making a separate privilege bit, but I doubt that it's worth using
>> up one of our limited set of spare bits for.

> I do not agree that we should just shift to using default roles instead
> of adding new options to GRANT because of an entirely internal
> implementation detail that we could fix (and should, as I've said for
> probably 10 years now...).

The default role is not a substitute for the GRANT bit, in my view of
this.  I think that what we're saying with that, or at least what
Peter evidently had in mind, is that we want extension installers to
have *both* privileges from the superuser and privileges from the
specific DB's owner.  We can manage the latter with GRANT, but not the
former.

It's certainly arguable that requiring a superuser-granted role is
enough privilege and we shouldn't bother with having a per-DB
restriction capability.  I'd be more inclined to go that route than
to add the overhead of a brand new ACL bit.

> Really?  Why do you think that DB ownership shouldn't be enough for
> this, for trusted extensions?

DB owners have never been particularly highly privileged in the past.
I think that suddenly saying they can install extensions is moving
the understanding of that privilege level quite a bit.  Although
admittedly, the precedent of trusted PLs would point to allowing them
to install trusted extensions without further ado.  So maybe a
different take on this is "allow installing trusted extensions if you
are DB owner *or* have the pg_install_trusted_extensions role"?

			regards, tom lane