Re: [PATCHES] Users/Groups -> Roles

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> We probably need to think a bit harder about the meaning of CREATEROLE
>> though. Right now it gives free license not only to create roles but
>> to alter any property of existing roles. This seems appropriate if you
>> think of it as a "safer form of superuser", which is how I was thinking
>> of it. It would be too powerful for Fabien's situation though.

> Well, what about it makes it 'too powerful'?

The fact that you could alter roles that (in some sense) don't belong to
you. In particular you could allow yourself into other people's
databases, if you can alter the roles that they are using to define who
can connect to those databases. Or cause denials of service by revoking
other people's memberships in those roles.

On the other hand, CREATEROLE as-is does exactly what it was intended
to do, namely let the DBA do all normal admin maintenance of users/groups
without taking the risks involved in doing stuff as superuser. If we
restrict it more, then we'll be back to the situation where there are
routine manual admin tasks that require superuserdom. So on reflection
I don't think we should restrict it. If we need a more restrictive
feature, then we need a different feature.

I'm of the opinion that it's too late to do much about Fabien's use-case
in this devel cycle. We could possibly have designed something rational
if it had been brought up earlier in the discussion of roles (which I
remind you has been going on for months). But now it's too late to do
anything that wouldn't be a kluge, and probably a kluge we'd regret
later.

Possibly for 8.2 we could invent a notion of roles having owners.
Offhand I don't see any harm in letting non-CREATEROLE users create
non-login roles, and manipulate the membership of roles they have
created (or that have been assigned to them by a superuser). On the
other hand, it could be that the WITH ADMIN OPTION feature is already
sufficient for this. This really needs some thought ...

regards, tom lane