aboutsummaryrefslogtreecommitdiffstats
path: root/notes
diff options
context:
space:
mode:
authorDavid M. Syzdek <syzdek@bindlebinaries.com>2019-05-20 22:07:33 -0800
committerDavid M. Syzdek <syzdek@bindlebinaries.com>2019-05-20 22:07:33 -0800
commit5185cbf5faf2763ceb0bf1dffb47a9b023ab2cf9 (patch)
tree425833adca41332bf9fb3693ffb41f6641d43cfe /notes
parent5e50256fcdb96c6899fd5efd2dff5f824ae799a3 (diff)
downloadscratchpad-5185cbf5faf2763ceb0bf1dffb47a9b023ab2cf9.tar.gz
scratchpad-5185cbf5faf2763ceb0bf1dffb47a9b023ab2cf9.tar.bz2
scratchpad-5185cbf5faf2763ceb0bf1dffb47a9b023ab2cf9.tar.xz
scratchpad-5185cbf5faf2763ceb0bf1dffb47a9b023ab2cf9.zip
Checkpointing changes
Diffstat (limited to 'notes')
-rw-r--r--notes/projects/radius/sql/ippool-demo.sql40
-rw-r--r--notes/projects/radius/sql/ippool-functions.sql277
-rw-r--r--notes/projects/radius/sql/ippool-queries.sql27
-rw-r--r--notes/projects/radius/sql/ippool-schema.sql10
4 files changed, 244 insertions, 110 deletions
diff --git a/notes/projects/radius/sql/ippool-demo.sql b/notes/projects/radius/sql/ippool-demo.sql
index e6eb48a..3693c7a 100644
--- a/notes/projects/radius/sql/ippool-demo.sql
+++ b/notes/projects/radius/sql/ippool-demo.sql
@@ -41,7 +41,7 @@ SELECT netblock_add
41 poolName := 'DHCP', 41 poolName := 'DHCP',
42 regionName := 'Soldotna', 42 regionName := 'Soldotna',
43 netDesc := 'Reserved for engineering test accounts', 43 netDesc := 'Reserved for engineering test accounts',
44 netActive := FALSE 44 netAssignable := FALSE
45 ); 45 );
46SELECT netblock_add 46SELECT netblock_add
47 ( netAddress := '209.193.4.4/30', 47 ( netAddress := '209.193.4.4/30',
@@ -51,7 +51,8 @@ SELECT netblock_add
51-- 51--
52SELECT netblock_add 52SELECT netblock_add
53 ( netAddress := '209.193.4.16/28', 53 ( netAddress := '209.193.4.16/28',
54 poolName := 'DHCP' 54 poolName := 'PPPoE',
55 regionName := 'Soldotna'
55 ); 56 );
56SELECT netblock_add 57SELECT netblock_add
57 ( netAddress := '209.193.4.32/28', 58 ( netAddress := '209.193.4.32/28',
@@ -60,25 +61,34 @@ SELECT netblock_add
60 ); 61 );
61SELECT netblock_add 62SELECT netblock_add
62 ( netAddress := '209.193.4.48/28', 63 ( netAddress := '209.193.4.48/28',
63 poolName := 'DHCP', 64 poolName := 'PPPoE',
64 regionName := 'Fairbanks' 65 regionName := 'Anchorage'
66 );
67SELECT netblock_add
68 ( netAddress := '209.193.4.64/28',
69 poolName := 'DHCP'
70 );
71SELECT netblock_add
72 ( netAddress := '209.193.4.80/28',
73 poolName := 'PPPoE'
65 ); 74 );
66SELECT netblock_add 75SELECT netblock_add
67 ( netAddress := '209.193.4.64/26', 76 ( netAddress := '209.193.4.96/28',
68 poolName := 'DHCP', 77 poolName := 'DHCP',
69 netDesc := 'Reserved for documentation', 78 netAssignable := FALSE,
70 netActive := FALSE,
71 addIpEntries := FALSE 79 addIpEntries := FALSE
72 ); 80 );
73SELECT netblock_add 81SELECT netblock_add
74 ( netAddress := '209.193.4.128/26', 82 ( netAddress := '209.193.4.112/28',
75 poolName := 'PPPoE', 83 poolName := 'PPPoE',
76 regionName := 'Soldotna' 84 netAssignable := FALSE,
85 addIpEntries := FALSE
77 ); 86 );
78SELECT netblock_add 87SELECT netblock_add
79 ( netAddress := '209.193.4.192/26', 88 ( netAddress := '209.193.4.128/25',
80 poolName := 'PPPoE', 89 poolName := 'Reserved',
81 regionName := 'Anchorage' 90 netAssignable := FALSE,
91 addIpEntries := FALSE
82 ); 92 );
83 93
84 94
@@ -86,14 +96,12 @@ SELECT netblock_add
86SELECT ip_assign 96SELECT ip_assign
87 ( assignIp := '209.193.4.8/32', 97 ( assignIp := '209.193.4.8/32',
88 assignNas := 'br1.sol', 98 assignNas := 'br1.sol',
89 assignUser := 'jdoe8@example.net', 99 assignUser := 'jdoe8@example.net'
90 assignSess := 'aqaqqaasdqweaasdqa8'
91 ); 100 );
92SELECT ip_assign 101SELECT ip_assign
93 ( assignIp := '209.193.4.9/32', 102 ( assignIp := '209.193.4.9/32',
94 assignNas := 'br1.sol', 103 assignNas := 'br1.sol',
95 assignUser := 'jdoe9@aexample.net', 104 assignUser := 'jdoe9@aexample.net'
96 assignSess := 'qweadszcxqweasdzcx9'
97 ); 105 );
98SELECT ip_assign 106SELECT ip_assign
99 ( assignIp := '209.193.4.10/32', 107 ( assignIp := '209.193.4.10/32',
diff --git a/notes/projects/radius/sql/ippool-functions.sql b/notes/projects/radius/sql/ippool-functions.sql
index a879cf3..616f5b2 100644
--- a/notes/projects/radius/sql/ippool-functions.sql
+++ b/notes/projects/radius/sql/ippool-functions.sql
@@ -121,7 +121,6 @@ $$;
121 * assignUser := 'jdoe@acsalaska.net', 121 * assignUser := 'jdoe@acsalaska.net',
122 * assignPortLimit := 5, 122 * assignPortLimit := 5,
123 * assignNas := 'br1.cwc', 123 * assignNas := 'br1.cwc',
124 * assignSess := '596c3b82-75df-11e9-bcbb-6003089b9f24',
125 * assignForce := FALSE 124 * assignForce := FALSE
126 * ); 125 * );
127 * 126 *
@@ -137,11 +136,9 @@ $$;
137 * assignNas 136 * assignNas
138 * : session NAS (attempt to guess if not specified) 137 * : session NAS (attempt to guess if not specified)
139 * 138 *
140 * assignSess
141 * : RADIUS session
142 *
143 * assignForce 139 * assignForce
144 * : force IP assignment, even if netblock is inactive (i.e. static assignments) 140 * : force IP assignment, even if netblock is not assignable (i.e. static
141 * assignments)
145 * 142 *
146 * Returns TRUE if assigned and FALSE if not assigned 143 * Returns TRUE if assigned and FALSE if not assigned
147 */ 144 */
@@ -151,7 +148,6 @@ CREATE OR REPLACE FUNCTION ip_assign
151 assignUser TEXT, 148 assignUser TEXT,
152 assignPortLimit INTEGER DEFAULT 2, 149 assignPortLimit INTEGER DEFAULT 2,
153 assignNas TEXT DEFAULT NULL, 150 assignNas TEXT DEFAULT NULL,
154 assignSess TEXT DEFAULT NULL,
155 assignForce BOOLEAN DEFAULT FALSE 151 assignForce BOOLEAN DEFAULT FALSE
156) 152)
157 RETURNS BOOLEAN 153 RETURNS BOOLEAN
@@ -163,11 +159,6 @@ DECLARE
163 assignPortCount INTEGER; 159 assignPortCount INTEGER;
164BEGIN 160BEGIN
165 161
166 -- sets defaults
167 IF ( assignSess IS NULL ) THEN
168 assignSess := 'user:' || assignUser;
169 END IF;
170
171 -- tallies existing sessions 162 -- tallies existing sessions
172 SELECT count( address ) AS ipCount 163 SELECT count( address ) AS ipCount
173 INTO assignPortCount 164 INTO assignPortCount
@@ -179,12 +170,12 @@ BEGIN
179 RETURN FALSE; 170 RETURN FALSE;
180 END IF; 171 END IF;
181 172
182 -- verifies IP address is not inactive 173 -- verifies IP address is assignable
183 IF ( assignForce = FALSE ) THEN 174 IF ( assignForce = FALSE ) THEN
184 PERFORM network 175 PERFORM network
185 FROM netblock 176 FROM netblock
186 WHERE netblock.network >>= assignIp 177 WHERE netblock.network >>= assignIp
187 AND netblock.active = FALSE; 178 AND netblock.assignable = FALSE;
188 IF FOUND THEN 179 IF FOUND THEN
189 RETURN FALSE; 180 RETURN FALSE;
190 END IF; 181 END IF;
@@ -213,8 +204,7 @@ BEGIN
213 -- updates record 204 -- updates record
214 UPDATE ip 205 UPDATE ip
215 SET nasId = ( SELECT id FROM nas WHERE name LIKE assignNas ), 206 SET nasId = ( SELECT id FROM nas WHERE name LIKE assignNas ),
216 username = assignUser, 207 username = assignUser
217 acctSession = assignSess
218 WHERE address = set_masklen( assignIp, 32 ) 208 WHERE address = set_masklen( assignIp, 32 )
219 AND nasId IS NULL; 209 AND nasId IS NULL;
220 210
@@ -224,6 +214,63 @@ $$;
224 214
225 215
226/* 216/*
217 * audits ip
218 *
219 * % SELECT * FROM ip_audit();
220 *
221 * Returns table of ip
222 */
223CREATE OR REPLACE FUNCTION ip_audit
224(
225)
226 RETURNS TABLE
227 (
228 poolName VARCHAR(32),
229 regionName VARCHAR(32),
230 ipAddress INET,
231 enabled BOOLEAN,
232 assigned BOOLEAN,
233 modified TIMESTAMP WITH TIME ZONE,
234 nasName VARCHAR(32),
235 username VARCHAR(32)
236 )
237 LANGUAGE plpgsql
238 SECURITY DEFINER
239 AS $$
240DECLARE
241BEGIN
242
243 RETURN QUERY
244 SELECT DISTINCT pool.name AS poolName,
245 region.name AS regionName,
246 ip.address AS address,
247 TRUE = ALL
248 (
249 SELECT nb2.assignable
250 FROM netblock AS nb2
251 WHERE ip.address <<= nb2.network
252 ) AS assignable,
253 (
254 nasId IS NOT NULL
255 ) AS assigned,
256 ip.modified AS modified,
257 nas.name AS nasName,
258 ip.username AS username
259 FROM ip
260 INNER JOIN netblock
261 ON ip.address <<= netblock.network
262 INNER JOIN pool
263 ON netblock.poolId = pool.id
264 LEFT OUTER JOIN region
265 ON netblock.regionId = region.id
266 LEFT OUTER JOIN nas
267 ON ip.nasId = nas.id;
268
269END;
270$$;
271
272
273/*
227 * Auto-assigns IP address to specified user/session 274 * Auto-assigns IP address to specified user/session
228 * 275 *
229 * % SELECT ip_autoassign( 276 * % SELECT ip_autoassign(
@@ -236,7 +283,6 @@ $$;
236 * assignNas := 'br1.sol', 283 * assignNas := 'br1.sol',
237 * assignPool := 'dhcp', 284 * assignPool := 'dhcp',
238 * assignPortLimit := 5, 285 * assignPortLimit := 5,
239 * assignSess := 'b4356b24-7670-11e9-84a0-6003089b9f24',
240 * assignPrevIp := TRUE 286 * assignPrevIp := TRUE
241 * ); 287 * );
242 * 288 *
@@ -252,9 +298,6 @@ $$;
252 * assignPortLimit 298 * assignPortLimit
253 * : specifies maximum IP assignments per user allowed 299 * : specifies maximum IP assignments per user allowed
254 * 300 *
255 * assignSess (default: auto-generated from username)
256 * : RADIUS session
257 *
258 * assignPrevIp (default: TRUE) 301 * assignPrevIp (default: TRUE)
259 * : attempt to re-assign user's previous IP address 302 * : attempt to re-assign user's previous IP address
260 * 303 *
@@ -266,7 +309,6 @@ CREATE OR REPLACE FUNCTION ip_autoassign
266 assignNas TEXT, 309 assignNas TEXT,
267 assignPool TEXT, 310 assignPool TEXT,
268 assignPortLimit INTEGER DEFAULT 2, 311 assignPortLimit INTEGER DEFAULT 2,
269 assignSess TEXT DEFAULT NULL,
270 assignPrevIp BOOLEAN DEFAULT TRUE 312 assignPrevIp BOOLEAN DEFAULT TRUE
271) 313)
272 RETURNS INET 314 RETURNS INET
@@ -276,83 +318,118 @@ CREATE OR REPLACE FUNCTION ip_autoassign
276DECLARE 318DECLARE
277 assignedIp INET; 319 assignedIp INET;
278 assignNasId INTEGER; 320 assignNasId INTEGER;
321 assignRegionId INTEGER;
322 assignPoolId INTEGER;
279 assignPortCount INTEGER; 323 assignPortCount INTEGER;
280BEGIN 324BEGIN
281 325
282 -- sets defaults
283 IF ( assignSess IS NULL ) THEN
284 assignSess := 'user:' || assignUser;
285 END IF;
286
287 -- tallies existing sessions 326 -- tallies existing sessions
288 SELECT count( address ) 327 SELECT count( address )
289 INTO assignPortCount 328 INTO assignPortCount
290 FROM ip 329 FROM ip
291 WHERE ip.username LIKE assignUser 330 WHERE ip.username LIKE lower( assignUser )
292 AND ip.nasId IS NOT NULL 331 AND ip.nasId IS NOT NULL;
293 AND ip.address != assignIp;
294 IF ( assignPortCount >= assignPortLimit ) THEN 332 IF ( assignPortCount >= assignPortLimit ) THEN
295 RETURN NULL; 333 RETURN NULL;
296 END IF; 334 END IF;
297 335
298 -- search for user's previous address 336 -- determines NAS ID and region ID
299 IF ( assignPrevIp IS TRUE ) THEN 337 SELECT region.id,
300 SELECT ip.address,
301 nas.id 338 nas.id
302 INTO assignedIp, 339 INTO assignRegionId,
303 assignNasId 340 assignNasId
341 FROM nas
342 INNER JOIN region
343 ON region.id = nas.regionId
344 WHERE nas.name LIKE lower( assignNas );
345 IF NOT FOUND THEN
346 RETURN NULL;
347 END IF;
348
349 -- determines pool ID
350 SELECT id
351 INTO assignPoolId
352 FROM pool
353 WHERE pool.name LIKE lower( assignPool );
354 IF NOT FOUND THEN
355 RETURN NULL;
356 END IF;
357
358 -- search for user's previous address in region
359 IF ( assignPrevIp IS TRUE ) THEN
360 SELECT ip.address
361 INTO assignedIp
304 FROM ip 362 FROM ip
305 INNER JOIN netblock 363 INNER JOIN netblock AS nb
306 ON netblock.network >>= ip.address 364 ON nb.network >>= ip.address
307 INNER JOIN region
308 ON region.id = netblock.regionId
309 INNER JOIN pool
310 ON pool.id = netblock.poolId
311 INNER JOIN nas
312 ON nas.regionId = region.id
313 WHERE ip.nasId IS NULL 365 WHERE ip.nasId IS NULL
314 AND ip.username LIKE lower( assignUser ) 366 AND ip.username LIKE lower( assignUser )
315 AND nas.name LIKE lower( assignNas ) 367 AND nb.poolId = assignPoolId
316 AND pool.name LIKE lower( assignPool ) 368 AND nb.regionId = assignRegionId
317 AND netblock.active = TRUE 369 AND nb.assignable = TRUE
318 ORDER BY ip.modified DESC, 370 ORDER BY ip.modified DESC,
319 ip.address ASC 371 ip.address ASC
320 LIMIT 1; 372 LIMIT 1;
321 END IF; 373 END IF;
322 374
323 -- if previous address is unavailable, find longest idle address 375 -- if previous address is unavailable, find longest idle address in region
324 IF ( assignedIp IS NULL ) THEN 376 IF ( assignedIp IS NULL ) THEN
325 SELECT ip.address, 377 SELECT ip.address
326 nas.id 378 INTO assignedIp
327 INTO assignedIp,
328 assignNasId
329 FROM ip 379 FROM ip
330 INNER JOIN netblock 380 INNER JOIN netblock AS nb
331 ON netblock.network >>= ip.address 381 ON nb.network >>= ip.address
332 INNER JOIN region 382 WHERE ip.nasId IS NULL
333 ON region.id = netblock.regionId 383 AND nb.poolId = assignPoolId
334 INNER JOIN pool 384 AND nb.regionId = assignRegionId
335 ON pool.id = netblock.poolId 385 AND nb.assignable = TRUE
336 INNER JOIN nas 386 ORDER BY ip.modified ASC,
337 ON nas.regionId = region.id 387 ip.address ASC
388 LIMIT 1;
389 END IF;
390
391 -- search for user's previous address out of region
392 IF ( ( assignedIp IS NULL ) AND ( assignPrevIp IS TRUE ) ) THEN
393 SELECT ip.address
394 INTO assignedIp
395 FROM ip
396 INNER JOIN netblock AS nb
397 ON nb.network >>= ip.address
338 WHERE ip.nasId IS NULL 398 WHERE ip.nasId IS NULL
339 AND ip.username LIKE lower( assignUser ) 399 AND ip.username LIKE lower( assignUser )
340 AND nas.name LIKE lower( assignNas ) 400 AND nb.poolId = assignPoolId
341 AND pool.name LIKE lower( assignPool ) 401 AND nb.regionId IS NULL
342 AND netblock.active = TRUE 402 AND nb.assignable = TRUE
343 ORDER BY ip.modified DESC, 403 ORDER BY ip.modified DESC,
344 ip.address ASC 404 ip.address ASC
345 LIMIT 1; 405 LIMIT 1;
346 IF NOT FOUND THEN 406 END IF;
347 RETURN NULL; 407
348 END IF; 408 -- if previous address is unavailable, find longest idle address out of region
409 IF ( assignedIp IS NULL ) THEN
410 SELECT ip.address
411 INTO assignedIp
412 FROM ip
413 INNER JOIN netblock AS nb
414 ON nb.network >>= ip.address
415 WHERE ip.nasId IS NULL
416 AND nb.poolId = assignPoolId
417 AND nb.regionId IS NULL
418 AND nb.assignable = TRUE
419 ORDER BY ip.modified ASC,
420 ip.address ASC
421 LIMIT 1;
422 END IF;
423
424 -- exits if an appropiate IP address was not found
425 IF ( assignedIp IS NULL ) THEN
426 RETURN NULL;
349 END IF; 427 END IF;
350 428
351 -- update record 429 -- update record
352 UPDATE ip 430 UPDATE ip
353 SET nasId = assignNasId, 431 SET nasId = assignNasId,
354 username = assignUser, 432 username = assignUser
355 acctSession = assignSess
356 WHERE address = assignedIp; 433 WHERE address = assignedIp;
357 IF NOT FOUND THEN 434 IF NOT FOUND THEN
358 RETURN NULL; 435 RETURN NULL;
@@ -382,8 +459,7 @@ BEGIN
382 459
383 -- updates record 460 -- updates record
384 UPDATE ip 461 UPDATE ip
385 SET nasId = NULL, 462 SET nasId = NULL
386 acctSession = NULL
387 WHERE address = set_masklen( assignIp, 32 ); 463 WHERE address = set_masklen( assignIp, 32 );
388 464
389 RETURN FOUND; 465 RETURN FOUND;
@@ -454,7 +530,7 @@ $$;
454 * netAddress := '203.0.113.0/24', 530 * netAddress := '203.0.113.0/24',
455 * poolName := 'DHCP', 531 * poolName := 'DHCP',
456 * regionName := 'Soldotna', 532 * regionName := 'Soldotna',
457 * netActive := FALSE, 533 * netAssignable := FALSE,
458 * netDesc := 'Lab IP Pool', 534 * netDesc := 'Lab IP Pool',
459 * addIpEntries := TRUE 535 * addIpEntries := TRUE
460 * ); 536 * );
@@ -468,7 +544,7 @@ $$;
468 * regionName 544 * regionName
469 * : the name of the region in which the network will physically serve 545 * : the name of the region in which the network will physically serve
470 * 546 *
471 * netActive 547 * netAssignable
472 * : TRUE if addresses within network are assignable, FALSE if addresses are 548 * : TRUE if addresses within network are assignable, FALSE if addresses are
473 * : not assignable 549 * : not assignable
474 * 550 *
@@ -485,7 +561,7 @@ CREATE OR REPLACE FUNCTION netblock_add
485 netAddress CIDR, 561 netAddress CIDR,
486 poolName VARCHAR(32), 562 poolName VARCHAR(32),
487 regionName VARCHAR(32) DEFAULT NULL, 563 regionName VARCHAR(32) DEFAULT NULL,
488 netActive BOOLEAN DEFAULT TRUE, 564 netAssignable BOOLEAN DEFAULT TRUE,
489 netDesc VARCHAR(128) DEFAULT NULL, 565 netDesc VARCHAR(128) DEFAULT NULL,
490 addIpEntries BOOLEAN DEFAULT TRUE 566 addIpEntries BOOLEAN DEFAULT TRUE
491) 567)
@@ -524,13 +600,13 @@ BEGIN
524 ( network, 600 ( network,
525 poolId, 601 poolId,
526 regionId, 602 regionId,
527 active, 603 assignable,
528 description 604 description
529 ) 605 )
530 VALUES ( netAddress, 606 VALUES ( netAddress,
531 netPoolId, 607 netPoolId,
532 netRegionId, 608 netRegionId,
533 netActive, 609 netAssignable,
534 netDesc 610 netDesc
535 ) 611 )
536 RETURNING id INTO returnId; 612 RETURNING id INTO returnId;
@@ -545,4 +621,63 @@ END;
545$$; 621$$;
546 622
547 623
624/*
625 * audits netblocks
626 *
627 * % SELECT * FROM netblock_audit();
628 *
629 * Returns table of netblocks
630 */
631CREATE OR REPLACE FUNCTION netblock_audit
632(
633)
634 RETURNS TABLE
635 (
636 poolName VARCHAR(32),
637 regionName VARCHAR(32),
638 network CIDR,
639 assignable BOOLEAN,
640 addressesAssigned BIGINT,
641 addressesAvailable BIGINT,
642 addressesTotal BIGINT,
643 description VARCHAR(256)
644 )
645 LANGUAGE plpgsql
646 SECURITY DEFINER
647 AS $$
648DECLARE
649BEGIN
650
651 RETURN QUERY
652 SELECT DISTINCT pool.name AS poolName,
653 region.name AS regionname,
654 netblock.network AS network,
655 netblock.assignable AS assignable,
656 (
657 SELECT count(address)
658 FROM ip
659 WHERE address <<= netblock.network
660 AND ip.nasId IS NOT NULL
661 ) AS addressAssigned,
662 (
663 SELECT count(address)
664 FROM ip
665 WHERE address <<= netblock.network
666 AND ip.nasId IS NULL
667 ) AS addressesAvailable,
668 (
669 SELECT count(address)
670 FROM ip
671 WHERE address <<= netblock.network
672 ) AS addressesTotal,
673 netblock.description AS description
674 FROM netblock
675 INNER JOIN pool
676 ON netblock.poolId = pool.id
677 LEFT OUTER JOIN region
678 ON netblock.regionId = region.id;
679
680END;
681$$;
682
548/* end of sql */ 683/* end of sql */
diff --git a/notes/projects/radius/sql/ippool-queries.sql b/notes/projects/radius/sql/ippool-queries.sql
index bf3225b..9a82fc6 100644
--- a/notes/projects/radius/sql/ippool-queries.sql
+++ b/notes/projects/radius/sql/ippool-queries.sql
@@ -21,7 +21,6 @@ SET ROLE radius_user;
21SELECT ip.address AS address, 21SELECT ip.address AS address,
22 ip.modified AS modified, 22 ip.modified AS modified,
23 ip.username AS username, 23 ip.username AS username,
24 ip.acctsession AS acctsession,
25 pool.name AS pool, 24 pool.name AS pool,
26 region.name AS region 25 region.name AS region
27 FROM ip 26 FROM ip
@@ -37,7 +36,7 @@ SELECT ip.address AS address,
37 AND ip.username LIKE lower( :'myUsername' ) 36 AND ip.username LIKE lower( :'myUsername' )
38 AND nas.name LIKE lower( :'myNasName' ) 37 AND nas.name LIKE lower( :'myNasName' )
39 AND pool.name LIKE lower( :'myPoolName' ) 38 AND pool.name LIKE lower( :'myPoolName' )
40 AND netblock.active = TRUE 39 AND netblock.assignable = TRUE
41 ORDER BY ip.modified DESC, 40 ORDER BY ip.modified DESC,
42 ip.address ASC 41 ip.address ASC
43 LIMIT 1; 42 LIMIT 1;
@@ -54,12 +53,12 @@ SELECT ip.address AS address,
54SELECT ip.address AS address, 53SELECT ip.address AS address,
55 ip.modified AS modified, 54 ip.modified AS modified,
56 ip.username AS username, 55 ip.username AS username,
57 ip.acctsession AS acctsession,
58 pool.name AS pool, 56 pool.name AS pool,
59 region.name AS region 57 region.name AS region
60 FROM ip 58 FROM ip
61 INNER JOIN netblock 59 INNER JOIN netblock
62 ON netblock.network >>= ip.address 60 ON netblock.network >>= ip.address
61 AND netblock.assignable IS TRUE
63 INNER JOIN region 62 INNER JOIN region
64 ON region.id = netblock.regionId 63 ON region.id = netblock.regionId
65 INNER JOIN pool 64 INNER JOIN pool
@@ -69,7 +68,7 @@ SELECT ip.address AS address,
69 WHERE ip.nasId IS NULL 68 WHERE ip.nasId IS NULL
70 AND nas.name LIKE lower( :'myNasName' ) 69 AND nas.name LIKE lower( :'myNasName' )
71 AND pool.name LIKE lower( :'myPoolName' ) 70 AND pool.name LIKE lower( :'myPoolName' )
72 AND netblock.active = TRUE 71 AND netblock.assignable = TRUE
73 ORDER BY ip.modified ASC, 72 ORDER BY ip.modified ASC,
74 ip.address ASC 73 ip.address ASC
75 LIMIT 1; 74 LIMIT 1;
@@ -96,7 +95,7 @@ SELECT DISTINCT pool.name AS poolName,
96 ip.address AS address, 95 ip.address AS address,
97 TRUE = ALL 96 TRUE = ALL
98 ( 97 (
99 SELECT nb2.active 98 SELECT nb2.assignable
100 FROM netblock AS nb2 99 FROM netblock AS nb2
101 WHERE ip.address <<= nb2.network 100 WHERE ip.address <<= nb2.network
102 ) AS active, 101 ) AS active,
@@ -105,8 +104,7 @@ SELECT DISTINCT pool.name AS poolName,
105 ) AS assigned, 104 ) AS assigned,
106 ip.modified AS modified, 105 ip.modified AS modified,
107 nas.name AS nasName, 106 nas.name AS nasName,
108 ip.username AS username, 107 ip.username AS username
109 ip.acctSession AS acctSession
110 FROM ip 108 FROM ip
111 INNER JOIN netblock 109 INNER JOIN netblock
112 ON ip.address <<= netblock.network 110 ON ip.address <<= netblock.network
@@ -127,15 +125,14 @@ SELECT DISTINCT pool.name AS poolName,
127 SELECT * 125 SELECT *
128 FROM netblock AS nb2 126 FROM netblock AS nb2
129 WHERE ip.address <<= nb2.network 127 WHERE ip.address <<= nb2.network
130 AND NOT nb2.active 128 AND NOT nb2.assignable
131 ) AS active, 129 ) AS active,
132 ( 130 (
133 nasId IS NOT NULL 131 nasId IS NOT NULL
134 ) AS assigned, 132 ) AS assigned,
135 ip.modified AS modified, 133 ip.modified AS modified,
136 nas.name AS nasName, 134 nas.name AS nasName,
137 ip.username AS username, 135 ip.username AS username
138 ip.acctSession AS acctSession
139 FROM ip 136 FROM ip
140 INNER JOIN netblock 137 INNER JOIN netblock
141 ON ip.address <<= netblock.network 138 ON ip.address <<= netblock.network
@@ -153,11 +150,11 @@ SELECT DISTINCT pool.name AS poolName,
153 */ 150 */
154SELECT DISTINCT ip.address AS ipAddress, 151SELECT DISTINCT ip.address AS ipAddress,
155 netblock.network AS network, 152 netblock.network AS network,
156 netblock.active AS active 153 netblock.assignable AS active
157 FROM ip 154 FROM ip
158 INNER JOIN netblock 155 INNER JOIN netblock
159 ON ip.address <<= netblock.network 156 ON ip.address <<= netblock.network
160 WHERE netblock.active = FALSE 157 WHERE netblock.assignable = FALSE
161 ORDER BY ipAddress; 158 ORDER BY ipAddress;
162 159
163 160
@@ -166,18 +163,18 @@ SELECT DISTINCT ip.address AS ipAddress,
166 */ 163 */
167SELECT DISTINCT netblock.regionId AS regionId, 164SELECT DISTINCT netblock.regionId AS regionId,
168 ip.address AS ipAddress, 165 ip.address AS ipAddress,
169 netblock.active AS active 166 netblock.assignable AS active
170 FROM ip 167 FROM ip
171 INNER JOIN netblock 168 INNER JOIN netblock
172 ON ip.address <<= netblock.network 169 ON ip.address <<= netblock.network
173 WHERE netblock.active = TRUE 170 WHERE netblock.assignable = TRUE
174 AND ip.address NOT IN 171 AND ip.address NOT IN
175 ( 172 (
176 SELECT address AS ipAddress 173 SELECT address AS ipAddress
177 FROM ip 174 FROM ip
178 INNER JOIN netblock 175 INNER JOIN netblock
179 ON ip.address <<= netblock.network 176 ON ip.address <<= netblock.network
180 AND netblock.active = FALSE 177 AND netblock.assignable = FALSE
181 ) 178 )
182 ORDER BY ipAddress; 179 ORDER BY ipAddress;
183 180
diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql
index 3730051..9a92ef8 100644
--- a/notes/projects/radius/sql/ippool-schema.sql
+++ b/notes/projects/radius/sql/ippool-schema.sql
@@ -157,7 +157,7 @@ CREATE TABLE IF NOT EXISTS netblock
157 network CIDR NOT NULL UNIQUE, 157 network CIDR NOT NULL UNIQUE,
158 poolId INTEGER NOT NULL, 158 poolId INTEGER NOT NULL,
159 regionId INTEGER, 159 regionId INTEGER,
160 active BOOLEAN NOT NULL DEFAULT TRUE, 160 assignable BOOLEAN NOT NULL DEFAULT TRUE,
161 description VARCHAR(256), 161 description VARCHAR(256),
162 PRIMARY KEY ( id ), 162 PRIMARY KEY ( id ),
163 FOREIGN KEY ( poolId ) 163 FOREIGN KEY ( poolId )
@@ -173,7 +173,7 @@ CREATE INDEX
173 ( id, 173 ( id,
174 poolId, 174 poolId,
175 network, 175 network,
176 active 176 assignable
177 ); 177 );
178 178
179 179
@@ -192,9 +192,6 @@ CREATE INDEX
192 * username 192 * username
193 * : username currently assigned or last assigned address 193 * : username currently assigned or last assigned address
194 * 194 *
195 * acctSession
196 * : RADIUS accounting session currently holding address
197 *
198 * nasId 195 * nasId
199 * : reference to RADIUS NAS client currently holding address 196 * : reference to RADIUS NAS client currently holding address
200 */ 197 */
@@ -205,7 +202,6 @@ CREATE TABLE IF NOT EXISTS ip
205 modified timestamp WITH TIME ZONE NOT NULL 202 modified timestamp WITH TIME ZONE NOT NULL
206 DEFAULT TIMESTAMP 'epoch', 203 DEFAULT TIMESTAMP 'epoch',
207 username VARCHAR(64), 204 username VARCHAR(64),
208 acctSession VARCHAR(32),
209 nasId INTEGER, 205 nasId INTEGER,
210 PRIMARY KEY ( address ), 206 PRIMARY KEY ( address ),
211 FOREIGN KEY ( nasId ) 207 FOREIGN KEY ( nasId )
@@ -222,7 +218,6 @@ CREATE INDEX
222 netblockId, 218 netblockId,
223 modified, 219 modified,
224 username, 220 username,
225 acctSession,
226 nasId 221 nasId
227 ); 222 );
228 223
@@ -264,7 +259,6 @@ BEGIN
264 259
265 -- forces initial values 260 -- forces initial values
266 NEW.username := NULL; 261 NEW.username := NULL;
267 NEW.acctSession := NULL;
268 NEW.nasId := NULL; 262 NEW.nasId := NULL;
269 NEW.modified := TIMESTAMP 'epoch'; 263 NEW.modified := TIMESTAMP 'epoch';
270 264