5 # SQL connection information
7 sql-instance-name = "sql"
9 # lease_duration. fix for lost acc-stop packets
12 # Attribute which should be considered unique per NAS
13 pool-key = "%{NAS-Port}"
14 # pool-key = "%{Calling-Station-Id}"
18 # This series of queries allocates an IP address
20 allocate-clear = "UPDATE radippool \
21 SET nas_ip_address = '', pool_key = 0, calling_station_id = '', \
22 expiry_time = 'now'::timestamp(0) - '1 second'::interval \
23 WHERE pool_key = '%{Calling-Station-Id}'"
25 # note the ORDER BY clause of next query, it'll try to allocate IPs
26 # like Cisco internal pools do - it _trys_ to allocate the same IP-address
27 # which user had last session...
28 allocate-find = "SELECT ip_address FROM radippool \
29 WHERE pool_name = '%{reply:Pool-Name}' AND expiry_time < 'now'::timestamp(0) \
30 ORDER BY pool_name, (username <> '%{User-Name}'), (calling_station_id <> '%{Calling-Station-Id}'), expiry_time \
34 allocate-update = "UPDATE radippool \
35 SET nas_ip_address = '%{NAS-IP-Address}', pool_key = '%{Calling-Station-Id}', \
36 calling_station_id = '%{Calling-Station-Id}', username = '%{User-Name}', \
37 expiry_time = 'now'::timestamp(0) + '${lease-duration} second'::interval \
38 WHERE ip_address = '%I'"
43 # This series of queries frees an IP number when an accounting
44 # START record arrives
46 start-update = "UPDATE radippool \
47 SET expiry_time = 'now'::timestamp(0) + '%J second'::interval \
48 WHERE nas_ip_address = '%n' AND nas_port = '%p' AND pool_name = '%P'"
51 # This series of queries frees an IP number when an accounting
54 stop-clear = "UPDATE radippool \
55 SET nas_ip_address = '', pool_key = 0, calling_station_id = '', \
56 expiry_time = 'now'::timestamp(0) - '1 second'::interval \
57 WHERE nas_ip_address = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' AND username = '%{User-Name}' \
58 AND calling_station_id = '%{Calling-Station-Id}' AND ip_address = '%{Framed-IP-Address}'"
64 # This series of queries frees an IP number when an accounting
65 # ALIVE record arrives
67 alive-update = "UPDATE radippool \
68 SET expiry_time = 'now'::timestamp(0) + '${lease-duration} seconds'::interval \
69 WHERE nas_ip_address = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' AND username = '%{User-Name}' \
70 AND calling_station_id = '%{Calling-Station-Id}' AND ip_address = '%{Framed-IP-Address}'"
74 # This series of queries frees the IP numbers allocate to a
75 # NAS when an accounting ON record arrives
77 on-clear = "UPDATE radippool \
78 SET nas_ip_address = '', pool_key = 0, calling_station_id = '', \
79 expiry_time = 'now'::timestamp(0) - '1 second'::interval \
80 WHERE nas_ip_address = '%{Nas-IP-Address}' AND username = '%{User-Name}' \
81 AND calling_station_id = '%{Calling-Station-Id}' AND ip_address = '%{Framed-IP-Address}'"
84 # This series of queries frees the IP numbers allocate to a
85 # NAS when an accounting OFF record arrives
87 off-clear = "UPDATE radippool \
88 SET nas_ip_address = '', pool_key = 0, calling_station_id = '', \
89 expiry_time = 'now'::timestamp(0) - '1 second'::interval \
90 WHERE nas_ip_address = '%{Nas-IP-Address}' AND username = '%{User-Name}' \
91 AND calling_station_id = '%{Calling-Station-Id}' AND ip_address = '%{Framed-IP-Address}'"