##
## The database schemas are available at:
##
-## doc/examples/*.sql
+## raddb/sql/DB/ippool.sql
##
## $Id$
-sqlippool sqlippool {
+sqlippool {
- ################################################
- ## SQL instance to use (from postgresql.conf) ##
- ################################################
+ #########################################
+ ## SQL instance to use (from sql.conf) ##
+ ##
+ ## If you have multiple sql instances, such as "sql sql1 {...}",
+ ## use the *instance* name here: sql1.
+ #########################################
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)
- ## 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
## 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}"
################################################################
- ## MySQL specific queries. ##
+ #
+ # WARNING: MySQL has certain limitations that means it can
+ # hand out the same IP address to 2 different users.
+ #
+ # We suggest using an SQL DB with proper transaction
+ # support, such as PostgreSQL, or using MySQL
+ # with InnoDB.
+ #
################################################################
- ## 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 = '%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 = '%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() + 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
- ## 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() - INTERVAL 1 SECOND \
- 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() + 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() - 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}'"
-
- ## 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() + 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}'"
-
-
- ## 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() - 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() - INTERVAL 1 SECOND \
- WHERE NASIPAddress = '%{NAS-IP-Address}'"
+ #
+ # Use the same database as configured in the "sql" module, "database"
+ # configuration item. Change the "postgresql" name below to be the
+ # same as the "database" field of the SQL module referred to in the
+ # "sql-instance-name", above.
+ #
+$INCLUDE sql/postgresql/ippool.conf
+
+ ## Logging configuration. (Comment out to disable logging)
+ 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 %{control: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 %{control: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})"
}
projects / freeradius.git / blobdiff