Filter-Id SQL xlat function

13 Oct 2008

      Hi
I want FreeRADIUS to return the Filter-Id attribute, on behalf of a 
service of the client which is in another table (not in radreply or 
radgroupreply). The idea is to have a different Filter-Id depending on 
the region the client is connecting to (which means depending on the 
CallingStation-Id parameter). So I have a separate table in which 
CallingStation-Ids and Filter-Ids are mapped.

For example:
mysql> select service from userinfo where username='ljupco' limit 1;
+---------+
| service |
+---------+
| net1    |
+---------+

Now if I put that query in the Filter-Id, the XLAT function doesn't get 
executed:
==In radreply=====================================
UserName......Attribute..........op.........Value
ljupco............Filter-Id.............=.........%{sql:select service 
from userinfo where username='ljupco' limit 1;}
==============================================
# radtest ljupco ljupco localhost 523 testing123
Sending Access-Request of id 201 to 127.0.0.1 port 1812
        User-Name = "ljupco"
        User-Password = "ljupco"
        NAS-IP-Address = 255.255.255.255
        NAS-Port = 523
rad_recv: Access-Accept packet from host 127.0.0.1:1812, id=201, length=89
        Filter-Id = "%{sql:select service from userinfo where 
username='ljupco' limit 1;}"

Instead of "%{sql:select service from userinfo where username='ljupco' 
limit 1;}" there should be net1.
How can I do this?

Output from radiusd -X (freeradius v1.1.6)
rad_recv: Access-Request packet from host 127.0.0.1:59998, id=201, length=58
        User-Name = "ljupco"
        User-Password = "ljupco"
        NAS-IP-Address = 255.255.255.255
        NAS-Port = 523
rad_lowerpair:  User-Name now 'ljupco'
  Processing the authorize section of radiusd.conf
modcall: entering group authorize for request 7
  modcall[authorize]: module "preprocess" returns ok for request 7
  modcall[authorize]: module "chap" returns noop for request 7
  modcall[authorize]: module "mschap" returns noop for request 7
    rlm_realm: No '@' in User-Name = "ljupco", looking up realm NULL
    rlm_realm: No such realm "NULL"
  modcall[authorize]: module "suffix" returns noop for request 7
  rlm_eap: No EAP-Message, not doing EAP
  modcall[authorize]: module "eap" returns noop for request 7
radius_xlat:  'ljupco'
rlm_sql (sql): sql_set_user escaped user --> 'ljupco'
radius_xlat:  'SELECT id, UserName, Attribute, Value, op           FROM 
radcheck           WHERE Username = 'ljupco'           ORDER BY id'
rlm_sql (sql): Reserving sql socket id: 3
radius_xlat:  'SELECT 
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op  
FROM radgroupcheck,usergroup WHERE usergroup.Username = 'ljupco' AND 
usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id'
radius_xlat:  'SELECT id, UserName, Attribute, Value, op           FROM 
radreply           WHERE Username = 'ljupco'           ORDER BY id'
radius_xlat:  'SELECT 
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op  
FROM radgroupreply,usergroup WHERE usergroup.Username = 'ljupco' AND 
usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id'
rlm_sql (sql): Released sql socket id: 3
  modcall[authorize]: module "sql" returns ok for request 7
  modcall[authorize]: module "pap" returns updated for request 7
modcall: leaving group authorize (returns updated) for request 7
  rad_check_password:  Found Auth-Type pap
auth: type "PAP"
  Processing the authenticate section of radiusd.conf
modcall: entering group PAP for request 7
rlm_pap: login attempt with password ljupco
rlm_pap: Using clear text password "ljupco".
rlm_pap: User authenticated successfully
  modcall[authenticate]: module "pap" returns ok for request 7
modcall: leaving group PAP (returns ok) for request 7
Login OK: [ljupco/ljupco] (from client localhost port 523)
  Processing the post-auth section of radiusd.conf
modcall: entering group post-auth for request 7
rlm_sql (sql): Processing sql_postauth
radius_xlat:  'ljupco'
rlm_sql (sql): sql_set_user escaped user --> 'ljupco'
radius_xlat:  'INSERT into radpostauth (id, user, pass, reply, date) 
values ('', 'ljupco', 'ljupco', 'Access-Accept', NOW())'
rlm_sql (sql) in sql_postauth: query is INSERT into radpostauth (id, 
user, pass, reply, date) values ('', 'ljupco', 'ljupco', 
'Access-Accept', NOW())
rlm_sql (sql): Reserving sql socket id: 2
rlm_sql (sql): Released sql socket id: 2
  modcall[post-auth]: module "sql" returns ok for request 7
modcall: leaving group post-auth (returns ok) for request 7
Sending Access-Accept of id 201 to 127.0.0.1 port 59998
        Filter-Id = "%{sql:select service from userinfo where 
username='ljupco' limit 1}"
Finished request 7
Going to the next request

Tried as well with freeradius v2.0.5, the result is the same.
Regards,

-- 
Ljupco

Filter-Id SQL xlat function

Ljupco Vangelski