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

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 "fuse" user and role 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