Re: [PATCHES] Users/Groups -> Roles - Mailing list pgsql-hackers

[sorry, resent because stalled]

Dear Stephen,

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

Ok. AFAIC remember, the specification is pretty subtle and fuzzy enough so that 
there is room for little design options.

> 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:
> [...]

Ok. I think I understand but I'm not sure it is done the right way.
Let me explain my (possibly wrong) point of view:

The standard talks about 2 distinct concepts: USER and ROLE (4.34). I'm
not sure it is a good idea to drop the user concept to replace it by role.
If you do so, you may miss something about what roles are about.

The SESSION_USER/CURRENT_USER has a CURRENT_ROLE which defines the rights at a 
given time. This role can be changed by the user, based on user/role 
membership, so the user can change its 'effective' rights. Roles are potential 
privileges that a user can set himself in if he/she desires so. example:

sh> psql -u calvin mydb
calvin@mydb>
-- I'm user calvin with no role or a default role on mydb.
-- I can do all which is allowed to 'calvin' as a user.

calvin@mydb> SET ROLE admin;
calvin/admin@mydb> ...
-- I'm allowed to do that if the role 'admin' is granted to 'calvin'
-- now I can do whatever is allowed to role 'admin'.

calvin/admin@mydb> SET ROLE basic;
calvin/basic@mydb> ...
-- now I can do what is allowed to role 'basic' and the roles 'basic' are in.
-- things that where allowed to admin may *not* be accessible now.

This is a very useful feature, and a key idea of the specs IMVVHO. ISTM 
that the way user and role are "fused" misses that important point, as I 
have not seen a "set role" in the grammar file.

Although in the spec role rights are transitive in the role realm, it should 
*stop* at the user. If you drop the user concept, you just have a group with 
automatically provided rights.

The fact that the spec does not specify the USER stuff and specifies the ROLE 
stuff does not mean that having only roles is the good way to go.


So for me we should have per-cluser users as they where up to now, per-catalog 
roles with the properties I described, and possibly per-cluster group just for 
the sake of compatibility/simplicity of the access control and managing group 
of users as a whole. ROLE should not replace USER/GROUP. It should be added 
next to it.


Maybe I'm wrong at my reading of the spec and its intent, and at my quick check 
through the status of the cvs head, but that's my current understanding, and I 
think it should be checked seriously.

Have a nice day,

-- 
Fabien