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

It is a bug. That should be "... expiry_time = NULL WHERE ..."

Hi, this is working fine. But there seems to be a problem with this statement now: ------------- SELECT framedipaddress FROM radippool WHERE pool_name = 'poolDE' AND expiry_time < NOW() ORDER BY (username <> 'peter2'), (callingstationid <> ''), expiry_time LIMIT 1 FOR UPDATE ------------ This statement should receive a free ip-adress, i think, but it doesn't. I think the problem is the part "expiry_time < NOW()". This is not working in mysql. Does anyone know how to fix this? Thanks Sebastian complete debug: rad_recv: Access-Request packet from host 10.98.6.33 port 2577, id=127, 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 14:27:45 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: 2 [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: 2 ++[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: 1 [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 = NULL WHERE expiry_time <= NOW() - INTERVAL 1 SECOND AND nasipaddress = '%{Nas-IP-Address}' -> UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time = 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 = 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] SQL query did not return any results [sqlippool] expand: COMMIT -> COMMIT rlm_sql_mysql: query: COMMIT [sqlippool] expand: SELECT id FROM radippool WHERE pool_name='%{control:Pool-Name}' LIMIT 1 -> SELECT id FROM radippool WHERE pool_name='poolDE' LIMIT 1 rlm_sql_mysql: query: SELECT id FROM radippool WHERE pool_name='poolDE' LIMIT 1 rlm_sql (sql): Released sql socket id: 1 [sqlippool] pool appears to be full [sqlippool] expand: IP Allocation FAILED from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) -> IP Allocation FAILED from poolDE (did xxx cli port user peter2) IP Allocation FAILED from poolDE (did xxx cli port user peter2) ++[sqlippool] returns notfound ++[exec] returns noop Sending Access-Accept of id 127 to 10.98.6.33 port 2577 Service-Type = Framed-User Framed-Protocol = PPP Framed-IP-Netmask = 255.255.255.0 Finished request 1. Going to the next request Waking up in 4.9 seconds. Cleaning up request 1 ID 127 with timestamp +1000 Ready to process requests. -- NUR NOCH BIS 31.01.! GMX FreeDSL - Telefonanschluss + DSL für nur 16,37 EURO/mtl.!* http://dsl.gmx.de/?ac=OM.AD.PD003K11308T4569a

Hm, does it "see" NULL greater than now()? Replace NULL in expiry_time column with "0000-00-00 00:00:00" (that's what MySQL thinks null datetime is - it will match IS NULL). And in that previous query replace = NULL with = '0000-00-00 00:00:00'.

If this is so, sqlippool schema will need to be changed.

This works. Yes, i think the schema really needs a update. i have another question: when does the server free an ip-address? i tested a login with the same username within one minute and the server reserved two different ip-addresses. i thought, that the server will use the same ip-address for the second login, but the server doesn't. Which behaviour is the right one? -- NUR NOCH BIS 31.01.! GMX FreeDSL - Telefonanschluss + DSL für nur 16,37 EURO/mtl.!* http://dsl.gmx.de/?ac=OM.AD.PD003K11308T4569a

...

i have another question: when does the server free an ip-address? i tested a login with the same username within one minute and the server reserved two different ip-addresses. i thought, that the server will use the same ip-address for the second login, but the server doesn't.

Which behaviour is the right one?

The server frees IP's when accounting stop packets are received. This is the correct behavior.

ok, i understand this. :-) Is there another possibility to reassign the same ip-address to the user again? Thanks Sebastian -- Psssst! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger

...

Is there another possibility to reassign the same ip-address to the user again?

Not while first connection is still active. That can't possibly work. NAS will reject such IP.

Are you trying to create a multilink? That is NAS, not radius capability.

Ivan Kalik Kalik Informatika ISP

Ok, i will check that with our NAS. At the moment i am only testing with radtest. But thanks a lot so far for all the help! -- NUR NOCH BIS 31.01.! GMX FreeDSL - Telefonanschluss + DSL für nur 16,37 EURO/mtl.!* http://dsl.gmx.de/?ac=OM.AD.PD003K11308T4569a

...

...

...

Hm, does it "see" NULL greater than now()? Replace NULL in expiry_time column with "0000-00-00 00:00:00" (that's what MySQL thinks null datetime is - it will match IS NULL). And in that previous query replace = NULL with = '0000-00-00 00:00:00'.

If this is so, sqlippool schema will need to be changed.

This works. Yes, i think the schema really needs a update.

As always, patches are welcome.

It probably needs just a change from NULL to '0000-00-00 00:00:00' for the expiry_time default value in ippool.sql.

expiry_time IS NULL will match if value is '0000-00-00 00:00:00'. I will fix the queries to use MySQL null value (0000-00-00 00:00:00) and not NULL, since NULL doesn't work in <now() compare.

Yes, that should work... There is another problem with the mysql-configuration. In the file ippool.conf there is the following part: ----------------- ## This series of queries allocates an IP address ## (Note: If your pool-key is set to Calling-Station-Id and not NAS-Port ## then you may wish to delete the "AND nasipaddress = '%{Nas-IP-Address}' ## from the WHERE clause) allocate-clear = "UPDATE ${ippool_table} \ SET nasipaddress = '', pool_key = 0, \ callingstationid = '', username = '', \ expiry_time IS NULL \ WHERE expiry_time <= NOW() - INTERVAL 1 SECOND AND nasipaddress = '%{Nas-IP-Address}'" ---------------- after the line "WHERE expiry_time <= NOW() - INTERVAL 1 SECOND" there is the "\" missing. This will cause an error, if you start the server. -- Psssst! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger

I have attached new schema and queries for mysql sqlippool. These should work with both 4 and 5 MySQL versions (I have done some manual testing on both; 4 on Linux and 5 on Windows). In version 5 (at least the Windows one I have tested on) CURRENT_TIMESTAMP defaults to '0000-00-00 00:00:00' instead of current date. This has no impact on how queries work. It might have been fixed in later MySQL verions (this is 5.0.18). I am not sure if I have done diff from the correct files so I included both patches and files. Ivan Kalik Kalik Informatika ISP

Now, the "behaviour" of the server changed in the way, that the freeradius reserves only one ip-address per user. if the same user logs in again on the same nas (without accounting-stop-packet before), the old ip-address is freed and the user receives a new one.

That should happen only if IP allocation has expired (see lease-duration in sqlippool.conf). There is another allocate-find query that issues random IPs.

Is there a possibility to assign also a specific subnetmask with the radippool-table? we have different subnetmasks for the different pools.

If these are PPP connections you should use 255.255.255.255 for all of them. That will match any gateway subnet and mask. You coral them with firewall. Ivan Kalik Kalik Informatika ISP

...

That should happen only if IP allocation has expired (see lease-duration in sqlippool.conf). There is another allocate-find query that issues random IPs.

Hmmm, maybe there is another problem in my config. I tried two requests within ten seconds. Attached you'll find the debug. During the second request the first ip-address is freed and can be used again. The lease-duration has the standard value of 3600, so this can't be the reason.

This is the table radippool after the second request:

+-----------+-----------------+--------------+---------------------+----------+----------+ | pool_name | framedipaddress | nasipaddress | expiry_time | username | pool_key | +-----------+-----------------+--------------+---------------------+----------+----------+ | poolUK | 10.10.10.10 | 10.98.6.95 | 2009-02-02 10:14:32 | peter2 | | | poolUK | 10.10.10.11 | | 2009-02-02 09:14:31 | | 0 | +-----------+-----------------+--------------+---------------------+----------+----------+

You don't have a pool_key because you are doing radtest requests. Proper request will have NAS-Port or Calling-Station-Id as pool_key. With updated queries user, nas *and* pool_key need to match for IP to be released. Queries in the distribution don't have pool_key so double login will release the older IP. Ivan Kalik Kalik Informatika ISP