Re: [PATCHES] Users/Groups -> Roles

Hi Fabien,

* Fabien COELHO (fabien.coelho@ensmp.fr) wrote:
> I've looked very quickly at the patch. ISTM that the proposed patch is a
> reworking of the user/group stuff, which are both unified for a new "role"
> concept where a user is a kind of role and a role can be a member of
> another role. Well, why not.

Right, it's a beginning to proper 'Role' support as defined by the SQL
specification.

> Some added files seems not to be provided in the patch :

pg_authid.h and pg_auth_members.h were attached to the email.  They're
also available at http://kenobi.snowman.net/~sfrost/pg_role/ ; but the
patch has already been applied by Tom to CVS HEAD (well, with lots of
modifications and whatnot), so you probably should just take a look at
that.

> Anyway, from what I can see in the patch it seems that the roles are per
> cluster, and not per catalog. So this is not so conceptually different
> from user/group as already provided in pg.

It's conceptually different from users/groups in that it's roles, which
aren't the same thing.  You're right, it's still per-cluster though.

> What would have been much more interesting for me would be a per catalog
> role, so that rights could be administrated locally in each database. I'm
> not sure how to provide such a feature, AFAICS the current version does
> not give me new abilities wrt right management.

I understand your concerns here and while I agree with the basic idea
that per-catalog role sets would be nice it wasn't what I had set out to
do with this patch.  Perhaps what you're asking for will be added later
on.  Some things this patch does do though are:

Allow role ownership.  This role can also have members, and doesn't
necessairly have to be allowed to log in.  Members of a role which owns
an object have owner-level rights on that object (so, fe: roles user1,
user2 and group1 where user1 and user2 are members of group1, a table
owned by group1 can be vacuumed, have columns added/removed, have
indexes create on it, etc, by user1 or user2).

Allow granting roles to other roles based on the 'with admin option'.
This means you don't have to be a superuser to add a member to a role
which you have the 'admin option' on.

There's other things (startup may be a bit faster since the pg_auth file
is sorted by the backend instead of during each startup, etc) but the
above were the types of things that I was looking to do mainly.

I'd like to see it possible to distinguish between 'superuser' and
'createrole' permissions, but I didn't get to that point with the roles
support (it's really a seperate issue anyway).
Thanks,
    Stephen