Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost <sfr...@snowman.net> writes: > > So I'm at a loss for why there is this insistence on a default role and > > a superuser-explicit-granting based approach that goes beyond "is it > > installed on the filesystem?" and "is it marked as trusted?". > > Okay, so it seems like we're down to just this one point of contention.
I agree that this seems to be the crux of the contention- though I need to revisit what I wrote above because I didn't cover everything relevant. I'm suggesting that having: - Extension installed on the filesystem - Extension is marked as trusted - Calling user has been made a DB owner (which, from a bare initdb, requires a superuser to take action to make happen) is what would be needed to install a trusted extension. > You feel that the superuser can control what is in the extension library > directory and that that ought to be sufficient control. I disagree > with that, for two reasons: > > * ISTM that that's assuming that the DBA and the sysadmin are the same > person (or at least hold identical views on this subject). In many > installations it'd only be root who has control over what's in that > directory, and I don't think it's unreasonable for the DBA to wish > to be able to exercise additional filtering. I don't think we should start conflating roles by saying things like "DBA" because it's not clear if that's "PG superuser" or "DB owner" or "DB user". In many, many, many installations the DBA is *not* the superuser- in fact, pretty much every cloud-provider installation of PG is that way. Even so though, I don't agree with this particular rationale as, at least largely in my experience, the sysadmin isn't going to go installing things on their own- they're going to install what they've been asked to, and it'll be a PG superuser or DB owner or DB user doing the asking (and hopefully they'll consult with whomever is appropriate before installing anything anyway). The idea that additional filtering on that is needed strikes me as highly unlikely. Now, that said, I'm not strictly against the idea of allowing a superuser, if they wish, to do additional filtering of what's allowed (though I think there's a fair bit of complication in coming up with a sensible way for them to do so, but that's an independent discussion). I still don't think that the privilege to install a trusted extension should be done through a default role though, or that it needs to be independent from the DB owner role. > * The point of a default role would be for the DBA to be able to > control which database users can install extensions. Even if the > DBA has full authority over the extension library, that would not > provide control over who can install, only over what is available > for any of them to install. In my approach, a superuser absolutely still has control over which database users can install extensions, by virtue of being in control over which users are DB owners, and further by being able to GRANT out the right to install extensions, in specific databases, to specific users. If we want to have a mechanism where superusers can further whitelist or blacklist extensions across the cluster, that's fine, but, again, that's largely orthogonal to what I'm talking about. Also, consider this- with the default role approach, is a user granted that role allowed to create extensions in *every* database they can connect to, or do they need some additional privilege, like CREATE rights on the database, which is then under the purview of the database owner? What if the superuser wishes to allow a given role the ability to install extensions only in a specific database? That strikes me as highly likely use-case ("you can install extensions in this other database, but not in the 'postgres' database that I use for my monitoring and other stuff") that isn't addressed at all with this default role approach (looking at the patch, it seems to switch to the superuser role to actually create objects, of course, so the caller doesn't need create rights in the database), but is with mine- and done so in a natural, intuitive, way that works just like the rest of our privilege system. I've always viewed the privilege system in PG to be a hierarchchy of privileges- superuser - ultimate owner, able to do everything DB owner - controls create/use/modify for objects in the database, and altering of the database itself schema owner- controls create/use/modify for objects in a schema, and altering of the schema itself table owner - controls access/use/modify for the table, and altering of table itself Now we're moving outside of that to start using default roles to control who can create objects in a database, and that's what I don't agree with. If the superuser doesn't feel that a particular role should be able to create extensions in a given database, there is a simple and well understood solution- don't have that role own that database. None of what I've been talking about has ever taken away anything from the superuser or made it such that the superuser wouldn't have any say in who can install extensions. I'll accept that if a superuser wished for a given role to be able to run ALTER DATABASE but not install extensions then that would present an issue in this model, but that feels like a really tenuous argument and isn't one that anyone here has actually been making, instead it seems to be a bunch of hand-wringing about how this might be giving DB owners too much power, when they're already the ones who control who is allowed to create objects in the database, or not. Thanks, Stephen
signature.asc
Description: PGP signature