Re: fixing CREATEROLE

On Mon, Nov 28, 2022 at 11:57 AM <walther(at)technowledgy(dot)de> wrote:

> Robert Haas:
> > I don't know if changing the syntax from A to B is really getting us
> > anywhere. I generally agree that the ALTER DEFAULT PRIVILEGES syntax
> > looks nicer than the CREATE/ALTER ROLE syntax, but I'm not sure that's
> > a sufficient reason to move the control over this behavior to ALTER
> > DEFAULT PRIVILEGES.
>
> Your patch is introducing a new category of role attributes - those that
> are affecting default behavior. But there is already a way to express
> this right now, and that's ALTER DEFAULT PRIVILEGES in this case.

I do not like ALTER DEFAULT PRIVILEGES (ADP) for this. I don't really like
defaults, period, for this.

The role doing the creation and the role being created are both in scope
when the command is executed and if anything it is the role doing to the
creation that is receiving the privileges not the role being created. For
ADP, the role being created gets the privileges and it is objects not in
the scope of the executed command that are being affected.

> > One thing to consider is that, as I've designed
> > this, whether or not ADMIN is included in the grant is non-negotiable.
> > I am, at least at present, inclined to think that was the right call,
> > partly because Mark Dilger expressed a lot of concern about the
> > CREATEROLE user losing control over the role they'd just created, and
> > allowing ADMIN to be turned off would have exactly that effect. Plus a
> > grant with INHERIT FALSE, SET FALSE, ADMIN FALSE would end up being
> > almost identical to no great at all, which seems pointless. Basically,
> > without ADMIN, the implicit GRANT fails to accomplish its intended
> > purpose, so I don't like having that as a possibility.
>
> With how you implemented it right now, is it possible to do the following?
>
> CREATE ROLE alice;
> REVOKE ADMIN OPTION FOR alice FROM CURRENT_USER;
>
> If the answer is yes, then there is no reason to allow a user to set a
> shortcut for SET and INHERIT, but not for ADMIN.
>
> If the answer is no, then you could just not allow specifying the ADMIN
> option in the ALTER DEFAULT PRIVILEGES statement and always force it to
> be TRUE.
>

A prior email described that the creation of a role by a CREATEROLE role
results in the necessary creation of an ADMIN grant from the creator to the
new role granted by the bootstrap superuser (or, possibly, whichever
superuser granted CREATEROLE). That REVOKE will not work as there would be
no existing "grant by current_user over alice granted by current_user"
immediately after current_user creates alice.

Or we could just decide not to,
> because is it really that hard to just issue a GRANT statement
> immediately after CREATE ROLE, when you want to have SET or INHERIT
> options on that role?
>
> The answer to that question was "yes it is too hard" a while back and as
> such DEFAULT PRIVILEGES were introduced.
>
>
A quick tally of the thread so far:

No Defaults needed: David J., Mark?, Tom?
Defaults needed - attached to role directly: Robert
Defaults needed - defined within Default Privileges: Walther?
The capability itself seems orthogonal to the rest of the patch to track
these details better. I think we can "Fix CREATEROLE" without any feature
regarding optional default behaviors and would suggest this patch be so
limited and that another thread be started for discussion of (assuming a
default specifying mechanism is wanted overall) how it should look. Let's
not let a usability debate distract us from fixing a real problem.

David J.