rename sqlippool.conf to postgresqlippool.conf and add MySQL version as sqlippool...

diff --git a/raddb/postgresqlippool.conf b/raddb/postgresqlippool.conf
new file mode 100644 (file)
index 0000000..802582e
--- /dev/null
+++ b/raddb/postgresqlippool.conf
@@ -0,0 +1,122 @@
+##  Configuration for the SQL based IP Pool module (rlm_sqlippool)
+##
+##  The database schemas are available at:
+##
+##       doc/examples/*.sql
+##
+##  $Id$
+
+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}'"
+
+}

diff --git a/raddb/sqlippool.conf b/raddb/sqlippool.conf
index 9698f08..f88db12 100644 (file)
--- a/raddb/sqlippool.conf
+++ b/raddb/sqlippool.conf
@@ -17,6 +17,8 @@ sqlippool sqlippool {
  ippool_table = "radippool"
 
  ## IP lease duration. (Leases expire even if Acct Stop packet is lost)
+ ## NOTE that for lease-duration to work as expected, you must enable
+ ## Interim Accounting packets from the NAS.
  lease-duration = 3600
 
  ## Attribute which should be considered unique per NAS
@@ -24,40 +26,37 @@ sqlippool sqlippool {
  ## 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}"
-
+ #pool-key = "%{Calling-Station-Id}"
 
  ################################################################
- ## PostgreSQL specific queries.                               ##
- ## The 2.x release supports other SQL dialects.               ##
+ ## MySQL 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 \
+ allocate-find = "SELECT FramedIPAddress FROM ${ippool_table} \
+  WHERE pool_name = '%P' AND expiry_time < NOW() \
+  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() \
+ ## 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 = '%P' AND expiry_time < NOW() \
+ # ORDER BY RAND() \
  # 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'"
-
+  SET NASIPAddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
+  CallingStationId = '%{Calling-Station-Id}', UserName = '%{SQL-User-Name}', \
+  expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
+  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
@@ -68,56 +67,53 @@ sqlippool sqlippool {
  ## 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 \
+  SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \
+  expiry_time = NOW() - INTERVAL 1 SECOND \
   WHERE pool_key = '${pool-key}' \
-  AND nasipaddress = '%{Nas-IP-Address}'"
+  AND NASIPAddress = '%{NAS-IP-Address}'"
 
 
- ## This query extends an IP address lease by "lease-duration" when an accounting
- ## START record arrives
+ ## 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}'"
-
+  SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
+  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}' \
+  SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \
+  expiry_time = NOW() - INTERVAL 1 SECOND \
+  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}'"
-
+  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
+ ## 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}' \
+  SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
+  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}'"
+  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}'"
-
+  SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \
+  expiry_time = NOW() - INTERVAL 1 SECOND \
+  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}'"
+  SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \
+  expiry_time = NOW() - INTERVAL 1 SECOND \
+  WHERE NASIPAddress = '%{NAS-IP-Address}'"
 
 }