Horton

Thread

Commits

  1. Invent "trusted" extensions, and remove the pg_pltemplate catalog.

  1. Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-08-21T19:29:22Z

    We've repeatedly kicked around the idea of getting rid of the
pg_pltemplate catalog in favor of keeping that information directly in
the languages' extension files [1][2][3][4].  The primary abstract
argument for that is that it removes a way in which our in-tree PLs
are special compared to out-of-tree PLs, which can't have entries in
pg_pltemplate.  A concrete argument for it is that it might simplify
fixing the python-2-vs-python-3 mess, since one of the issues there
is that pg_pltemplate has hard-wired knowledge that "plpythonu" is
Python 2.  Accordingly, attached is a patch series that ends by
removing that catalog.

As I noted in [2], the main stumbling block to doing this is that
the code associated with pg_pltemplate provides a privilege override
mechanism that allows non-superuser database owners to install trusted
PLs.  For backwards compatibility if nothing else, we probably want to
keep that ability, though it'd be nice if it weren't such a hard-wired
behavior.

Patch 0001 below addresses this problem by inventing a concept of
"trustable" (not necessarily trusted) extensions.  An extension that
would normally require superuser permissions (e.g., because it creates
C functions) can now be installed by a non-superuser if (a) it is
marked trustable in the extension's control file, AND (b) it is
listed as trusted in one of two new GUCs, trusted_extensions_dba and
trusted_extensions_anyone.  (These names could stand a visit to the
bikeshed, no doubt.)  Extensions matching trusted_extensions_dba can
be installed by a database owner, while extensions matching
trusted_extensions_anyone can be installed by anybody.  The default
settings of these GUCs provide backwards-compatible behavior, but
they can be adjusted to provide more or less ability to install
extensions.  (This design is basically what Andres advocated in [2].)

In this patch series, I've only marked the trusted-PL extensions as
trustable, but we should probably make most of the contrib extensions
trustable --- not, say, adminpack, but surely most of the datatype
and transform modules could be marked trustable.  (Maybe we could
make the default GUC settings more permissive, too.)

As coded, the two GUCs are not lists of extension names but rather
regexes.  You could use them as lists, eg "^plperl$|^plpgsql$|^pltcl$"
but that's a bit tedious, especially if someone wants to trust most
or all of contrib.  I am a tad worried about user-friendliness of
this notation, but I think we need something with wild-cards, and
that's the only wild-card-capable matching engine we have available
at a low level.

You might wonder why bother with the trustable flag rather than just
relying on the GUCs.  The answer is mostly paranoia: I'm worried about
somebody writing e.g. "plperl" with no anchors and not realizing that
that will match "plperlu" as well.  Anyway, since we're talking about
potential escalation-to-superuser security problems, I think having
both belt and suspenders protection on untrusted languages is wise.

There are no regression tests for this functionality in 0001,
but I added one in 0002.

Patch 0002 converts all the in-tree PLs to use fully specified
CREATE LANGUAGE and not rely on pg_pltemplate.

I had a better idea about how to manage permissions than what was
discussed in [3]; we can just give ownership of the language
object to the user calling CREATE EXTENSION.  Doing it that way
means that we end up with exactly the same catalog state as we
do in existing releases.  And that should mean that we don't have
to treat this as an extension version upgrade.  So I just modified
the 1.0 scripts in-place instead of adding 1.0--1.1 scripts.  It
looks to me like there's no need to touch the from-unpackaged
scripts, either.  And by the same token this isn't really an issue
for pg_upgrade.

(I noticed while testing this that pg_upgrade fails to preserve
ownership on extensions, but that's not new; this patch is not
making that situation any better or worse than it was.  Still,
maybe we oughta try to fix that sometime soon too.)

Patch 0003 removes CREATE LANGUAGE's reliance on pg_pltemplate.
CREATE LANGUAGE without parameters is now interpreted as
CREATE EXTENSION, thus providing a forward compatibility path
for old dump files.

Note: this won't help for *really* old dump files, ie those containing
CREATE LANGUAGE commands that do have parameters but the parameters are
wrong according to modern usage.  This is a hazard for dumps coming
from 8.0 or older servers; we invented pg_pltemplate in 8.1 primarily
as a way of cleaning up such dumps [5].  I think that that's far enough
back that we don't have to worry about how convenient it will be to go
from 8.0-or-older to v13-or-newer in one jump.

Finally, patch 0004 removes the now-unused catalog and cleans up some
incidental comments referring to it.

Once this is in, we could start thinking about whether we actually
want to change anything about plpython in the near future.

			regards, tom lane

[1] https://www.postgresql.org/message-id/flat/763f2fe4-743f-d530-8831-20811edd3d6a%402ndquadrant.com
[2] https://www.postgresql.org/message-id/flat/7495.1524861244%40sss.pgh.pa.us
[3] https://www.postgresql.org/message-id/flat/5351890.TdMePpdHBD%40nb.usersys.redhat.com
[4] https://www.postgresql.org/message-id/flat/CAKmB1PGDAy9mXxSTqUchYEi4iJAA6NKVj4P5BtAzvQ9wSDUwJw@mail.gmail.com
[5] https://www.postgresql.org/message-id/flat/5088.1125525412@sss.pgh.pa.us

  2. Re: Removing pg_pltemplate and creating "trustable" extensions

    Peter Eisentraut <peter.eisentraut@2ndquadrant.com> — 2019-11-06T07:52:29Z

    On 2019-08-21 21:29, Tom Lane wrote:
> Patch 0001 below addresses this problem by inventing a concept of
> "trustable" (not necessarily trusted) extensions.  An extension that
> would normally require superuser permissions (e.g., because it creates
> C functions) can now be installed by a non-superuser if (a) it is
> marked trustable in the extension's control file, AND (b) it is
> listed as trusted in one of two new GUCs, trusted_extensions_dba and
> trusted_extensions_anyone.  (These names could stand a visit to the
> bikeshed, no doubt.)  Extensions matching trusted_extensions_dba can
> be installed by a database owner, while extensions matching
> trusted_extensions_anyone can be installed by anybody.  The default
> settings of these GUCs provide backwards-compatible behavior, but
> they can be adjusted to provide more or less ability to install
> extensions.  (This design is basically what Andres advocated in [2].)

I think this overall direction is good.  I'm not so fond of the interfaces.

Using GUCs to control some of this creates yet another place where 
permission information is kept, and with it questions about how to get 
to it, how to edit it, or to back it up and restore it, etc.  Also, 
list-based parameters are particularly hard to manage by automated 
tools.  I think we can do this within the existing permission system, 
for example with pre-defined roles (for example, GRANT 
pg_create_trusted_extension ...).  Also, CREATE EXTENSION should somehow 
be controlled by the CREATE privilege on the containing database, so a 
separate setting for database owner vs. regular user might not be 
necessary.  Regular users would need both the role membership (given by 
the overall superuser) and the privilege within the database (given by 
the database owner).

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  3. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-11-06T15:06:42Z

    Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> On 2019-08-21 21:29, Tom Lane wrote:
>> Patch 0001 below addresses this problem by inventing a concept of
>> "trustable" (not necessarily trusted) extensions.  An extension that
>> would normally require superuser permissions (e.g., because it creates
>> C functions) can now be installed by a non-superuser if (a) it is
>> marked trustable in the extension's control file, AND (b) it is
>> listed as trusted in one of two new GUCs, trusted_extensions_dba and
>> trusted_extensions_anyone.

> I think this overall direction is good.  I'm not so fond of the interfaces.

> Using GUCs to control some of this creates yet another place where 
> permission information is kept, and with it questions about how to get 
> to it, how to edit it, or to back it up and restore it, etc.  Also, 
> list-based parameters are particularly hard to manage by automated 
> tools.  I think we can do this within the existing permission system, 
> for example with pre-defined roles (for example, GRANT 
> pg_create_trusted_extension ...).  Also, CREATE EXTENSION should somehow 
> be controlled by the CREATE privilege on the containing database, so a 
> separate setting for database owner vs. regular user might not be 
> necessary.  Regular users would need both the role membership (given by 
> the overall superuser) and the privilege within the database (given by 
> the database owner).

Hm.  In principle I'm okay with the idea of having a predefined role
or two for extension installation.  I think though that we could not
easily make that design emulate the current behavior, wherein database
owners automatically have the ability to install trusted PLs.  The
superuser would have to take the additional step of granting them a
role to let them do that.  Maybe that's just fine --- from some
angles it could be seen as an improvement --- but it is an
incompatibility.  Anybody have a problem with that?

Do we need more than one level of extension trust-ability (and more
than one predefined role to go with that)?  Assuming that we go ahead
and mark all the safe-looking contrib modules as trustable, granting
"pg_install_extensions" or whatever we call it would then give a DB
owner more privilege than just the ability to install trusted PLs.
But maybe that's fine too.

I agree with the idea of requiring a DB-level privilege as well as
the overall role.  Is it okay to re-use the CREATE privilege (which
today only allows for CREATE SCHEMA), or do we need another one?
I think re-using CREATE is probably all right, since it would only be
useful for this purpose to users who also have "pg_install_extensions".

			regards, tom lane

  4. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2019-11-07T18:38:56Z

    Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> > On 2019-08-21 21:29, Tom Lane wrote:
> >> Patch 0001 below addresses this problem by inventing a concept of
> >> "trustable" (not necessarily trusted) extensions.  An extension that
> >> would normally require superuser permissions (e.g., because it creates
> >> C functions) can now be installed by a non-superuser if (a) it is
> >> marked trustable in the extension's control file, AND (b) it is
> >> listed as trusted in one of two new GUCs, trusted_extensions_dba and
> >> trusted_extensions_anyone.
> 
> > I think this overall direction is good.  I'm not so fond of the interfaces.

I'm not really thrilled with this interface either.

> > Using GUCs to control some of this creates yet another place where 
> > permission information is kept, and with it questions about how to get 
> > to it, how to edit it, or to back it up and restore it, etc.  Also, 
> > list-based parameters are particularly hard to manage by automated 
> > tools.  I think we can do this within the existing permission system, 
> > for example with pre-defined roles (for example, GRANT 
> > pg_create_trusted_extension ...).  Also, CREATE EXTENSION should somehow 
> > be controlled by the CREATE privilege on the containing database, so a 
> > separate setting for database owner vs. regular user might not be 
> > necessary.  Regular users would need both the role membership (given by 
> > the overall superuser) and the privilege within the database (given by 
> > the database owner).

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".  Second,
this approach is requiring that a user who is allowed to create
extensions must also be allowed to create schemas on the database in
question.

> Hm.  In principle I'm okay with the idea of having a predefined role
> or two for extension installation.  I think though that we could not
> easily make that design emulate the current behavior, wherein database
> owners automatically have the ability to install trusted PLs.  The
> superuser would have to take the additional step of granting them a
> role to let them do that.  Maybe that's just fine --- from some
> angles it could be seen as an improvement --- but it is an
> incompatibility.  Anybody have a problem with that?

I'm certainly fine with a little backwards incompatibility breakage to
remove pg_pltemplate.

> Do we need more than one level of extension trust-ability (and more
> than one predefined role to go with that)?  Assuming that we go ahead
> and mark all the safe-looking contrib modules as trustable, granting
> "pg_install_extensions" or whatever we call it would then give a DB
> owner more privilege than just the ability to install trusted PLs.
> But maybe that's fine too.

I also agree with the idea of making PLs be closer to extensions, and
this change would move us in that direction too.

> I agree with the idea of requiring a DB-level privilege as well as
> the overall role.  Is it okay to re-use the CREATE privilege (which
> today only allows for CREATE SCHEMA), or do we need another one?

If we just created another one, wouldn't that remove the need to have a
database role?  I certainly understand that default roles in the
database are useful, but I don't think we should be using them in cases
where a traditional GRANT-based privilege could be used instead, and
this certainly seems like a case where a user could just have "CREATE
EXTENSION" as a privilege GRANT'd to their role, at a database level,
and they would then be able to create (trusted) extensions in that
database.  That would also make it independent of the "CREATE SCHEMA"
privilege that we have now, removing the need to wonder about the above
question regarding combining the two.

This is far from the first time we've talked about allowing privilege
based control over who is allowed to create what kind of objects in the
system.  That kind of fine-grained control over other objects would also
be a good improvement to our privilege system (not everyone needs to be
able to create functions and operators, particularly when those are
actually roles that are logged into by services who shouldn't ever be
creating those kinds of objects even if they, maybe, need to create
tables or similar...).

> I think re-using CREATE is probably all right, since it would only be
> useful for this purpose to users who also have "pg_install_extensions".

With this, you couldn't have a user who is able to create extensions but
not able to create schemas though.  That kind of combining of privileges
together really goes against the general principle of 'least privilege',
unless the action associated with one necessairly requires the other,
but I don't believe that's the case here.

Thanks,

Stephen

  5. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-11-07T18:54:12Z

    Stephen Frost <sfrost@snowman.net> writes:
>> Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
>>> Using GUCs to control some of this creates yet another place where 
>>> permission information is kept, and with it questions about how to get 
>>> to it, how to edit it, or to back it up and restore it, etc.  Also, 
>>> list-based parameters are particularly hard to manage by automated 
>>> tools.  I think we can do this within the existing permission system, 
>>> for example with pre-defined roles (for example, GRANT 
>>> pg_create_trusted_extension ...).  Also, CREATE EXTENSION should somehow 
>>> be controlled by the CREATE privilege on the containing database, so a 
>>> separate setting for database owner vs. regular user might not be 
>>> necessary.  Regular users would need both the role membership (given by 
>>> the overall superuser) and the privilege within the database (given by 
>>> the database owner).

> 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'd still keep the "trustable" property (probably renamed to "trusted"
for simplicity) for extensions, so in the worst case, an admin could
edit extension control files to add or remove the per-extension flag.

> Second,
> this approach is requiring that a user who is allowed to create
> extensions must also be allowed to create schemas on the database in
> question.

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 agree with the idea of requiring a DB-level privilege as well as
>> the overall role.  Is it okay to re-use the CREATE privilege (which
>> today only allows for CREATE SCHEMA), or do we need another one?

> If we just created another one, wouldn't that remove the need to have a
> database role?

No, because then being DB owner would be alone be enough to let you
install extensions (since as owner, you could certainly grant yourself
all privileges on the DB, even if this were somehow not the default).
We'd have to mangle GRANT's behavior to avoid that, and I don't think
we should.  Nor do I think that DB ownership ought to be enough
privilege by itself.

>> I think re-using CREATE is probably all right, since it would only be
>> useful for this purpose to users who also have "pg_install_extensions".

> With this, you couldn't have a user who is able to create extensions but
> not able to create schemas though.  That kind of combining of privileges
> together really goes against the general principle of 'least privilege',
> unless the action associated with one necessairly requires the other,
> but I don't believe that's the case here.

A point here is that many extensions involve creating their own schemas
anyway.  Also, the ability to "relocate" an extension to a different
schema is pretty meaningless if you can't create a schema to put it in.

If I thought that there were a use-case for letting someone create
extensions but not schemas, I'd be more eager to invent a new bit.
But I'm having a *really* hard time envisioning a live use-case
for that.  Granting extension-creation ability requires a whole lot
more trust in the grantee than the ability to make new schemas
(which, in themselves, have about zero impact on anybody else).

			regards, tom lane

  6. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2019-11-07T19:13:20Z

    Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> >> Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> >>> Using GUCs to control some of this creates yet another place where 
> >>> permission information is kept, and with it questions about how to get 
> >>> to it, how to edit it, or to back it up and restore it, etc.  Also, 
> >>> list-based parameters are particularly hard to manage by automated 
> >>> tools.  I think we can do this within the existing permission system, 
> >>> for example with pre-defined roles (for example, GRANT 
> >>> pg_create_trusted_extension ...).  Also, CREATE EXTENSION should somehow 
> >>> be controlled by the CREATE privilege on the containing database, so a 
> >>> separate setting for database owner vs. regular user might not be 
> >>> necessary.  Regular users would need both the role membership (given by 
> >>> the overall superuser) and the privilege within the database (given by 
> >>> the database owner).
> 
> > 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.

> I'd still keep the "trustable" property (probably renamed to "trusted"
> for simplicity) for extensions, so in the worst case, an admin could
> edit extension control files to add or remove the per-extension flag.

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.

> > Second,
> > this approach is requiring that a user who is allowed to create
> > extensions must also be allowed to create schemas on the database in
> > question.
> 
> 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...).

> >> I agree with the idea of requiring a DB-level privilege as well as
> >> the overall role.  Is it okay to re-use the CREATE privilege (which
> >> today only allows for CREATE SCHEMA), or do we need another one?
> 
> > If we just created another one, wouldn't that remove the need to have a
> > database role?
> 
> No, because then being DB owner would be alone be enough to let you
> install extensions (since as owner, you could certainly grant yourself
> all privileges on the DB, even if this were somehow not the default).
> We'd have to mangle GRANT's behavior to avoid that, and I don't think
> we should.  Nor do I think that DB ownership ought to be enough
> privilege by itself.

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

I agree that we don't want to mangle GRANT's behavior, at all, for this.

> >> I think re-using CREATE is probably all right, since it would only be
> >> useful for this purpose to users who also have "pg_install_extensions".
> 
> > With this, you couldn't have a user who is able to create extensions but
> > not able to create schemas though.  That kind of combining of privileges
> > together really goes against the general principle of 'least privilege',
> > unless the action associated with one necessairly requires the other,
> > but I don't believe that's the case here.
> 
> A point here is that many extensions involve creating their own schemas
> anyway.  Also, the ability to "relocate" an extension to a different
> schema is pretty meaningless if you can't create a schema to put it in.

What extensions require creating their own schema?  Every single
extension that's in contrib can be installed into the public schema
(concurrently, even) except for two hacks- plpgsql and adminpack, and
those go into pg_catalog for historical reasons more than anything else.

Creating a schema is an option for extensions but it isn't a
requirement.  I agree that you need the ability to create schemas if you
want to relocate one, but that's like needing SELECT to do an UPDATE
without a WHERE clause.  I also don't know that extension relocation is
really something that's commonly done.

> If I thought that there were a use-case for letting someone create
> extensions but not schemas, I'd be more eager to invent a new bit.
> But I'm having a *really* hard time envisioning a live use-case
> for that.  Granting extension-creation ability requires a whole lot
> more trust in the grantee than the ability to make new schemas
> (which, in themselves, have about zero impact on anybody else).

The idea of 'least privilege' isn't "well, I'm gonna grant you this
other thing that you don't actually need, just because I trust you with
this privilege that you do need."

Thanks,

Stephen

  7. Re: Removing pg_pltemplate and creating "trustable" extensions

    Chapman Flack <chap@anastigmatix.net> — 2019-11-07T19:40:57Z

    On 11/7/19 2:13 PM, Stephen Frost wrote:

>> 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

Am I mis-following the conversation in some way? I'm having trouble
seeing this as a question about a privilege bit, because that leads
straight on to the question of what database object carries the acl
item that grants that bit to a role. An extension isn't yet a database
object until after you create it.

So isn't this more a proposal to add another boolean attribute
to pg_authid, along the lines of rolcreatedb or rolbypassrls?


On the other hand, maybe thinking of it as a privilege bit could
lead somewhere interesting. A not-yet-installed extension isn't
a real database object, but it does have a synthesized existence
as a row in the pg_available_extensions view. Maybe that could
have an acl column, where a privilege (why not just CREATE?) could
be granted to one or more roles. Synthesizing that could rely on
some directive in the control file, or in some separate
extension_creators.conf file that would associate extensions with
roles.

That would avoid using a new bit, avoid adding a pg_authid attribute,
and avoid setting in stone a particular predefined role or two or
a single final meaning of 'trusted'. A site could create a few roles
and edit extension_creators.conf to associate extensions with them.

Maybe that's just a more ad-hoc and GUCless way of circling back
to what the original proposal would be doing with GUCs....

Regards,
-Chap

  8. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-11-07T19:45:32Z

    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

  9. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-11-07T20:00:37Z

    Chapman Flack <chap@anastigmatix.net> writes:
> So isn't this more a proposal to add another boolean attribute
> to pg_authid, along the lines of rolcreatedb or rolbypassrls?

I think we've mostly concluded that default roles are superior
to pg_authid attributes.  The latter are legacy things rather
than a model to keep extending.

> On the other hand, maybe thinking of it as a privilege bit could
> lead somewhere interesting. A not-yet-installed extension isn't
> a real database object, but it does have a synthesized existence
> as a row in the pg_available_extensions view. Maybe that could
> have an acl column, where a privilege (why not just CREATE?) could
> be granted to one or more roles. Synthesizing that could rely on
> some directive in the control file, or in some separate
> extension_creators.conf file that would associate extensions with
> roles.

Meh ... that seems like building a whole new set of infrastructure
to solve something that we already have a couple of good models
for (i.e., default roles and object-based permissions).  I really
doubt it's worth the trouble to do that.

Although upthread I mentioned the possibility of a database admin
editing extension control files, I think most people would consider
that to be a truly last resort; you generally want those files to
remain as-distributed.  The alternative of a new config file is
slightly less unmaintainable, but only slightly.  There'd be no
way to update it from inside the database, short of writing a lot
of new infrastructure comparable to ALTER SYSTEM, and surely we
don't want to do that.

> Maybe that's just a more ad-hoc and GUCless way of circling back
> to what the original proposal would be doing with GUCs....

Yeah, I think if we really need per-extension configurability
of this, we're going to end up with a GUC.  It's just not worth
the trouble to build another mechanism that would support such a
need.  But I'm currently taking the position that we don't need
to support that.

			regards, tom lane

  10. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-11-09T20:24:12Z

    I wrote:
> Stephen Frost <sfrost@snowman.net> writes:
>> 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"?

After sleeping on it, I'm liking that idea; it's simple, and it
preserves the existing behavior that DB owners can install trusted PLs
without any extra permissions.  Now, if we follow this up by marking
most of contrib as trusted, we'd be expanding that existing privilege.
But I think that's all right: I don't recall anybody ever complaining
that they wanted to prevent DB owners from installing trusted PLs, and
I do recall people wishing that it didn't take superuser to install
the other stuff.

Accordingly, here's a patchset that does it like that.

I decided after looking at the existing default role names that
"pg_install_trusted_extension" (no plural) was more consistent
with the existing names than adding an "s".  I don't find that
precedent particularly charming, but it's what we've got.

I also renamed the extension property from "trustable" to "trusted".
There are at least a couple of reasons to be dissatisfied with that:

(1) There's potential for confusion between the notion of a trusted
extension and that of a trusted PL; those properties do roughly
similar things, but they're not exactly equivalent.  I didn't think
this was enough of a problem to justify choosing a different name,
but somebody else might think differently.

(2) If we were starting this design from scratch, we'd probably not
have two interrelated boolean properties "superuser" and "trusted",
but one three-state enum property.  The enum approach would likely
be a lot easier to extend if we eventually grow more privilege
levels for extension installation.  I'm not sure whether it's worth
breaking backwards compatibility now to keep our options open for
that, though.  We could preserve extension control file
compatibility easily enough by keeping "superuser = true" and
"superuser = false" as allowed legacy spellings for two values of
an enum property.  But the pg_available_extension_versions view is
a tougher nut.  On the other hand, maybe replacing its "superuser"
column with something else wouldn't really cause many problems.

Other than getting rid of the GUCs in favor of this design,
it's mostly the same patchset as before.  0003 and 0004 haven't
changed at all, and 0002 only differs by adjusting the test case.

			regards, tom lane

  11. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-11-15T21:22:17Z

    I wrote:
> Accordingly, here's a patchset that does it like that.

The cfbot noticed that a couple of patches committed this week
created (trivial) conflicts with this patchset.  Here's a v3
rebased up to HEAD; no interesting changes.

			regards, tom lane

  12. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-05T22:51:37Z

    I wrote:
> The cfbot noticed that a couple of patches committed this week
> created (trivial) conflicts with this patchset.  Here's a v3
> rebased up to HEAD; no interesting changes.

The 2020 copyright update broke this patchset again.  Here's a rebase.
No changes except for some minor rearrangement of the CREATE LANGUAGE
man page in 0003.

			regards, tom lane

  13. Re: Removing pg_pltemplate and creating "trustable" extensions

    Robert Haas <robertmhaas@gmail.com> — 2020-01-06T16:29:02Z

    On Thu, Nov 7, 2019 at 2:13 PM Stephen Frost <sfrost@snowman.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.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  14. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-06T18:27:47Z

    Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Nov 7, 2019 at 2:13 PM Stephen Frost <sfrost@snowman.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?

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.

			regards, tom lane

  15. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-06T19:14:41Z

    Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > On Thu, Nov 7, 2019 at 2:13 PM Stephen Frost <sfrost@snowman.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

  16. Re: Removing pg_pltemplate and creating "trustable" extensions

    Robert Haas <robertmhaas@gmail.com> — 2020-01-06T20:56:55Z

    On Mon, Jan 6, 2020 at 1:27 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> So, is that actually an objection to the current proposal, or just
> an unrelated rant?

Well, you brought up the topic of remaining bits in the context of the
proposal, so I guess it's related. And I said pretty clearly that it
wasn't necessarily an objection.

But regarding your proposal:

> After sleeping on it, I'm liking that idea; it's simple, and it
> preserves the existing behavior that DB owners can install trusted PLs
> without any extra permissions.  Now, if we follow this up by marking
> most of contrib as trusted, we'd be expanding that existing privilege.
> But I think that's all right: I don't recall anybody ever complaining
> that they wanted to prevent DB owners from installing trusted PLs, and
> I do recall people wishing that it didn't take superuser to install
> the other stuff.

If somebody were to complain about this, what could they complain
about? Potential complaints:

1. I'm the superuser and I don't want my DB owners to be able to
install extensions other than trusted PLs.
2. Or I want to control which specific ones they can install.
3. I'm a non-superuser DB owner and I want to delegate permissions to
install trusted extensions to some other user who is not a DB owner.

All of those sound reasonably legitimate; against that, you can always
argue that permissions should be more finely grained, and it's not
always worth the implementation effort to make it possible. On #1, I
tend to think that *most* people would be happy rather than sad about
DB owners being able to install extensions; after all, evil extensions
can be restricted by removing them from the disk (or marking them
untrusted), and most people who set up a database are hoping it's
going to get used for something. But somebody might not like it,
especially if e.g. it turns out that one of our "trusted" extensions
has a horrible security vulnerability. On #2, I can certainly imagine
large providers having a view about which extensions they think are
safe enough for users to install that differs from ours, and if that
horrible security vulnerability materializes it sure would be nice to
be able to easily disable access to just that one. #3 seems less
likely to be an issue, but it's not unthinkable.

"GRANT INSTALL ON mydb" seems like it would solve #1 and #3. You could
grant a particular DB owner permission to install extensions, or not.
If you have them that power WITH GRANT OPTION, then they could
sub-delegate. It wouldn't do anything about #2; that would require
some more complex scheme.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  17. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-06T22:38:38Z

    Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Jan 6, 2020 at 1:27 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> After sleeping on it, I'm liking that idea; it's simple, and it
>> preserves the existing behavior that DB owners can install trusted PLs
>> without any extra permissions.  Now, if we follow this up by marking
>> most of contrib as trusted, we'd be expanding that existing privilege.
>> But I think that's all right: I don't recall anybody ever complaining
>> that they wanted to prevent DB owners from installing trusted PLs, and
>> I do recall people wishing that it didn't take superuser to install
>> the other stuff.

> If somebody were to complain about this, what could they complain
> about? Potential complaints:

> 1. I'm the superuser and I don't want my DB owners to be able to
> install extensions other than trusted PLs.
> 2. Or I want to control which specific ones they can install.
> 3. I'm a non-superuser DB owner and I want to delegate permissions to
> install trusted extensions to some other user who is not a DB owner.

Sure, but all of these seem to be desires for features that could be
added later.  As for #1, we could have that just by not taking the
next step of marking anything but the PLs trusted (something that is
going to happen anyway for v13, if this patch doesn't move faster).
#2 is not a feature that exists now, either; actually, the patch *adds*
it, to the extent that the superuser is willing to adjust extension
control files.  Likewise, #3 is not a feature that exists now.  Also,
the patch adds something that looks partly like #3, in that the
superuser could grant pg_install_trusted_extension with admin option
to database users who should be allowed to delegate it.  Perhaps that's
inadequate, but I don't see why we can't wait for complaints before
trying to design something that satisfies hypothetical use cases.

The facts that I'm worried about are that this is already the January
'fest, and if we don't want to ship v13 with python 2 as still the
preferred python, we need to not only get this patch committed but do
some less-than-trivial additional work (that hasn't even been started).
So I'm getting very resistant to requests for more features in this patch.
I think everything you're suggesting above could be tackled later,
when and if there's actual field demand for it.

> "GRANT INSTALL ON mydb" seems like it would solve #1 and #3.

It's not apparent to me that that's better, and it seems possible that
it's worse.  The fact that a DB owner could grant that privilege to
himself means that you might as well just have it on all the time.
Like a table owner's DML rights, it would only be useful to prevent
accidentally shooting yourself in the foot ... but who accidentally
issues CREATE EXTENSION?  And if they do (for an extension that
deserves to be marked trusted) what harm is done really?  Worst
case is you drop it again.

			regards, tom lane

  18. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-06T23:07:18Z

    Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > On Mon, Jan 6, 2020 at 1:27 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> After sleeping on it, I'm liking that idea; it's simple, and it
> >> preserves the existing behavior that DB owners can install trusted PLs
> >> without any extra permissions.  Now, if we follow this up by marking
> >> most of contrib as trusted, we'd be expanding that existing privilege.
> >> But I think that's all right: I don't recall anybody ever complaining
> >> that they wanted to prevent DB owners from installing trusted PLs, and
> >> I do recall people wishing that it didn't take superuser to install
> >> the other stuff.
> 
> > If somebody were to complain about this, what could they complain
> > about? Potential complaints:
> 
> > 1. I'm the superuser and I don't want my DB owners to be able to
> > install extensions other than trusted PLs.

I don't agree that this is actually a sensible use-case, so I'm not
really sure why we're discussing solutions to make it work.  It happens
to be how things work because pg_pltemplate exists before we had
extensions and we never went back and cleaned that up- but that's
exactly what we're trying to do here, and adding in a nice feature at
the same time.

> > 2. Or I want to control which specific ones they can install.

This is exactly what the 'trusted' bit is for, isn't it?  If you think
that we need something that's actually a permission matrix between roles
and specific extensions, that's a whole different level, certainly, and
I don't think anyone's asked for or contemplated such a need.

I do like the idea of having a way to install more-or-less all
extensions out on to the filesystem and then giving superusers an
ability to decide which ones are 'trusted' and which ones are not,
without having to hand-hack the control files.  I don't particularly
like using GUCs for that but I'm not sure what a better option looks
like and I'm not completely convinced we really need this.  If we really
go down this route (without resorting to GUCs or something) then we'd 
need an additional catalog table that $someone is allowed to populate
through some kind of SQL and a whole lot of extra work and I definitely
don't think we need that right now.

> > 3. I'm a non-superuser DB owner and I want to delegate permissions to
> > install trusted extensions to some other user who is not a DB owner.

This is a use-case that I do think exists (or, at least, I'm a superuser
or a DB owner and I'd like to delegate that privilege to another user).

> Sure, but all of these seem to be desires for features that could be
> added later.  

We can't very well add a default role in one release and then decide we
want to use the GRANT-privilege system in the next and remove it...

> As for #1, we could have that just by not taking the
> next step of marking anything but the PLs trusted (something that is
> going to happen anyway for v13, if this patch doesn't move faster).

Ugh.  I find that to be a pretty horrible result.  Yes, we could mark
extensions later as 'trusted' but that'd be another year..

> #2 is not a feature that exists now, either; actually, the patch *adds*
> it, to the extent that the superuser is willing to adjust extension
> control files.  Likewise, #3 is not a feature that exists now.  Also,
> the patch adds something that looks partly like #3, in that the
> superuser could grant pg_install_trusted_extension with admin option
> to database users who should be allowed to delegate it.  Perhaps that's
> inadequate, but I don't see why we can't wait for complaints before
> trying to design something that satisfies hypothetical use cases.

#3 from Robert's list certainly strikes me as a valid use-case and not
just hypothetical.

> The facts that I'm worried about are that this is already the January
> 'fest, and if we don't want to ship v13 with python 2 as still the
> preferred python, we need to not only get this patch committed but do
> some less-than-trivial additional work (that hasn't even been started).
> So I'm getting very resistant to requests for more features in this patch.
> I think everything you're suggesting above could be tackled later,
> when and if there's actual field demand for it.

Perhaps I'm wrong, but I wouldn't think changing this from a
default-role based approach over to a GRANT'able right using our
existing GRANT system would be a lot of work.  I agree that addressing
some of the use-cases proposed above could be a great deal of work but,
as I say above, I don't agree that we need to address all of them.

> > "GRANT INSTALL ON mydb" seems like it would solve #1 and #3.
> 
> It's not apparent to me that that's better, and it seems possible that
> it's worse.  The fact that a DB owner could grant that privilege to
> himself means that you might as well just have it on all the time.

I agree that the DB owner should have that right by default, just like
they have any of the other DB-level rights that exist, just like how the
GRANT system in general works today.  If they remove it from themselves,
then that's on them and they won't be able to create extensions until
they GRANT it back to themselves.

I do *not* agree that this means we shouldn't have DB-level rights for
database owners and that we should just go hand-hack the system to have
explicit "is this the DB owner?" checks.  The suggestion you're making
here seems to imply we should go hack up the CREATE SCHEMA check to have
it see if the user is the DB owner and then allow it, instead of doing
our normal privilege checks, and I don't think that makes any sense.  I
definitely don't like the idea of having this privilege act in any more
special way than our other privileges (be it a default-role or a GRANT'd
privilege, though obviously I'm much happier with the latter than the
former for this case).

> Like a table owner's DML rights, it would only be useful to prevent
> accidentally shooting yourself in the foot ... but who accidentally
> issues CREATE EXTENSION?  And if they do (for an extension that
> deserves to be marked trusted) what harm is done really?  Worst
> case is you drop it again.

Why are we talking about adding code for this though?  The GRANT system
already handles all of this just fine and we rarely hear complaints from
people about it.

Thanks,

Stephen

  19. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-06T23:26:45Z

    Stephen Frost <sfrost@snowman.net> writes:
> Perhaps I'm wrong, but I wouldn't think changing this from a
> default-role based approach over to a GRANT'able right using our
> existing GRANT system would be a lot of work.

Nobody has proposed a GRANT-based API that seems even close to
acceptable from where I sit.  A new privilege bit on databases
is not it, at least not unless it works completely unlike
any other privilege bit.  It's giving control to the DB owners,
not the superuser, and that seems like quite the wrong thing
for this purpose.

Or to put it another way: I think that the grantable role, which
ultimately is handed out by the superuser, is the primary permissions
API in this design.  The fact that DB owners effectively have that
same privilege is a wart for backwards-compatibility.  If we were
doing this from scratch, that wart wouldn't be there.  What you're
proposing is to make the wart the primary (indeed sole) permissions
control mechanism for extension installation, and that just seems
completely wrong.  Superusers would have effectively *no* say in
who gets to install trusted extensions, which is turning the whole
thing on its head I think; it's certainly not responding to either
of Robert's first two points.

If we were willing to break backwards compatibility, what I'd prefer
is to just have the grantable role, and to say that you have to grant
that to DB owners if you want them to be able to install PLs.  I'm
not sure how loud the howls would be if we did that, but it'd be a
lot cleaner than any of these other ideas.

> I do *not* agree that this means we shouldn't have DB-level rights for
> database owners and that we should just go hand-hack the system to have
> explicit "is this the DB owner?" checks.  The suggestion you're making
> here seems to imply we should go hack up the CREATE SCHEMA check to have
> it see if the user is the DB owner and then allow it, instead of doing
> our normal privilege checks, and I don't think that makes any sense.

Uh, what?  Nothing in what I'm proposing goes anywhere near the
permissions needed for CREATE SCHEMA.

			regards, tom lane

  20. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-06T23:56:23Z

    Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > Perhaps I'm wrong, but I wouldn't think changing this from a
> > default-role based approach over to a GRANT'able right using our
> > existing GRANT system would be a lot of work.
> 
> Nobody has proposed a GRANT-based API that seems even close to
> acceptable from where I sit.  A new privilege bit on databases
> is not it, at least not unless it works completely unlike
> any other privilege bit.  It's giving control to the DB owners,
> not the superuser, and that seems like quite the wrong thing
> for this purpose.

I'm seriously confused by this.  Maybe we need to step back for a moment
because there are things that already exist today that I don't think
we're really contemplating.

The first is this- ANYONE can create an extension in the system today,
if it's marked as superuser=false.  If anything, it seems like that's
probably too loose- certainly based on your contention that ONLY
superusers should wield such a power and that letting anyone else do so
is a right that a superuser must explicitly grant.

> Or to put it another way: I think that the grantable role, which
> ultimately is handed out by the superuser, is the primary permissions
> API in this design.  The fact that DB owners effectively have that
> same privilege is a wart for backwards-compatibility.  If we were
> doing this from scratch, that wart wouldn't be there.  What you're
> proposing is to make the wart the primary (indeed sole) permissions
> control mechanism for extension installation, and that just seems
> completely wrong.  Superusers would have effectively *no* say in
> who gets to install trusted extensions, which is turning the whole
> thing on its head I think; it's certainly not responding to either
> of Robert's first two points.

Superusers don't have any (direct) say in who gets to create schemas
either, yet we don't seem to have a lot of people complaining about it.
In fact, superusers don't have any say in who gets to create functions,
or operators, or tables, or indexes, or EXTENSIONS, either.  The fact is
that DB owners can *already* create most objects, including extensions,
in the DB system without the superuser being able to say anything about
it.

I really don't understand this hold-up when it comes to (trusted)
extensions.  Consider that, today, in many ways, PLs *are* the 'trusted'
extensions that DB owners are already allowed to install.  They're
libraries of C functions that are trusted to do things right and
therefore they can be allowed to be installed by DB owners.

If we had a generic way to have a C library declare that it only exposes
'trusted' C functions, would we deny users the ability to create those
functions in the database, when they can create functions in a variety
of other trusted languages?  Why would the fact that they're C
functions, in that case, make them somehow special?  That is, in fact,
*exactly* what's already going on with pltemplate and trusted languages.

Having trusted extensions is giving us exactly what pltemplate does, but
in a generic way where any C library (or whatever else) can be declared
as trusted, as defined by the extension framework around it, and
therefore able to be installed by DB owners.  Considering we haven't got
any kind of check in the system today around extension creation, itself,
this hardly seems like a large step to me- one could even argue that
maybe we should just let ANYONE create them, but I'm not asking for
that (in fact, I could probably be argued into agreeing to remove the
ability for $anyone to create non-superuser extensions today, if we
added this privilege...).

> If we were willing to break backwards compatibility, what I'd prefer
> is to just have the grantable role, and to say that you have to grant
> that to DB owners if you want them to be able to install PLs.  I'm
> not sure how loud the howls would be if we did that, but it'd be a
> lot cleaner than any of these other ideas.

If we can't come to agreement regarding using a regular GRANT'able
right, then I'd much rather break backwards compatibility than have such
a hacked up wart like this special case you're talking about for PLs.

> > I do *not* agree that this means we shouldn't have DB-level rights for
> > database owners and that we should just go hand-hack the system to have
> > explicit "is this the DB owner?" checks.  The suggestion you're making
> > here seems to imply we should go hack up the CREATE SCHEMA check to have
> > it see if the user is the DB owner and then allow it, instead of doing
> > our normal privilege checks, and I don't think that makes any sense.
> 
> Uh, what?  Nothing in what I'm proposing goes anywhere near the
> permissions needed for CREATE SCHEMA.

I understand that- you're talking about just having this 'wart' for
CREATE EXTENSION and I don't agree with having the 'wart' at all.  To
start doing this for PLs would be completely inconsistent with the way
the rest of the privilege system works and that's not ok.

Thanks,

Stephen

  21. Re: Removing pg_pltemplate and creating "trustable" extensions

    Robert Haas <robertmhaas@gmail.com> — 2020-01-07T17:08:45Z

    On Mon, Jan 6, 2020 at 6:56 PM Stephen Frost <sfrost@snowman.net> wrote:
> The first is this- ANYONE can create an extension in the system today,
> if it's marked as superuser=false.  If anything, it seems like that's
> probably too loose- certainly based on your contention that ONLY
> superusers should wield such a power and that letting anyone else do so
> is a right that a superuser must explicitly grant.

I don't think this argument makes any sense. Sure, anyone can create
an extension with superuser=false, but so what? From a security point
of view, when you create such an extension, you are using your own
privileges to do things that you could do anyway. The interesting case
is creating an extension that requires superuser privileges, probably
because it's going to call C functions. The superuser can and must
have the last word regarding who is allowed to do such things, because
the superuser is equivalent to the OS user and any other user of the
system is not. The "tenants" of the database system can't be allowed
to use it for things which the "owner" does not wish to permit.

On Mon, Jan 6, 2020 at 6:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> If we were willing to break backwards compatibility, what I'd prefer
> is to just have the grantable role, and to say that you have to grant
> that to DB owners if you want them to be able to install PLs.  I'm
> not sure how loud the howls would be if we did that, but it'd be a
> lot cleaner than any of these other ideas.

That seems like a fine idea. Then the superuser has ultimate control,
and can decide which database owners they want to trust, and whether
they'd like the database owners to be able to subdelegate those
permissions. The only thing it doesn't do is give any control over
exactly which extensions can be installed by non-superusers, which
would be a really nice thing to have, especially if we're going to
significant expand the list of trusted extensions (something that I
think is, overall, quite a good idea). I accept Tom's argument that he
isn't obliged to add every related feature somebody might want just
because he's doing some work in this area, but not his contention that
the demand for such a feature is entirely hypothetical and the
suggestion that perhaps nobody will care anyway. I expect the reaction
to be along the lines of "hey, it's great that we can let DB owners do
this now, but it's really too bad that I can't blacklist
$SCARY_EXTENSION". I don't think that we'll be better off if this
entire proposal gets voted down for lack of that capability, but I
think it would be a really good thing to add.

FWIW, I don't really buy the argument that you can adjust the
extension control files to get out from under this problem.
Technically, that is true. But in practice, the extension control
files are provided by your packager, and you don't want to modify them
because then your packaging system will get grumpy with you. While
it's reasonable for the packaging to provide a tentative answer to the
question of what should be trusted, trust is ultimately a matter of
local policy, and that policy should be configured someplace that's
not managed by RPM.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  22. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-07T18:17:27Z

    Greetings,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Mon, Jan 6, 2020 at 6:56 PM Stephen Frost <sfrost@snowman.net> wrote:
> > The first is this- ANYONE can create an extension in the system today,
> > if it's marked as superuser=false.  If anything, it seems like that's
> > probably too loose- certainly based on your contention that ONLY
> > superusers should wield such a power and that letting anyone else do so
> > is a right that a superuser must explicitly grant.
> 
> I don't think this argument makes any sense. Sure, anyone can create
> an extension with superuser=false, but so what? From a security point
> of view, when you create such an extension, you are using your own
> privileges to do things that you could do anyway. The interesting case
> is creating an extension that requires superuser privileges, probably
> because it's going to call C functions. The superuser can and must
> have the last word regarding who is allowed to do such things, because
> the superuser is equivalent to the OS user and any other user of the
> system is not. The "tenants" of the database system can't be allowed
> to use it for things which the "owner" does not wish to permit.

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

> On Mon, Jan 6, 2020 at 6:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > If we were willing to break backwards compatibility, what I'd prefer
> > is to just have the grantable role, and to say that you have to grant
> > that to DB owners if you want them to be able to install PLs.  I'm
> > not sure how loud the howls would be if we did that, but it'd be a
> > lot cleaner than any of these other ideas.
> 
> That seems like a fine idea. Then the superuser has ultimate control,
> and can decide which database owners they want to trust, and whether
> they'd like the database owners to be able to subdelegate those
> permissions. The only thing it doesn't do is give any control over
> exactly which extensions can be installed by non-superusers, which
> would be a really nice thing to have, especially if we're going to
> significant expand the list of trusted extensions (something that I
> think is, overall, quite a good idea). I accept Tom's argument that he
> isn't obliged to add every related feature somebody might want just
> because he's doing some work in this area, but not his contention that
> the demand for such a feature is entirely hypothetical and the
> suggestion that perhaps nobody will care anyway. I expect the reaction
> to be along the lines of "hey, it's great that we can let DB owners do
> this now, but it's really too bad that I can't blacklist
> $SCARY_EXTENSION". I don't think that we'll be better off if this
> entire proposal gets voted down for lack of that capability, but I
> think it would be a really good thing to add.

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?

If the extension is trusted, then there isn't a security issue with it,
and it isn't scary, by definition, imv, which negates these arguments
about making the right to install it have to be hand delegated by a
superuser and needing a system for managing who is allowed to install
what extension.

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.

> FWIW, I don't really buy the argument that you can adjust the
> extension control files to get out from under this problem.
> Technically, that is true. But in practice, the extension control
> files are provided by your packager, and you don't want to modify them
> because then your packaging system will get grumpy with you. While
> it's reasonable for the packaging to provide a tentative answer to the
> question of what should be trusted, trust is ultimately a matter of
> local policy, and that policy should be configured someplace that's
> not managed by RPM.

This I tend to agree with- hacking around with control files or other
files installed with extensions from RPMs isn't a great plan.

A possible alternative would be to have a *new* configuration file (not
part of the GUC system) which admins could hack up to specify who should
be allowed to install what extension.  Or we make that a catalog table
instead because, well, such things should probably be in the database
where we can have dependency management and validity checking...

On the other hand, having individual packages for different extensions
is a pretty handy way of letting an administrator decide if they want
that extension to be installed on their system or not.  That's a pain
for contrib because, really, all of those should really just be in core,
or not included if we aren't confident that they're safe to use.

Thanks,

Stephen

  23. Re: Removing pg_pltemplate and creating "trustable" extensions

    Robert Haas <robertmhaas@gmail.com> — 2020-01-07T18:55:50Z

    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. 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.

> 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.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  24. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-07T21:36:18Z

    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

  25. Re: Removing pg_pltemplate and creating "trustable" extensions

    Robert Haas <robertmhaas@gmail.com> — 2020-01-07T23:49:01Z

    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.

> 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.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  26. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-08T00:32:47Z

    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

  27. Re: Removing pg_pltemplate and creating "trustable" extensions

    Robert Haas <robertmhaas@gmail.com> — 2020-01-08T22:49:06Z

    On Tue, Jan 7, 2020 at 7:32 PM Stephen Frost <sfrost@snowman.net> wrote:
> 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.

To me, this seems more accidental than the natural fallout of good design.

> Why would a $SCARY_EXTENSION be marked as trusted?

Well, again, my point in using postgres_fdw as an example was not that
it should be untrusted, or that it should be trusted, but that
different people might have different views about that question, and
therefore configurability would be good. I believe the same thing
applies in other cases. For me, this boils down to the view that the
superuser can have arbitrary preferences about what C code they want
to let users run, and they need not justify such views with reference
to anything in particular. Some superuser can decide that they think
hstore is great stuff but bloom is too experimental and isn is a pile
of crap, and that all seems perfectly legitimate to me. And some other
superuser can have a different view and that seems fine, too. I can't
think of any reason why a particular installation should have to
decide between certifying most of contrib and certifying none of it,
with no intermediate options. I guess you see it differently.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  28. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-08T23:09:35Z

    Greetings,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Tue, Jan 7, 2020 at 7:32 PM Stephen Frost <sfrost@snowman.net> wrote:
> > 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.

(guessing it was clear, but sorry for the typo above, 'now' should have
been 'not')

> To me, this seems more accidental than the natural fallout of good design.

I disagree that the privilege design for FDWs was accidental.  Perhaps
it was a mistake to automatically run the CREATE FDW as part of the
extension script, but that doesn't really change any of the argument I'm
making.

> > Why would a $SCARY_EXTENSION be marked as trusted?
> 
> Well, again, my point in using postgres_fdw as an example was not that
> it should be untrusted, or that it should be trusted, but that
> different people might have different views about that question, and
> therefore configurability would be good. I believe the same thing
> applies in other cases. For me, this boils down to the view that the
> superuser can have arbitrary preferences about what C code they want
> to let users run, and they need not justify such views with reference
> to anything in particular. Some superuser can decide that they think
> hstore is great stuff but bloom is too experimental and isn is a pile
> of crap, and that all seems perfectly legitimate to me. And some other
> superuser can have a different view and that seems fine, too. I can't
> think of any reason why a particular installation should have to
> decide between certifying most of contrib and certifying none of it,
> with no intermediate options. I guess you see it differently.

What I see differently is that the purview of those decisions should be
that of the DB owner and not the superuser.  If the superuser really
wants to own those decisions, then they should be the DB owner too, and
then they can also control what schemas exist and who can use them, or
they can GRANT that right out only to whomever they trust with it, and
likewise could GRANT out this install-extension right out to whomever
they deem worthy.

As it relates to things in contrib that could be classified as 'a pile
of crap' or 'too experimental'- that's *our* failing, and one which we
should accept and address instead of punting on it.  In my recollection,
this is far from the first time someone's suggested that maybe we should
try to clean up contrib.  I don't accept that our reluctance to punt
things out of contrib that shouldn't be there is an acceptable argument
against using our existing GRANT system for this new right or sufficient
justification to use a default role instead.

Thanks,

Stephen

  29. Re: Removing pg_pltemplate and creating "trustable" extensions

    Robert Haas <robertmhaas@gmail.com> — 2020-01-09T02:07:20Z

    On Wed, Jan 8, 2020 at 6:09 PM Stephen Frost <sfrost@snowman.net> wrote:
> > To me, this seems more accidental than the natural fallout of good design.
>
> I disagree that the privilege design for FDWs was accidental.

That seems like a stronger statement than the one I made...

> What I see differently is that the purview of those decisions should be
> that of the DB owner and not the superuser.

Yeah, I don't agree with that at all. If I'm a hosting provider, I
want to tell my customers what they're allowed to run and what they're
not allowed to run, but I don't want them to have to call me when they
want to run an extension I've decided is OK.

> As it relates to things in contrib that could be classified as 'a pile
> of crap' or 'too experimental'- that's *our* failing, and one which we
> should accept and address instead of punting on it.

I don't think changing what's in contrib helps much. Even if we rm
-rf'd it, there's the same problem with out-of-core extensions. Joe
Extensionman may think his extension ought to be trusted, and package
it as such, but Paula Skepticaldba is entitled to think Joe's view of
the security risks originating from his code is overly rosy.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  30. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-09T15:09:11Z

    Greetings,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Wed, Jan 8, 2020 at 6:09 PM Stephen Frost <sfrost@snowman.net> wrote:
> > > To me, this seems more accidental than the natural fallout of good design.
> >
> > I disagree that the privilege design for FDWs was accidental.
> 
> That seems like a stronger statement than the one I made...

Perhaps I misunderstood what you were referring to then.

> > What I see differently is that the purview of those decisions should be
> > that of the DB owner and not the superuser.
> 
> Yeah, I don't agree with that at all. If I'm a hosting provider, I
> want to tell my customers what they're allowed to run and what they're
> not allowed to run, but I don't want them to have to call me when they
> want to run an extension I've decided is OK.

Now I'm really floored because I've also been contemplating exactly the
situation of a hosting provider.  In my experience, there's a few pretty
common themes among the ones that I've played with:

- Users definitely don't get true PG superuser
- They certainly like users to be able to install trusted extensions
- They'd generally prefer to minimize the size of their fork

I've been feeling like the solution I'm pushing for would, in the end,
*reduce* the amount of code they have that's different from PG, which I
believe they'd see as an entirely good thing.  The approach proposed in
this thread seems likely to either increase the size of the fork or, at
best, be about the same size.

Now the question boils down to "what's trusted?" and if I'm a hosting
provider, it'd sure be nice to foist off the responsibility of figuring
that out on a community that I can trust that will do so at no cost to
me, and who will address any bugs or security holes in those trusted
extensions for me.

Realistically, we pretty much already do that for contrib and that's
really all that's relevant here- anything else would need to be
physically installed on the system in order for a user to be able to
install it, and I expect hosting providers to be pretty happy with a
solution that boils down to just having to install RPMs, or not (or the
equivilant with regard to putting files into the right places and such).

Now, if we're talking about defining repositories or allowing users to
upload their *own* C code from their systems in to PG and install that
as an extension, then, sure, that's gotta be limited to a superuser (and
wouldn't be allowed by hosting providers).  If the extension is
installed on the system though, and it's marked as trusted, then why do
we need a superuser to take some additional action to allow it to be
installed..?

When it comes to the hosting provider case, I'd argue that what we're
missing here is a way for them to give their "not-quite-a-superuser
role" the ability to have certain capabilities (at install time, of
course)- a good example of that would be "allowed to make outbound
network connections", with a way to then be able to delegate out that
ability to others.  Then they'd actually be able to use things like
postgres_fdw and dblink after they're installed and without having to
get a superuser to grant them that ability post-install (and that order
of operations issue is a pretty key one- it's far simpler to set things
up and hand them over to the user than to have some operation that has
to happen as an actual superuser after the installation is done).

Even if we marked postgres_fdw as trusted, and used this default-role
based approach, we're almost certainly going to have the FDW itself be
owned by the bootstrap superuser and therefore whatever non-superuser
role that installs it wouldn't be able to actually use it without some
other changes.  I'd love to get to a point where you could have an
initially set up system with a not-quite-superuser role that would be
able to actually install AND use postgres_fdw, without having to fork
PG.

> > As it relates to things in contrib that could be classified as 'a pile
> > of crap' or 'too experimental'- that's *our* failing, and one which we
> > should accept and address instead of punting on it.
> 
> I don't think changing what's in contrib helps much. Even if we rm
> -rf'd it, there's the same problem with out-of-core extensions. Joe
> Extensionman may think his extension ought to be trusted, and package
> it as such, but Paula Skepticaldba is entitled to think Joe's view of
> the security risks originating from his code is overly rosy.

Out of core extensions have to get installed on to the system though,
they don't just show up magically, and lots and lots of folks out there
from corporate infrastructure groups to hosting providers have got lots
of experience with deciding what they'll allow to be installed on a
system and what they won't, what repositories of code they'll trust and
which they won't.

Of course, when it comes to contrib extensions, if we don't feel
comfortable with them and we don't want to spend the time to vet them,
we can certainly just leave them marked as 'untrusted' and tell anyone
who wants to make them trusted that they need to put in the effort to
review that extension and get everyone comfortable with it.

Thanks,

Stephen

  31. Re: Removing pg_pltemplate and creating "trustable" extensions

    Robert Haas <robertmhaas@gmail.com> — 2020-01-09T15:42:35Z

    On Thu, Jan 9, 2020 at 10:09 AM Stephen Frost <sfrost@snowman.net> wrote:
> [ wall of text ]

I don't see anything in here I really disagree with, but nor do I
understand why any of it means that giving superusers the ability to
customize which extensions are database-owner-installable would be a
bad thing.

> > I don't think changing what's in contrib helps much. Even if we rm
> > -rf'd it, there's the same problem with out-of-core extensions. Joe
> > Extensionman may think his extension ought to be trusted, and package
> > it as such, but Paula Skepticaldba is entitled to think Joe's view of
> > the security risks originating from his code is overly rosy.
>
> Out of core extensions have to get installed on to the system though,
> they don't just show up magically, and lots and lots of folks out there
> from corporate infrastructure groups to hosting providers have got lots
> of experience with deciding what they'll allow to be installed on a
> system and what they won't, what repositories of code they'll trust and
> which they won't.

You seem to be ignoring the actual point of that example, which is
that someone may want to install the extension but have a different
view than the packager about whether it should be trusted.

You seem to think that that hosting providers and system
administrators will be thrilled to accept the judgement of developers
about which extensions should be trusted in their environment. Great!
I'm not trying to take away their ability to accept the judgement of
developers on that question. However, I also think some people will
want more control.

Evidently you disagree, and that's fine, even if I don't understand
why. Given some of the development projects you've done in the past, I
find it extremely surprising to here you now taking the position that
fine-grained security controls are, in this case, unnecessary and
useless, but you don't have to like it everywhere just because you
like it for some things.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  32. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-09T16:30:14Z

    Greetings,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Thu, Jan 9, 2020 at 10:09 AM Stephen Frost <sfrost@snowman.net> wrote:
> > [ wall of text ]

This really isn't helpful.

> I don't see anything in here I really disagree with, but nor do I
> understand why any of it means that giving superusers the ability to
> customize which extensions are database-owner-installable would be a
> bad thing.

Alright, I think there's definitely something we need to sort through.

If you agree that the approach I advocated means less code for hosting
providers to have to change in their fork, and that they don't want to
give users superuser, and that they want non-superusers to be able to
install extensions, and that they really don't want to modify things
post-install, then I don't get why you're against the DB-level privilege
that I've been advocating for except that it's "not as customizable."

Are you saying that in order to have something here that we must make it
so that a superuser is able to specifiy, individually, which extensions
can be installed by which users?  You keep coming back to this point of
saying that you want this to be 'customizable' but I really don't see
any justification for the level of customization you're asking for- but
I see an awful lot of work involved.  When there's a lot of work
involved for a use-case that no one is actually asking for, I'm really
skeptical.  The use-cases that you've presented, at least thus far,
certainly haven't swayed me into thinking that you're right that there's
a justifiable use-case here for this level of complicated privileges.

I'm also not convinced that such a design would even be practical- we
don't know all of the extensions that a given PG install will be able to
have when it's first installed.  If postgis isn't on the filesystem when
someone installs PG, how do I, as a superuser, say that $user is allowed
to install postgis?  Or do we always have to have this two-step "install
on filesystem", "grant privs to $user to install" process?  What if that
extension is then uninstalled from the filesystem?  Do we have to clean
up the GRANT that was done?

> > > I don't think changing what's in contrib helps much. Even if we rm
> > > -rf'd it, there's the same problem with out-of-core extensions. Joe
> > > Extensionman may think his extension ought to be trusted, and package
> > > it as such, but Paula Skepticaldba is entitled to think Joe's view of
> > > the security risks originating from his code is overly rosy.
> >
> > Out of core extensions have to get installed on to the system though,
> > they don't just show up magically, and lots and lots of folks out there
> > from corporate infrastructure groups to hosting providers have got lots
> > of experience with deciding what they'll allow to be installed on a
> > system and what they won't, what repositories of code they'll trust and
> > which they won't.
> 
> You seem to be ignoring the actual point of that example, which is
> that someone may want to install the extension but have a different
> view than the packager about whether it should be trusted.

Why would someone want to install something that isn't trusted?  You're
implying that's what is happening here, but it doesn't make any sense to
me and without it making sense I can't agree that it's a sensible enough
use-case to demand a lot of work be put into it.

> You seem to think that that hosting providers and system
> administrators will be thrilled to accept the judgement of developers
> about which extensions should be trusted in their environment. Great!

Huh?  Hosting providers are the ones that choose what gets installed on
the filesystem, certainly not developers, so I am baffled how you came
to the conclusion that I'm suggesting administrators are trusting the
judgement of developers.  That's just not at all the case.

> Evidently you disagree, and that's fine, even if I don't understand
> why. Given some of the development projects you've done in the past, I
> find it extremely surprising to here you now taking the position that
> fine-grained security controls are, in this case, unnecessary and
> useless, but you don't have to like it everywhere just because you
> like it for some things.

I'm all for fine-grained control- where it makes sense.  I'm still
*very* much of the opinion that we should be able to let DB owners and
schema owners control what kind of objects users are allowed to create
in their DBs/schemas.  I want a "GRANT CREATE FUNCTION ON SCHEMA mine TO
you;" ability.  I'm not clamouring for a way to say "GRANT CREATE
THISSPECIFICFUNCTION ON SCHEMA mine TO you;" or something like "GRANT
CREATE FUNCTION MATCHING REGEXP 'abc_*' ON SCHEMA mine TO you;".

In the end, superusers are, in fact, the ones who grant out ALL access,
the question is what level of access they want to grant out and to whom.
When it comes to trusted objects, we've historically said that it's the
DB owner who gets to say who can grant out that access and all I'm
trying to argue for is that we continue with that.

Thanks,

Stephen

  33. Re: Removing pg_pltemplate and creating "trustable" extensions

    Robert Haas <robertmhaas@gmail.com> — 2020-01-09T17:55:04Z

    On Thu, Jan 9, 2020 at 11:30 AM Stephen Frost <sfrost@snowman.net> wrote:
> * Robert Haas (robertmhaas@gmail.com) wrote:
> > On Thu, Jan 9, 2020 at 10:09 AM Stephen Frost <sfrost@snowman.net> wrote:
> > > [ wall of text ]
>
> This really isn't helpful.

Sorry.

That being said, I'm pretty tired of writing emails that say the same
thing over and over again and having you write long responses that
don't seem to actually respond to the points being raised in the
email.

Like:

> > I don't see anything in here I really disagree with, but nor do I
> > understand why any of it means that giving superusers the ability to
> > customize which extensions are database-owner-installable would be a
> > bad thing.
>
> Alright, I think there's definitely something we need to sort through.
>
> If you agree that the approach I advocated means less code for hosting
> providers to have to change in their fork, and that they don't want to
> give users superuser, and that they want non-superusers to be able to
> install extensions, and that they really don't want to modify things
> post-install, then I don't get why you're against the DB-level privilege
> that I've been advocating for except that it's "not as customizable."

What I was writing about in the quoted paragraph and what you are
writing about in the response are two different things. I said
*nothing* about a DB-level privilege in the paragraph I wrote, and yet
somehow your response to that paragraph says that I'm opposing a
DB-level privilege.

> Are you saying that in order to have something here that we must make it
> so that a superuser is able to specifiy, individually, which extensions
> can be installed by which users?  You keep coming back to this point of
> saying that you want this to be 'customizable' but I really don't see
> any justification for the level of customization you're asking for- but
> I see an awful lot of work involved.  When there's a lot of work
> involved for a use-case that no one is actually asking for, I'm really
> skeptical.  The use-cases that you've presented, at least thus far,
> certainly haven't swayed me into thinking that you're right that there's
> a justifiable use-case here for this level of complicated privileges.

I set forth my exact views in
http://postgr.es/m/CA+TgmoZK=5EC2O13J3sfOUCvYtvjGtxUKg=wQ11Q-wy4sc4b+g@mail.gmail.com

Everything since then has been trying to somehow clarify what I wrote
in that email, which has resulted in me repeating everything I said
there several times in different words. I would like to stop doing
that now. It appears to be clarifying nothing, failing to advance the
patch, and irritating you.

> I'm also not convinced that such a design would even be practical- ...

Again, as I said upthread, Tom had the exact feature about which I am
talking in the first version of the patch. That is a strong argument
in favor of it being practical. It's also a pretty good argument that
it is at least potentially useful, because Tom doesn't usually do
useless things for no reason.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  34. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-09T18:35:07Z

    Robert Haas <robertmhaas@gmail.com> writes:
> Again, as I said upthread, Tom had the exact feature about which I am
> talking in the first version of the patch. That is a strong argument
> in favor of it being practical. It's also a pretty good argument that
> it is at least potentially useful, because Tom doesn't usually do
> useless things for no reason.

To try to clarify that a bit: I think there is certainly some value
in allowing superusers to control which extensions could be installed
by non-superusers, further restricting what we may think is trustworthy.

However, I felt at the time that my GUC-based implementation of that
was ugly, and then Peter raised some concrete points against it,
so I took it out.  I don't want to put it back in the same form.
I think we could leave designing a replacement for later, because it's
pretty optional, especially if we aren't aggressive about promoting
contrib modules to "trusted" status.  I don't agree that the lack of
such a feature is a reason not to commit what I've got.

In any case, AFAICT most of the heat-vs-light in this thread has not
been about which extensions are trustworthy, but about which users
should be allowed to install extensions, which seems like a totally
independent discussion.  And controlling that is also a feature that
we don't have today, so I'd rather get a minimal feature committed
for v13 and then later consider whether we need more functionality.

The idea of a DB-level INSTALL privilege addresses the second
point not the first, unless I'm totally misunderstanding it.  As
I said before, I'm not terribly comfortable with handing control
of that over to non-superuser DB owners, and I sure don't see why
doing so should be a required part of the minimal feature.

			regards, tom lane

  35. Re: Removing pg_pltemplate and creating "trustable" extensions

    Robert Haas <robertmhaas@gmail.com> — 2020-01-09T19:06:03Z

    On Thu, Jan 9, 2020 at 1:35 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > Again, as I said upthread, Tom had the exact feature about which I am
> > talking in the first version of the patch. That is a strong argument
> > in favor of it being practical. It's also a pretty good argument that
> > it is at least potentially useful, because Tom doesn't usually do
> > useless things for no reason.
>
> To try to clarify that a bit: I think there is certainly some value
> in allowing superusers to control which extensions could be installed
> by non-superusers, further restricting what we may think is trustworthy.

Cool.

> However, I felt at the time that my GUC-based implementation of that
> was ugly, and then Peter raised some concrete points against it,
> so I took it out.  I don't want to put it back in the same form.
> I think we could leave designing a replacement for later, because it's
> pretty optional, especially if we aren't aggressive about promoting
> contrib modules to "trusted" status.

Agreed.

> I don't agree that the lack of
> such a feature is a reason not to commit what I've got.

I said the same in
http://postgr.es/m/CA+TgmoYgwgS_RnMOooczZCgrZFqtfngshAq2Gu7LM5SKxrf_xQ@mail.gmail.com
- penultimate paragraph, last sentence.

> In any case, AFAICT most of the heat-vs-light in this thread has not
> been about which extensions are trustworthy, but about which users
> should be allowed to install extensions, which seems like a totally
> independent discussion.

I agree it's independent. It wasn't really the main point of what *I*
was trying to talk about, but the heat-vs-light problem seems to have
totally obscured what I *was* trying to talk about.

> And controlling that is also a feature that
> we don't have today, so I'd rather get a minimal feature committed
> for v13 and then later consider whether we need more functionality.
>
> The idea of a DB-level INSTALL privilege addresses the second
> point not the first, unless I'm totally misunderstanding it.  As
> I said before, I'm not terribly comfortable with handing control
> of that over to non-superuser DB owners, and I sure don't see why
> doing so should be a required part of the minimal feature.

So, if I understand correctly, the patch you are proposing to commit
has a new system role, and if you've got that system role, then you
can install extensions. I thought that part of the earlier debate was
whether DB owners should also be able to install trusted extensions
even without that role, and I thought it would be cleaner if the
answer was "no," because then the superuser could decide whether to
grant that role or not in particular cases. But I'm not clear whether
you agreed with that, what Stephen thought about it, or whether that's
still what you are proposing to commit.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  36. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-09T19:48:40Z

    Robert Haas <robertmhaas@gmail.com> writes:
> So, if I understand correctly, the patch you are proposing to commit
> has a new system role, and if you've got that system role, then you
> can install extensions.

Install *trusted* extensions, correct.  The patch as it stands also
allows DB owners to install trusted extensions.

> I thought that part of the earlier debate was
> whether DB owners should also be able to install trusted extensions
> even without that role, and I thought it would be cleaner if the
> answer was "no," because then the superuser could decide whether to
> grant that role or not in particular cases. But I'm not clear whether
> you agreed with that, what Stephen thought about it, or whether that's
> still what you are proposing to commit.

I agree that if we dropped the proviso about DB owners, it would be
a cleaner design.  I included that only for backwards compatibility
with the existing behavior that DB owners can install trusted PLs.
If we can agree that we're willing to lose that behavior, I'd be
perfectly fine with removing the special case for DB owners.
However, I'm unsure whether that compatibility cost is acceptable.
It's definitely likely that it would cause an upgrade headache
for some installations.

One idea for working around the upgrade problem would be to teach
pg_dumpall to automatically issue "GRANT pg_install_trusted_extension"
to each DB-owner role, when dumping from a pre-v13 database.  There's
room to object to that, because it would end with more privilege than
before (that is, an owner of some DB could now install extensions
even in DBs she doesn't own, as long as she can connect to them).
So maybe it's a bad idea.  But it would probably reduce the number
of complaints --- and I think a lot of installations would end up
making such grants anyway, because otherwise their DB owners can't
do things they expect to be able to do.

I should not put words into Stephen's mouth, but perhaps his
concern about having some DB-level privilege here is to alleviate
the problem that there's no exact equivalent to the old level of
privilege that DB ownership afforded, ie you can install in your
own DB but not others.  It's not clear to me whether that behavior
is critical to preserve.

			regards, tom lane

  37. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-09T20:00:28Z

    Greetings,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Thu, Jan 9, 2020 at 1:35 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Robert Haas <robertmhaas@gmail.com> writes:
> > > Again, as I said upthread, Tom had the exact feature about which I am
> > > talking in the first version of the patch. That is a strong argument
> > > in favor of it being practical. It's also a pretty good argument that
> > > it is at least potentially useful, because Tom doesn't usually do
> > > useless things for no reason.
> >
> > To try to clarify that a bit: I think there is certainly some value
> > in allowing superusers to control which extensions could be installed
> > by non-superusers, further restricting what we may think is trustworthy.
> 
> Cool.

I'm arguing for the position that superusers/admins have the ability to
control which extensions exist on the filesystem, and that plus the
'trusted' marking is sufficient flexibility.

> > However, I felt at the time that my GUC-based implementation of that
> > was ugly, and then Peter raised some concrete points against it,
> > so I took it out.  I don't want to put it back in the same form.
> > I think we could leave designing a replacement for later, because it's
> > pretty optional, especially if we aren't aggressive about promoting
> > contrib modules to "trusted" status.
> 
> Agreed.

Also agreed- which is why I figured we weren't really discussing that
any more.

> > I don't agree that the lack of
> > such a feature is a reason not to commit what I've got.
> 
> I said the same in
> http://postgr.es/m/CA+TgmoYgwgS_RnMOooczZCgrZFqtfngshAq2Gu7LM5SKxrf_xQ@mail.gmail.com
> - penultimate paragraph, last sentence.

I also agree that we don't need the "who can install what extension"
flexibility that the original GUC-based approach contemplated, but
that's because I don't think we are likely to ever need it.  If we do
and someone comes up with a good design for it, that'd be fine too.

> > In any case, AFAICT most of the heat-vs-light in this thread has not
> > been about which extensions are trustworthy, but about which users
> > should be allowed to install extensions, which seems like a totally
> > independent discussion.
> 
> I agree it's independent. It wasn't really the main point of what *I*
> was trying to talk about, but the heat-vs-light problem seems to have
> totally obscured what I *was* trying to talk about.

I'm entirely confused about what you were trying to talk about then.

Most of the back-and-forth, as I saw it anyway, were points being raised
to say "we can't let the right of installing extensions be allowed to DB
owners", which I don't agree with and which I've yet to see an actual
justification for beyond "well, we think it should require some explicit
superuser privilege-granting, beyond the granting that the superuser
does when they create a database owned by a given user."

> > And controlling that is also a feature that
> > we don't have today, so I'd rather get a minimal feature committed
> > for v13 and then later consider whether we need more functionality.
> >
> > The idea of a DB-level INSTALL privilege addresses the second
> > point not the first, unless I'm totally misunderstanding it.  As
> > I said before, I'm not terribly comfortable with handing control
> > of that over to non-superuser DB owners, and I sure don't see why
> > doing so should be a required part of the minimal feature.
> 
> So, if I understand correctly, the patch you are proposing to commit
> has a new system role, and if you've got that system role, then you
> can install extensions. I thought that part of the earlier debate was
> whether DB owners should also be able to install trusted extensions
> even without that role, and I thought it would be cleaner if the
> answer was "no," because then the superuser could decide whether to
> grant that role or not in particular cases. But I'm not clear whether
> you agreed with that, what Stephen thought about it, or whether that's
> still what you are proposing to commit.

I do *not* agree with having a default role for this, at all.  This
looks just like the right to CREATE tables or functions inside a schema,
except with a DB-level object (an extension) instead of a schema-level
object, and that is the purview of the DB owner.

The arguments raised about $SCARYEXTENSION and security concerns make a
lot of sense- I agree that those are things we should discuss and make
sure that allowing a DB owner this privilege won't pave a way for them
to get superuser access, but, imv anyway, we discussed those and didn't
actually come up with any cases where it'd be an issue, in part thanks
to Tom's design where the objects end up owned by the bootstrap
superuser except in specific cases.

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?".

Thanks,

Stephen

  38. Re: Removing pg_pltemplate and creating "trustable" extensions

    Robert Haas <robertmhaas@gmail.com> — 2020-01-09T20:06:26Z

    On Thu, Jan 9, 2020 at 2:48 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I agree that if we dropped the proviso about DB owners, it would be
> a cleaner design.  I included that only for backwards compatibility
> with the existing behavior that DB owners can install trusted PLs.
> If we can agree that we're willing to lose that behavior, I'd be
> perfectly fine with removing the special case for DB owners.
> However, I'm unsure whether that compatibility cost is acceptable.
> It's definitely likely that it would cause an upgrade headache
> for some installations.

I was assuming that installing extensions was fairly infrequent and
that it probably gets done mostly by superusers anyway, so probably
most people won't care if, after upgrading, they needed an extra GRANT
to get things working again. That might be wrong, though.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  39. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-09T20:18:19Z

    Greetings,

On Thu, Jan 9, 2020 at 14:48 Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Robert Haas <robertmhaas@gmail.com> writes:
> > So, if I understand correctly, the patch you are proposing to commit
> > has a new system role, and if you've got that system role, then you
> > can install extensions.
>
> Install *trusted* extensions, correct.  The patch as it stands also
> allows DB owners to install trusted extensions.
>
> > I thought that part of the earlier debate was
> > whether DB owners should also be able to install trusted extensions
> > even without that role, and I thought it would be cleaner if the
> > answer was "no," because then the superuser could decide whether to
> > grant that role or not in particular cases. But I'm not clear whether
> > you agreed with that, what Stephen thought about it, or whether that's
> > still what you are proposing to commit.
>
> I agree that if we dropped the proviso about DB owners, it would be
> a cleaner design.  I included that only for backwards compatibility
> with the existing behavior that DB owners can install trusted PLs.
> If we can agree that we're willing to lose that behavior, I'd be
> perfectly fine with removing the special case for DB owners.
> However, I'm unsure whether that compatibility cost is acceptable.
> It's definitely likely that it would cause an upgrade headache
> for some installations.
>
> One idea for working around the upgrade problem would be to teach
> pg_dumpall to automatically issue "GRANT pg_install_trusted_extension"
> to each DB-owner role, when dumping from a pre-v13 database.  There's
> room to object to that, because it would end with more privilege than
> before (that is, an owner of some DB could now install extensions
> even in DBs she doesn't own, as long as she can connect to them).
> So maybe it's a bad idea.  But it would probably reduce the number
> of complaints --- and I think a lot of installations would end up
> making such grants anyway, because otherwise their DB owners can't
> do things they expect to be able to do.
>
> I should not put words into Stephen's mouth, but perhaps his
> concern about having some DB-level privilege here is to alleviate
> the problem that there's no exact equivalent to the old level of
> privilege that DB ownership afforded, ie you can install in your
> own DB but not others.  It's not clear to me whether that behavior
> is critical to preserve.


I am not particularly concerned about that backwards compatibility issue
and I don’t intend to base my argument on it, but I would use that case to
point out that we have long had the ability to install trusted C functions
into the backend as a DB owner, without complaint from either users or
security pedants, and that backs up my position that we are setting up this
privilege at the wrong level by using a default role which a superuser must
grant independently from DB ownership.

Thanks,

Stephen

>

  40. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-09T20:18:30Z

    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.
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.

* 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.

			regards, tom lane

  41. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-09T20:33:41Z

    Stephen Frost <sfrost@snowman.net> writes:
> I am not particularly concerned about that backwards compatibility issue
> and I don’t intend to base my argument on it, but I would use that case to
> point out that we have long had the ability to install trusted C functions
> into the backend as a DB owner, without complaint from either users or
> security pedants,

Right, which is why my patch proposes generalizing that feature for
trusted PLs into a general feature for other extensions.  I'd be
much leerier of that if we'd had any pushback on it for trusted PLs.

> ... and that backs up my position that we are setting up this
> privilege at the wrong level by using a default role which a superuser must
> grant independently from DB ownership.

Don't see how this follows.  It's somewhat accidental I think that
the existing behavior is tied to DB ownership.  That's just because
at the time, that's the only sort of privilege we had that seemed
intermediate between superuser and Joe User.  If we were designing
the behavior today, with default roles already a done deal for
handing out possibly-dangerous privileges, I think there's no
question that we'd be setting up this privilege as a default role
rather than tying it to DB ownership.  We don't make DB ownership
a prerequisite to creating other sorts of functions, yet other
functions can be just as dangerous in some cases as C functions.

			regards, tom lane

  42. Re: Removing pg_pltemplate and creating "trustable" extensions

    Robert Haas <robertmhaas@gmail.com> — 2020-01-09T20:35:23Z

    On Thu, Jan 9, 2020 at 3:18 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> * 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.

An emphatic +1 from me. This is what I've been trying to argue over
and over, apparently rather unclearly.

> * 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.

I agree with that, too. I guess you could decide that the answer to
the question "who can install extensions?" must be the same as the
answer to the question "who owns a database?" but having the
flexibility to make the answers to those questions different seems
better than forcing them to always be the same.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  43. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-10T19:18:59Z

    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

  44. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-10T19:26:46Z

    Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> > ... and that backs up my position that we are setting up this
> > privilege at the wrong level by using a default role which a superuser must
> > grant independently from DB ownership.
> 
> Don't see how this follows.  It's somewhat accidental I think that
> the existing behavior is tied to DB ownership.  That's just because
> at the time, that's the only sort of privilege we had that seemed
> intermediate between superuser and Joe User.  If we were designing
> the behavior today, with default roles already a done deal for
> handing out possibly-dangerous privileges, I think there's no
> question that we'd be setting up this privilege as a default role
> rather than tying it to DB ownership.  We don't make DB ownership
> a prerequisite to creating other sorts of functions, yet other
> functions can be just as dangerous in some cases as C functions.

I suppose I'll just have to say that I disagree.  I see a lot of value
in having a level between superuser and Joe User, and DB owner looks
pretty natural as exactly that, particularly for creating database-level
objects like extensions.

If anything, I tend to think we need more levels, not less- like a level
that's "cluster owner" or something along those lines, that's also
independent from "superuser" but would allow creating of cluster-level
objects like databases and roles (with the right to then GRANT the
ability to create those objects to other roles, if they wish).

I don't really see default roles as a better alternative to a privilege
hierarchy, but rather as a way for controlling access to things that
don't really fall into the hierarchy.  Maybe for cluster-level things
like what I hint at above they'd be better, but for database-level
objects, where you might decide you want to give a user access to create
something in database X but not in database Y?  Doesn't seem to fit very
well to me.

Thanks,

Stephen

  45. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-10T19:39:58Z

    Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> Don't see how this follows.  It's somewhat accidental I think that
>> the existing behavior is tied to DB ownership.  That's just because
>> at the time, that's the only sort of privilege we had that seemed
>> intermediate between superuser and Joe User.  If we were designing
>> the behavior today, with default roles already a done deal for
>> handing out possibly-dangerous privileges, I think there's no
>> question that we'd be setting up this privilege as a default role
>> rather than tying it to DB ownership.  We don't make DB ownership
>> a prerequisite to creating other sorts of functions, yet other
>> functions can be just as dangerous in some cases as C functions.

> I suppose I'll just have to say that I disagree.  I see a lot of value
> in having a level between superuser and Joe User, and DB owner looks
> pretty natural as exactly that, particularly for creating database-level
> objects like extensions.

Well, the other direction we could go here, which I guess is what
you are arguing for, is to forget the new default role and just
say that marking an extension trusted allows it to be installed by
DB owners, full stop.  That's nice and simple and creates no
backwards-compatibility issues.  If we later decide that we want
a default role, or any other rules about who-can-install, we might
feel like this was a mistake --- but the backwards-compatibility issues
we'd incur by changing it later are exactly the same as what we'd have
today if we do something different from this.  The only difference
is that there'd be more extensions affected later (assuming we mark
more things trusted).

I'm willing to go with this solution if it'll end the argument.
Robert, Peter, what do you think?

			regards, tom lane

  46. Re: Removing pg_pltemplate and creating "trustable" extensions

    Robert Haas <robertmhaas@gmail.com> — 2020-01-10T20:23:48Z

    On Fri, Jan 10, 2020 at 2:40 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Well, the other direction we could go here, which I guess is what
> you are arguing for, is to forget the new default role and just
> say that marking an extension trusted allows it to be installed by
> DB owners, full stop.  That's nice and simple and creates no
> backwards-compatibility issues.  If we later decide that we want
> a default role, or any other rules about who-can-install, we might
> feel like this was a mistake --- but the backwards-compatibility issues
> we'd incur by changing it later are exactly the same as what we'd have
> today if we do something different from this.  The only difference
> is that there'd be more extensions affected later (assuming we mark
> more things trusted).

I agree with your analysis, but I'm still inclined to feel that the
new pre-defined roll is a win.

Generally, decoupled permissions are better. Being able to grant
someone either A or B or both or neither is usually superior to having
to grant either both permissions or neither.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  47. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-10T20:50:03Z

    Greetings,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Fri, Jan 10, 2020 at 2:40 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Well, the other direction we could go here, which I guess is what
> > you are arguing for, is to forget the new default role and just
> > say that marking an extension trusted allows it to be installed by
> > DB owners, full stop.  That's nice and simple and creates no
> > backwards-compatibility issues.  If we later decide that we want
> > a default role, or any other rules about who-can-install, we might
> > feel like this was a mistake --- but the backwards-compatibility issues
> > we'd incur by changing it later are exactly the same as what we'd have
> > today if we do something different from this.  The only difference
> > is that there'd be more extensions affected later (assuming we mark
> > more things trusted).
> 
> I agree with your analysis, but I'm still inclined to feel that the
> new pre-defined roll is a win.
> 
> Generally, decoupled permissions are better. Being able to grant
> someone either A or B or both or neither is usually superior to having
> to grant either both permissions or neither.

Right- I like the idea of decoupled permissions too.

To be clear, I was advocating for a NEW DB-level privilege ('INSTALL' or
'CREATE EXTENSION' if we could make that work), so that we have it be
distinct from CREATE (which, today, really means 'CREATE SCHEMA').

I'd be willing to accept making this part of DB-level 'CREATE' rights if
there is a huge amount of push-back about burning a privilege bit for
it, but, as discussed up-thread, I don't think we should really be
stressing ourselves about that.

I do like the idea of having it be decoupled from explicit DB ownership,
so that a DB owner (or superuser) could say "I want this role to be able
to install extensions, but NOT run ALTER DATABASE", and optionally even
include ADMIN so that it could be further delegated (and also because
then it'd be just like the rest of our GRANT privilege system, and I
like that..).

Thanks,

Stephen

  48. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-10T22:53:10Z

    Stephen Frost <sfrost@snowman.net> writes:
> To be clear, I was advocating for a NEW DB-level privilege ('INSTALL' or
> 'CREATE EXTENSION' if we could make that work), so that we have it be
> distinct from CREATE (which, today, really means 'CREATE SCHEMA').

I still say this is wrong, or at least pointless, because it'd be a
right that any DB owner could grant to himself.  If we're to have any
meaningful access control on extension installation, the privilege
would have to be attached to some other object ... and there's no clear
candidate for what.  As someone noted awhile back, if we could somehow
attach ACLs to potentially-installable extensions, that might be an
interesting avenue to pursue.  That's well beyond what I'm willing
to pursue for v13, though.

In the meantime, though, this idea as stated doesn't do anything except
let a DB owner grant install privileges to someone else.  I'm not even
convinced that we want that, or that anyone needs it (I can recall zero
such requests related to PLs in the past).  And for sure it does not
belong in a minimal implementation of this feature.

			regards, tom lane

  49. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-13T18:40:19Z

    Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > To be clear, I was advocating for a NEW DB-level privilege ('INSTALL' or
> > 'CREATE EXTENSION' if we could make that work), so that we have it be
> > distinct from CREATE (which, today, really means 'CREATE SCHEMA').
> 
> I still say this is wrong, or at least pointless, because it'd be a
> right that any DB owner could grant to himself.

Yes, of course it is, that the DB owner would have this privilege was
something you agreed to in the prior email- I'd rather not just have a
"if (DBOwner())" check, I'd rather use our actual privilege system and
have this be a right that the DB owner has but can then GRANT out to
others if they wish to.

I'm certainly not suggesting that such a privilege wouldn't be
controlled by the DB owner.  Forcing it to only be allowed for the DB
owner and not be something that the DB owner can GRANT out isn't much
better than "if (superuser())"-style checks.

> If we're to have any
> meaningful access control on extension installation, the privilege
> would have to be attached to some other object ... and there's no clear
> candidate for what.

Extensions are installed at the DB level, not at any other level, and
therefore that's the appropriate place to attach them, which is exactly
what I'm suggesting we do here.

> As someone noted awhile back, if we could somehow
> attach ACLs to potentially-installable extensions, that might be an
> interesting avenue to pursue.  That's well beyond what I'm willing
> to pursue for v13, though.

Sure, having some catalog of installable extensions where someone (in my
thinking, the DB owner) could GRANT out access to install certain
extensions to others might be interesting, but it's not what I'm
suggesting here.

> In the meantime, though, this idea as stated doesn't do anything except
> let a DB owner grant install privileges to someone else.  I'm not even
> convinced that we want that, or that anyone needs it (I can recall zero
> such requests related to PLs in the past).  And for sure it does not
> belong in a minimal implementation of this feature.

Yes, that's what this approach would do.  I suppose an alternative would
be to lump it in with "CREATE" rights on the DB, but I've advocated and
will continue to advocate for splitting up of such broad rights.
DB-level CREATE rights currently cover both schemas and publications,
for example, even though the two have rather little to do with each
other.

If the only agreeable option is a if (DBOwner())-type check, or lumping
the privilege to CREATE (trusted) EXTENSION in with other DB-level
CREATE rights, then I'll go along with one of those.  I'll be happy
enough with that, since it avoids having an additional default role that
has to be GRANT'd by a superuser.  Ideally, down the road, we'll split
out the CREATE privilege (both at DB and at schema level) to be more
fine grained, but that can certainly be done later.

Thanks,

Stephen

  50. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-13T20:38:59Z

    Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> In the meantime, though, this idea as stated doesn't do anything except
>> let a DB owner grant install privileges to someone else.  I'm not even
>> convinced that we want that, or that anyone needs it (I can recall zero
>> such requests related to PLs in the past).  And for sure it does not
>> belong in a minimal implementation of this feature.

> Yes, that's what this approach would do.  I suppose an alternative would
> be to lump it in with "CREATE" rights on the DB, but I've advocated and
> will continue to advocate for splitting up of such broad rights.
> DB-level CREATE rights currently cover both schemas and publications,
> for example, even though the two have rather little to do with each
> other.

The patch as I'm proposing it has nothing to do with "CREATE" rights.
You're attacking something different from what I actually want to do.

			regards, tom lane

  51. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-13T20:57:57Z

    Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> >> In the meantime, though, this idea as stated doesn't do anything except
> >> let a DB owner grant install privileges to someone else.  I'm not even
> >> convinced that we want that, or that anyone needs it (I can recall zero
> >> such requests related to PLs in the past).  And for sure it does not
> >> belong in a minimal implementation of this feature.
> 
> > Yes, that's what this approach would do.  I suppose an alternative would
> > be to lump it in with "CREATE" rights on the DB, but I've advocated and
> > will continue to advocate for splitting up of such broad rights.
> > DB-level CREATE rights currently cover both schemas and publications,
> > for example, even though the two have rather little to do with each
> > other.
> 
> The patch as I'm proposing it has nothing to do with "CREATE" rights.
> You're attacking something different from what I actually want to do.

Yes, as an aside, I'm argueing that we should split up the general
CREATE privileges, but I also said that's not required for this.

You're asking "what's the best way to add this privilege to PG?".  I'm
saying that it should be done through the privilege system, similar to
publications.  I'd prefer it not be lumped into CREATE, but that at
least makes sense to me- adding a default role for this doesn't.  I
suppose making it akin to ALTER DATABASE and having it be limited to the
DB owner is also alright (as I said in my last email) but it means that
someone has to be given DB ownership rights in order to install
extensions.  I don't really see CREATE EXTENSION as being like ALTER
DATABASE from a privilege perspective, but having it be DB owner still
makes more sense than a default role for this.

Thanks,

Stephen

  52. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-21T17:40:12Z

    Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> The patch as I'm proposing it has nothing to do with "CREATE" rights.
>> You're attacking something different from what I actually want to do.

> Yes, as an aside, I'm argueing that we should split up the general
> CREATE privileges, but I also said that's not required for this.

So how do we move this forward?  I really don't want this patch to be
blocked by what's fundamentally a side point about permissions.

The minimum committable patch seems like it would just grant the
"can install trusted extensions" ability to DB owners, full stop.
This is small, it's exactly the same as our historical behavior for
trusted PLs, and it's upward compatible with either of two possible
future extensions:

* adding a predefined role (which'd let superusers give out the install
privilege, in addition to DB owners having it)

* converting DB owners' hard-wired privilege to a grantable privilege
(which'd let DB owners give out the install privilege, if the privilege
is attached to the DBs themselves; but maybe there's some other way?)

Given the lack of consensus about either of those being what we want,
it doesn't seem like we're going to come to an agreement in a
reasonable timeframe on a patch that includes either.  So I'd like
to get this done and move on to the next problem (ie, what is it
we're actually going to do about the python 2/3 mess).

			regards, tom lane

  53. Re: Removing pg_pltemplate and creating "trustable" extensions

    Robert Haas <robertmhaas@gmail.com> — 2020-01-21T21:55:20Z

    On Tue, Jan 21, 2020 at 12:40 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Given the lack of consensus about either of those being what we want,
> it doesn't seem like we're going to come to an agreement in a
> reasonable timeframe on a patch that includes either.  So I'd like
> to get this done and move on to the next problem (ie, what is it
> we're actually going to do about the python 2/3 mess).

I'm fine with that.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  54. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-28T20:29:18Z

    Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> >> The patch as I'm proposing it has nothing to do with "CREATE" rights.
> >> You're attacking something different from what I actually want to do.
> 
> > Yes, as an aside, I'm argueing that we should split up the general
> > CREATE privileges, but I also said that's not required for this.
> 
> So how do we move this forward?  I really don't want this patch to be
> blocked by what's fundamentally a side point about permissions.
> 
> The minimum committable patch seems like it would just grant the
> "can install trusted extensions" ability to DB owners, full stop.

If you're alright with making it something a DB owner can do, what is
the issue with making it part of the CREATE right on the database?  That
doesn't require any additional permission bits, in a default setup
doesn't change who is able to create extensions (in either your proposal
or mine, it's the DB owner), and in either proposal means that people
who couldn't create extensions with PG12 will be able to create them in
PG13.

We've added other things to the DB-level CREATE rights rather recently
too, so it's not like we've historically avoided that.

The argument you presented previously against that idea was because it
would mean the DB owner would still be able to exercise that right,
which is what you're now proposing anyway and which I was always
advocating for and wasn't trying to say wouldn't be the case with that
approach.

So I'm at a loss for what the actual argument is against making it part
of DB-level CREATE.

> This is small, it's exactly the same as our historical behavior for
> trusted PLs, and it's upward compatible with either of two possible
> future extensions:
> 
> * adding a predefined role (which'd let superusers give out the install
> privilege, in addition to DB owners having it)

Uh, just to be clear, even with your approach, a DB owner could 'GRANT'
the necessary right for another user to install extensions by simply
GRANT'ing their own role to that user.  Obviously, that conveys other
privileges with it, but we have that problem at any level as long as we
constrain ourselves to a single set of 32 bits for representing
privileges.  I see it as being manifestly better to lump it in with the
DB-level CREATE privilege though.

> * converting DB owners' hard-wired privilege to a grantable privilege
> (which'd let DB owners give out the install privilege, if the privilege
> is attached to the DBs themselves; but maybe there's some other way?)

In either of these proposals, we could split up the bounded-together
privileges down the road, and, sure, there might be more than one way to
do that, but I really don't want to go down a road where every privilege
ends up being split up into a seperate default-role (or predefined role
or whatever we want to call those things today).

> Given the lack of consensus about either of those being what we want,
> it doesn't seem like we're going to come to an agreement in a
> reasonable timeframe on a patch that includes either.  So I'd like
> to get this done and move on to the next problem (ie, what is it
> we're actually going to do about the python 2/3 mess).

I get that you want to push forward with making this part of the DB
owner, and I said up-thread that I'd be able to live with that, but I
still don't understand what the argument is against making it part of
CREATE instead.

Thanks,

Stephen

  55. Re: Removing pg_pltemplate and creating "trustable" extensions

    Robert Haas <robertmhaas@gmail.com> — 2020-01-28T20:45:33Z

    On Tue, Jan 28, 2020 at 3:29 PM Stephen Frost <sfrost@snowman.net> wrote:
> I get that you want to push forward with making this part of the DB
> owner, and I said up-thread that I'd be able to live with that, but I
> still don't understand what the argument is against making it part of
> CREATE instead.

It's a change from the status quo. If we're going to how it works, we
should try to agree on how it ought to work. Tom's proposal dodges
that by leaving things exactly as they are, deferring any actual
modifications to who can do what to a future patch.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  56. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-28T20:52:36Z

    Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> The minimum committable patch seems like it would just grant the
>> "can install trusted extensions" ability to DB owners, full stop.

> If you're alright with making it something a DB owner can do, what is
> the issue with making it part of the CREATE right on the database?

Um, well, people were complaining that it should be a distinct privilege,
which I for one wasn't sold on.

I continue to think that allowing DB owners to decide this is, if not
fundamentally the wrong thing, at least not a feature that anybody has
asked for in the past.  The feature *I* want in this area is for the
superuser to be able to decide who's got install privilege.  Making
it a DB-level privilege doesn't serve that goal, more the opposite.

Still, if we can compromise by making this part of DB "CREATE" privilege
for the time being, I'm willing to take that compromise.  It's certainly
better than failing to get rid of pg_pltemplate.

			regards, tom lane

  57. Re: Removing pg_pltemplate and creating "trustable" extensions

    Robert Haas <robertmhaas@gmail.com> — 2020-01-28T20:58:28Z

    On Tue, Jan 28, 2020 at 3:52 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I continue to think that allowing DB owners to decide this is, if not
> fundamentally the wrong thing, at least not a feature that anybody has
> asked for in the past.  The feature *I* want in this area is for the
> superuser to be able to decide who's got install privilege.  Making
> it a DB-level privilege doesn't serve that goal, more the opposite.

I agree.

> Still, if we can compromise by making this part of DB "CREATE" privilege
> for the time being, I'm willing to take that compromise.  It's certainly
> better than failing to get rid of pg_pltemplate.

Doesn't that have exactly the issue you describe above?

bob=> grant create on database bob to fred;
GRANT

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  58. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-28T21:16:56Z

    Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Jan 28, 2020 at 3:52 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I continue to think that allowing DB owners to decide this is, if not
>> fundamentally the wrong thing, at least not a feature that anybody has
>> asked for in the past.  The feature *I* want in this area is for the
>> superuser to be able to decide who's got install privilege.  Making
>> it a DB-level privilege doesn't serve that goal, more the opposite.

> I agree.

>> Still, if we can compromise by making this part of DB "CREATE" privilege
>> for the time being, I'm willing to take that compromise.  It's certainly
>> better than failing to get rid of pg_pltemplate.

> Doesn't that have exactly the issue you describe above?
> bob=> grant create on database bob to fred;
> GRANT

Either of them do, in that a DB owner can always grant his whole role;
"grant bob to fred" will give fred install privileges (in bob's DBs)
regardless of which of these choices we adopt.  And that was true before
(with respect to trusted PLs), too.  Attaching the ability to the CREATE
bit would at least allow DB owners to be a bit more selective about how
they give it out.

The reason I'm happier about doing this with CREATE than inventing
a separate INSTALL bit is that once we do the latter, we're more or
less bound to keep supporting that ability forever.  If we extend
the definition of CREATE in v13, and then narrow it again in some
future release, that seems less likely to cause problems than taking
away a named privilege bit would do.

On the other hand, there's the point that lots of people have probably
given out schema-CREATE privilege to users whom they wouldn't necessarily
wish to trust with INSTALL privilege.  Schema-CREATE is a pretty harmless
privilege, INSTALL much less so.

I do like your point about how maybe we shouldn't change the status quo
without more consensus than we've got ... but in the end I just want
to get this done and move on.

			regards, tom lane

  59. Re: Removing pg_pltemplate and creating "trustable" extensions

    Stephen Frost <sfrost@snowman.net> — 2020-01-28T21:58:32Z

    Greetings,

On Tue, Jan 28, 2020 at 16:17 Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Robert Haas <robertmhaas@gmail.com> writes:
> > On Tue, Jan 28, 2020 at 3:52 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> I continue to think that allowing DB owners to decide this is, if not
> >> fundamentally the wrong thing, at least not a feature that anybody has
> >> asked for in the past.  The feature *I* want in this area is for the
> >> superuser to be able to decide who's got install privilege.  Making
> >> it a DB-level privilege doesn't serve that goal, more the opposite.
>
> > I agree.
>
> >> Still, if we can compromise by making this part of DB "CREATE" privilege
> >> for the time being, I'm willing to take that compromise.  It's certainly
> >> better than failing to get rid of pg_pltemplate.
>
> > Doesn't that have exactly the issue you describe above?
> > bob=> grant create on database bob to fred;
> > GRANT
>
> Either of them do, in that a DB owner can always grant his whole role;
> "grant bob to fred" will give fred install privileges (in bob's DBs)
> regardless of which of these choices we adopt.  And that was true before
> (with respect to trusted PLs), too.  Attaching the ability to the CREATE
> bit would at least allow DB owners to be a bit more selective about how
> they give it out.


Right.

The reason I'm happier about doing this with CREATE than inventing
> a separate INSTALL bit is that once we do the latter, we're more or
> less bound to keep supporting that ability forever.  If we extend
> the definition of CREATE in v13, and then narrow it again in some
> future release, that seems less likely to cause problems than taking
> away a named privilege bit would do.


I would like to segregate these privileges more than just “install” vs
“other stuff” in the future anyway, so mixing it with the existing CREATE
isn’t that big of a deal in my view.

On the other hand, there's the point that lots of people have probably
> given out schema-CREATE privilege to users whom they wouldn't necessarily
> wish to trust with INSTALL privilege.  Schema-CREATE is a pretty harmless
> privilege, INSTALL much less so.


CREATE doesn’t just control the ability to create schemas these days- it
was extended to cover publications also not that long ago.  We never said
we wouldn’t extend CREATE to cover more objects and we’ve already extended
it recently, without anyone being up in arms about it that I can recall, so
this doesn’t feel like a huge issue or concern to me.  Note that, again,
these are trusted extensions which means, in their regular install, they
shouldn’t be able to “break outside the box” any more than a plpgsql
function is able to.

Thanks,

Stephen

  60. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-28T22:35:00Z

    Stephen Frost <sfrost@snowman.net> writes:
> On Tue, Jan 28, 2020 at 16:17 Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> On the other hand, there's the point that lots of people have probably
>> given out schema-CREATE privilege to users whom they wouldn't necessarily
>> wish to trust with INSTALL privilege.  Schema-CREATE is a pretty harmless
>> privilege, INSTALL much less so.

> CREATE doesn’t just control the ability to create schemas these days- it
> was extended to cover publications also not that long ago.

Oh really ... hm, that does make it a much bigger deal than I was
thinking.  Given that, I don't think there's any huge objection to
attaching this to CREATE, at least till we get around to a more
significant redesign.

			regards, tom lane

  61. Re: Removing pg_pltemplate and creating "trustable" extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-29T18:19:25Z

    I wrote:
> Stephen Frost <sfrost@snowman.net> writes:
>> On Tue, Jan 28, 2020 at 16:17 Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> On the other hand, there's the point that lots of people have probably
>>> given out schema-CREATE privilege to users whom they wouldn't necessarily
>>> wish to trust with INSTALL privilege.  Schema-CREATE is a pretty harmless
>>> privilege, INSTALL much less so.

>> CREATE doesn't just control the ability to create schemas these days- it
>> was extended to cover publications also not that long ago.

> Oh really ... hm, that does make it a much bigger deal than I was
> thinking.  Given that, I don't think there's any huge objection to
> attaching this to CREATE, at least till we get around to a more
> significant redesign.

Here's a v5 that drops the new predefined role and allows
trusted-extension installation when you have CREATE on the current
database.  There's no other changes except a bit of documentation
wordsmithing.

Barring further complaints, I'm going to push this fairly soon.

			regards, tom lane