3 ## ippool.conf -- PostgreSQL queries for rlm_sqlippool
7 ## This query allocates an IP address from the Pool
8 ## The ORDER BY clause of this query tries to allocate the same IP-address
9 ## to the user that they had last session...
10 allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
11 WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < 'now'::timestamp(0) \
12 ORDER BY (username <> '%{SQL-User-Name}'), \
13 (callingstationid <> '%{Calling-Station-Id}'), expiry_time \
17 ## If you prefer to allocate a random IP address every time, use this query instead
18 #allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
19 # WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < 'now'::timestamp(0) \
25 ## If an IP could not be allocated, check to see whether the pool exists or not
26 ## This allows the module to differentiate between a full pool and no pool
27 ## Note: If you are not running redundant pool modules this query may be commented
28 ## out to save running this query every time an ip is not allocated.
29 pool-check = "SELECT id FROM ${ippool_table} \
30 WHERE pool_name='%{control:Pool-Name}' LIMIT 1"
33 ## This query marks the IP address handed out by "allocate-find" as used
34 ## for the period of "lease-duration" after which time it may be reused.
35 allocate-update = "UPDATE ${ippool_table} \
36 SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
37 callingstationid = '%{Calling-Station-Id}', username = '%{SQL-User-Name}', \
38 expiry_time = 'now'::timestamp(0) + '${lease-duration} second'::interval \
39 WHERE framedipaddress = '%I'"
42 ## This query frees the IP address assigned to "pool-key" when a new request
43 ## comes in for the same "pool-key". This means that either you are losing
44 ## accounting Stop records or you use Calling-Station-Id instead of NAS-Port
45 ## as your "pool-key" and your users are able to reconnect before your NAS
46 ## has timed out their previous session. (Generally on wireless networks)
47 ## (Note: If your pool-key is set to Calling-Station-Id and not NAS-Port
48 ## then you may wish to delete the "AND nasipaddress = '%{Nas-IP-Address}'
49 ## from the WHERE clause)
50 allocate-clear = "UPDATE ${ippool_table} \
51 SET nasipaddress = '', pool_key = 0, callingstationid = '', \
52 expiry_time = 'now'::timestamp(0) - '1 second'::interval \
53 WHERE nasipaddress = '%{NAS-IP-Address}' \
54 AND pool_key = '${pool-key}'"
57 ## This query extends an IP address lease by "lease-duration" when an accounting
58 ## START record arrives
59 start-update = "UPDATE ${ippool_table} \
60 SET expiry_time = 'now'::timestamp(0) + '${lease-duration} second'::interval \
61 WHERE nasipaddress = '%{NAS-IP-Address}' \
62 AND pool_key = '${pool-key}'"
65 ## This query frees an IP address when an accounting
66 ## STOP record arrives
67 stop-clear = "UPDATE ${ippool_table} \
68 SET nasipaddress = '', pool_key = 0, callingstationid = '', \
69 expiry_time = 'now'::timestamp(0) - '1 second'::interval \
70 WHERE nasipaddress = '%{Nas-IP-Address}' \
71 AND pool_key = '${pool-key}' \
72 AND username = '%{SQL-User-Name}' \
73 AND callingstationid = '%{Calling-Station-Id}' \
74 AND framedipaddress = '%{Framed-IP-Address}'"
77 ## This query extends an IP address lease by "lease-duration" when an accounting
78 ## ALIVE record arrives
79 alive-update = "UPDATE ${ippool_table} \
80 SET expiry_time = 'now'::timestamp(0) + '${lease-duration} seconds'::interval \
81 WHERE nasipaddress = '%{Nas-IP-Address}' \
82 AND pool_key = '${pool-key}' \
83 AND framedipaddress = '%{Framed-IP-Address}' \
84 AND username = '%{SQL-User-Name}' \
85 AND callingstationid = '%{Calling-Station-Id}'"
88 ## This query frees all IP addresses allocated to a NAS when an
89 ## accounting ON record arrives from that NAS
90 on-clear = "UPDATE ${ippool_table} \
91 SET nasipaddress = '', pool_key = 0, callingstationid = '', \
92 expiry_time = 'now'::timestamp(0) - '1 second'::interval \
93 WHERE nasipaddress = '%{Nas-IP-Address}'"
96 ## This query frees all IP addresses allocated to a NAS when an
97 ## accounting OFF record arrives from that NAS
98 off-clear = "UPDATE ${ippool_table} \
99 SET nasipaddress = '', pool_key = 0, callingstationid = '', \
100 expiry_time = 'now'::timestamp(0) - '1 second'::interval \
101 WHERE nasipaddress = '%{Nas-IP-Address}'"