Re: CREATE ROLE IF NOT EXISTS

> On Nov 9, 2021, at 8:50 AM, Stephen Frost <sfrost@snowman.net> wrote:
>
>> 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.

Just FYI, I have a patch underway to fix it.  I'm not super close to posting it, though.

> 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'..?

I mean that the dangling reference could point at a role that no longer exists, but if the oid gets recycled, it could
pointat the *wrong* role rather than merely at no role.  So we'd need that fixed before we could rely on the "grantor"
fieldfor anything.  I think Robert mentioned this issue already, on another thread. 

>  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?

Suppose user "stephen.frost" owns a database and runs a script which creates roles, schemas, etc:

  CREATE OR REPLACE ROLE super_alice SUPERUSER;
  SET SESSION AUTHORIZATION super_alice;
  CREATE OR REPLACE ROLE charlie;
  CREATE OR REPLACE ROLE david IN ROLE charlie;

User "stephen.frost" runs that script again.  The system cannot tell, as things currently are implemented, that
"stephen.frost"was the original creator of role "super_alice", nor that "super_alice" was the original creator of
"charlie"and "david".  

The "grantor" field for "david"'s membership in "charlie" points at "super_alice", so we know enough to allow the "IN
ROLEcharlie" part, at least if we fix the dangling reference bug. 

If we add an "owner" (or perhaps a "creator") field to pg_authid, the first time the script runs, it could be set to
"stephen.frost"for "super_alice" and to "super_alice" for "charlie" and "david".  When the script gets re-run, the
CREATEOR REPLACE commands can succeed because that field matches. 

>  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.

I agree that super_alice might know that, but perhaps we can make this feature less of a foot-gun and still achieve the
goalof making idempotent role creation scripts work? 

> 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.

Agreed.  You shouldn't be able to CREATE OR REPLACE a role that you couldn't CREATE in the first case.

>  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.

This sounds reasonable.  It means, of course, implementing a role ownership system.  I thought you had other concerns
aboutdoing so. 

> 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.

I'm not sure what to do about this.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company