From cf9cdd6b146c9cac2d3693b5a3c14cd2d4d87298 Mon Sep 17 00:00:00 2001 From: "David M. Syzdek" Date: Fri, 24 May 2019 12:07:09 -0800 Subject: Finishing ip assignment functions --- notes/projects/radius/sql/ippool-demo.sql | 31 +++++++++++++++- notes/projects/radius/sql/ippool-functions.sql | 49 ++++++++++++++------------ notes/projects/radius/sql/ippool-schema.sql | 4 ++- 3 files changed, 60 insertions(+), 24 deletions(-) (limited to 'notes') diff --git a/notes/projects/radius/sql/ippool-demo.sql b/notes/projects/radius/sql/ippool-demo.sql index 57352d8..d0a992b 100644 --- a/notes/projects/radius/sql/ippool-demo.sql +++ b/notes/projects/radius/sql/ippool-demo.sql @@ -89,11 +89,17 @@ SELECT netblock_add ( netAddress := '209.193.4.128/25', poolName := 'Reserved', netBusyout := TRUE, - addIpEntries := FALSE + addIpEntries := TRUE ); -- create example explicit IP reservations +SELECT ip_assign + ( assignIp := '209.193.4.3/32', + assignNas := 'br1.sol', + assignUser := 'busy1@example.net', + assignForce := TRUE + ); SELECT ip_assign ( assignIp := '209.193.4.8/32', assignNas := 'br1.sol', @@ -126,6 +132,29 @@ SELECT ip_assign ); +-- auto-assign IP reservations +SELECT ip_autoassign + ( assignUser := 'autoassign1@example.net', + assignNas := 'br1.sol', + assignPool := 'dhcp' + ); +SELECT ip_autoassign + ( assignUser := 'autoassign2@example.net', + assignNas := 'br1.sol', + assignPool := 'dhcp' + ); +SELECT ip_autoassign + ( assignUser := 'autoassign3@example.net', + assignNas := 'br1.sol', + assignPool := 'dhcp' + ); +SELECT ip_autoassign + ( assignUser := 'autoassign4@example.net', + assignNas := 'br1.sol', + assignPool := 'dhcp' + ); + + -- add missing IP addresses to netblock -- SELECT ip_add_network( ipNetwork := '209.112.192.0/19' ); -- SELECT ip_add_network( ipNetwork := '209.112.128.0/18' ); diff --git a/notes/projects/radius/sql/ippool-functions.sql b/notes/projects/radius/sql/ippool-functions.sql index 7ec18a6..df9e27b 100644 --- a/notes/projects/radius/sql/ippool-functions.sql +++ b/notes/projects/radius/sql/ippool-functions.sql @@ -54,6 +54,7 @@ DECLARE resultAddress INET; ipAdded INTEGER; ipMask INTEGER; + ipOctet4 INET; BEGIN -- verifies input @@ -81,26 +82,30 @@ BEGIN END IF; -- sets starting values - ipAdded := 0; - ipAddress := set_masklen( network(ipNetwork), 32 ); + ipAdded := 0; + ipAddress := set_masklen( network(ipNetwork), 32 ); -- loops through individual IP addresses LOOP EXIT WHEN ipAddress > set_masklen( broadcast(ipNetwork), 32 ); - INSERT INTO ip - ( address, - netblockId - ) - SELECT ipAddress, - ipNetblockId - WHERE NOT EXISTS - ( SELECT address FROM ip WHERE address = ipAddress ) - RETURNING address INTO resultAddress; - IF ( resultAddress IS NOT NULL ) THEN - ipAdded := ipAdded + 1; + ipOctet4 := ipAddress & '0.0.0.255'; + + IF ( ( ipOctet4 != '0.0.0.0' ) AND ( ipOctet4 != '0.0.0.255' ) ) THEN + INSERT INTO ip + ( address, + netblockId + ) + SELECT ipAddress, + ipNetblockId + WHERE NOT EXISTS + ( SELECT address FROM ip WHERE address = ipAddress ) + RETURNING address INTO resultAddress; + IF ( resultAddress IS NOT NULL ) THEN + ipAdded := ipAdded + 1; + END IF; END IF; - ipAddress := ipAddress + 1; + ipAddress := ipAddress + 1; END LOOP; @@ -361,12 +366,12 @@ BEGIN INTO assignedIp FROM ip INNER JOIN netblock AS nb - ON nb.network >>= ip.address + ON nb.id = ip.netblockId WHERE ip.nasId IS NULL AND ip.username LIKE lower( assignUser ) AND nb.poolId = assignPoolId AND nb.regionId = assignRegionId - AND nb.assignable = TRUE + AND nb.busyout = FALSE ORDER BY ip.modified DESC, ip.address ASC LIMIT 1; @@ -378,11 +383,11 @@ BEGIN INTO assignedIp FROM ip INNER JOIN netblock AS nb - ON nb.network >>= ip.address + ON nb.id = ip.netblockId WHERE ip.nasId IS NULL AND nb.poolId = assignPoolId AND nb.regionId = assignRegionId - AND nb.assignable = TRUE + AND nb.busyout = FALSE ORDER BY ip.modified ASC, ip.address ASC LIMIT 1; @@ -394,12 +399,12 @@ BEGIN INTO assignedIp FROM ip INNER JOIN netblock AS nb - ON nb.network >>= ip.address + ON nb.id = ip.netblockId WHERE ip.nasId IS NULL AND ip.username LIKE lower( assignUser ) AND nb.poolId = assignPoolId AND nb.regionId IS NULL - AND nb.assignable = TRUE + AND nb.busyout = FALSE ORDER BY ip.modified DESC, ip.address ASC LIMIT 1; @@ -411,11 +416,11 @@ BEGIN INTO assignedIp FROM ip INNER JOIN netblock AS nb - ON nb.network >>= ip.address + ON nb.id = ip.netblockId WHERE ip.nasId IS NULL AND nb.poolId = assignPoolId AND nb.regionId IS NULL - AND nb.assignable = TRUE + AND nb.busyout = FALSE ORDER BY ip.modified ASC, ip.address ASC LIMIT 1; diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql index d64dfd5..7348ca3 100644 --- a/notes/projects/radius/sql/ippool-schema.sql +++ b/notes/projects/radius/sql/ippool-schema.sql @@ -328,7 +328,9 @@ BEGIN END IF; -- update timestamp - IF ( ( OLD.nasId != NEW.nasId ) OR ( OLD.username != NEW.username ) ) THEN + -- RAISE EXCEPTION 'Old.nasId = %; NEW.nasId = %;', OLD.nasId, NEW.nasId; + IF ( ( OLD.nasId IS DISTINCT FROM NEW.nasId ) OR + ( OLD.username IS DISTINCT FROM NEW.username ) ) THEN NEW.modified := current_timestamp; END IF; -- cgit v1.2.3