Re: Interesting quirk using MySQL

[Brad Stockdale]

Heya all,

I'm working on switching from cistron radius to freeradius using a MySQL backend, and I ran across an interesting problem...

First, here's some data from my database:

mysql> select * from radgroupreply;
1 dialup_dynamic Framed-Protocol := PPP 0
2 dialup_dynamic Framed-Address := 255.255.255.254 0
3 dialup_dynamic Framed-Netmask := 255.255.255.255 0
4 dialup_dynamic Framed-Routing := None 0
5 dialup_dynamic Framed-Compression := Van-Jacobsen-TCP-IP 0
6 dialup_dynamic Framed-MTU := 1500 0
7 dialup_dynamic Session-Timeout := 43200 0
8 dialup_dynamic Idle-Timeout := 1800 0
9 adsl_dynamic Service-Type := Framed 0
10 adsl_dynamic Framed-Protocol := PPP 0
11 adsl_dynamic Idle-Timeout := 0 0
12 adsl_dynamic Cisco-AVPair := ip:route=65.173.147.0 255.255.255.0 CONTINUED FROM PREVIOUS LINE 65.173.147.1 0
13 adsl_dynamic Cisco-AVPair := ip:addr-pool=pool1 0


mysql> select * from radcheck where UserName = 'testDSL';
2143 testDSL Password == testpass enabled


(Note that I modified the radcheck table to include a 'Status' column, which is what the enabled is from. I have tested this change a lot, and altered the proper statement in the sql.conf file to get it to work right)

mysql > select * from usergroup where UserName = 'testDSL';
2143 testDSL adsl_dynamic


So, as you can see, I have a user 'testDSL' with a password of 'testpass'. This user belongs to the 'adsl_dynamic' group, which has the above mentioned attributes to send back... (9-13 in the radgroupreply table)...

Yet, whenever I use NTRadPing to check it, I get the following:

Service-Type=Framed
Framed-Protocol=PPP
Idle-Timeout=0
vendor Cisco cisco-avpair=ip:route=65.173.147.0 255.255.255.0 65.173.147.1


That's it... I'm missing one of my reply attributes, even though as far as I can tell, it's in the database and added correctly...

When I perform the same authentication against our existing cistron radius server, I get the following reply:

Service-Type=Framed
Framed-Protocol=PPP
Idle-Timeout=0
vendor Cisco cisco-avpair=ip:route=65.173.147.0 255.255.255.0 65.173.147.1
vendor Cisco cisco-avpair=ip:addr-pool=pool1

This is the correct set of attributes...

I ran freeradius in debug mode, and got the following relating to getting the info from radgroupreply:

radius_xlat: 'SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.UserName = 'testDSL' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id'
rlm_sql_mysql: query: SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.UserName = 'testDSL' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id
rlm_sql (sql): Released sql socket id: 4
modcall[authorize]: module "sql" returns ok
modcall[authorize]: module "files" returns notfound
modcall: group authorize returns ok
auth: type Local
auth: user supplied User-Password matches local User-Password
Login OK: [testDSL/testpass] (from client 65.173.148.35 port 0)
Sending Access-Accept of id 13 to 65.173.148.35:1531
Service-Type := Framed-User
Framed-Protocol := PPP
Idle-Timeout := 0
Cisco-AVPair := "ip:route=65.173.147.0 255.255.255.0 65.173.147.1"
Finished request 0
Going to the next request

But yet when I run the query by hand I get the following:



mysql> SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.UserName = 'testDSL' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id
-> ;
+----+--------------+-----------------+--------------------------------------------------+----+
| id | GroupName | Attribute | Value | op |
+----+--------------+-----------------+--------------------------------------------------+----+
| 9 | adsl_dynamic | Service-Type | Framed | := |
| 10 | adsl_dynamic | Framed-Protocol | PPP | := |
| 11 | adsl_dynamic | Idle-Timeout | 0 | := |
| 12 | adsl_dynamic | Cisco-AVPair | ip:route=65.173.147.0 255.255.255.0 65.173.147.1 | := |
| 13 | adsl_dynamic | Cisco-AVPair | ip:addr-pool=pool1 | := |
+----+--------------+-----------------+--------------------------------------------------+----+
5 rows in set (0.01 sec)


So, when I run it manually, it returns five attributes... When it gets ran by freeradius, it only gets four attributes...

Can anyone out there shed some light on what might cause the problem??

The big mystery to me is why it finds the right attributes when ran by hand, but doesnt seem to find them when ran by freeradius...

Thanks,
Brad


- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html