1 # Configuration for the SQLIPPool module
3 # The database schemas are available at:
12 # SQL instance to use (from sql.conf)
14 sql-instance-name = "sql"
16 # Table to keep ippool info
17 ippool_table = "radippool"
19 # lease_duration. fix for lost acc-stop packets
22 # Attribute which should be considered unique per NAS
23 # Using NAS-Port gives behaviour similar to rlm_ippool. Calling-Station-Id is for NAS who send fixed NAS-Port
24 pool-key = "%{NAS-Port}"
25 # pool-key = "%{Calling-Station-Id}"
27 # Logging configuration.
28 sqlippool_log_exists = "Existing IP: %{reply:Framed-IP-Address} \
29 (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
31 sqlippool_log_success = "Allocating IP: %{reply:Framed-IP-Address} from %{check:Pool-Name} \
32 (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
34 sqlippool_log_failed = "IP Allocation FAILED from %{check:Pool-Name} \
35 (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
37 sqlippool_log_nopool = "No Pool-Name defined \
38 (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
41 # This series of queries allocates an IP address
43 allocate-clear = "UPDATE ${ippool_table} \
44 SET nasipaddress = '', pool_key = 0, callingstationid = '', \
45 expiry_time = 'now'::timestamp(0) - '1 second'::interval \
46 WHERE pool_key = '${pool-key}'"
48 # The ORDER BY clause of this query tries to allocate the same IP-address
49 # which user had last session...
50 allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
51 WHERE pool_name = '%{check:Pool-Name}' AND expiry_time < 'now'::timestamp(0) \
52 ORDER BY (username <> '%{SQL-User-Name}'), (callingstationid <> '%{Calling-Station-Id}'), expiry_time \
56 # If you prefer to allocate a random IP address every time, use this query instead
57 #allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
58 # WHERE pool_name = '%P' AND expiry_time < 'now'::timestamp(0) \
64 allocate-update = "UPDATE ${ippool_table} \
65 SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
66 callingstationid = '%{Calling-Station-Id}', username = '%{SQL-User-Name}', \
67 expiry_time = 'now'::timestamp(0) + '${lease-duration} second'::interval \
68 WHERE framedipaddress = '%I'"
72 # This series of queries frees an IP number when an accounting
73 # START record arrives
75 start-update = "UPDATE ${ippool_table} \
76 SET expiry_time = 'now'::timestamp(0) + '${lease-duration} second'::interval \
77 WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool-key}'"
80 # This series of queries frees an IP number when an accounting
83 stop-clear = "UPDATE ${ippool_table} \
84 SET nasipaddress = '', pool_key = 0, callingstationid = '', \
85 expiry_time = 'now'::timestamp(0) - '1 second'::interval \
86 WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' AND username = '%{SQL-User-Name}' \
87 AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"
92 # This series of queries frees an IP number when an accounting
93 # 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}'"
102 # This series of queries frees the IP numbers allocate to a
103 # NAS when an accounting ON record arrives
105 on-clear = "UPDATE ${ippool_table} \
106 SET nasipaddress = '', pool_key = 0, callingstationid = '', \
107 expiry_time = 'now'::timestamp(0) - '1 second'::interval \
108 WHERE nasipaddress = '%{Nas-IP-Address}' AND username = '%{SQL-User-Name}' \
109 AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"
112 # This series of queries frees the IP numbers allocate to a
113 # NAS when an accounting OFF record arrives
115 off-clear = "UPDATE ${ippool_table} \
116 SET nasipaddress = '', pool_key = 0, callingstationid = '', \
117 expiry_time = 'now'::timestamp(0) - '1 second'::interval \
118 WHERE nasipaddress = '%{Nas-IP-Address}' AND username = '%{SQL-User-Name}' \
119 AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"