Re: Removing pg_pltemplate and creating "trustable" extensions

Greetings,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Thu, Nov 7, 2019 at 2:13 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> >> 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...).
>
> > +1.
>
> > I'm not sure that Tom's latest design idea is a bad one, but I
> > strongly suspect that wrapping ourselves around the axle to work
> > around our unwillingness to widen a 16-bit quantity to 32 bits (or a
> > 32 bit quantity to 64 bits) is a bad idea. Perhaps there are also
> > design ideas that we should consider, like separating "basic"
> > privileges and "extended" privileges or coming up with some altogether
> > new and better representation. But limiting ourselves to 4 more
> > privileges ever cannot be the right solution.
>
> So, is that actually an objection to the current proposal, or just
> an unrelated rant?

It strikes me as related since using a bit was one of the objections to
using the GRANT-a-privilege approach.

> If we think that a privilege bit on databases can actually add something
> useful to this design, the fact that it moves us one bit closer to needing
> to widen AclMode doesn't seem like a serious objection. But I don't
> actually see what such a bit will buy for this purpose. A privilege bit
> on a database is presumably something that can be granted or revoked by
> the database owner, and I do not see that we want any such behavior for
> extension installation privileges.

Given that extensions are database-level objects, I ask: why not?
Database owners are already able to create schema, and therefore to
create any object inside an extension that doesn't require a superuser
to create, why not let them also create the framework for those objects
to exist in, in the form of an extension?

When it comes to *trusted* extensions, I would view those in basically
the exact same way we view *trusted* languages- that is, if they're
trusted, then they can't be used to bypass the privilege system that
exists in PG, nor can they be used to operate directly on the filesystem
or open sockets, etc, at least- not without further checks. For
example, I would think postgres_fdw could be a 'trusted' extension,
since it only allows superusers to create FDWs, and you can't create a
server unless you have rights on the FDW.

When it comes to *untrusted* extensions, we could limit those to being
only installable by superusers, in the same way that functions in
untrusted languages are only able to be created by superusers (except,
perhaps as part of a trusted extension, assuming we can work through
this).

Now, I'm no fan of growing the set of things that only a superuser can
do, but I don't see that as being what we're doing here because we're
(hopefully) going to at least make it so that non-superusers can do some
things (create trusted extensions) that used to only be possible for
superusers to do, even if it still requires being a superuser to create
untrusted extensions. If someone comes up with a really strong use-case
then for allowing non-superusers to create untrusted extensions, then we
could consider how to enable that and maybe a default role makes sense
for that specific case, but I don't think anyone's really made that
case and I certainly don't think we want the privilege to create trusted
extensions and the privilege to create untrusted ones to be the same-
it's clear made that users will want to grant out those abilities
independently.

Thanks,

Stephen