1 # Configuration for the SQL based IPPool module (rlm_sqlippool)
3 # The database schemas are available at:
11 ## SQL instance to use (from sql.conf)
12 sql-instance-name = "sql"
14 ## Table to keep ippool info
15 ippool_table = "radippool"
17 ## lease_duration. fix for lost acc-stop packets
20 ## Attribute which should be considered unique per NAS
21 ## Using NAS-Port gives behaviour similar to rlm_ippool. Calling-Station-Id is for NAS that send fixed NAS-Port
22 pool-key = "%{NAS-Port}"
23 # pool-key = "%{Calling-Station-Id}"
25 ## Logging configuration.
26 sqlippool_log_exists = "Existing IP: %{reply:Framed-IP-Address} \
27 (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
29 sqlippool_log_success = "Allocated IP: %{reply:Framed-IP-Address} from %{check:Pool-Name} \
30 (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
32 sqlippool_log_clear = "Released IP %{Framed-IP-Address}\
33 (did %{Called-Station-Id} cli %{Calling-Station-Id} user %{User-Name})"
35 sqlippool_log_failed = "IP Allocation FAILED from %{check:Pool-Name} \
36 (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
38 sqlippool_log_nopool = "No Pool-Name defined \
39 (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
41 ## This series of queries allocates an IP address
42 allocate-clear = "UPDATE ${ippool_table} \
43 SET nasipaddress = '', pool_key = 0, callingstationid = '', \
44 expiry_time = 'now'::timestamp(0) - '1 second'::interval \
45 WHERE pool_key = '${pool-key}'"
47 ## The ORDER BY clause of this query tries to allocate the same IP-address
48 ## which user had last session...
49 allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
50 WHERE pool_name = '%{check:Pool-Name}' AND expiry_time < 'now'::timestamp(0) \
51 ORDER BY (username <> '%{SQL-User-Name}'), (callingstationid <> '%{Calling-Station-Id}'), expiry_time \
55 ## If you prefer to allocate a random IP address every time, use this query instead
56 #allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
57 # WHERE pool_name = '%P' AND expiry_time < 'now'::timestamp(0) \
63 ## If an IP could not be allocated, check to see if the pool exists or not
64 ## This allows the module to differentiate between a full pool and no pool
65 ## Note: If you are not running redundant pool modules this query may be commented
66 ## out to save running this query every time an ip is not allocated.
67 pool-check = "SELECT id FROM ${ippool_table} WHERE pool_name='%{check:Pool-Name}' LIMIT 1"
70 allocate-update = "UPDATE ${ippool_table} \
71 SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
72 callingstationid = '%{Calling-Station-Id}', username = '%{SQL-User-Name}', \
73 expiry_time = 'now'::timestamp(0) + '${lease-duration} second'::interval \
74 WHERE framedipaddress = '%I'"
77 ## This series of queries frees an IP number when an accounting
78 ## START record arrives
79 start-update = "UPDATE ${ippool_table} \
80 SET expiry_time = 'now'::timestamp(0) + '${lease-duration} second'::interval \
81 WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool-key}'"
83 ## This series of queries frees an IP number when an accounting
84 ## STOP record arrives
85 stop-clear = "UPDATE ${ippool_table} \
86 SET nasipaddress = '', pool_key = 0, callingstationid = '', \
87 expiry_time = 'now'::timestamp(0) - '1 second'::interval \
88 WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' AND username = '%{SQL-User-Name}' \
89 AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"
93 ## This series of queries frees an IP number when an accounting
94 ## ALIVE record arrives
95 alive-update = "UPDATE ${ippool_table} \
96 SET expiry_time = 'now'::timestamp(0) + '${lease-duration} seconds'::interval \
97 WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' AND username = '%{SQL-User-Name}' \
98 AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"
101 ## This series of queries frees the IP numbers allocate to a
102 ## NAS when an accounting ON record arrives
103 on-clear = "UPDATE ${ippool_table} \
104 SET nasipaddress = '', pool_key = 0, callingstationid = '', \
105 expiry_time = 'now'::timestamp(0) - '1 second'::interval \
106 WHERE nasipaddress = '%{Nas-IP-Address}' AND username = '%{SQL-User-Name}' \
107 AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"
109 ## This series of queries frees the IP numbers allocate to a
110 ## NAS when an accounting OFF record arrives
111 off-clear = "UPDATE ${ippool_table} \
112 SET nasipaddress = '', pool_key = 0, callingstationid = '', \
113 expiry_time = 'now'::timestamp(0) - '1 second'::interval \
114 WHERE nasipaddress = '%{Nas-IP-Address}' AND username = '%{SQL-User-Name}' \
115 AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"