IP-Assignment with sqlippool based on nas-ip-address

Sebastian Heil s3b0 at gmx.de
Wed Jan 28 13:16:30 CET 2009


> 
> You don't. You use sql groups.
> 
> Create two groups, group1 and group2; add to radgroupcheck:
> Nas-IP-Address == nas1 address and Pool-Name := pool1 for group1 and
> Nas-IP-Address == nas2 address and Pool-Name := pool2 for group2. Add
> the user to both groups.

Thanks, 

it seems, as if this is working...

But there seems to be another problem or even a bug:

What does this errormessage mean?

------------------------------------
rlm_sql_mysql: MYSQL check_error: 1064 received
sqlippool_command: database query error in: 'UPDATE radippool   SET nasipaddress = '', pool_key = 0,   callingstationid = '', username = '',   expiry_time IS NULL   WHERE expiry_time <= NOW() - INTERVAL 1 SECOND   AND nasipaddress = '10.98.6.33''
-------------------------------------

I tried the same statement in the mysql-console and received the following error:

-------------------------
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IS NULL   WHERE expiry_time <= NOW() - INTERVAL 1 SECOND   AND nasipaddress = '1' at line 1
-------------------------

Here is the complete debug-output:

-----------------------
rad_recv: Access-Request packet from host 10.98.6.33 port 4356, id=23, length=67
        User-Name = "peter2"
        User-Password = "peter2"
        Called-Station-Id = "xxx"
+- entering group authorize {...}
++[preprocess] returns ok
[auth_log]      expand: /var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d -> /var/log/radius/radacct/10.98.6.33/auth-detail-20090128
[auth_log] /var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d expands to /var/log/radius/radacct/10.98.6.33/auth-detail-20090128
[auth_log]      expand: %t -> Wed Jan 28 13:10:04 2009
++[auth_log] returns ok
++[chap] returns noop
++[mschap] returns noop
[suffix] No '@' in User-Name = "peter2", looking up realm NULL
[suffix] No such realm "NULL"
++[suffix] returns noop
[eap] No EAP-Message, not doing EAP
++[eap] returns noop
++[unix] returns notfound
[files] users: Matched entry DEFAULT at line 183
++[files] returns ok
[sql]   expand: %{User-Name} -> peter2
[sql] sql_set_user escaped user --> 'peter2'
rlm_sql (sql): Reserving sql socket id: 1
[sql]   expand: SELECT id, username, attribute, value, op           FROM radcheck           WHERE username = '%{SQL-User-Name}'           ORDER BY id -> SELECT id, username, attribute, value, op           FROM radcheck           WHERE username = 'peter2'           ORDER BY id
rlm_sql_mysql: query:  SELECT id, username, attribute, value, op           FROM radcheck           WHERE username = 'peter2'           ORDER BY id
[sql] User found in radcheck table
[sql]   expand: SELECT id, username, attribute, value, op           FROM radreply           WHERE username = '%{SQL-User-Name}'           ORDER BY id -> SELECT id, username, attribute, value, op           FROM radreply           WHERE username = 'peter2'           ORDER BY id
rlm_sql_mysql: query:  SELECT id, username, attribute, value, op           FROM radreply           WHERE username = 'peter2'           ORDER BY id
[sql]   expand: SELECT groupname           FROM radusergroup           WHERE username = '%{SQL-User-Name}'           ORDER BY priority -> SELECT groupname           FROM radusergroup           WHERE username = 'peter2'           ORDER BY priority
rlm_sql_mysql: query:  SELECT groupname           FROM radusergroup           WHERE username = 'peter2'           ORDER BY priority
[sql]   expand: SELECT id, groupname, attribute,           Value, op           FROM radgroupcheck           WHERE groupname = '%{Sql-Group}'           ORDER BY id -> SELECT id, groupname, attribute,           Value, op           FROM radgroupcheck           WHERE groupname = 'Deutschland'           ORDER BY id
rlm_sql_mysql: query:  SELECT id, groupname, attribute,           Value, op           FROM radgroupcheck           WHERE groupname = 'Deutschland'           ORDER BY id
[sql] User found in group Deutschland
[sql]   expand: SELECT id, groupname, attribute,           value, op           FROM radgroupreply           WHERE groupname = '%{Sql-Group}'           ORDER BY id -> SELECT id, groupname, attribute,           value, op           FROM radgroupreply           WHERE groupname = 'Deutschland'           ORDER BY id
rlm_sql_mysql: query:  SELECT id, groupname, attribute,           value, op           FROM radgroupreply           WHERE groupname = 'Deutschland'           ORDER BY id
rlm_sql (sql): Released sql socket id: 1
++[sql] returns ok
++[expiration] returns noop
++[logintime] returns noop
++[pap] returns updated
Found Auth-Type = PAP
+- entering group PAP {...}
[pap] login attempt with password "peter2"
[pap] Using clear text password "peter2"
[pap] User authenticated successfully
++[pap] returns ok
+- entering group post-auth {...}
rlm_sql (sql): Reserving sql socket id: 0
[sqlippool]     expand: %{User-Name} -> peter2
[sqlippool] sql_set_user escaped user --> 'peter2'
[sqlippool]     expand: START TRANSACTION -> START TRANSACTION
rlm_sql_mysql: query:  START TRANSACTION
[sqlippool]     expand: UPDATE radippool   SET nasipaddress = '', pool_key = 0,   callingstationid = '', username = '',   expiry_time IS NULL   WHERE expiry_time <= NOW() - INTERVAL 1 SECOND   AND nasipaddress = '%{Nas-IP-Address}' -> UPDATE radippool   SET nasipaddress = '', pool_key = 0,   callingstationid = '', username = '',   expiry_time IS NULL   WHERE expiry_time <= NOW() - INTERVAL 1 SECOND   AND nasipaddress = '10.98.6.33'
rlm_sql_mysql: query:  UPDATE radippool   SET nasipaddress = '', pool_key = 0,   callingstationid = '', username = '',   expiry_time IS NULL   WHERE expiry_time <= NOW() - INTERVAL 1 SECOND   AND nasipaddress = '10.98.6.33'
rlm_sql_mysql: MYSQL check_error: 1064 received
sqlippool_command: database query error in: 'UPDATE radippool   SET nasipaddress = '', pool_key = 0,   callingstationid = '', username = '',   expiry_time IS NULL   WHERE expiry_time <= NOW() - INTERVAL 1 SECOND   AND nasipaddress = '10.98.6.33''
[sqlippool]     expand: SELECT framedipaddress FROM radippool  WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < NOW()  ORDER BY (username <> '%{User-Name}'),  (callingstationid <> '%{Calling-Station-Id}'),  expiry_time  LIMIT 1  FOR UPDATE -> SELECT framedipaddress FROM radippool  WHERE pool_name = 'poolDE' AND expiry_time < NOW()  ORDER BY (username <> 'peter2'),  (callingstationid <> ''),  expiry_time  LIMIT 1  FOR UPDATE
rlm_sql_mysql: query:  SELECT framedipaddress FROM radippool  WHERE pool_name = 'poolDE' AND expiry_time < NOW()  ORDER BY (username <> 'peter2'),  (callingstationid <> ''),  expiry_time  LIMIT 1  FOR UPDATE
[sqlippool]     expand: UPDATE radippool  SET nasipaddress = '%{NAS-IP-Address}', pool_key = '%{NAS-Port}',  callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}',  expiry_time = NOW() + INTERVAL 3600 SECOND  WHERE framedipaddress = '192.168.1.4' -> UPDATE radippool  SET nasipaddress = '10.98.6.33', pool_key = '',  callingstationid = '', username = 'peter2',  expiry_time = NOW() + INTERVAL 3600 SECOND  WHERE framedipaddress = '192.168.1.4'
rlm_sql_mysql: query:  UPDATE radippool  SET nasipaddress = '10.98.6.33', pool_key = '',  callingstationid = '', username = 'peter2',  expiry_time = NOW() + INTERVAL 3600 SECOND  WHERE framedipaddress = '192.168.1.4'
[sqlippool] Allocated IP 192.168.1.4 [0401a8c0]
[sqlippool]     expand: COMMIT -> COMMIT
rlm_sql_mysql: query:  COMMIT
rlm_sql (sql): Released sql socket id: 0
[sqlippool]     expand: Allocated IP: %{reply:Framed-IP-Address} from %{control:Pool-Name}   (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) -> Allocated IP: 192.168.1.4 from poolDE   (did xxx cli  port  user peter2)
Allocated IP: 192.168.1.4 from poolDE   (did xxx cli  port  user peter2)
++[sqlippool] returns ok
++[exec] returns noop
Sending Access-Accept of id 23 to 10.98.6.33 port 4356
        Service-Type = Framed-User
        Framed-Protocol = PPP
        Framed-IP-Netmask = 255.255.255.0
        Framed-IP-Address = 192.168.1.4
Finished request 4.
Going to the next request
Waking up in 4.9 seconds.
Cleaning up request 4 ID 23 with timestamp +7373
Ready to process requests.
------------------------------

Do you have any ideas?

Thanks
Sebastian
-- 
Psssst! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger



More information about the Freeradius-Users mailing list