Greetings, * Mark Dilger (mark.dil...@enterprisedb.com) wrote: > > On Nov 9, 2021, at 8:22 AM, Stephen Frost <sfr...@snowman.net> wrote: > > In terms of least-surprise, I do tend to think that the answer is "only > > care about what is explicitly put into the command"- that is, if it > > isn't in the CREATE ROLE statement then it gets left as-is. Not sure > > how others feel about that though. > > bob: CREATE ROLE charlie; > bob: GRANT charlie TO david; > > super_alice: CREATE OR REPLACE ROLE charlie SUPERUSER; > > I think this is the sort of thing Tom and I are worried about. "david" is > now a member of a superuser role, and it is far from clear that "super_alice" > intended that. Even if "bob" is not malicious, having this happen by > accident is pretty bad.
I understand the concern that you and Tom have raised, I just don't see it as such an issue that we can't give users this option. They're already doing it via DO blocks and that's surely not any better. Documenting that you should care about who is able to create roles in your system when thinking about this is certainly reasonable, but just saying we won't add it because someone might somewhere mis-use it isn't. > If we fix the existing bug that the pg_auth_members.grantor field can end up > as a dangling reference, instead making sure that it is always accurate, then > perhaps this would be ok if all roles granted into "charlie" had > grantor="super_alice". I'm not sure that is really good enough, but it is a > lot closer to making this safe than allowing the command to succeed when role > "charlie" has been granted away by someone else. I agree we should fix the issue of the grantor field being a dangling reference, that's clearly not a good thing. I'm not sure what is meant by making sure they're always 'accurate' or why 'accurate' in this case means that the grantor is always 'super_alice'..? Are you suggesting that the CREATE OR REPLACE ROLE run by super_alice would remove the GRANT that bob made of granting charlie to david? I would argue that it's entirely possible that super_alice knows exactly what is going on and intends for charlie to have superuser access and understands that any role which charlie has been GRANT'd to would therefore be able to become charlie, that's not a surprise. Now, bringing this around to the more general discussion about making it possible for folks who aren't superuser to be able to create roles, I think there's another way to address this that might satisfy everyone, particularly with the CREATE OR REPLACE approach- to wit: if the role create isn't one that you've got appropriate rights on, then you shouldn't be able to CREATE OR REPLACE it. This, perhaps, gets to a distinction between having ADMIN rights on a role vs. the ability to redefine the role (perhaps by virtue of being the 'owner' of that role) that's useful. In other words, in the case outlined above: bob: CREATE ROLE charlie; -- charlie is now a role 'owned' by bob and that isn't able to be -- changed by bob to be some other owner, unless bob can become the -- role to which they want to change ownership to bob: GRANT charlie TO david; alice: CREATE OR REPLACE ROLE charlie; -- This now fails because while alice is able to create roles, alice -- can only 'replace' roles which alice owns. I appreciate that the case of 'super_alice' doing things where they're an actual superuser might still be an issue, but running around doing things with superuser is already risky business and the point here is to get away from doing that by splitting superuser up, ideally in a way that privileges can be given out to non-superusers in a manner that's safer than doing things as a superuser and where independent non-superusers aren't able to do bad things to each other. Thanks, Stephen
signature.asc
Description: PGP signature
