Re: CREATE ROLE IF NOT EXISTS

Greetings,

* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
> > On Nov 9, 2021, at 8:22 AM, Stephen Frost <sfrost@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
isfar from clear that "super_alice" intended that.  Even if "bob" is not malicious, having this happen by accident is
prettybad. 

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