User in Multiple Groups

[Scott Reed]

OK, Phil, you got me. I thought all I did was copy the to address, but must
have used a reply instead.  Sorry.

Thanks for the code suggestions.  I understand what you see as the issue. 
Makes sense.  I will experiment with what you suggest and see what I get.

Scott Reed 
 Owner 
 NewWays 
 Wireless Networking 
 Network Design, Installation and Administration 
 www.nwwnet.net

---------- Original Message ----------- 
 From: Phil Mayers <p.mayers at imperial.ac.uk> 
 To: FreeRadius users mailing list <freeradius-users at lists.freeradius.org> 
 Sent: Fri, 07 Apr 2006 11:09:48 +0100 
 Subject: Re: User in Multiple Groups

> Scott Reed wrote: 
> > I did not usurp a thread, I reposted my own. 
> 
> Really? How odd: 
> 
> Message-ID: <002101c658de$6ceb9400$0500a8c0 at laptop> 
> From: "debik" <debik at vp.pl> 
> Subject: Re: Couldn't stop freeradius server!! 
> 
> From: "Scott Reed" <sreed at nwwnet.net> 
> Date: Wed, 5 Apr 2006 07:25:29 -0500 
> Message-Id: <20060405121401.M70783 at nwwnet.net> 
> In-Reply-To: <002101c658de$6ceb9400$0500a8c0 at laptop> 
> Subject: User in Multiple Groups 
> 
> > 
> > I changed radcheck to have := instead of ==.  No change. 
> > 
> > First query returns: 
> > +----+--------------+--------------+-------------+----+ 
> > | id | GroupName    | Attribute    | Value       | op | 
> > +----+--------------+--------------+-------------+----+ 
> > | 28 | MS1-AP1      | Service-Type | Framed-User | == | 
> > | 31 | Router-Admin | Service-Type | Login-User  | == | 
> > +----+--------------+--------------+-------------+----+ 
> 
> Ah ok. Lightbulb moment. 
> 
> Disclaimer: I'm not an expert w.r.t. rlm_sql (or much else in the server 
> in fact) 
> 
> BUT I've taken quite a detailed look at the code in the past, and as far 
> as I can tell it does this: 
> 
> check_items = [] 
> 
> radcheck_items = query("<radcheck query>") 
> check_items += radcheck_items 
> 
> groupcheck_items = query("<radgroupcheck query>") 
> check_items += groupcheck_items 
> 
> ...that is, ALL the groupcheck items for a user are added to the check 
> items (see src/modules/rlm_sql/rlm_sql.c line 782, at least in 1.1.0 
> source). 
> 
> So, in your case the check items from both groups will be merged: 
> 
> username Service-Type == Framed-User, Service-Type == Login-User 
> 
> ...and obviously will never match. So you're correct, with the default 
> queries >1 groupcheck where the groups have the same check item will 
> seldom (if ever) work as expected. 
> 
> You could try changing the groupcheck query to something like: 
> 
> SELECT 
>   ${groupcheck_table}.id, 
>   ${groupcheck_table}.GroupName, 
>   ${groupcheck_table}.Attribute, 
>   ${groupcheck_table}.Value, 
>   ${groupcheck_table}.op 
> FROM 
>   ${groupcheck_table}, 
>   ${usergroup_table} 
> WHERE 
>   ${usergroup_table}.Username = '%{SQL-User-Name}' 
> AND 
>   ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName 
> -- this bit has been added 
> AND 
>   ( 
>     -- all groups without Service-Type checks 
>     NOT EXISTS ( 
>       select 1 from ${groupcheck_table} as ot 
>       where ot.Attribute=='Service-Type' 
>       and ot.GroupName==${groupcheck_table}.GroupName 
>     ) 
>   OR 
>     -- all groups with Service-Type checks matching our Service-Type 
>     EXISTS ( 
>       select 1 from ${groupcheck_table} as ot 
>       where ot.Attribute=='Service-Type' 
>       -- WARNING: this assumes ot.Op is "==" 
>       and ot.Value=='%{Service-Type}' 
>       and ot.GroupName==${groupcheck_table}.GroupName 
>     ) 
>   ) 
> -- the above bit has been added 
> ORDER BY ${groupcheck_table}.id 
> 
> ...which is a bit complex (and untested / off the top of my head) but 
> should work. Having said that I note you're using MySQL, which I can't 
> remember if it support sub-selects. 
> 
> Really the module should be recoded IMHO to do this: 
> 
> usercheck = query("<radcheck query>") 
> if usercheck AND paircmp(usercheck, request): 
>     userreply = query("<radreply query>") 
>     pairxlatmove(request.reply, userreply) 
> groups = query("<usergroup query> order by priority") 
> for group in groups: 
>   groupcheck = query("<groupcheck query> WHERE GroupName=$group") 
>   if groupcheck and paircmp(groupcheck, request): 
>     groupreply = query("<groupreply query> WHERE GroupName=$group") 
>     pairxlatmove(request.reply, groupreply) 
> 
> ...but I don't know if there's any interest in doing that. 
> - 
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html 
------- End of Original Message -------