Re: CREATE USER and pg_user

On Mon, Aug 22, 2005 at 10:42:15PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
> > My original point was that if you don't have permission to do something,
> > you shouldn't be able to grant permissions to do it. This applies to all
<snip>
> I'm not convinced.  It seems reasonable to say that you can't grant
> CREATEDB if you don't have it, but that's only because it's hard to
> imagine why anyone would make a role that's CREATEROLE and not CREATEDB.

I don't think it's unreasonable that an admin would want to allow
someone to be able to grant permissions to others, but not create
databases. Regardless, the real issue comes in when more privleges are
added in the future. We can either cross that bridge when we come to it
or we can look at it now. My argument is that we should look at it now.

> But, for example, we allow a CREATEROLE role to grant and revoke role
> memberships without itself being an admin of those roles.

Which is arguably not good...

> Basically the point of CREATEROLE is to be a safer kind of superuser:
> you can do what you like with respect to creating and dropping and
> altering users and groups (other than superusers), but you don't have
> permission to, say, "DELETE FROM pg_proc".  If we restrict CREATEROLE
> to not have any privileges that an ordinary user wouldn't have except
> the ability to create users, then we'll be back at square one in the
> sense that lots of common administrative situations will require
> superuser privilege.

I definately don't think we should restrict CREATEROLE to the point
where it's just another user, but I also don't think it should be given
full reign, either. To an extent we've already addressed that with the
special condition of SUPERUSER. My point is that instead of treating
SUPERUSER as a special case, why not just restrict all privileges the
same way?

An alternative would be a second set of privileges that determine what
privileges a role can assign, but that seems like overkill and a
potential foot-gun.

> It's quite likely that we don't have CREATEROLE fully done yet, and
> that it still needs some more tweaks that we haven't thought of.
> But I don't think I buy the premise that it ought to be confined to
> creating users with no other special privileges.  If we do that,
> we'll just have to re-invent the limited-superuser facility under
> a different name, because there's a serious need for that.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software        http://pervasive.com        512-569-9461