SQL IP Pool maximum timeout.

[Dave]

Hugh Messenger wrote:
> Dave <dave at optionsdsl.ca> said:
>   
>> I use the sqlippool setup for handling IP pools, and it works well,
>> except I want to rid of the expiry time, (maximum timeout=0). right now
>> its setting for 24 hours, and then it cleans itself out, and then
>> freeradius starts handing out already assigned/used IP addresses.  Im
>> not sure where to put the maximum timeout=0 value when using sqlippool.
>>     
>
> That shouldn't happen, regardless of the expiry time.  The expiry_time value
> in the radippool entries is derived from the 'lease-duraction' set in
> sqlippool.conf.  But the expiry_time should only affect clearing unique
> sessions which have gotten "stuck" (like for lost 'stop' packets).  It
> shouldn't just start handing out in-use IP's based on expiry time.
>
> What do you have $pool-key set to in sqlippool.conf?  And are you sure
> whatever value you are using (usually either NAS-Port or Calling-Station-Id)
> is a unique value from the NAS?
>   
  I have pool-key = "%{Calling-Station-Id}", which I just realized is not always unique, (NAS is returning MAC address for Calling station ID, which if passing thru one of my bridge devices always returns the MAC address of the ethernet bridge)



> What flavor of db do you have - postgres or mysql?  They use different
> configs, and depending where you got the query file from, you may have an
> earlier broken version (especially if you are using MySQL).
>
> What version of freeradius are you running?
>
>   
 Im using mysql, and I believe I have a working version of 
sqlippool.conf, Ill paste it here.   Freeradius 1.1.6

sqlippool.conf 
*******************************************************************
sqlippool {

## SQL instance to use (from sql.conf)
sql-instance-name = "sql"

## Table to keep ippool info
ippool_table = "radippool"

## lease_duration. fix for lost acc-stop packets
lease-duration = 3600

## Attribute which should be considered unique per NAS
## Using NAS-Port gives behaviour similar to rlm_ippool. 
Calling-Station-Id is for NAS that send fixed NAS-Port
# pool-key = "%{NAS-Port}"
 pool-key = "%{Calling-Station-Id}"

## Logging configuration.
sqlippool_log_exists = "Existing IP: %{reply:Framed-IP-Address} \
 (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} 
user %{User-Name})"

sqlippool_log_success = "Allocated IP: %{reply:Framed-IP-Address} from 
%{check:Pool-Name} \
 (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} 
user %{User-Name})"

sqlippool_log_clear = "Released IP %{Framed-IP-Address}\
(did %{Called-Station-Id} cli %{Calling-Station-Id} user %{User-Name})"

sqlippool_log_failed = "IP Allocation FAILED from %{check:Pool-Name} \
 (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} 
user %{User-Name})"

sqlippool_log_nopool = "No Pool-Name defined \
 (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} 
user %{User-Name})"

# ## This series of queries allocates an IP address
# allocate-clear = "UPDATE ${ippool_table} \
#  SET nasipaddress = '', pool_key = 0, callingstationid = '', username 
= '', \
#  expiry_time = '0000-00-00 00:00:00' \
#  WHERE pool_key = '${pool-key}'"

## This will clear all expired leases for lost acc-stop packets
allocate-clear = "UPDATE radippool \
 SET nasipaddress = '', pool_key = 0, callingstationid = '', username = 
'', \
 expiry_time = '0000-00-00 00:00:00' \
 WHERE expiry_time <= NOW() - INTERVAL 1 SECOND"


# ## The ORDER BY clause of this query tries to allocate the same IP-address
# ## which user had last session...
# allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
#  WHERE pool_name = '%{check:Pool-Name}' AND expiry_time < NOW() \
#  ORDER BY (username <> '%{User-Name}'), (callingstationid <> 
'%{Calling-Station-Id}'), expiry_time \
#  LIMIT 1 \
#  FOR UPDATE"

## If you prefer to allocate a random IP address every time, use this 
query instead
allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
 WHERE pool_name = '%{check:Pool-Name}' AND expiry_time = '0000-00-00 
00:00:00' \
 ORDER BY RAND() \
 LIMIT 1 \
 FOR UPDATE"


## If an IP could not be allocated, check to see if the pool exists or not
## This allows the module to differentiate between a full pool and no pool
## Note: If you are not running redundant pool modules this query may be 
commented
## out to save running this query every time an ip is not allocated.
pool-check = "SELECT id FROM ${ippool_table} WHERE 
pool_name='%{check:Pool-Name}' LIMIT 1"


allocate-update = "UPDATE ${ippool_table} \
 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 = '%I'"


## This series of queries frees an IP number when an accounting
## START record arrives
start-update = "UPDATE ${ippool_table} \
 SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
 WHERE nasipaddress = '%{NAS-IP-Address}' AND  pool_key = '${pool-key}'"

## This series of queries frees an IP number when an accounting
## STOP record arrives
stop-clear = "UPDATE ${ippool_table} \
 SET nasipaddress = '', pool_key = 0, callingstationid = '', username = 
'', \
 expiry_time = '0000-00-00 00:00:00' \
 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 ${ippool_table} \
 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 ${ippool_table} \
 SET nasipaddress = '', pool_key = 0, callingstationid = '', username = 
'', \
 expiry_time = '0000-00-00 00:00:00' \
 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 ${ippool_table} \
 SET nasipaddress = '', pool_key = 0, callingstationid = '', username = 
'', \
 expiry_time = '0000-00-00 00:00:00' \
 WHERE nasipaddress = '%{Nas-IP-Address}' AND username = '%{User-Name}' \
 AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = 
'%{Framed-IP-Address}'"

}

************************************************************************