User in Multiple Groups

[Phil Mayers]

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.