Removing pg_pltemplate and creating "trustable" extensions

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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