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 -------