1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
# -*- text -*-
#
# ippool/sqlite/queries.conf -- SQLite queries for rlm_sqlippool
#
# $Id$
#
# SQLite does not implement SELECT FOR UPDATE which is normally used to place
# an exclusive lock over rows to prevent the same address from being
# concurrently selected for allocation to multiple users.
#
# The most granular read-blocking lock that SQLite has is an exclusive lock
# over the database, so that's what we use. All locking in SQLite is performed
# over the entire database and we perform a row update for any IP that we
# allocate, requiring an exclusive lock. Taking the exclusive lock from the
# start of the transaction (even if it were not required to guard the SELECT)
# is actually quicker than if we deferred it causing SQLite to "upgrade" the
# automatic shared lock for the transaction to an exclusive lock for the
# subsequent UPDATE.
#
allocate_begin = "BEGIN EXCLUSIVE"
allocate_commit = "COMMIT"
#
# This series of queries allocates an IP address
#
#
# Attempt to allocate the address a client previously had. This is based on pool_key
# and nasipaddress. Change the criteria if the identifier for "stickyness" is different.
# If different criteria are used, check the indexes on the IP pool table to ensure the fields
# are appropriately indexed. To disable stickyness comment out this query.
#
allocate_existing = "\
SELECT framedipaddress \
FROM ${ippool_table} \
WHERE pool_name = '%{control:${pool_name}}' \
AND nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool_key}' \
ORDER BY expiry_time DESC \
LIMIT 1"
#
# Find a free IP address from the pool, choosing the oldest expired one.
#
allocate_find = "\
SELECT framedipaddress \
FROM ${ippool_table} \
WHERE pool_name = '%{control:${pool_name}}' \
expiry_time < datetime('now') \
ORDER BY expiry_time \
LIMIT 1"
#
# If you prefer to allocate a random IP address every time, i
# use this query instead
# Note: This is very slow if you have a lot of free IPs.
#
#allocate_find = "\
# SELECT framedipaddress \
# FROM ${ippool_table} \
# WHERE pool_name = '%{control:${pool_name}}' \
# AND expiry_time IS NULL \
# ORDER BY RAND() \
# LIMIT 1"
#
# If an IP could not be allocated, check to see if the pool exists or not
# This allows the module to differentiate between a full pool and no pool
# Note: If you are not running redundant pool modules this query may be
# commented out to save running this query every time an ip is not allocated.
#
pool_check = "\
SELECT id \
FROM ${ippool_table} \
WHERE pool_name='%{control:${pool_name}}' \
LIMIT 1"
#
# This is the final IP Allocation query, which saves the allocated ip details
#
allocate_update = "\
UPDATE ${ippool_table} \
SET \
nasipaddress = '%{NAS-IP-Address}', \
pool_key = '${pool_key}', \
callingstationid = '%{Calling-Station-Id}', \
username = '%{User-Name}', \
expiry_time = datetime(strftime('%%s', 'now') + ${lease_duration}, 'unixepoch') \
WHERE framedipaddress = '%I'"
#
# Extend an IP expiry time when an accounting START record arrives
#
start_update = "\
UPDATE ${ippool_table} \
SET \
expiry_time = datetime(strftime('%%s', 'now') + ${lease_duration}, 'unixepoch') \
WHERE nasipaddress = '%{NAS-IP-Address}' \
AND pool_key = '${pool_key}' \
AND username = '%{User-Name}' \
AND callingstationid = '%{Calling-Station-Id}' \
AND framedipaddress = '%{${attribute_name}}'"
#
# Expire an IP when an accounting STOP record arrives
#
stop_clear = "\
UPDATE ${ippool_table} \
SET \
expiry_time = datetime('now') \
WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \
AND pool_key = '${pool_key}' \
AND username = '%{User-Name}' \
AND callingstationid = '%{Calling-Station-Id}' \
AND framedipaddress = '%{${attribute_name}}'"
#
# Update the expiry time for an IP when an accounting ALIVE record arrives
#
alive_update = "\
UPDATE ${ippool_table} \
SET \
expiry_time = datetime(strftime('%%s', 'now') + ${lease_duration}, 'unixepoch') \
WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \
AND pool_key = '${pool_key}' \
AND username = '%{User-Name}' \
AND callingstationid = '%{Calling-Station-Id}' \
AND framedipaddress = '%{${attribute_name}}'"
#
# Expires all IPs allocated to a NAS when an accounting ON record arrives
#
on_clear = "\
UPDATE ${ippool_table} \
SET \
expiry_time = datetime('now') \
WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'"
#
# Expires all IPs allocated to a NAS when an accounting OFF record arrives
#
off_clear = "\
UPDATE ${ippool_table} \
SET \
expiry_time = datetime('now') \
WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'"
|
