sqlippool + MySQL

[Jan Mulders]

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