sqlippool + MySQL

16 Oct 2006

      -----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-----