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
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
|
# -*- text -*-
#
# ippool/postgresql/queries.conf -- PostgreSQL queries for rlm_sqlippool
#
# $Id$
# Using SKIP LOCKED speeds up selection queries
# However, it requires PostgreSQL >= 9.5 Uncomment the
# following if you are running a suitable version of PostgreSQL
#
#skip_locked = "SKIP LOCKED"
skip_locked = ""
#
# This series of queries allocates an IP address
#
#
# The suggested queries locate IPs and update them in one query
# so no need for transaction wrappers
#
allocate_begin = ""
allocate_commit = ""
#
# 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 = "\
WITH cte AS ( \
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 \
FOR UPDATE ${skip_locked} \
) \
UPDATE ${ippool_table} \
SET \
nasipaddress = '%{NAS-IP-Address}', \
pool_key = '${pool_key}', \
callingstationid = '%{Calling-Station-Id}', \
username = '%{SQL-User-Name}', \
expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \
FROM cte WHERE cte.framedipaddress = ${ippool_table}.framedipaddress \
RETURNING cte.framedipaddress"
#
# Find a free IP address from the pool, choosing the oldest expired one.
#
allocate_find = "\
WITH cte AS ( \
SELECT framedipaddress FROM ${ippool_table} \
WHERE pool_name = '%{control:${pool_name}}' \
AND expiry_time < 'now'::timestamp(0) \
ORDER BY expiry_time \
LIMIT 1 \
FOR UPDATE ${skip_locked} \
) \
UPDATE ${ippool_table} \
SET \
nasipaddress = '%{NAS-IP-Address}', \
pool_key = '${pool_key}', \
callingstationid = '%{Calling-Station-Id}', \
username = '%{SQL-User-Name}', \
expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \
FROM cte WHERE cte.framedipaddress = ${ippool_table}.framedipaddress \
RETURNING cte.framedipaddress"
#
# If you prefer to allocate a random IP address every time, use this query instead
# Note: This is very slow if you have a lot of free IPs.
#
#allocate_find = "\
# WITH cte AS ( \
# SELECT framedipaddress FROM ${ippool_table} \
# WHERE pool_name = '%{control:${pool_name}}' \
# AND expiry_time < 'now'::timestamp(0) \
# ORDER BY RANDOM() \
# LIMIT 1 \
# FOR UPDATE ${skip_locked} \
# ) \
# UPDATE ${ippool_table} \
# SET \
# nasipaddress = '%{NAS-IP-Address}', \
# pool_key = '${pool_key}', \
# callingstationid = '%{Calling-Station-Id}', \
# username = '%{SQL-User-Name}', \
# expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \
# FROM cte WHERE cte.framedipaddress = ${ippool_table}.framedipaddress \
# RETURNING cte.framedipaddress"
#
# If an IP could not be allocated, check to see whether 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 query marks the IP address handed out by "allocate-find" as used
# for the period of "lease_duration" after which time it may be reused.
# This is only needed if the allocate_existing / allocate_find queries
# do not update the pool
#
#allocate_update = "\
# UPDATE ${ippool_table} \
# SET \
# nasipaddress = '%{NAS-IP-Address}', \
# pool_key = '${pool_key}', \
# callingstationid = '%{Calling-Station-Id}', \
# username = '%{SQL-User-Name}', \
# expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \
# WHERE framedipaddress = '%I'"
#
# Use a stored procedure to find AND allocate the address. Read and customise
# `procedure.sql` in this directory to determine the optimal configuration.
#
# This requires PostgreSQL >= 9.5 as SKIP LOCKED is used.
#
# The "NO LOAD BALANCE" comment is included here to indicate to a PgPool
# system that this needs to be a write transaction. PgPool itself cannot
# detect this from the statement alone. If you are using PgPool and do not
# have this comment, the query may go to a read only server, and will fail.
# This has no negative effect if you are not using PgPool.
#
#allocate_begin = ""
#allocate_find = "\
# /*NO LOAD BALANCE*/ \
# SELECT fr_allocate_previous_or_new_framedipaddress( \
# '%{control:${pool_name}}', \
# '%{SQL-User-Name}', \
# '%{Calling-Station-Id}', \
# '%{NAS-IP-Address}', \
# '${pool_key}', \
# '${lease_duration}' \
# )"
#allocate_update = ""
#allocate_commit = ""
#
# This query extends an IP address lease by "lease_duration" when an accounting
# START record arrives
#
start_update = "\
UPDATE ${ippool_table} \
SET \
expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \
WHERE nasipaddress = '%{NAS-IP-Address}' \
AND pool_key = '${pool_key}'"
#
# This query expires an IP address when an accounting
# STOP record arrives
#
stop_clear = "\
UPDATE ${ippool_table} \
SET \
expiry_time = 'now'::timestamp(0) - '1 second'::interval \
WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \
AND pool_key = '${pool_key}' \
AND username = '%{SQL-User-Name}' \
AND callingstationid = '%{Calling-Station-Id}' \
AND framedipaddress = '%{${attribute_name}}'"
#
# This query extends an IP address lease by "lease_duration" when an accounting
# ALIVE record arrives
#
alive_update = "\
UPDATE ${ippool_table} \
SET \
expiry_time = 'now'::timestamp(0) + '${lease_duration} seconds'::interval \
WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \
AND pool_key = '${pool_key}' \
AND framedipaddress = '%{${attribute_name}}' \
AND username = '%{SQL-User-Name}' \
AND callingstationid = '%{Calling-Station-Id}'"
#
# This query expires all IP addresses allocated to a NAS when an
# accounting ON record arrives from that NAS
#
on_clear = "\
UPDATE ${ippool_table} \
SET \
expiry_time = 'now'::timestamp(0) - '1 second'::interval \
WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'"
#
# This query expires all IP addresses allocated to a NAS when an
# accounting OFF record arrives from that NAS
#
off_clear = "\
UPDATE ${ippool_table} \
SET \
expiry_time = 'now'::timestamp(0) - '1 second'::interval \
WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'"
|
