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

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > So I'm at a loss for why there is this insistence on a default role and
> > a superuser-explicit-granting based approach that goes beyond "is it
> > installed on the filesystem?" and "is it marked as trusted?".
>
> Okay, so it seems like we're down to just this one point of contention.

I agree that this seems to be the crux of the contention- though I need
to revisit what I wrote above because I didn't cover everything
relevant.  I'm suggesting that having:

- Extension installed on the filesystem
- Extension is marked as trusted
- Calling user has been made a DB owner (which, from a bare initdb,
  requires a superuser to take action to make happen)

is what would be needed to install a trusted extension.

> You feel that the superuser can control what is in the extension library
> directory and that that ought to be sufficient control.  I disagree
> with that, for two reasons:
>
> * ISTM that that's assuming that the DBA and the sysadmin are the same
> person (or at least hold identical views on this subject).  In many
> installations it'd only be root who has control over what's in that
> directory, and I don't think it's unreasonable for the DBA to wish
> to be able to exercise additional filtering.

I don't think we should start conflating roles by saying things like
"DBA" because it's not clear if that's "PG superuser" or "DB owner" or
"DB user".  In many, many, many installations the DBA is *not* the
superuser- in fact, pretty much every cloud-provider installation of PG
is that way.

Even so though, I don't agree with this particular rationale as, at
least largely in my experience, the sysadmin isn't going to go
installing things on their own- they're going to install what they've
been asked to, and it'll be a PG superuser or DB owner or DB user
doing the asking (and hopefully they'll consult with whomever is
appropriate before installing anything anyway).  The idea that
additional filtering on that is needed strikes me as highly unlikely.

Now, that said, I'm not strictly against the idea of allowing a
superuser, if they wish, to do additional filtering of what's allowed
(though I think there's a fair bit of complication in coming up with a
sensible way for them to do so, but that's an independent discussion).
I still don't think that the privilege to install a trusted extension
should be done through a default role though, or that it needs to be
independent from the DB owner role.

> * The point of a default role would be for the DBA to be able to
> control which database users can install extensions.  Even if the
> DBA has full authority over the extension library, that would not
> provide control over who can install, only over what is available
> for any of them to install.

In my approach, a superuser absolutely still has control over which
database users can install extensions, by virtue of being in control
over which users are DB owners, and further by being able to GRANT out
the right to install extensions, in specific databases, to specific
users.  If we want to have a mechanism where superusers can further
whitelist or blacklist extensions across the cluster, that's fine, but,
again, that's largely orthogonal to what I'm talking about.

Also, consider this- with the default role approach, is a user granted
that role allowed to create extensions in *every* database they can
connect to, or do they need some additional privilege, like CREATE
rights on the database, which is then under the purview of the database
owner?  What if the superuser wishes to allow a given role the ability
to install extensions only in a specific database?  That strikes me as
highly likely use-case ("you can install extensions in this other
database, but not in the 'postgres' database that I use for my
monitoring and other stuff") that isn't addressed at all with this
default role approach (looking at the patch, it seems to switch to the
superuser role to actually create objects, of course, so the caller
doesn't need create rights in the database), but is with mine- and
done so in a natural, intuitive, way that works just like the rest of
our privilege system.

I've always viewed the privilege system in PG to be a hierarchchy of
privileges-

superuser   - ultimate owner, able to do everything
DB owner    - controls create/use/modify for objects in the database,
              and altering of the database itself
schema owner- controls create/use/modify for objects in a schema, and
              altering of the schema itself
table owner - controls access/use/modify for the table, and altering of
              table itself

Now we're moving outside of that to start using default roles to control
who can create objects in a database, and that's what I don't agree
with.  If the superuser doesn't feel that a particular role should be
able to create extensions in a given database, there is a simple and
well understood solution- don't have that role own that database.  None
of what I've been talking about has ever taken away anything from the
superuser or made it such that the superuser wouldn't have any say in
who can install extensions.  I'll accept that if a superuser wished for
a given role to be able to run ALTER DATABASE but not install extensions
then that would present an issue in this model, but that feels like a
really tenuous argument and isn't one that anyone here has actually been
making, instead it seems to be a bunch of hand-wringing about how this
might be giving DB owners too much power, when they're already the ones
who control who is allowed to create objects in the database, or not.

Thanks,

Stephen