Hi Jan and Roberto We ARE doing serious work on sqlippool but it is all with Postgresql. As Jan says someone with a little MySQL knowledge shoudn't have problems making those queries work with MySQL. Once you have them working please send them to my so I can include them in cvs. Cheers Peter On Tue 17 Oct 2006 00:58, Jan Mulders wrote:
Someone needs to do some serious work on sqlippool. I'd do so, but currently I have no need for SQL-assigned IPs, as I only have one RADIUS server - and if it fails over, the least thing I have to worry about is current IP assignments.
I recommend finding someone who is adept at *SQL and buy them a pizza. Then ask them to 'translate' those queries for you.
Jan
On 16/10/06, Roberto Gonzalez Azevedo <rgonzalez@censanet.com.br> wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Does somebody knows how to configure sqlippool with MySQL ? The sqlippool.conf example is for pgsql. And for MySQL ?
Here is my sqlippool.conf, corrected for MySQL: "
sqlippool sqlippool {
# # SQL connection information # sql-instance-name = "sql"
# lease_duration. fix for lost acc-stop packets lease-duration = 3600
# Attribute which should be considered unique per NAS pool-key = "%{Acct-Session-Id}" pool-name = "mypool"
# pool-key = "%{Calling-Station-Id}"
# # This series of queries allocates an IP address # allocate-clear = "UPDATE radippool \ SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \ expiry_time = NOW() - INTERVAL 1 SECOND \ WHERE pool_key = '${pool-key}'"
# note the ORDER BY clause of next query, it'll try to allocate IPs # like Cisco internal pools do - it _trys_ to allocate the same IP-address # which user had last session... allocate-find = "SELECT FramedIPAddress FROM radippool \ WHERE pool_name = '%{reply:Pool-Name}' AND expiry_time < NOW() \ ORDER BY pool_name, (UserName <> '%{User-Name}'), (CallingStationId <> '%{Calling-Station-Id}'), expiry_time \ LIMIT 1 \ FOR UPDATE"
allocate-update = "UPDATE radippool \ SET NASIPAddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \ CallingStationId = '%{Calling-Station-Id}', UserName = '%{User-Name}', \ expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \ WHERE FramedIPAddress = '%{Framed-IP-Address}'"
# # This series of queries frees an IP number when an accounting # START record arrives # start-update = "UPDATE radippool \ SET expiry_time = NOW() + INTERVAL %J SECOND \ WHERE NASIPAddress = '%n' AND pool_key = '${pool-key}' AND pool_name = '%P'"
# # This series of queries frees an IP number when an accounting # STOP record arrives # stop-clear = "UPDATE radippool \ SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \ expiry_time = NOW() - INTERVAL 1 SECOND \ WHERE NASIPAddress = '%{NAS-IP-Address}' AND pool_key = '${pool-key}' AND UserName = '%{User-Name}' \ AND CallingStationId = '%{Calling-Station-Id}' AND FramedIPAddress = '%{Framed-IP-Address}'"
# # This series of queries frees an IP number when an accounting # ALIVE record arrives # alive-update = "UPDATE radippool \ SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \ WHERE NASIPAddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' AND UserName = '%{User-Name}' \ AND CallingStationId = '%{Calling-Station-Id}' AND FramedIPAddress = '%{Framed-IP-Address}'"
# # This series of queries frees the IP numbers allocate to a # NAS when an accounting ON record arrives # on-clear = "UPDATE radippool \ SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \ expiry_time = NOW() - INTERVAL 1 SECOND \ WHERE NASIPAddress = '%{NAS-IP-Address}' AND UserName = '%{User-Name}' \ AND CallingStationId = '%{Calling-Station-Id}' AND FramedIPAddress = '%{Framed-IP-Address}'"
# # This series of queries frees the IP numbers allocate to a # NAS when an accounting OFF record arrives # off-clear = "UPDATE radippool \ SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \ expiry_time = NOW() - INTERVAL 1 SECOND \ WHERE NASIPAddress = '%{NAS-IP-Address}' AND UserName = '%{User-Name}' \ AND CallingStationId = '%{Calling-Station-Id}' AND FramedIPAddress = '%{Framed-IP-Address}'"
} "
Here is the radiusd -X: " ... Mon Oct 16 17:50:50 2006 : Debug: Processing the post-auth section of radiusd.conf Mon Oct 16 17:50:50 2006 : Debug: modcall: entering group post-auth for request 0 Mon Oct 16 17:50:50 2006 : Debug: modsingle[post-auth]: calling sqlippool (rlm_sqlippool) for request 0 Mon Oct 16 17:50:50 2006 : Debug: rlm_sqlippool: Framed-IP-Address already exists Mon Oct 16 17:50:50 2006 : Debug: modsingle[post-auth]: returned from sqlippool (rlm_sqlippool) for request 0 Mon Oct 16 17:50:50 2006 : Debug: modcall[post-auth]: module "sqlippool" returns noop for request 0 Mon Oct 16 17:50:50 2006 : Debug: modcall: leaving group post-auth (returns noop) for request 0 ... ... Mon Oct 16 17:50:50 2006 : Debug: modsingle[accounting]: calling sqlippool (rlm_sqlippool) for request 1 Mon Oct 16 17:50:50 2006 : Debug: rlm_sql (sql): Reserving sql socket id: 2 Mon Oct 16 17:50:50 2006 : Debug: radius_xlat: 'BEGIN' Mon Oct 16 17:50:50 2006 : Debug: radius_xlat: 'UPDATE radippool SET expiry_time = NOW() + INTERVAL 3600 SECOND WHERE NASIPAddress = 'xx.xx.xx.xx' AND pool_key = '4533F0AA608100' AND pool_name = 'sqlippool'' ... "
This 'update' will never match !!! Here is the radippool's current line: " mysql> select * from radippool;
+----+-----------+-----------------+--------------+-----------------+---- --------------+---------------------+----------+----------+
| id | pool_name | FramedIPAddress | NASIPAddress | CalledStationId |
CallingStationId | expiry_time | UserName | pool_key | +----+-----------+-----------------+--------------+-----------------+---- --------------+---------------------+----------+----------+
| 1 | mypool | 192.168.0.1 | | | | | 0000-00-00 00:00:00 | | |
+----+-----------+-----------------+--------------+-----------------+---- --------------+---------------------+----------+----------+ 1 row in set (0.00 sec) "
In radgroupcheck: " In (mysql) radgroupcheck :
+-----+-------------------+------------------+----+-------------+------+
| id | GroupName | Attribute | op | Value | prio |
+-----+-------------------+------------------+----+-------------+------+
| 39 | mygroup | Pool-Name | := | mypool | 1 |
... "
Thanks. - -- - ---------------------------- Roberto Gonzalez Azevedo -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux)
iD8DBQFFM/aEF+EMwkXLsEwRAsmvAKC5gV8xZN8g4gxZwrdpVQi7eTCzHgCdEfNn 1O0G6WtRGvyLBter1vtzQSk= =z+5W -----END PGP SIGNATURE----- - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc