Re: fixing CREATEROLE

Robert Haas <robertmhaas@gmail.com> writes:
> Consider:

> robert.haas=# create role alice;
> CREATE ROLE

> There is now a role called 'alice' and you have all of alice's
> privileges. But now consider:

> robert.haas=# create role admin createrole;
> CREATE ROLE
> robert.haas=# set role admin;
> SET
> robert.haas=> create role alice;
> CREATE ROLE

> There is now a role called 'alice' but you do not have alice's
> privileges unless you subsequently run "GRANT alice to admin". One
> problem, as I say, is that this is confusing and the admin user isn't
> likely to understand what they need to do. But maybe the bigger
> question is: how do you justify this being randomly different?

To be blunt, I don't buy this argument in the least.  When you say
that "the superuser has all of alice's privileges", that is wrong:
no such grant is recorded in the system, nor does the code consult
alice's privileges to decide what the superuser can do.  Reality is
that the superuser can do whatever she wants regardless of alice's
privileges, because she bypasses all privilege checks.  Moreover,
there's no way for either the superuser or alice to revoke that
(short of the superuser giving up superuser-ness).

So I think that the idea that admin should implicitly get a grant of
alice's privileges is a misreading of what happens for superusers.
A closer approximation perhaps would be a role property that says
"you automatically have the privileges of any role you have created".
I'm not sure how this would interact with the INHERIT property of
roles and role grants, but there's probably something to think about
there.

In any case, I'd be happier about createrole_self_grant if it had
been a role property bit instead of a GUC.  But we'd still need
to worry about whether it corrupts the results of dump/restore
(offhand I think it still would, if it results in GRANTs that
weren't there before).

			regards, tom lane