Re: CREATE ROLE IF NOT EXISTS - Mailing list pgsql-hackers

From Mark Dilger
Subject Re: CREATE ROLE IF NOT EXISTS
Date
Msg-id D4E40F96-723C-4547-BE5C-AFAAFA365447@enterprisedb.com
Whole thread Raw
In response to Re: CREATE ROLE IF NOT EXISTS  (Stephen Frost <sfrost@snowman.net>)
Responses Re: CREATE ROLE IF NOT EXISTS
List pgsql-hackers

> 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 is
farfrom clear that "super_alice" intended that.  Even if "bob" is not malicious, having this happen by accident is
prettybad. 

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. 

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






pgsql-hackers by date:

Previous
From: Stephen Frost
Date:
Subject: Re: Predefined role pg_maintenance for VACUUM, ANALYZE, CHECKPOINT.
Next
From: Stephen Frost
Date:
Subject: Re: CREATE ROLE IF NOT EXISTS