Huntgroup specific radreply items in rlm_sql

[Hugh Messenger]

I thought I'd post this in case anyone else has the same requirement.  If
you have no need for per-Huntgroup 'radreply' items using rlm_sql, you need
read no further.

The Problem
-----------

In our provisioning setup, users can belong to multiple 'service type'
groups.  So a single user might have both 'dialup' and 'wireless' service
types (so they can still dialup if the wireless goes down).

The rlm_sql module almost gets me there, and smoothly handles generic group
related check and reply items.  But the remaining problem was that all per
user 'radreply' items get returned, regardless of the service type.  Which
is the as-advertized behaviour for rlm_sql, but not what I need.

For instance, if the user is making a wireless connection, I need to send
back a user specific Mikrotik-Rate-Limit item, as well as whatever generic
wireless attributes I have in the 'wireless' group.  But I don't want to
send that wireless rate limit item to a dialup NAS.

The Solution
------------

For me, the most reliable way of identifying the 'service type' of the
incoming request is by Huntgroup.  The simplest form of which is:

wireless        NAS-IP-Address == x.x.x.1
wireless        NAS-IP-Address == x.x.x.2
dialup          NAS-IP-Address == y.y.y.1
dialup          NAS-IP-Address == y.y.y.2

For convenience, I call my Huntgroups the same as my rlm_sql Groupnames, but
this isn't a requirement.

OK, so now we know if this is a 'wireless' or 'dialup' session.  But we
still have no way of relating that to the 'radreply' table.  So we need to
add a column to 'radreply':

Mysql> alter table radreply add column 'Huntgroup' varchar(32) not null
default = '';

... and index it, as it'll be used in a WHERE clause:

mysql> alter table radreply add key Huntgroup (Huntgroup(32));

Then tweak the 'authorize_reply_query' in the appropriate SQL config file,
in my case mysql-dialup.conf:

SELECT id, UserName, Attribute, Value, op \
FROM ${authreply_table} \
WHERE Username = '%{SQL-User-Name}' \
AND (Huntgroup = '' OR Huntgroup = '%{Huntgroup-Name}') \
ORDER BY id"

The only change is the addition of the AND line.  So now the query will only
return per user attributes if the Huntgroup field is blank, or matches the
Huntgroup-Name of the current session.

This is the only query in rlm_sql that references the 'radreply' table, so
there are no other changes you need in FreeRadius itself.  The only other
thing you need to do is modify your backend SQL provisioning to add the
Huntgroup value to any 'radreply' entries that need it.

This is totally backward compatible with existing 'radreply' behavior and
pre-existing data, as it only affects entries with the Huntgroup set to
something.  So any existing entries will continue to be handed back every
time, unless you set the Huntgroup on them.  The standard rlm_sql Usergroup
behaviour is unaffected.

An example set of my 'radreply' entries now looks like this:

+----+------------+---------------------+----+-----------+-----------+
| id | UserName   | Attribute           | op | Value     | Huntgroup |
+----+------------+---------------------+----+-----------+-----------+
|  1 | radiustest | Mikrotik-Rate-Limit | =  | 512k/512k | wireless  |
|  3 | radiustest | Session-Timeout     | =  | 86400     | dialup    |
|  5 | radiustest | Fall-Through        | =  | 1         |           |
+----+------------+---------------------+----+-----------+-----------+

So now if user 'radiustest' connects via wireless, only the
'Mikrotik-Rate-Limit' radreply item gets returned to the NAS.  If they
connect via dialup, only the Session-Timeout is returned.  But
'Fall-Through' gets set regardless of Huntgroup (I prefer to set this on a
per user basis rather than setting 'use_groups' in sql.conf).

So far this seem to work like a charm.

   -- hugh