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>, "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Mark Dilger <mark.dilger@enterprisedb.com>, Tom Lane <tgl@sss.pgh.pa.us>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2022-11-29T07:32:19Z
Lists: pgsql-hackers 
Robert Haas:
>> 1) Automatically install an additional membership grant, with the CREATEROLE user as the grantor, specifying INHERIT OR SET as TRUE (I personally favor attaching these to ALTER ROLE, modifiable only by oneself)
> 
> Hmm, that's an interesting alternative to what I actually implemented.
> Some people might like it better, because it puts the behavior fully
> under the control of the CREATEROLE user, which a number of you seem
> to favor.

+1

> I suppose if we did it that way, it could even be a GUC, like
> create_role_automatic_grant_options.

I don't think using GUCs for that is any better. ALTER DEFAULT 
PRIVILEGES is the correct way to do it. The only argument against it 
was, so far, that it's easy to confuse with default options for newly 
created role grants, due to the abbreviated grant syntax.

I propose a slightly different syntax instead:

ALTER DEFAULT PRIVILEGES GRANT CREATED ROLE TO role_specification WITH ...;

This, together with the proposal above regarding the grantor, should be 
consistent.

Is there any other argument to be made against ADP?

Note, that ADP allows much more than just creating a grant for the 
CREATEROLE user, which would be the case if the default GRANT was made 
TO the_create_role_user. But it could be made towards *other* users as 
well, so you could do something like this:

CREATE ROLE alice CREATEROLE;
CREATE ROLE bob;

ALTER DEFAULT PRIVILEGES FOR alice GRANT CREATED ROLE TO bob WITH SET 
TRUE, INHERIT FALSE;

This is much more flexible than role attributes or GUCs.

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.