1 ## Configuration for the SQL based IP Pool module (rlm_sqlippool)
3 ## The database schemas are available at:
11 ################################################
12 ## SQL instance to use (from postgresql.conf) ##
13 ################################################
14 sql-instance-name = "sql"
16 ## SQL table to use for ippool range and lease info
17 ippool_table = "radippool"
19 ## IP lease duration. (Leases expire even if Acct Stop packet is lost)
20 ## NOTE that for lease-duration to work as expected, you must enable
21 ## Interim Accounting packets from the NAS.
24 ## Attribute which should be considered unique per NAS
25 ## Using NAS-Port gives behaviour similar to rlm_ippool. (And ACS)
26 ## Using Calling-Station-Id works for NAS that send fixed NAS-Port
27 ## ONLY change this if you know what you are doing!
28 pool-key = "%{NAS-Port}"
29 #pool-key = "%{Calling-Station-Id}"
31 ################################################################
32 ## MySQL specific queries. ##
33 ################################################################
35 ## This query allocates an IP address from the Pool
36 ## The ORDER BY clause of this query tries to allocate the same IP-address
37 ## to the user that they had last session...
38 allocate-find = "SELECT FramedIPAddress FROM ${ippool_table} \
39 WHERE pool_name = '%P' AND expiry_time < NOW() \
40 ORDER BY (UserName <> '%{SQL-User-Name}'), \
41 (CallingStationId <> '%{Calling-Station-Id}'), expiry_time \
45 ## If you prefer to allocate a random IP address every time,
46 ## use this query instead
47 #allocate-find = "SELECT FramedIPAddress FROM ${ippool_table} \
48 # WHERE pool_name = '%P' AND expiry_time < NOW() \
53 ## This query marks the IP address handed out by "allocate-find" as used
54 ## for the period of "lease-duration" after which time it may be reused.
55 allocate-update = "UPDATE ${ippool_table} \
56 SET NASIPAddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
57 CallingStationId = '%{Calling-Station-Id}', UserName = '%{SQL-User-Name}', \
58 expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
59 WHERE FramedIPAddress = '%I'"
61 ## This query frees the IP address assigned to "pool-key" when a new request
62 ## comes in for the same "pool-key". This means that either you are losing
63 ## accounting Stop records or you use Calling-Station-Id instead of NAS-Port
64 ## as your "pool-key" and your users are able to reconnect before your NAS
65 ## has timed out their previous session. (Generally on wireless networks)
66 ## (Note: If your pool-key is set to Calling-Station-Id and not NAS-Port
67 ## then you may wish to delete the "AND nasipaddress = '%{Nas-IP-Address}'
68 ## from the WHERE clause)
69 allocate-clear = "UPDATE ${ippool_table} \
70 SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \
71 expiry_time = NOW() - INTERVAL 1 SECOND \
72 WHERE pool_key = '${pool-key}' \
73 AND NASIPAddress = '%{NAS-IP-Address}'"
76 ## This query extends an IP address lease by "lease-duration" when an
77 ## accounting START record arrives
78 start-update = "UPDATE ${ippool_table} \
79 SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
80 WHERE NASIPAddress = '%{NAS-IP-Address}' \
81 AND pool_key = '${pool-key}'"
83 ## This query frees an IP address when an accounting
84 ## STOP record arrives
85 stop-clear = "UPDATE ${ippool_table} \
86 SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \
87 expiry_time = NOW() - INTERVAL 1 SECOND \
88 WHERE NASIPAddress = '%{NAS-IP-Address}' \
89 AND pool_key = '${pool-key}' \
90 AND UserName = '%{SQL-User-Name}' \
91 AND CallingStationId = '%{Calling-Station-Id}' \
92 AND FramedIPAddress = '%{Framed-IP-Address}'"
94 ## This query extends an IP address lease by "lease-duration" when an
95 ## accounting ALIVE record arrives
96 alive-update = "UPDATE ${ippool_table} \
97 SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
98 WHERE NASIPAddress = '%{Nas-IP-Address}' \
99 AND pool_key = '${pool-key}' \
100 AND UserName = '%{SQL-User-Name}' \
101 AND CallingStationId = '%{Calling-Station-Id}' \
102 AND FramedIPAddress = '%{Framed-IP-Address}'"
105 ## This query frees all IP addresses allocated to a NAS when an
106 ## accounting ON record arrives from that NAS
107 on-clear = "UPDATE ${ippool_table} \
108 SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \
109 expiry_time = NOW() - INTERVAL 1 SECOND \
110 WHERE NASIPAddress = '%{NAS-IP-Address}'"
112 ## This query frees all IP addresses allocated to a NAS when an
113 ## accounting OFF record arrives from that NAS
114 off-clear = "UPDATE ${ippool_table} \
115 SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \
116 expiry_time = NOW() - INTERVAL 1 SECOND \
117 WHERE NASIPAddress = '%{NAS-IP-Address}'"