From 5185cbf5faf2763ceb0bf1dffb47a9b023ab2cf9 Mon Sep 17 00:00:00 2001 From: "David M. Syzdek" Date: Mon, 20 May 2019 22:07:33 -0800 Subject: Checkpointing changes --- notes/projects/radius/sql/ippool-demo.sql | 40 ++-- notes/projects/radius/sql/ippool-functions.sql | 277 ++++++++++++++++++------- notes/projects/radius/sql/ippool-queries.sql | 27 ++- notes/projects/radius/sql/ippool-schema.sql | 10 +- 4 files changed, 244 insertions(+), 110 deletions(-) (limited to 'notes') 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 poolName := 'DHCP', regionName := 'Soldotna', netDesc := 'Reserved for engineering test accounts', - netActive := FALSE + netAssignable := FALSE ); SELECT netblock_add ( netAddress := '209.193.4.4/30', @@ -51,7 +51,8 @@ SELECT netblock_add -- SELECT netblock_add ( netAddress := '209.193.4.16/28', - poolName := 'DHCP' + poolName := 'PPPoE', + regionName := 'Soldotna' ); SELECT netblock_add ( netAddress := '209.193.4.32/28', @@ -60,25 +61,34 @@ SELECT netblock_add ); SELECT netblock_add ( netAddress := '209.193.4.48/28', - poolName := 'DHCP', - regionName := 'Fairbanks' + poolName := 'PPPoE', + regionName := 'Anchorage' + ); +SELECT netblock_add + ( netAddress := '209.193.4.64/28', + poolName := 'DHCP' + ); +SELECT netblock_add + ( netAddress := '209.193.4.80/28', + poolName := 'PPPoE' ); SELECT netblock_add - ( netAddress := '209.193.4.64/26', + ( netAddress := '209.193.4.96/28', poolName := 'DHCP', - netDesc := 'Reserved for documentation', - netActive := FALSE, + netAssignable := FALSE, addIpEntries := FALSE ); SELECT netblock_add - ( netAddress := '209.193.4.128/26', + ( netAddress := '209.193.4.112/28', poolName := 'PPPoE', - regionName := 'Soldotna' + netAssignable := FALSE, + addIpEntries := FALSE ); SELECT netblock_add - ( netAddress := '209.193.4.192/26', - poolName := 'PPPoE', - regionName := 'Anchorage' + ( netAddress := '209.193.4.128/25', + poolName := 'Reserved', + netAssignable := FALSE, + addIpEntries := FALSE ); @@ -86,14 +96,12 @@ SELECT netblock_add SELECT ip_assign ( assignIp := '209.193.4.8/32', assignNas := 'br1.sol', - assignUser := 'jdoe8@example.net', - assignSess := 'aqaqqaasdqweaasdqa8' + assignUser := 'jdoe8@example.net' ); SELECT ip_assign ( assignIp := '209.193.4.9/32', assignNas := 'br1.sol', - assignUser := 'jdoe9@aexample.net', - assignSess := 'qweadszcxqweasdzcx9' + assignUser := 'jdoe9@aexample.net' ); SELECT ip_assign ( 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 @@ $$; * assignUser := 'jdoe@acsalaska.net', * assignPortLimit := 5, * assignNas := 'br1.cwc', - * assignSess := '596c3b82-75df-11e9-bcbb-6003089b9f24', * assignForce := FALSE * ); * @@ -137,11 +136,9 @@ $$; * assignNas * : session NAS (attempt to guess if not specified) * - * assignSess - * : RADIUS session - * * assignForce - * : force IP assignment, even if netblock is inactive (i.e. static assignments) + * : force IP assignment, even if netblock is not assignable (i.e. static + * assignments) * * Returns TRUE if assigned and FALSE if not assigned */ @@ -151,7 +148,6 @@ CREATE OR REPLACE FUNCTION ip_assign assignUser TEXT, assignPortLimit INTEGER DEFAULT 2, assignNas TEXT DEFAULT NULL, - assignSess TEXT DEFAULT NULL, assignForce BOOLEAN DEFAULT FALSE ) RETURNS BOOLEAN @@ -163,11 +159,6 @@ DECLARE assignPortCount INTEGER; BEGIN - -- sets defaults - IF ( assignSess IS NULL ) THEN - assignSess := 'user:' || assignUser; - END IF; - -- tallies existing sessions SELECT count( address ) AS ipCount INTO assignPortCount @@ -179,12 +170,12 @@ BEGIN RETURN FALSE; END IF; - -- verifies IP address is not inactive + -- verifies IP address is assignable IF ( assignForce = FALSE ) THEN PERFORM network FROM netblock WHERE netblock.network >>= assignIp - AND netblock.active = FALSE; + AND netblock.assignable = FALSE; IF FOUND THEN RETURN FALSE; END IF; @@ -213,8 +204,7 @@ BEGIN -- updates record UPDATE ip SET nasId = ( SELECT id FROM nas WHERE name LIKE assignNas ), - username = assignUser, - acctSession = assignSess + username = assignUser WHERE address = set_masklen( assignIp, 32 ) AND nasId IS NULL; @@ -223,6 +213,63 @@ END; $$; +/* + * audits ip + * + * % SELECT * FROM ip_audit(); + * + * Returns table of ip + */ +CREATE OR REPLACE FUNCTION ip_audit +( +) + RETURNS TABLE + ( + poolName VARCHAR(32), + regionName VARCHAR(32), + ipAddress INET, + enabled BOOLEAN, + assigned BOOLEAN, + modified TIMESTAMP WITH TIME ZONE, + nasName VARCHAR(32), + username VARCHAR(32) + ) + LANGUAGE plpgsql + SECURITY DEFINER + AS $$ +DECLARE +BEGIN + + RETURN QUERY + SELECT DISTINCT pool.name AS poolName, + region.name AS regionName, + ip.address AS address, + TRUE = ALL + ( + SELECT nb2.assignable + FROM netblock AS nb2 + WHERE ip.address <<= nb2.network + ) AS assignable, + ( + nasId IS NOT NULL + ) AS assigned, + ip.modified AS modified, + nas.name AS nasName, + ip.username AS username + FROM ip + INNER JOIN netblock + ON ip.address <<= netblock.network + INNER JOIN pool + ON netblock.poolId = pool.id + LEFT OUTER JOIN region + ON netblock.regionId = region.id + LEFT OUTER JOIN nas + ON ip.nasId = nas.id; + +END; +$$; + + /* * Auto-assigns IP address to specified user/session * @@ -236,7 +283,6 @@ $$; * assignNas := 'br1.sol', * assignPool := 'dhcp', * assignPortLimit := 5, - * assignSess := 'b4356b24-7670-11e9-84a0-6003089b9f24', * assignPrevIp := TRUE * ); * @@ -252,9 +298,6 @@ $$; * assignPortLimit * : specifies maximum IP assignments per user allowed * - * assignSess (default: auto-generated from username) - * : RADIUS session - * * assignPrevIp (default: TRUE) * : attempt to re-assign user's previous IP address * @@ -266,7 +309,6 @@ CREATE OR REPLACE FUNCTION ip_autoassign assignNas TEXT, assignPool TEXT, assignPortLimit INTEGER DEFAULT 2, - assignSess TEXT DEFAULT NULL, assignPrevIp BOOLEAN DEFAULT TRUE ) RETURNS INET @@ -276,83 +318,118 @@ CREATE OR REPLACE FUNCTION ip_autoassign DECLARE assignedIp INET; assignNasId INTEGER; + assignRegionId INTEGER; + assignPoolId INTEGER; assignPortCount INTEGER; BEGIN - -- sets defaults - IF ( assignSess IS NULL ) THEN - assignSess := 'user:' || assignUser; - END IF; - -- tallies existing sessions SELECT count( address ) INTO assignPortCount FROM ip - WHERE ip.username LIKE assignUser - AND ip.nasId IS NOT NULL - AND ip.address != assignIp; + WHERE ip.username LIKE lower( assignUser ) + AND ip.nasId IS NOT NULL; IF ( assignPortCount >= assignPortLimit ) THEN RETURN NULL; END IF; - -- search for user's previous address - IF ( assignPrevIp IS TRUE ) THEN - SELECT ip.address, + -- determines NAS ID and region ID + SELECT region.id, nas.id - INTO assignedIp, + INTO assignRegionId, assignNasId + FROM nas + INNER JOIN region + ON region.id = nas.regionId + WHERE nas.name LIKE lower( assignNas ); + IF NOT FOUND THEN + RETURN NULL; + END IF; + + -- determines pool ID + SELECT id + INTO assignPoolId + FROM pool + WHERE pool.name LIKE lower( assignPool ); + IF NOT FOUND THEN + RETURN NULL; + END IF; + + -- search for user's previous address in region + IF ( assignPrevIp IS TRUE ) THEN + SELECT ip.address + INTO assignedIp FROM ip - INNER JOIN netblock - ON netblock.network >>= ip.address - INNER JOIN region - ON region.id = netblock.regionId - INNER JOIN pool - ON pool.id = netblock.poolId - INNER JOIN nas - ON nas.regionId = region.id + INNER JOIN netblock AS nb + ON nb.network >>= ip.address WHERE ip.nasId IS NULL AND ip.username LIKE lower( assignUser ) - AND nas.name LIKE lower( assignNas ) - AND pool.name LIKE lower( assignPool ) - AND netblock.active = TRUE + AND nb.poolId = assignPoolId + AND nb.regionId = assignRegionId + AND nb.assignable = TRUE ORDER BY ip.modified DESC, ip.address ASC LIMIT 1; END IF; - -- if previous address is unavailable, find longest idle address + -- if previous address is unavailable, find longest idle address in region IF ( assignedIp IS NULL ) THEN - SELECT ip.address, - nas.id - INTO assignedIp, - assignNasId + SELECT ip.address + INTO assignedIp FROM ip - INNER JOIN netblock - ON netblock.network >>= ip.address - INNER JOIN region - ON region.id = netblock.regionId - INNER JOIN pool - ON pool.id = netblock.poolId - INNER JOIN nas - ON nas.regionId = region.id + INNER JOIN netblock AS nb + ON nb.network >>= ip.address + WHERE ip.nasId IS NULL + AND nb.poolId = assignPoolId + AND nb.regionId = assignRegionId + AND nb.assignable = TRUE + ORDER BY ip.modified ASC, + ip.address ASC + LIMIT 1; + END IF; + + -- search for user's previous address out of region + IF ( ( assignedIp IS NULL ) AND ( assignPrevIp IS TRUE ) ) THEN + SELECT ip.address + INTO assignedIp + FROM ip + INNER JOIN netblock AS nb + ON nb.network >>= ip.address WHERE ip.nasId IS NULL AND ip.username LIKE lower( assignUser ) - AND nas.name LIKE lower( assignNas ) - AND pool.name LIKE lower( assignPool ) - AND netblock.active = TRUE + AND nb.poolId = assignPoolId + AND nb.regionId IS NULL + AND nb.assignable = TRUE ORDER BY ip.modified DESC, ip.address ASC LIMIT 1; - IF NOT FOUND THEN - RETURN NULL; - END IF; + END IF; + + -- if previous address is unavailable, find longest idle address out of region + IF ( assignedIp IS NULL ) THEN + SELECT ip.address + INTO assignedIp + FROM ip + INNER JOIN netblock AS nb + ON nb.network >>= ip.address + WHERE ip.nasId IS NULL + AND nb.poolId = assignPoolId + AND nb.regionId IS NULL + AND nb.assignable = TRUE + ORDER BY ip.modified ASC, + ip.address ASC + LIMIT 1; + END IF; + + -- exits if an appropiate IP address was not found + IF ( assignedIp IS NULL ) THEN + RETURN NULL; END IF; -- update record UPDATE ip SET nasId = assignNasId, - username = assignUser, - acctSession = assignSess + username = assignUser WHERE address = assignedIp; IF NOT FOUND THEN RETURN NULL; @@ -382,8 +459,7 @@ BEGIN -- updates record UPDATE ip - SET nasId = NULL, - acctSession = NULL + SET nasId = NULL WHERE address = set_masklen( assignIp, 32 ); RETURN FOUND; @@ -454,7 +530,7 @@ $$; * netAddress := '203.0.113.0/24', * poolName := 'DHCP', * regionName := 'Soldotna', - * netActive := FALSE, + * netAssignable := FALSE, * netDesc := 'Lab IP Pool', * addIpEntries := TRUE * ); @@ -468,7 +544,7 @@ $$; * regionName * : the name of the region in which the network will physically serve * - * netActive + * netAssignable * : TRUE if addresses within network are assignable, FALSE if addresses are * : not assignable * @@ -485,7 +561,7 @@ CREATE OR REPLACE FUNCTION netblock_add netAddress CIDR, poolName VARCHAR(32), regionName VARCHAR(32) DEFAULT NULL, - netActive BOOLEAN DEFAULT TRUE, + netAssignable BOOLEAN DEFAULT TRUE, netDesc VARCHAR(128) DEFAULT NULL, addIpEntries BOOLEAN DEFAULT TRUE ) @@ -524,13 +600,13 @@ BEGIN ( network, poolId, regionId, - active, + assignable, description ) VALUES ( netAddress, netPoolId, netRegionId, - netActive, + netAssignable, netDesc ) RETURNING id INTO returnId; @@ -545,4 +621,63 @@ END; $$; +/* + * audits netblocks + * + * % SELECT * FROM netblock_audit(); + * + * Returns table of netblocks + */ +CREATE OR REPLACE FUNCTION netblock_audit +( +) + RETURNS TABLE + ( + poolName VARCHAR(32), + regionName VARCHAR(32), + network CIDR, + assignable BOOLEAN, + addressesAssigned BIGINT, + addressesAvailable BIGINT, + addressesTotal BIGINT, + description VARCHAR(256) + ) + LANGUAGE plpgsql + SECURITY DEFINER + AS $$ +DECLARE +BEGIN + + RETURN QUERY + SELECT DISTINCT pool.name AS poolName, + region.name AS regionname, + netblock.network AS network, + netblock.assignable AS assignable, + ( + SELECT count(address) + FROM ip + WHERE address <<= netblock.network + AND ip.nasId IS NOT NULL + ) AS addressAssigned, + ( + SELECT count(address) + FROM ip + WHERE address <<= netblock.network + AND ip.nasId IS NULL + ) AS addressesAvailable, + ( + SELECT count(address) + FROM ip + WHERE address <<= netblock.network + ) AS addressesTotal, + netblock.description AS description + FROM netblock + INNER JOIN pool + ON netblock.poolId = pool.id + LEFT OUTER JOIN region + ON netblock.regionId = region.id; + +END; +$$; + /* 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; SELECT ip.address AS address, ip.modified AS modified, ip.username AS username, - ip.acctsession AS acctsession, pool.name AS pool, region.name AS region FROM ip @@ -37,7 +36,7 @@ SELECT ip.address AS address, AND ip.username LIKE lower( :'myUsername' ) AND nas.name LIKE lower( :'myNasName' ) AND pool.name LIKE lower( :'myPoolName' ) - AND netblock.active = TRUE + AND netblock.assignable = TRUE ORDER BY ip.modified DESC, ip.address ASC LIMIT 1; @@ -54,12 +53,12 @@ SELECT ip.address AS address, SELECT ip.address AS address, ip.modified AS modified, ip.username AS username, - ip.acctsession AS acctsession, pool.name AS pool, region.name AS region FROM ip INNER JOIN netblock ON netblock.network >>= ip.address + AND netblock.assignable IS TRUE INNER JOIN region ON region.id = netblock.regionId INNER JOIN pool @@ -69,7 +68,7 @@ SELECT ip.address AS address, WHERE ip.nasId IS NULL AND nas.name LIKE lower( :'myNasName' ) AND pool.name LIKE lower( :'myPoolName' ) - AND netblock.active = TRUE + AND netblock.assignable = TRUE ORDER BY ip.modified ASC, ip.address ASC LIMIT 1; @@ -96,7 +95,7 @@ SELECT DISTINCT pool.name AS poolName, ip.address AS address, TRUE = ALL ( - SELECT nb2.active + SELECT nb2.assignable FROM netblock AS nb2 WHERE ip.address <<= nb2.network ) AS active, @@ -105,8 +104,7 @@ SELECT DISTINCT pool.name AS poolName, ) AS assigned, ip.modified AS modified, nas.name AS nasName, - ip.username AS username, - ip.acctSession AS acctSession + ip.username AS username FROM ip INNER JOIN netblock ON ip.address <<= netblock.network @@ -127,15 +125,14 @@ SELECT DISTINCT pool.name AS poolName, SELECT * FROM netblock AS nb2 WHERE ip.address <<= nb2.network - AND NOT nb2.active + AND NOT nb2.assignable ) AS active, ( nasId IS NOT NULL ) AS assigned, ip.modified AS modified, nas.name AS nasName, - ip.username AS username, - ip.acctSession AS acctSession + ip.username AS username FROM ip INNER JOIN netblock ON ip.address <<= netblock.network @@ -153,11 +150,11 @@ SELECT DISTINCT pool.name AS poolName, */ SELECT DISTINCT ip.address AS ipAddress, netblock.network AS network, - netblock.active AS active + netblock.assignable AS active FROM ip INNER JOIN netblock ON ip.address <<= netblock.network - WHERE netblock.active = FALSE + WHERE netblock.assignable = FALSE ORDER BY ipAddress; @@ -166,18 +163,18 @@ SELECT DISTINCT ip.address AS ipAddress, */ SELECT DISTINCT netblock.regionId AS regionId, ip.address AS ipAddress, - netblock.active AS active + netblock.assignable AS active FROM ip INNER JOIN netblock ON ip.address <<= netblock.network - WHERE netblock.active = TRUE + WHERE netblock.assignable = TRUE AND ip.address NOT IN ( SELECT address AS ipAddress FROM ip INNER JOIN netblock ON ip.address <<= netblock.network - AND netblock.active = FALSE + AND netblock.assignable = FALSE ) ORDER BY ipAddress; 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 network CIDR NOT NULL UNIQUE, poolId INTEGER NOT NULL, regionId INTEGER, - active BOOLEAN NOT NULL DEFAULT TRUE, + assignable BOOLEAN NOT NULL DEFAULT TRUE, description VARCHAR(256), PRIMARY KEY ( id ), FOREIGN KEY ( poolId ) @@ -173,7 +173,7 @@ CREATE INDEX ( id, poolId, network, - active + assignable ); @@ -192,9 +192,6 @@ CREATE INDEX * username * : username currently assigned or last assigned address * - * acctSession - * : RADIUS accounting session currently holding address - * * nasId * : reference to RADIUS NAS client currently holding address */ @@ -205,7 +202,6 @@ CREATE TABLE IF NOT EXISTS ip modified timestamp WITH TIME ZONE NOT NULL DEFAULT TIMESTAMP 'epoch', username VARCHAR(64), - acctSession VARCHAR(32), nasId INTEGER, PRIMARY KEY ( address ), FOREIGN KEY ( nasId ) @@ -222,7 +218,6 @@ CREATE INDEX netblockId, modified, username, - acctSession, nasId ); @@ -264,7 +259,6 @@ BEGIN -- forces initial values NEW.username := NULL; - NEW.acctSession := NULL; NEW.nasId := NULL; NEW.modified := TIMESTAMP 'epoch'; -- cgit v1.2.3