Pre-allocation and association with framedipaddress and username in radius.radippool and effect if entry missing after a successful authentication

[Sophie Loewenthal]

Hi,

     We preallocate IP addresses with usernames into radius.radippool.  
Our provisioning script runs every 5 mins, extracting new usernames from 
our application and then associates a framedipaddress with the new 
username in the correct pool based on their APN set in our application.

The SQL in ippool.conf has been modified, so for example,
allocate-clear : has the  username = '' removed from the query because 
we want to keep the username/ipaddress associated.
alocat-find: has  AND username = '%{User-Name}'
Please find a copy of ippool.conf incldued below.

This modified SQL should not return an IP address if the username is not 
associated with a framedipaddress even if the user is in radius.radcheck.

How should radius respond if a user authenticates correctly and is in 
radcheck table, but did not have a username+framedipaddress in 
radius.radippool?

Should I expect to see a message in the logs like:
Wed Dec  2 10:37:59 2015 : Info: IP Allocation FAILED from 
proximus-carli   (did proximus.trimbletl.com cli 327711110064161 port 
23333 user 344544001217220)
Wed Dec  2 10:44:42 2015 : Info: IP Allocation FAILED from vodafone   
(did vodafone.trimbletl.com cli 31600044278 port 824256 user 
344466031488608)

Sophie



# grep -v ^# ippool.conf
allocate-clear = "UPDATE ${ippool_table} /* allocate-clear */ \
  SET nasipaddress = '', pool_key = 0, \
  callingstationid = '', calledstationid = '', \
  expiry_time = NULL, 3GPP_Imsi = '' \
  WHERE pool_key = '${pool-key}'"

  allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
  WHERE pool_name = '%{control:Pool-Name}' \
  AND username = '%{User-Name}' LIMIT 1"

pool-check = "SELECT id FROM ${ippool_table} /* pool-check */ \
  WHERE pool_name='%{control:Pool-Name}' LIMIT 1"

allocate-update = "UPDATE ${ippool_table} /* allocate-update */ \
  SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
  callingstationid = '%{Calling-Station-Id}', \
  expiry_time = NOW() + INTERVAL ${lease-duration} SECOND, \
  3GPP_Imsi = '%{3GPP-IMSI}', calledstationid = '%{Called-Station-Id}' \
  WHERE framedipaddress = '%I' AND username = '%{User-Name}'"

start-update = "UPDATE ${ippool_table} /* start-update */ \
  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}'"

stop-clear = "UPDATE ${ippool_table} /* stop-clear */ \
  SET nasipaddress = '', pool_key = 0, callingstationid = '', \
  expiry_time = NULL, 3GPP_Imsi = '', calledstationid = '' \
  WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' \
  AND username = '%{User-Name}' \
  AND callingstationid = '%{Calling-Station-Id}' \
  AND framedipaddress = '%{Framed-IP-Address}'"

alive-update = "UPDATE ${ippool_table} /* alive-update */ \
  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}'"

on-clear = "UPDATE ${ippool_table} /* on-clear */ \
  SET nasipaddress = '', pool_key = 0, callingstationid = '', \
  expiry_time = NULL, 3GPP_Imsi = '', calledstationid = '' \
  WHERE nasipaddress = '%{Nas-IP-Address}'"

off-clear = "UPDATE ${ippool_table} /* off-clear */ \
  SET nasipaddress = '', pool_key = 0, callingstationid = '', \
  expiry_time = NULL, 3GPP_Imsi = '', calledstationid = '' \
  WHERE nasipaddress = '%{Nas-IP-Address}'"