Horton

Re: fixing CREATEROLE

Wolfgang Walther <walther@technowledgy.de>

View thread View on postgr.es
From: walther@technowledgy.de
To: Robert Haas <robertmhaas@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: Mark Dilger <mark.dilger@enterprisedb.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2022-11-24T07:41:46Z
Lists: pgsql-hackers 
Robert Haas:
> I have to admit that when I realized that was the natural place to put
> them to make the patch work, my first reaction internally was "well,
> that can't possibly be right, role properties suck!". But I didn't and
> still don't see where else to put them that makes any sense at all, so
> I eventually decided that my initial reaction was misguided. So I
> can't really blame you for not liking it either, and would be happy if
> we could come up with something else that feels better. I just don't
> know what it is: at least as of this moment in time, I believe these
> naturally ARE properties of the role [...]
> 
> That might be the wrong view. As I say, I'm open to other ideas, and
> it's possible there's some nicer way to do it that I just don't see
> right now.

INHERITCREATEDROLES and SETCREATEDROLES behave much like DEFAULT 
PRIVILEGES. What about something like:

ALTER DEFAULT PRIVILEGES FOR alice
GRANT TO alice WITH INHERIT FALSE, SET TRUE, ADMIN TRUE

The "abbreviated grant" is very much abbreviated, because the original 
syntax GRANT a TO b is already quite short to begin with, i.e. there is 
no ON ROLE or something like that in it.

The initial DEFAULT privilege would be INHERIT FALSE, SET FALSE, ADMIN 
TRUE, I guess?

Best,

Wolfgang

Commits

  1. Add new GUC createrole_self_grant.

  2. Restrict the privileges of CREATEROLE users.

  3. Pass down current user ID to AddRoleMems and DelRoleMems.

  4. Refactor permissions-checking for role grants.

  5. Improve documentation of the CREATEROLE attibute.

  6. Make role grant system more consistent with other privileges.