Trouble "SQL Based IP Pool"

[Alan DeKok]

Leander S. wrote:
> this is how my radius.conf looks like in the IPPOOL section:

  The SQL IPpool module in 1.1.7 is broken.  Don't use it.

> ##################################################################################
> 
> ##################################################################################
> 
> 
>        ippool main_pool {
> 
>                #  range-start,range-stop: The start and end ip
>                #  addresses for the ip pool
> #               range-start = 192.168.1.1
> #               range-stop = 192.168.3.254
> 
>                #  netmask: The network mask used for the ip's
> #               netmask = 255.255.255.0
> 
>                #  cache-size: The gdbm cache size for the db
>                #  files. Should be equal to the number of ip's
>                #  available in the ip pool
> #               cache-size = 800
> 
>                # session-db: The main db file used to allocate ip's to
> clients
> #               session-db = ${raddbdir}/db.ippool
> 
>                # ip-index: Helper db index file used in multilink
> #               ip-index = ${raddbdir}/db.ipindex
> 
>                # override: Will this ippool override a Framed-IP-Address
> already set
> #               override = no
> 
>                # maximum-timeout: If not zero specifies the maximum time
> in seconds an
>                # entry may be active. Default: 0
> #               maximum-timeout = 0
>        }
> 
> #        $INCLUDE  ${confdir}/sqlippool.conf
> 
>        $INCLUDE  ${confdir}/postgresqlippool.conf
> 
> #        OTP token support.  Not included by default.
> #        $INCLUDE  ${confdir}/otp.conf
> 
> ##################################################################################
> 
> ##################################################################################
> 
> 
> 
> 
> 
> 
> and this is my postgresqlippool.conf which I'm using - it's left original.
> 
> 
> 
> 
> 
> 
> ##  Configuration for the SQL based IP Pool module (rlm_sqlippool)
> ##
> ##  The database schemas are available at:
> ##
> ##       doc/examples/*.sql
> ##
> ##  $Id: postgresqlippool.conf,v 1.1.2.1 2007/07/16 06:35:22 pnixon Exp $
> 
> sqlippool sqlippool {
> 
> ################################################
> ## SQL instance to use (from postgresql.conf) ##
> ################################################
> sql-instance-name = "sql"
> 
> ## SQL table to use for ippool range and lease info
> ippool_table = "radippool"
> 
> ## IP lease duration. (Leases expire even if Acct Stop packet is lost)
> lease-duration = 3600
> 
> ## Attribute which should be considered unique per NAS
> ## Using NAS-Port gives behaviour similar to rlm_ippool. (And ACS)
> ## Using Calling-Station-Id works for NAS that send fixed NAS-Port
> ## ONLY change this if you know what you are doing!
> pool-key = "%{NAS-Port}"
> #pool-key = "%{Calling-Station-Id}"
> 
> 
> ###########################################
> ## PostgreSQL specific queries.          ##
> ###########################################
> 
> ## This query allocates an IP address from the Pool
> ## The ORDER BY clause of this query tries to allocate the same IP-address
> ## to the user that they had last session...
> allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
>  WHERE pool_name = '%{check:Pool-Name}' AND expiry_time <
> 'now'::timestamp(0) \
>  ORDER BY (username <> '%{SQL-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 <
> 'now'::timestamp(0) \
> # ORDER BY RANDOM() \
> # LIMIT 1 \
> # FOR UPDATE"
> 
> 
> ## This query marks the IP address handed out by "allocate-find" as used
> ## for the period of "lease-duration" after which time it may be reused.
> allocate-update = "UPDATE ${ippool_table} \
>  SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
>  callingstationid = '%{Calling-Station-Id}', username =
> '%{SQL-User-Name}', \
>  expiry_time = 'now'::timestamp(0) + '${lease-duration} second'::interval \
>  WHERE framedipaddress = '%I'"
> 
> 
> ## This query frees the IP address assigned to "pool-key" when a new
> request
> ## comes in for the same "pool-key". This means that either you are losing
> ## accounting Stop records or you use Calling-Station-Id instead of
> NAS-Port
> ## as your "pool-key" and your users are able to reconnect before your NAS
> ## has timed out their previous session. (Generally on wireless networks)
> ## (Note: If your pool-key is set to Calling-Station-Id and not NAS-Port
> ## then you may wish to delete the "AND nasipaddress = '%{Nas-IP-Address}'
> ## from the WHERE clause)
> allocate-clear = "UPDATE ${ippool_table} \
>  SET nasipaddress = '', pool_key = 0, callingstationid = '', \
>  expiry_time = 'now'::timestamp(0) - '1 second'::interval \
>  WHERE pool_key = '${pool-key}' \
>  AND nasipaddress = '%{Nas-IP-Address}'"
> 
> 
> ## This query extends an IP address lease by "lease-duration" when an
> accounting
> ## START record arrives
> start-update = "UPDATE ${ippool_table} \
>  SET expiry_time = 'now'::timestamp(0) + '${lease-duration}
> second'::interval \
>  WHERE nasipaddress = '%{NAS-IP-Address}' \
>  AND  pool_key = '${pool-key}'"
> 
> 
> ## This query frees an IP address when an accounting
> ## STOP record arrives
> stop-clear = "UPDATE ${ippool_table} \
>  SET nasipaddress = '', pool_key = 0, callingstationid = '', \
>  expiry_time = 'now'::timestamp(0) - '1 second'::interval \
>  WHERE nasipaddress = '%{Nas-IP-Address}' \
>  AND pool_key = '${pool-key}' \
>  AND username = '%{SQL-User-Name}' \
>  AND callingstationid = '%{Calling-Station-Id}' \
>  AND framedipaddress = '%{Framed-IP-Address}'"
> 
> 
> ## This query extends an IP address lease by "lease-duration" when an
> accounting
> ## ALIVE record arrives
> alive-update = "UPDATE ${ippool_table} \
>  SET expiry_time = 'now'::timestamp(0) + '${lease-duration}
> seconds'::interval \
>  WHERE nasipaddress = '%{Nas-IP-Address}' \
>  AND pool_key = '${pool-key}' \
>  AND username = '%{SQL-User-Name}' \
>  AND callingstationid = '%{Calling-Station-Id}' \
>  AND framedipaddress = '%{Framed-IP-Address}'"
> 
> 
> ## This query frees all IP addresses allocated to a NAS when an
> ## accounting ON record arrives from that NAS
> on-clear = "UPDATE ${ippool_table} \
>  SET nasipaddress = '', pool_key = 0, callingstationid = '', \
>  expiry_time = 'now'::timestamp(0) - '1 second'::interval \
>  WHERE nasipaddress = '%{Nas-IP-Address}'"
> 
> 
> ## This query frees all IP addresses allocated to a NAS when an
> ## accounting OFF record arrives from that NAS
> off-clear = "UPDATE ${ippool_table} \
>  SET nasipaddress = '', pool_key = 0, callingstationid = '', \
>  expiry_time = 'now'::timestamp(0) - '1 second'::interval \
>  WHERE nasipaddress = '%{Nas-IP-Address}'"
> 
> }
> 
> 
> 
> 
> 
> 
> Thank you for helping
> 
> 
> 
> Regards,
> 
>     Leander
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
> 
>