Re: Removing pg_pltemplate and creating "trustable" extensions
I wrote: > Stephen Frost writes: >> On Tue, Jan 28, 2020 at 16:17 Tom Lane 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 diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 85ac79f..a10b665 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -8519,7 +8519,15 @@ SCRAM-SHA-256$iteration count: superuser bool - True if only superusers are allowed to install this extension + True if only superusers are allowed to install this extension + (but see trusted) + + + + trusted + bool + True if the extension can be installed by non-superusers + with appropriate privileges diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 3546e39..8d3a0d1 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1742,6 +1742,7 @@ REVOKE ALL ON accounts FROM PUBLIC; For databases, allows new schemas and publications to be created within + the database, and allows trusted extensions to be installed within the database. @@ -1753,8 +1754,11 @@ REVOKE ALL ON accounts FROM PUBLIC; For tablespaces, allows tables, indexes, and temporary files to be created within the tablespace, and allows databases to be created that - have the tablespace as their default tablespace. (Note that revoking - this privilege will not alter the placement of existing objects.) + have the tablespace as their default tablespace. + + + Note that revoking this privilege will not alter the existence or + location of existing objects. diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml index a3046f2..ffe068b 100644 --- a/doc/src/sgml/extend.sgml +++ b/doc/src/sgml/extend.sgml @@ -576,6 +576,31 @@ version. If it is set to false, just the privileges required to execute the commands in the installation or update script are required. +This should normally be set to true if any of the +script commands require superuser privileges. (Such commands would +fail anyway, but it's more user-friendly to give the error up front.) + + + + + + trusted (boolean) + + +This parameter, if set to true (which is not the +default), allows some non-superusers to install an extension that +has superuser set to true. +Specifically, installation will be permitted for anyone who has +CREATE privilege on the current database. +When the user executing CREATE EXTENSION is not +a superuser but is allowed to install by virtue of this parameter, +then the installation or update script is run as the bootstrap +superuser, not as the calling user. +This parameter is irrelevant if superuser is +false. +Generally, this should not be set true for extensions that could +allow access to otherwise-superuser-only abilities, such as +filesystem access. @@ -642,6 +667,18 @@ + If the extension script contains the + string @extowner@, that string is replaced with the + (suitably quoted) name of the user calling CREATE + EXTENSION or ALTER EXTENSION. Typically + this feature is used by extensions that are marked trusted to assign + ownership of selected objects to the calling user rather than the + bootstrap superuser. (One should be careful about doing so, however. + For example, assigning ownership of a C-language function to a + non-superuser would create a privilege escalation path for that user.) + + + While the script files can contain any characters allowed by the specified encoding, control files should contain only plain ASCII, because there is no way for PostgreSQL to know what encoding a diff --git a/doc/src/sgml/ref/create_extension.sgml b/doc/src/sgml/ref/create_extension.sgml index 36837f9..d76ac3e
Re: Removing pg_pltemplate and creating "trustable" extensions
Stephen Frost writes: > On Tue, Jan 28, 2020 at 16:17 Tom Lane 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings, On Tue, Jan 28, 2020 at 16:17 Tom Lane wrote: > Robert Haas writes: > > On Tue, Jan 28, 2020 at 3:52 PM Tom Lane 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Robert Haas writes: > On Tue, Jan 28, 2020 at 3:52 PM Tom Lane 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
Re: Removing pg_pltemplate and creating "trustable" extensions
On Tue, Jan 28, 2020 at 3:52 PM Tom Lane 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Stephen Frost writes: > * Tom Lane (t...@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
Re: Removing pg_pltemplate and creating "trustable" extensions
On Tue, Jan 28, 2020 at 3:29 PM Stephen Frost 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > * Tom Lane (t...@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 signature.asc Description: PGP signature
Re: Removing pg_pltemplate and creating "trustable" extensions
On Tue, Jan 21, 2020 at 12:40 PM Tom Lane 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Stephen Frost writes: > * Tom Lane (t...@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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > * Tom Lane (t...@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 signature.asc Description: PGP signature
Re: Removing pg_pltemplate and creating "trustable" extensions
Stephen Frost writes: > * Tom Lane (t...@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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings,
* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Stephen Frost 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
signature.asc
Description: PGP signature
Re: Removing pg_pltemplate and creating "trustable" extensions
Stephen Frost 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings,
* Robert Haas (robertmh...@gmail.com) wrote:
> On Fri, Jan 10, 2020 at 2:40 PM Tom Lane 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
signature.asc
Description: PGP signature
Re: Removing pg_pltemplate and creating "trustable" extensions
On Fri, Jan 10, 2020 at 2:40 PM Tom Lane 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Stephen Frost writes: > * Tom Lane (t...@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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings, * Tom Lane (t...@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 signature.asc Description: PGP signature
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings,
* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Stephen Frost 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
Re: Removing pg_pltemplate and creating "trustable" extensions
On Thu, Jan 9, 2020 at 3:18 PM Tom Lane 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Stephen Frost 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings, On Thu, Jan 9, 2020 at 14:48 Tom Lane wrote: > Robert Haas 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 >
Re: Removing pg_pltemplate and creating "trustable" extensions
Stephen Frost 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
Re: Removing pg_pltemplate and creating "trustable" extensions
On Thu, Jan 9, 2020 at 2:48 PM Tom Lane 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings, * Robert Haas (robertmh...@gmail.com) wrote: > On Thu, Jan 9, 2020 at 1:35 PM Tom Lane wrote: > > Robert Haas 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...@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 signature.asc Description: PGP signature
Re: Removing pg_pltemplate and creating "trustable" extensions
Robert Haas 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
Re: Removing pg_pltemplate and creating "trustable" extensions
On Thu, Jan 9, 2020 at 1:35 PM Tom Lane wrote: > Robert Haas 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...@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
Re: Removing pg_pltemplate and creating "trustable" extensions
Robert Haas 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
Re: Removing pg_pltemplate and creating "trustable" extensions
On Thu, Jan 9, 2020 at 11:30 AM Stephen Frost wrote: > * Robert Haas (robertmh...@gmail.com) wrote: > > On Thu, Jan 9, 2020 at 10:09 AM Stephen Frost 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-wy4sc4...@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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings, * Robert Haas (robertmh...@gmail.com) wrote: > On Thu, Jan 9, 2020 at 10:09 AM Stephen Frost 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings, * Robert Haas (robertmh...@gmail.com) wrote: > On Wed, Jan 8, 2020 at 6:09 PM Stephen Frost 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
Re: Removing pg_pltemplate and creating "trustable" extensions
On Wed, Jan 8, 2020 at 6:09 PM Stephen Frost 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings, * Robert Haas (robertmh...@gmail.com) wrote: > On Tue, Jan 7, 2020 at 7:32 PM Stephen Frost 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 signature.asc Description: PGP signature
Re: Removing pg_pltemplate and creating "trustable" extensions
On Tue, Jan 7, 2020 at 7:32 PM Stephen Frost 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings, * Robert Haas (robertmh...@gmail.com) wrote: > On Tue, Jan 7, 2020 at 4:36 PM Stephen Frost 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
Re: Removing pg_pltemplate and creating "trustable" extensions
On Tue, Jan 7, 2020 at 4:36 PM Stephen Frost 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings! * Robert Haas (robertmh...@gmail.com) wrote: > On Tue, Jan 7, 2020 at 1:17 PM Stephen Frost 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 signature.asc Description: PGP signature
Re: Removing pg_pltemplate and creating "trustable" extensions
On Tue, Jan 7, 2020 at 1:17 PM Stephen Frost 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings, * Robert Haas (robertmh...@gmail.com) wrote: > On Mon, Jan 6, 2020 at 6:56 PM Stephen Frost 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 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
Re: Removing pg_pltemplate and creating "trustable" extensions
On Mon, Jan 6, 2020 at 6:56 PM Stephen Frost 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 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Stephen Frost 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Robert Haas writes: > > On Mon, Jan 6, 2020 at 1:27 PM Tom Lane 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Robert Haas writes: > On Mon, Jan 6, 2020 at 1:27 PM Tom Lane 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
Re: Removing pg_pltemplate and creating "trustable" extensions
On Mon, Jan 6, 2020 at 1:27 PM Tom Lane 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Robert Haas writes: > > On Thu, Nov 7, 2019 at 2:13 PM Stephen Frost 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 signature.asc Description: PGP signature
Re: Removing pg_pltemplate and creating "trustable" extensions
Robert Haas writes: > On Thu, Nov 7, 2019 at 2:13 PM Stephen Frost 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
Re: Removing pg_pltemplate and creating "trustable" extensions
On Thu, Nov 7, 2019 at 2:13 PM Stephen Frost 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
Re: Removing pg_pltemplate and creating "trustable" extensions
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 diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 55694c4..a573dfb 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -8514,7 +8514,15 @@ SCRAM-SHA-256$iteration count: superuser bool - True if only superusers are allowed to install this extension + True if only superusers are allowed to install this extension + (but see trusted) + + + + trusted + bool + True if the extension can be installed by non-superusers + with appropriate privileges diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml index a3046f2..e2807d0 100644 --- a/doc/src/sgml/extend.sgml +++ b/doc/src/sgml/extend.sgml @@ -576,6 +576,32 @@ version. If it is set to false, just the privileges required to execute the commands in the installation or update script are required. +This should normally be set to true if any of the +script commands require superuser privileges. (Such commands would +fail anyway, but it's more user-friendly to give the error up front.) + + + + + + trusted (boolean) + + +This parameter, if set to true (which is not the +default), allows some non-superusers to install an extension that +has superuser set to true. +Specifically, installation will be permitted for the owner of the +current database, and for anyone who has been granted +the pg_install_trusted_extension role. +When the user executing CREATE EXTENSION is not +a superuser but is allowed to install by virtue of this parameter, +then the installation or update script is run as the bootstrap +superuser, not as the calling user. +This parameter is irrelevant if superuser is +false. +Generally, this should not be set true for extensions that could +allow access to otherwise-superuser-only abilities, such as +filesystem access. @@ -642,6 +668,18 @@ + If the extension script contains the + string @extowner@, that string is replaced with the + (suitably quoted) name of the user calling CREATE + EXTENSION or ALTER EXTENSION. Typically + this feature is used by extensions that are marked trusted to assign + ownership of selected objects to the calling user rather than the + bootstrap superuser. (One should be careful about doing so, however. + For example, assigning ownership of a C-language function to a + non-superuser would create a privilege escalation path for that user.) + + + While the script files can contain any characters allowed by the specified encoding, control files should contain only plain ASCII, because there is no way for PostgreSQL to know what encoding a diff --git a/doc/src/sgml/ref/create_extension.sgml b/doc/src/sgml/ref/create_extension.sgml index 36837f9..7cd4346 100644 --- a/doc/src/sgml/ref/create_extension.sgml +++ b/doc/src/sgml/ref/create_extension.sgml @@ -47,14 +47,26 @@ CREATE EXTENSION [ IF NOT EXISTS ] extension_name - Loading an extension requires the same privileges that would be - required to create its component objects. For most extensions this - means superuser or database owner privileges are needed. The user who runs CREATE EXTENSION becomes the owner of the extension for purposes of later privilege checks, as well as the owner of any objects created by the extension's script. + + Loading an extension ordinarily requires the same privileges that would + be required to create its component objects. For many extensions this + means superuser privileges are needed. + However, if the extension is marked trusted in + its control file, then it can be installed by a non-superuser who has + suitable privileges (that is, owns the current database or has been + granted the pg_install_trusted_extension role). In + this case the extension object itself will be owned by the calling user, + but the contained objects will be owned by the bootstrap superuser + (unless the extension's script explicitly assigns them to the calling + user). This configuration gives the calling user the right to drop the + extension, but not to modify individual objects within it. + + diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index 66f1627..90f637f 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@
Re: Removing pg_pltemplate and creating "trustable" extensions
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 diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 55694c4..a573dfb 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -8514,7 +8514,15 @@ SCRAM-SHA-256$iteration count: superuser bool - True if only superusers are allowed to install this extension + True if only superusers are allowed to install this extension + (but see trusted) + + + + trusted + bool + True if the extension can be installed by non-superusers + with appropriate privileges diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml index a3046f2..e2807d0 100644 --- a/doc/src/sgml/extend.sgml +++ b/doc/src/sgml/extend.sgml @@ -576,6 +576,32 @@ version. If it is set to false, just the privileges required to execute the commands in the installation or update script are required. +This should normally be set to true if any of the +script commands require superuser privileges. (Such commands would +fail anyway, but it's more user-friendly to give the error up front.) + + + + + + trusted (boolean) + + +This parameter, if set to true (which is not the +default), allows some non-superusers to install an extension that +has superuser set to true. +Specifically, installation will be permitted for the owner of the +current database, and for anyone who has been granted +the pg_install_trusted_extension role. +When the user executing CREATE EXTENSION is not +a superuser but is allowed to install by virtue of this parameter, +then the installation or update script is run as the bootstrap +superuser, not as the calling user. +This parameter is irrelevant if superuser is +false. +Generally, this should not be set true for extensions that could +allow access to otherwise-superuser-only abilities, such as +filesystem access. @@ -642,6 +668,18 @@ + If the extension script contains the + string @extowner@, that string is replaced with the + (suitably quoted) name of the user calling CREATE + EXTENSION or ALTER EXTENSION. Typically + this feature is used by extensions that are marked trusted to assign + ownership of selected objects to the calling user rather than the + bootstrap superuser. (One should be careful about doing so, however. + For example, assigning ownership of a C-language function to a + non-superuser would create a privilege escalation path for that user.) + + + While the script files can contain any characters allowed by the specified encoding, control files should contain only plain ASCII, because there is no way for PostgreSQL to know what encoding a diff --git a/doc/src/sgml/ref/create_extension.sgml b/doc/src/sgml/ref/create_extension.sgml index 36837f9..7cd4346 100644 --- a/doc/src/sgml/ref/create_extension.sgml +++ b/doc/src/sgml/ref/create_extension.sgml @@ -47,14 +47,26 @@ CREATE EXTENSION [ IF NOT EXISTS ] extension_name - Loading an extension requires the same privileges that would be - required to create its component objects. For most extensions this - means superuser or database owner privileges are needed. The user who runs CREATE EXTENSION becomes the owner of the extension for purposes of later privilege checks, as well as the owner of any objects created by the extension's script. + + Loading an extension ordinarily requires the same privileges that would + be required to create its component objects. For many extensions this + means superuser privileges are needed. + However, if the extension is marked trusted in + its control file, then it can be installed by a non-superuser who has + suitable privileges (that is, owns the current database or has been + granted the pg_install_trusted_extension role). In + this case the extension object itself will be owned by the calling user, + but the contained objects will be owned by the bootstrap superuser + (unless the extension's script explicitly assigns them to the calling + user). This configuration gives the calling user the right to drop the + extension, but not to modify individual objects within it. + + diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index 66f1627..90f637f 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@ -556,6 +556,10 @@ DROP ROLE doomed_role; Allow executing programs on the database server as the user
Re: Removing pg_pltemplate and creating "trustable" extensions
I wrote: > Stephen Frost 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 diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 55694c4..a573dfb 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -8514,7 +8514,15 @@ SCRAM-SHA-256$iteration count: superuser bool - True if only superusers are allowed to install this extension + True if only superusers are allowed to install this extension + (but see trusted) + + + + trusted + bool + True if the extension can be installed by non-superusers + with appropriate privileges diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml index a3046f2..e2807d0 100644 --- a/doc/src/sgml/extend.sgml +++ b/doc/src/sgml/extend.sgml @@ -576,6 +576,32 @@ version. If it is set to false, just the privileges required to execute the commands in the installation or update script are required. +This should normally be set to true if any of the +script commands require superuser privileges. (Such commands would +fail anyway, but it's more user-friendly to give the error up front.) + + + + + + trusted (boolean) + + +This parameter, if set to true (which is not the +default), allows some non-superusers to install an extension that +has superuser set to true. +Specifically, installation will be permitted for the owner of the +current database, and for anyone who has been granted +the pg_install_trusted_extension role. +When the user executing CREATE EXTENSION is not +a superuser but is allowed to install by virtue of this parameter, +then the installation or update script is run as the bootstrap +superuser, not as the calling user. +This parameter is irrelevant if superuser is +false. +Generally, this should not be set true for extensions that could +allow access to otherwise-superuser-only abilities, such as +filesystem access. @@ -642,6 +668,18 @@
Re: Removing pg_pltemplate and creating "trustable" extensions
Chapman Flack 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> Stephen Frost 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
Re: Removing pg_pltemplate and creating "trustable" extensions
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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > >> Peter Eisentraut 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Stephen Frost writes: >> Peter Eisentraut 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Peter Eisentraut 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
Re: Removing pg_pltemplate and creating "trustable" extensions
Peter Eisentraut 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
Re: 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
Removing pg_pltemplate and creating "trustable" extensions
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.
