aboutsummaryrefslogtreecommitdiffstats
path: root/notes/projects/radius/sql/ippool-schema.sql
diff options
context:
space:
mode:
Diffstat (limited to 'notes/projects/radius/sql/ippool-schema.sql')
-rw-r--r--notes/projects/radius/sql/ippool-schema.sql561
1 files changed, 11 insertions, 550 deletions
diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql
index 59914d3..fcf74ea 100644
--- a/notes/projects/radius/sql/ippool-schema.sql
+++ b/notes/projects/radius/sql/ippool-schema.sql
@@ -227,377 +227,14 @@ CREATE INDEX
227 ); 227 );
228 228
229 229
230-- ///////////////// 230-- /////////////////////////
231-- // // 231-- // //
232-- // Functions // 232-- // Trigger Functions //
233-- // // 233-- // //
234-- ///////////////// 234-- /////////////////////////
235 235
236/* 236/*
237 * Creates IP entries for the given network block 237 * trigger functions for ip table
238 *
239 * % SELECT ip_add_network(
240 * ipNetwork := '198.51.100.0/24'
241 * );
242 * % SELECT ip_add_network(
243 * ipNetblockId := (
244 * SELECT id
245 * FROM netblock
246 * WHERE network = '198.51.100.0/24'
247 * )
248 * );
249 *
250 * ipNetwork
251 * : network address and network size being added to database
252 *
253 * ipNetblockId
254 * : reference to netblock to add to database
255 *
256 * Returns the number of new IP addresses registered
257 */
258CREATE OR REPLACE FUNCTION ip_add_network
259(
260 ipNetwork CIDR DEFAULT NULL,
261 ipNetblockId INTEGER DEFAULT NULL
262)
263 RETURNS INTEGER
264 LANGUAGE plpgsql
265 SECURITY DEFINER
266 AS $$
267DECLARE
268 ipAddress INET;
269 resultAddress INET;
270 ipAdded INTEGER;
271 ipMask INTEGER;
272BEGIN
273
274 -- verifies input
275 IF ( ( ipNetwork IS NULL ) AND ( ipNetblockId IS NULL ) ) THEN
276 RAISE EXCEPTION 'must specify either ipNetwork or ipNetblockId';
277 END IF;
278
279 -- determines netblock
280 if ( ipNetblockId IS NULL ) THEN
281 SELECT id INTO ipNetblockId
282 FROM netblock
283 WHERE network >>= ipNetwork;
284 ELSE
285 SELECT network INTO ipNetwork
286 FROM netblock
287 WHERE id = ipNetblockId;
288 END IF;
289 if ( ( ipNetblockId IS NULL ) OR ( ipNetwork IS NULL ) ) THEN
290 RAISE EXCEPTION 'specified netblock does not exist';
291 END IF;
292
293 -- restricts access to IPv4 addresses
294 IF ( family(ipNetwork) != 4 ) THEN
295 RAISE EXCEPTION 'only IPv4 addresses are supported';
296 END IF;
297
298 -- sets starting values
299 ipAdded := 0;
300 ipAddress := set_masklen( network(ipNetwork), 32 );
301
302 -- loops through individual IP addresses
303 LOOP
304 EXIT WHEN ipAddress > set_masklen( broadcast(ipNetwork), 32 );
305
306 INSERT INTO ip
307 ( address,
308 netblockId
309 )
310 SELECT ipAddress,
311 ipNetblockId
312 WHERE NOT EXISTS
313 ( SELECT address FROM ip WHERE address = ipAddress )
314 RETURNING address INTO resultAddress;
315 IF ( resultAddress IS NOT NULL ) THEN
316 ipAdded := ipAdded + 1;
317 END IF;
318 ipAddress := ipAddress + 1;
319
320 END LOOP;
321
322 RETURN ipAdded;
323END;
324$$;
325
326
327/*
328 * Assigns specific IP address to specified user/session
329 *
330 * % SELECT ip_assign(
331 * assignIp := '198.51.100.1/32',
332 * assignUser := 'jdoe@acsalaska.net'
333 * );
334 * % SELECT ip_assign(
335 * assignIp := '198.51.100.1/32',
336 * assignUser := 'jdoe@acsalaska.net',
337 * assignPortLimit := 5,
338 * assignNas := 'br1.cwc',
339 * assignSess := '596c3b82-75df-11e9-bcbb-6003089b9f24',
340 * assignForce := FALSE
341 * );
342 *
343 * assignIp
344 * : IP address to assign to user
345 *
346 * assignUser
347 * : user to assign IP address
348 *
349 * assignPortLimit
350 * : specifies maximum IP assignments per user allowed
351 *
352 * assignNas
353 * : session NAS (attempt to guess if not specified)
354 *
355 * assignSess
356 * : RADIUS session
357 *
358 * assignForce
359 * : force IP assignment, even if netblock is inactive (i.e. static assignments)
360 *
361 * Returns TRUE if assigned and FALSE if not assigned
362 */
363CREATE OR REPLACE FUNCTION ip_assign
364(
365 assignIp INET,
366 assignUser TEXT,
367 assignPortLimit INTEGER DEFAULT 2,
368 assignNas TEXT DEFAULT NULL,
369 assignSess TEXT DEFAULT NULL,
370 assignForce BOOLEAN DEFAULT FALSE
371)
372 RETURNS BOOLEAN
373 LANGUAGE plpgsql
374 SECURITY DEFINER
375 AS $$
376DECLARE
377 assignNasId INTEGER;
378 assignPortCount INTEGER;
379BEGIN
380
381 -- sets defaults
382 IF ( assignSess IS NULL ) THEN
383 assignSess := 'user:' || assignUser;
384 END IF;
385
386 -- tallies existing sessions
387 SELECT count(id)
388 INTO assignPortCount
389 FROM ip
390 WHERE ip.username LIKE assignUser
391 AND ip.nasId IS NOT NULL
392 AND ip.address != assignIp;
393 IF ( assignPortCount >= assignPortLimit ) THEN
394 RETURN FALSE;
395 END IF;
396
397 -- verifies IP address is not inactive
398 IF ( assignForce = FALSE ) THEN
399 SELECT network
400 FROM netblock
401 WHERE netblock.network >>= assignIp
402 AND netblock.active IS FALSE;
403 IF FOUND THEN
404 RETURN FALSE;
405 END IF;
406 END IF;
407
408 -- attempts to determine NAS
409 IF ( assignNas IS NOT NULL ) THEN
410 SELECT id
411 INTO assignNasId
412 FROM nas
413 WHERE name LIKE assignNas;
414 ELSE
415 SELECT nas.id
416 INTO assignNasId
417 FROM nas
418 INNER JOIN region
419 ON region.id = nas.regionId
420 INNER JOIN netblock
421 ON netblock.regionId = regoin.id
422 WHERE netblock.network >>= assignIp;
423 END IF;
424 IF ( assignNasId IS NULL ) THEN
425 RETURN FALSE;
426 END IF;
427
428 -- updates record
429 UPDATE ip
430 SET nasId = ( SELECT id FROM nas WHERE name LIKE assignNas ),
431 username = assignUser,
432 acctSession = assignSess
433 WHERE address = set_masklen( assignIp, 32 )
434 AND nasId IS NULL;
435
436 RETURN FOUND;
437END;
438$$;
439
440
441/*
442 * Auto-assigns IP address to specified user/session
443 *
444 * % SELECT ip_autoassign(
445 * assignUser := 'jdoe@acsalaska.net',
446 * assignNas := 'br1.sol',
447 * assignPool := 'dhcp'
448 * );
449 * % SELECT ip_autoassign(
450 * assignUser := 'jdoe@acsalaska.net',
451 * assignNas := 'br1.sol',
452 * assignPool := 'dhcp',
453 * assignPortLimit := 5,
454 * assignSess := 'b4356b24-7670-11e9-84a0-6003089b9f24',
455 * assignPrevIp := TRUE
456 * );
457 *
458 * assignUser
459 * : user to assign IP address
460 *
461 * assignNas
462 * : NAS to assign to IP address and implies region from which to pull IP
463 *
464 * assignPool
465 * : IP pool to assign from which to assign IP address
466 *
467 * assignPortLimit
468 * : specifies maximum IP assignments per user allowed
469 *
470 * assignSess (default: auto-generated from username)
471 * : RADIUS session
472 *
473 * assignPrevIp (default: TRUE)
474 * : attempt to re-assign user's previous IP address
475 *
476 * Returns INET with value of assigned IP address
477 */
478CREATE OR REPLACE FUNCTION ip_autoassign
479(
480 assignUser TEXT,
481 assignNas TEXT,
482 assignPool TEXT,
483 assignPortLimit INTEGER, DEFAULT 2,
484 assignSess TEXT DEFAULT NULL,
485 assignPrevIp BOOLEAN DEFAULT TRUE
486)
487 RETURNS INET
488 LANGUAGE plpgsql
489 SECURITY DEFINER
490 AS $$
491DECLARE
492 assignedIp INET;
493 assignNasId INTEGER;
494BEGIN
495
496 -- sets defaults
497 IF ( assignSess IS NULL ) THEN
498 assignSess := 'user:' || assignUser;
499 END IF;
500
501 -- search for user's previous address
502 IF ( assignPrevIp IS TRUE ) THEN
503 SELECT ip.address,
504 nas.id
505 INTO assignedIp,
506 assignNasId
507 FROM ip
508 INNER JOIN netblock
509 ON netblock.network >>= ip.address
510 INNER JOIN region
511 ON region.id = netblock.regionId
512 INNER JOIN pool
513 ON pool.id = netblock.poolId
514 INNER JOIN nas
515 ON nas.regionId = region.id
516 WHERE ip.nasId IS NULL
517 AND ip.username LIKE lower( assignUser )
518 AND nas.name LIKE lower( assignNas )
519 AND pool.name LIKE lower( assignPool )
520 AND netblock.active = TRUE
521 ORDER BY ip.modified DESC,
522 ip.address ASC
523 LIMIT 1;
524 END IF;
525
526 -- if previous address is unavailable, find longest idle address
527 IF ( assignedIp IS NULL ) THEN
528 SELECT ip.address,
529 nas.id
530 INTO assignedIp,
531 assignNasId
532 FROM ip
533 INNER JOIN netblock
534 ON netblock.network >>= ip.address
535 INNER JOIN region
536 ON region.id = netblock.regionId
537 INNER JOIN pool
538 ON pool.id = netblock.poolId
539 INNER JOIN nas
540 ON nas.regionId = region.id
541 WHERE ip.nasId IS NULL
542 AND ip.username LIKE lower( assignUser )
543 AND nas.name LIKE lower( assignNas )
544 AND pool.name LIKE lower( assignPool )
545 AND netblock.active = TRUE
546 ORDER BY ip.modified DESC,
547 ip.address ASC
548 LIMIT 1;
549 IF NOT FOUND THEN
550 RETURN NULL;
551 END IF;
552 END IF;
553
554 -- update record
555 UPDATE ip
556 SET nasId = assignNasId,
557 username = assignUser,
558 acctSession = assignSess
559 WHERE address = assignedIp;
560 IF NOT FOUND THEN
561 RETURN NULL;
562 END IF;
563
564 RETURN assignedIp;
565END;
566$$;
567
568
569
570/*
571 * updates timestamp of addresses
572 *
573 * returns trigger
574 */
575CREATE OR REPLACE FUNCTION ip_release
576(
577 assignIp INET
578)
579 RETURNS BOOLEAN
580 LANGUAGE plpgsql
581 SECURITY DEFINER
582 AS $$
583DECLARE
584BEGIN
585
586 -- updates record
587 UPDATE ip
588 SET nasId = NULL,
589 acctSession = NULL
590 WHERE address = set_masklen( assignIp, 32 );
591
592 RETURN FOUND;
593END;
594$$;
595
596
597/*
598 * updates timestamp of addresses
599 *
600 * returns trigger
601 */ 238 */
602CREATE OR REPLACE FUNCTION ip_trigger_delete 239CREATE OR REPLACE FUNCTION ip_trigger_delete
603( ) 240( )
@@ -617,11 +254,6 @@ END;
617$$; 254$$;
618 255
619 256
620/*
621 * forces initial values on insert
622 *
623 * returns trigger
624 */
625CREATE OR REPLACE FUNCTION ip_trigger_insert 257CREATE OR REPLACE FUNCTION ip_trigger_insert
626( ) 258( )
627 RETURNS trigger 259 RETURNS trigger
@@ -646,11 +278,6 @@ END;
646$$; 278$$;
647 279
648 280
649/*
650 * verifies no assigned IP addresses before truncating
651 *
652 * returns trigger
653 */
654CREATE OR REPLACE FUNCTION ip_trigger_truncate 281CREATE OR REPLACE FUNCTION ip_trigger_truncate
655( ) 282( )
656 RETURNS trigger 283 RETURNS trigger
@@ -664,11 +291,6 @@ END;
664$$; 291$$;
665 292
666 293
667/*
668 * updates timestamp of addresses
669 *
670 * returns trigger
671 */
672CREATE OR REPLACE FUNCTION ip_trigger_update 294CREATE OR REPLACE FUNCTION ip_trigger_update
673( ) 295( )
674 RETURNS trigger 296 RETURNS trigger
@@ -691,61 +313,7 @@ $$;
691 313
692 314
693/* 315/*
694 * Creates entry for NAS client 316 * trigger functions for nas table
695 *
696 * % SELECT nas_add(
697 * nasName := 'br1.cwc',
698 * regionName := 'Anchorage'
699 * );
700 *
701 * nasName
702 * : name of NAS being added to database
703 *
704 * regionName
705 * : the name of the region in which the NAS is physically located
706 *
707 * Returns the `id` of the created nas client
708 */
709CREATE OR REPLACE FUNCTION nas_add
710(
711 nasName VARCHAR(32),
712 regionName VARCHAR(128)
713)
714 RETURNS INTEGER
715 LANGUAGE plpgsql
716 SECURITY DEFINER
717 AS $$
718DECLARE
719 nasRegionId INTEGER;
720 returnId INTEGER;
721BEGIN
722 -- searches for region id
723 SELECT id INTO nasRegionId
724 FROM region
725 WHERE name LIKE lower(regionName);
726 IF ( nasRegionId IS NULL ) THEN
727 RAISE EXCEPTION 'invalid region';
728 END IF;
729
730 -- adds new pool
731 INSERT INTO nas
732 ( name,
733 regionId
734 )
735 VALUES ( lower(nasName),
736 nasRegionId
737 )
738 RETURNING id INTO returnId;
739
740 RETURN returnId;
741END;
742$$;
743
744
745/*
746 * updates timestamp of addresses
747 *
748 * returns trigger
749 */ 317 */
750CREATE OR REPLACE FUNCTION nas_trigger_insert_or_update 318CREATE OR REPLACE FUNCTION nas_trigger_insert_or_update
751( ) 319( )
@@ -764,111 +332,7 @@ $$;
764 332
765 333
766/* 334/*
767 * Creates entry for network block 335 * trigger functions for netblock table
768 *
769 * % SELECT netblock_add(
770 * netAddress := '198.51.100.0/24',
771 * poolName := 'PPPoE'
772 * );
773 * % SELECT netblock_add(
774 * netAddress := '203.0.113.0/24',
775 * poolName := 'DHCP',
776 * regionName := 'Soldotna',
777 * netActive := FALSE,
778 * netDesc := 'Lab IP Pool',
779 * addIpEntries := TRUE
780 * );
781 *
782 * netAddress
783 * : network address and network size being added to database
784 *
785 * poolName
786 * : IP pool network is being assigned
787 *
788 * regionName
789 * : the name of the region in which the network will physically serve
790 *
791 * netActive
792 * : TRUE if addresses within network are assignable, FALSE if addresses are
793 * : not assignable
794 *
795 * netDesc
796 * : human readable description of the network
797 *
798 * addIpEntries
799 * : add coorisponding ip table entries for network
800 *
801 * Returns the `id` of the created nas client
802 */
803CREATE OR REPLACE FUNCTION netblock_add
804(
805 netAddress CIDR,
806 poolName VARCHAR(32),
807 regionName VARCHAR(32) DEFAULT NULL,
808 netActive BOOLEAN DEFAULT TRUE,
809 netDesc VARCHAR(128) DEFAULT NULL,
810 addIpEntries BOOLEAN DEFAULT TRUE
811)
812 RETURNS INTEGER
813 LANGUAGE plpgsql
814 SECURITY DEFINER
815 AS $$
816DECLARE
817 netRegionId INTEGER DEFAULT NULL;
818 netPoolId INTEGER;
819 returnId INTEGER DEFAULT 1;
820BEGIN
821
822 -- restricts access to IPv4 addresses
823 IF ( family(netAddress) != 4 ) THEN
824 RAISE EXCEPTION 'only IPv4 addresses are supported';
825 END IF;
826
827 -- searches for region id
828 if ( regionName IS NOT NULL ) THEN
829 SELECT id INTO netRegionId
830 FROM region
831 WHERE name LIKE lower(regionName);
832 END IF;
833
834 -- searches for pool id
835 SELECT id INTO netPoolId
836 FROM pool
837 WHERE name LIKE lower(poolName);
838 IF ( netPoolId IS NULL ) THEN
839 RAISE EXCEPTION 'invalid pool';
840 END IF;
841
842 -- adds new pool
843 INSERT INTO netblock
844 ( network,
845 poolId,
846 regionId,
847 active,
848 description
849 )
850 VALUES ( netAddress,
851 netPoolId,
852 netRegionId,
853 netActive,
854 netDesc
855 )
856 RETURNING id INTO returnId;
857
858 -- adds IP entries
859 if ( addIpEntries IS NOT FALSE ) THEN
860 PERFORM ip_add_network( ipNetblockId := returnId);
861 END IF;
862
863 RETURN returnId;
864END;
865$$;
866
867
868/*
869 * adds contraints to netblocks
870 *
871 * returns trigger
872 */ 336 */
873CREATE OR REPLACE FUNCTION netblock_trigger_insert_or_update 337CREATE OR REPLACE FUNCTION netblock_trigger_insert_or_update
874( ) 338( )
@@ -915,9 +379,7 @@ $$;
915 379
916 380
917/* 381/*
918 * updates timestamp of addresses 382 * trigger functions for pool table
919 *
920 * returns trigger
921 */ 383 */
922CREATE OR REPLACE FUNCTION pool_trigger_insert_or_update 384CREATE OR REPLACE FUNCTION pool_trigger_insert_or_update
923( ) 385( )
@@ -936,9 +398,7 @@ $$;
936 398
937 399
938/* 400/*
939 * updates timestamp of addresses 401 * trigger functions for region table
940 *
941 * returns trigger
942 */ 402 */
943CREATE OR REPLACE FUNCTION region_trigger_insert_or_update 403CREATE OR REPLACE FUNCTION region_trigger_insert_or_update
944( ) 404( )
@@ -1016,4 +476,5 @@ CREATE TRIGGER region_trigger_insert_or_update
1016 FOR EACH ROW 476 FOR EACH ROW
1017 EXECUTE PROCEDURE region_trigger_insert_or_update(); 477 EXECUTE PROCEDURE region_trigger_insert_or_update();
1018 478
479
1019/* end of sql */ 480/* end of sql */