+## Configuration for the SQL based IP Pool module (rlm_sqlippool)
+##
+## The database schemas are available at:
+##
+## doc/examples/*.sql
+##
+## $Id$
sqlippool sqlippool {
- #
- # SQL connection information
- #
+ #########################################
+ ## SQL instance to use (from sql.conf) ##
+ #########################################
sql-instance-name = "sql"
- # lease_duration. fix for lost acc-stop packets
+ ## 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
+ ## 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}"
- #
- # This series of queries allocates an IP address
- #
- allocate-clear = "UPDATE radippool \
- SET nasipaddress = '', pool_key = 0, callingstationid = '', \
- expiry_time = 'now'::timestamp(0) - '1 second'::interval \
- WHERE pool_key = '%{Calling-Station-Id}'"
-
- # 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'::timestamp(0) \
- ORDER BY pool_name, (username <> '%{User-Name}'), (callingstationid <> '%{Calling-Station-Id}'), expiry_time \
+ ################################################################
+ ## PostgreSQL specific queries. ##
+ ## The 2.x release supports other SQL dialects. ##
+ ################################################################
+
+ ## 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"
- allocate-update = "UPDATE radippool \
- SET nasipaddress = '%{NAS-IP-Address}', pool_key = '%{Calling-Station-Id}', \
- callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', \
- expiry_time = 'now'::timestamp(0) + '${lease-duration} second'::interval \
- WHERE framedipaddress = '%I'"
+ ## 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 series of queries frees an IP number when an accounting
- # START record arrives
- #
- start-update = "UPDATE radippool \
- SET expiry_time = 'now'::timestamp(0) + '%J second'::interval \
- WHERE nasipaddress = '%n' AND nas_port = '%p' AND pool_name = '%P'"
- #
- # This series of queries frees an IP number when an accounting
- # STOP record arrives
- #
- stop-clear = "UPDATE radippool \
+ ## 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 nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' AND username = '%{User-Name}' \
- AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"
+ 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 series of queries frees an IP number when an accounting
- # ALIVE record arrives
- #
- alive-update = "UPDATE radippool \
+
+ ## 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 = '%{User-Name}' \
- AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"
+ 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 series of queries frees the IP numbers allocate to a
- # NAS when an accounting ON record arrives
- #
- on-clear = "UPDATE radippool \
+ ## 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}' 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 \
+ 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}' AND username = '%{User-Name}' \
- AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"
-
+ WHERE nasipaddress = '%{Nas-IP-Address}'"
}
-