Re: fixing CREATEROLE

On Mon, Nov 28, 2022 at 1:56 PM <walther(at)technowledgy(dot)de> wrote:
> And now this reason is gone - there is no reason NOT to implement it as
> DEFAULT PRIVILEGES.

I think there is, and it's this, which you wrote further down:

> In my proposal, the "object" is not the GRANT of that role. It's the
> role itself. So the default privileges express what should happen when
> the role is created. The default privileges would NOT affect a regular
> GRANT role TO role_spec command. They only run that command when a role
> is created.

I agree that this is what you are proposing, but it is not what your
proposed syntax says. Your proposed syntax simply says ALTER DEFAULT
PRIVILEGES .. GRANT. Users who read that are going to think it
controls the default behavior for all grants, because that's what the
syntax says. If the proposed syntax mentioned CREATE ROLE someplace,
maybe that would have some potential. A proposal to make a command
that controls CREATE ROLE and only CREATE ROLE and mentions neither
CREATE nor ROLE anywhere in the syntax is never going to be
acceptable.

> 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.

It's no. Well, OK, you can do it, but it doesn't revoke anything,
because you can only revoke your own grant, not the bootstrap
superuser's grant.

> attributes vs. default privileges. 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?

It's not difficult in the sense that climbing Mount Everest is
difficult, but it makes the user experience as a CREATEROLE
non-superuser quite noticeably different from being a superuser.
Having a way to paper over such differences is, in my opinion, an
important usability feature.

--
Robert Haas
EDB: http://www.enterprisedb.com