Re: User in Multiple Groups

7 Apr 2006

      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@imperial.ac.uk> 
 To: FreeRadius users mailing list <freeradius-users@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@laptop> 
From: "debik" <debik@vp.pl> 
Subject: Re: Couldn't stop freeradius server!!
From: "Scott Reed" <sreed@nwwnet.net> 
Date: Wed, 5 Apr 2006 07:25:29 -0500 
Message-Id: <20060405121401.M70783@nwwnet.net> 
In-Reply-To: <002101c658de$6ceb9400$0500a8c0@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 -------