Re: fixing CREATEROLE

On Tue, Nov 29, 2022 at 2:32 AM <walther(at)technowledgy(dot)de> wrote:
> 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.

I think that is more powerful than what I proposed but less fit for
purpose. If alice is a CREATEROLE user and issues CREATE ROLE bob, my
proposal allows alice to automatically obtain access to bob's
privileges. Your proposal would allow that, but it would also allow
alice to automatically confer bob's privileges on some third user, say
charlie. Maybe that's useful to somebody, I don't know.

But one significant disadvantage of this is that every CREATEROLE user
must have their own configuration. If we have CREATE ROLE users alice,
dave, and ellen, then allice needs to execute ALTER DEFAULT PRIVILEGES
GRANT CREATED ROLE TO alice WITH ...; dave needs to do the same thing
with dave instead of alice; and ellen needs to do the same thing with
ellen instead of alice. There's no way to apply a system-wide
configuration that applies nicely to all CREATEROLE users.

A GUC would of course allow that, because it could be set in
postgresql.conf and then overridden for particular databases, users,
or sessions.

David claims that "these aren't privileges, they are memberships." I
don't entirely agree with that, because I think that we're basically
using memberships as a pseudonym for privileges where roles are
concerned. However, it is true that there's no precedent for referring
to role grants using the keyword PRIVILEGES at the SQL level, and the
fact that the underlying works in somewhat similar ways doesn't
necessarily mean that it's OK to conflate the two concepts at the SQL
level.

So I'm still not very sold on this idea.

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