From 1b668925976d68463d07192b6731ec6cda9b3445 Mon Sep 17 00:00:00 2001 From: "David M. Syzdek" Date: Tue, 14 May 2019 11:04:33 -0800 Subject: splitting user functions into separate source file --- notes/projects/radius/sql/ippool-functions.sql | 536 +++++++++++++++++++++++ notes/projects/radius/sql/ippool-schema.sql | 561 +------------------------ notes/projects/radius/sql/ippool.md | 14 +- 3 files changed, 555 insertions(+), 556 deletions(-) create mode 100644 notes/projects/radius/sql/ippool-functions.sql diff --git a/notes/projects/radius/sql/ippool-functions.sql b/notes/projects/radius/sql/ippool-functions.sql new file mode 100644 index 0000000..bbfd60f --- /dev/null +++ b/notes/projects/radius/sql/ippool-functions.sql @@ -0,0 +1,536 @@ +/* + * IP Engineering RADIUS Servers + * + * ippool-schema.sql - builds database schema for RADIUS IP Pools + * + * Usage: psql -f ippool-schema.sql radius_ippool + */ + + +SET ROLE radius; +SET client_min_messages TO WARNING; +\set ON_ERROR_STOP on + + +-- ///////////////// +-- // // +-- // Functions // +-- // // +-- ///////////////// + +/* + * Creates IP entries for the given network block + * + * % SELECT ip_add_network( + * ipNetwork := '198.51.100.0/24' + * ); + * % SELECT ip_add_network( + * ipNetblockId := ( + * SELECT id + * FROM netblock + * WHERE network = '198.51.100.0/24' + * ) + * ); + * + * ipNetwork + * : network address and network size being added to database + * + * ipNetblockId + * : reference to netblock to add to database + * + * Returns the number of new IP addresses registered + */ +CREATE OR REPLACE FUNCTION ip_add_network +( + ipNetwork CIDR DEFAULT NULL, + ipNetblockId INTEGER DEFAULT NULL +) + RETURNS INTEGER + LANGUAGE plpgsql + SECURITY DEFINER + AS $$ +DECLARE + ipAddress INET; + resultAddress INET; + ipAdded INTEGER; + ipMask INTEGER; +BEGIN + + -- verifies input + IF ( ( ipNetwork IS NULL ) AND ( ipNetblockId IS NULL ) ) THEN + RAISE EXCEPTION 'must specify either ipNetwork or ipNetblockId'; + END IF; + + -- determines netblock + if ( ipNetblockId IS NULL ) THEN + SELECT id INTO ipNetblockId + FROM netblock + WHERE network >>= ipNetwork; + ELSE + SELECT network INTO ipNetwork + FROM netblock + WHERE id = ipNetblockId; + END IF; + if ( ( ipNetblockId IS NULL ) OR ( ipNetwork IS NULL ) ) THEN + RAISE EXCEPTION 'specified netblock does not exist'; + END IF; + + -- restricts access to IPv4 addresses + IF ( family(ipNetwork) != 4 ) THEN + RAISE EXCEPTION 'only IPv4 addresses are supported'; + END IF; + + -- sets starting values + 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; + END IF; + ipAddress := ipAddress + 1; + + END LOOP; + + RETURN ipAdded; +END; +$$; + + +/* + * Assigns specific IP address to specified user/session + * + * % SELECT ip_assign( + * assignIp := '198.51.100.1/32', + * assignUser := 'jdoe@acsalaska.net' + * ); + * % SELECT ip_assign( + * assignIp := '198.51.100.1/32', + * assignUser := 'jdoe@acsalaska.net', + * assignPortLimit := 5, + * assignNas := 'br1.cwc', + * assignSess := '596c3b82-75df-11e9-bcbb-6003089b9f24', + * assignForce := FALSE + * ); + * + * assignIp + * : IP address to assign to user + * + * assignUser + * : user to assign IP address + * + * assignPortLimit + * : specifies maximum IP assignments per user allowed + * + * 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) + * + * Returns TRUE if assigned and FALSE if not assigned + */ +CREATE OR REPLACE FUNCTION ip_assign +( + assignIp INET, + assignUser TEXT, + assignPortLimit INTEGER DEFAULT 2, + assignNas TEXT DEFAULT NULL, + assignSess TEXT DEFAULT NULL, + assignForce BOOLEAN DEFAULT FALSE +) + RETURNS BOOLEAN + LANGUAGE plpgsql + SECURITY DEFINER + AS $$ +DECLARE + assignNasId INTEGER; + assignPortCount INTEGER; +BEGIN + + -- sets defaults + IF ( assignSess IS NULL ) THEN + assignSess := 'user:' || assignUser; + END IF; + + -- tallies existing sessions + SELECT count(id) + INTO assignPortCount + FROM ip + WHERE ip.username LIKE assignUser + AND ip.nasId IS NOT NULL + AND ip.address != assignIp; + IF ( assignPortCount >= assignPortLimit ) THEN + RETURN FALSE; + END IF; + + -- verifies IP address is not inactive + IF ( assignForce = FALSE ) THEN + SELECT network + FROM netblock + WHERE netblock.network >>= assignIp + AND netblock.active IS FALSE; + IF FOUND THEN + RETURN FALSE; + END IF; + END IF; + + -- attempts to determine NAS + IF ( assignNas IS NOT NULL ) THEN + SELECT id + INTO assignNasId + FROM nas + WHERE name LIKE assignNas; + ELSE + SELECT nas.id + INTO assignNasId + FROM nas + INNER JOIN region + ON region.id = nas.regionId + INNER JOIN netblock + ON netblock.regionId = regoin.id + WHERE netblock.network >>= assignIp; + END IF; + IF ( assignNasId IS NULL ) THEN + RETURN FALSE; + END IF; + + -- updates record + UPDATE ip + SET nasId = ( SELECT id FROM nas WHERE name LIKE assignNas ), + username = assignUser, + acctSession = assignSess + WHERE address = set_masklen( assignIp, 32 ) + AND nasId IS NULL; + + RETURN FOUND; +END; +$$; + + +/* + * Auto-assigns IP address to specified user/session + * + * % SELECT ip_autoassign( + * assignUser := 'jdoe@acsalaska.net', + * assignNas := 'br1.sol', + * assignPool := 'dhcp' + * ); + * % SELECT ip_autoassign( + * assignUser := 'jdoe@acsalaska.net', + * assignNas := 'br1.sol', + * assignPool := 'dhcp', + * assignPortLimit := 5, + * assignSess := 'b4356b24-7670-11e9-84a0-6003089b9f24', + * assignPrevIp := TRUE + * ); + * + * assignUser + * : user to assign IP address + * + * assignNas + * : NAS to assign to IP address and implies region from which to pull IP + * + * assignPool + * : IP pool to assign from which to assign IP address + * + * 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 + * + * Returns INET with value of assigned IP address + */ +CREATE OR REPLACE FUNCTION ip_autoassign +( + assignUser TEXT, + assignNas TEXT, + assignPool TEXT, + assignPortLimit INTEGER, DEFAULT 2, + assignSess TEXT DEFAULT NULL, + assignPrevIp BOOLEAN DEFAULT TRUE +) + RETURNS INET + LANGUAGE plpgsql + SECURITY DEFINER + AS $$ +DECLARE + assignedIp INET; + assignNasId INTEGER; +BEGIN + + -- sets defaults + IF ( assignSess IS NULL ) THEN + assignSess := 'user:' || assignUser; + END IF; + + -- search for user's previous address + IF ( assignPrevIp IS TRUE ) THEN + SELECT ip.address, + nas.id + INTO assignedIp, + assignNasId + 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 + 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 + ORDER BY ip.modified DESC, + ip.address ASC + LIMIT 1; + END IF; + + -- if previous address is unavailable, find longest idle address + IF ( assignedIp IS NULL ) THEN + SELECT ip.address, + nas.id + INTO assignedIp, + assignNasId + 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 + 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 + ORDER BY ip.modified DESC, + ip.address ASC + LIMIT 1; + IF NOT FOUND THEN + RETURN NULL; + END IF; + END IF; + + -- update record + UPDATE ip + SET nasId = assignNasId, + username = assignUser, + acctSession = assignSess + WHERE address = assignedIp; + IF NOT FOUND THEN + RETURN NULL; + END IF; + + RETURN assignedIp; +END; +$$; + + + +/* + * updates timestamp of addresses + * + * returns trigger + */ +CREATE OR REPLACE FUNCTION ip_release +( + assignIp INET +) + RETURNS BOOLEAN + LANGUAGE plpgsql + SECURITY DEFINER + AS $$ +DECLARE +BEGIN + + -- updates record + UPDATE ip + SET nasId = NULL, + acctSession = NULL + WHERE address = set_masklen( assignIp, 32 ); + + RETURN FOUND; +END; +$$; + + +/* + * Creates entry for NAS client + * + * % SELECT nas_add( + * nasName := 'br1.cwc', + * regionName := 'Anchorage' + * ); + * + * nasName + * : name of NAS being added to database + * + * regionName + * : the name of the region in which the NAS is physically located + * + * Returns the `id` of the created nas client + */ +CREATE OR REPLACE FUNCTION nas_add +( + nasName VARCHAR(32), + regionName VARCHAR(128) +) + RETURNS INTEGER + LANGUAGE plpgsql + SECURITY DEFINER + AS $$ +DECLARE + nasRegionId INTEGER; + returnId INTEGER; +BEGIN + -- searches for region id + SELECT id INTO nasRegionId + FROM region + WHERE name LIKE lower(regionName); + IF ( nasRegionId IS NULL ) THEN + RAISE EXCEPTION 'invalid region'; + END IF; + + -- adds new pool + INSERT INTO nas + ( name, + regionId + ) + VALUES ( lower(nasName), + nasRegionId + ) + RETURNING id INTO returnId; + + RETURN returnId; +END; +$$; + + +/* + * Creates entry for network block + * + * % SELECT netblock_add( + * netAddress := '198.51.100.0/24', + * poolName := 'PPPoE' + * ); + * % SELECT netblock_add( + * netAddress := '203.0.113.0/24', + * poolName := 'DHCP', + * regionName := 'Soldotna', + * netActive := FALSE, + * netDesc := 'Lab IP Pool', + * addIpEntries := TRUE + * ); + * + * netAddress + * : network address and network size being added to database + * + * poolName + * : IP pool network is being assigned + * + * regionName + * : the name of the region in which the network will physically serve + * + * netActive + * : TRUE if addresses within network are assignable, FALSE if addresses are + * : not assignable + * + * netDesc + * : human readable description of the network + * + * addIpEntries + * : add coorisponding ip table entries for network + * + * Returns the `id` of the created nas client + */ +CREATE OR REPLACE FUNCTION netblock_add +( + netAddress CIDR, + poolName VARCHAR(32), + regionName VARCHAR(32) DEFAULT NULL, + netActive BOOLEAN DEFAULT TRUE, + netDesc VARCHAR(128) DEFAULT NULL, + addIpEntries BOOLEAN DEFAULT TRUE +) + RETURNS INTEGER + LANGUAGE plpgsql + SECURITY DEFINER + AS $$ +DECLARE + netRegionId INTEGER DEFAULT NULL; + netPoolId INTEGER; + returnId INTEGER DEFAULT 1; +BEGIN + + -- restricts access to IPv4 addresses + IF ( family(netAddress) != 4 ) THEN + RAISE EXCEPTION 'only IPv4 addresses are supported'; + END IF; + + -- searches for region id + if ( regionName IS NOT NULL ) THEN + SELECT id INTO netRegionId + FROM region + WHERE name LIKE lower(regionName); + END IF; + + -- searches for pool id + SELECT id INTO netPoolId + FROM pool + WHERE name LIKE lower(poolName); + IF ( netPoolId IS NULL ) THEN + RAISE EXCEPTION 'invalid pool'; + END IF; + + -- adds new pool + INSERT INTO netblock + ( network, + poolId, + regionId, + active, + description + ) + VALUES ( netAddress, + netPoolId, + netRegionId, + netActive, + netDesc + ) + RETURNING id INTO returnId; + + -- adds IP entries + if ( addIpEntries IS NOT FALSE ) THEN + PERFORM ip_add_network( ipNetblockId := returnId); + END IF; + + RETURN returnId; +END; +$$; + + +/* end of sql */ 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 ); --- ///////////////// --- // // --- // Functions // --- // // --- ///////////////// +-- ///////////////////////// +-- // // +-- // Trigger Functions // +-- // // +-- ///////////////////////// /* - * Creates IP entries for the given network block - * - * % SELECT ip_add_network( - * ipNetwork := '198.51.100.0/24' - * ); - * % SELECT ip_add_network( - * ipNetblockId := ( - * SELECT id - * FROM netblock - * WHERE network = '198.51.100.0/24' - * ) - * ); - * - * ipNetwork - * : network address and network size being added to database - * - * ipNetblockId - * : reference to netblock to add to database - * - * Returns the number of new IP addresses registered - */ -CREATE OR REPLACE FUNCTION ip_add_network -( - ipNetwork CIDR DEFAULT NULL, - ipNetblockId INTEGER DEFAULT NULL -) - RETURNS INTEGER - LANGUAGE plpgsql - SECURITY DEFINER - AS $$ -DECLARE - ipAddress INET; - resultAddress INET; - ipAdded INTEGER; - ipMask INTEGER; -BEGIN - - -- verifies input - IF ( ( ipNetwork IS NULL ) AND ( ipNetblockId IS NULL ) ) THEN - RAISE EXCEPTION 'must specify either ipNetwork or ipNetblockId'; - END IF; - - -- determines netblock - if ( ipNetblockId IS NULL ) THEN - SELECT id INTO ipNetblockId - FROM netblock - WHERE network >>= ipNetwork; - ELSE - SELECT network INTO ipNetwork - FROM netblock - WHERE id = ipNetblockId; - END IF; - if ( ( ipNetblockId IS NULL ) OR ( ipNetwork IS NULL ) ) THEN - RAISE EXCEPTION 'specified netblock does not exist'; - END IF; - - -- restricts access to IPv4 addresses - IF ( family(ipNetwork) != 4 ) THEN - RAISE EXCEPTION 'only IPv4 addresses are supported'; - END IF; - - -- sets starting values - 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; - END IF; - ipAddress := ipAddress + 1; - - END LOOP; - - RETURN ipAdded; -END; -$$; - - -/* - * Assigns specific IP address to specified user/session - * - * % SELECT ip_assign( - * assignIp := '198.51.100.1/32', - * assignUser := 'jdoe@acsalaska.net' - * ); - * % SELECT ip_assign( - * assignIp := '198.51.100.1/32', - * assignUser := 'jdoe@acsalaska.net', - * assignPortLimit := 5, - * assignNas := 'br1.cwc', - * assignSess := '596c3b82-75df-11e9-bcbb-6003089b9f24', - * assignForce := FALSE - * ); - * - * assignIp - * : IP address to assign to user - * - * assignUser - * : user to assign IP address - * - * assignPortLimit - * : specifies maximum IP assignments per user allowed - * - * 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) - * - * Returns TRUE if assigned and FALSE if not assigned - */ -CREATE OR REPLACE FUNCTION ip_assign -( - assignIp INET, - assignUser TEXT, - assignPortLimit INTEGER DEFAULT 2, - assignNas TEXT DEFAULT NULL, - assignSess TEXT DEFAULT NULL, - assignForce BOOLEAN DEFAULT FALSE -) - RETURNS BOOLEAN - LANGUAGE plpgsql - SECURITY DEFINER - AS $$ -DECLARE - assignNasId INTEGER; - assignPortCount INTEGER; -BEGIN - - -- sets defaults - IF ( assignSess IS NULL ) THEN - assignSess := 'user:' || assignUser; - END IF; - - -- tallies existing sessions - SELECT count(id) - INTO assignPortCount - FROM ip - WHERE ip.username LIKE assignUser - AND ip.nasId IS NOT NULL - AND ip.address != assignIp; - IF ( assignPortCount >= assignPortLimit ) THEN - RETURN FALSE; - END IF; - - -- verifies IP address is not inactive - IF ( assignForce = FALSE ) THEN - SELECT network - FROM netblock - WHERE netblock.network >>= assignIp - AND netblock.active IS FALSE; - IF FOUND THEN - RETURN FALSE; - END IF; - END IF; - - -- attempts to determine NAS - IF ( assignNas IS NOT NULL ) THEN - SELECT id - INTO assignNasId - FROM nas - WHERE name LIKE assignNas; - ELSE - SELECT nas.id - INTO assignNasId - FROM nas - INNER JOIN region - ON region.id = nas.regionId - INNER JOIN netblock - ON netblock.regionId = regoin.id - WHERE netblock.network >>= assignIp; - END IF; - IF ( assignNasId IS NULL ) THEN - RETURN FALSE; - END IF; - - -- updates record - UPDATE ip - SET nasId = ( SELECT id FROM nas WHERE name LIKE assignNas ), - username = assignUser, - acctSession = assignSess - WHERE address = set_masklen( assignIp, 32 ) - AND nasId IS NULL; - - RETURN FOUND; -END; -$$; - - -/* - * Auto-assigns IP address to specified user/session - * - * % SELECT ip_autoassign( - * assignUser := 'jdoe@acsalaska.net', - * assignNas := 'br1.sol', - * assignPool := 'dhcp' - * ); - * % SELECT ip_autoassign( - * assignUser := 'jdoe@acsalaska.net', - * assignNas := 'br1.sol', - * assignPool := 'dhcp', - * assignPortLimit := 5, - * assignSess := 'b4356b24-7670-11e9-84a0-6003089b9f24', - * assignPrevIp := TRUE - * ); - * - * assignUser - * : user to assign IP address - * - * assignNas - * : NAS to assign to IP address and implies region from which to pull IP - * - * assignPool - * : IP pool to assign from which to assign IP address - * - * 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 - * - * Returns INET with value of assigned IP address - */ -CREATE OR REPLACE FUNCTION ip_autoassign -( - assignUser TEXT, - assignNas TEXT, - assignPool TEXT, - assignPortLimit INTEGER, DEFAULT 2, - assignSess TEXT DEFAULT NULL, - assignPrevIp BOOLEAN DEFAULT TRUE -) - RETURNS INET - LANGUAGE plpgsql - SECURITY DEFINER - AS $$ -DECLARE - assignedIp INET; - assignNasId INTEGER; -BEGIN - - -- sets defaults - IF ( assignSess IS NULL ) THEN - assignSess := 'user:' || assignUser; - END IF; - - -- search for user's previous address - IF ( assignPrevIp IS TRUE ) THEN - SELECT ip.address, - nas.id - INTO assignedIp, - assignNasId - 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 - 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 - ORDER BY ip.modified DESC, - ip.address ASC - LIMIT 1; - END IF; - - -- if previous address is unavailable, find longest idle address - IF ( assignedIp IS NULL ) THEN - SELECT ip.address, - nas.id - INTO assignedIp, - assignNasId - 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 - 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 - ORDER BY ip.modified DESC, - ip.address ASC - LIMIT 1; - IF NOT FOUND THEN - RETURN NULL; - END IF; - END IF; - - -- update record - UPDATE ip - SET nasId = assignNasId, - username = assignUser, - acctSession = assignSess - WHERE address = assignedIp; - IF NOT FOUND THEN - RETURN NULL; - END IF; - - RETURN assignedIp; -END; -$$; - - - -/* - * updates timestamp of addresses - * - * returns trigger - */ -CREATE OR REPLACE FUNCTION ip_release -( - assignIp INET -) - RETURNS BOOLEAN - LANGUAGE plpgsql - SECURITY DEFINER - AS $$ -DECLARE -BEGIN - - -- updates record - UPDATE ip - SET nasId = NULL, - acctSession = NULL - WHERE address = set_masklen( assignIp, 32 ); - - RETURN FOUND; -END; -$$; - - -/* - * updates timestamp of addresses - * - * returns trigger + * trigger functions for ip table */ CREATE OR REPLACE FUNCTION ip_trigger_delete ( ) @@ -617,11 +254,6 @@ END; $$; -/* - * forces initial values on insert - * - * returns trigger - */ CREATE OR REPLACE FUNCTION ip_trigger_insert ( ) RETURNS trigger @@ -646,11 +278,6 @@ END; $$; -/* - * verifies no assigned IP addresses before truncating - * - * returns trigger - */ CREATE OR REPLACE FUNCTION ip_trigger_truncate ( ) RETURNS trigger @@ -664,11 +291,6 @@ END; $$; -/* - * updates timestamp of addresses - * - * returns trigger - */ CREATE OR REPLACE FUNCTION ip_trigger_update ( ) RETURNS trigger @@ -691,61 +313,7 @@ $$; /* - * Creates entry for NAS client - * - * % SELECT nas_add( - * nasName := 'br1.cwc', - * regionName := 'Anchorage' - * ); - * - * nasName - * : name of NAS being added to database - * - * regionName - * : the name of the region in which the NAS is physically located - * - * Returns the `id` of the created nas client - */ -CREATE OR REPLACE FUNCTION nas_add -( - nasName VARCHAR(32), - regionName VARCHAR(128) -) - RETURNS INTEGER - LANGUAGE plpgsql - SECURITY DEFINER - AS $$ -DECLARE - nasRegionId INTEGER; - returnId INTEGER; -BEGIN - -- searches for region id - SELECT id INTO nasRegionId - FROM region - WHERE name LIKE lower(regionName); - IF ( nasRegionId IS NULL ) THEN - RAISE EXCEPTION 'invalid region'; - END IF; - - -- adds new pool - INSERT INTO nas - ( name, - regionId - ) - VALUES ( lower(nasName), - nasRegionId - ) - RETURNING id INTO returnId; - - RETURN returnId; -END; -$$; - - -/* - * updates timestamp of addresses - * - * returns trigger + * trigger functions for nas table */ CREATE OR REPLACE FUNCTION nas_trigger_insert_or_update ( ) @@ -764,111 +332,7 @@ $$; /* - * Creates entry for network block - * - * % SELECT netblock_add( - * netAddress := '198.51.100.0/24', - * poolName := 'PPPoE' - * ); - * % SELECT netblock_add( - * netAddress := '203.0.113.0/24', - * poolName := 'DHCP', - * regionName := 'Soldotna', - * netActive := FALSE, - * netDesc := 'Lab IP Pool', - * addIpEntries := TRUE - * ); - * - * netAddress - * : network address and network size being added to database - * - * poolName - * : IP pool network is being assigned - * - * regionName - * : the name of the region in which the network will physically serve - * - * netActive - * : TRUE if addresses within network are assignable, FALSE if addresses are - * : not assignable - * - * netDesc - * : human readable description of the network - * - * addIpEntries - * : add coorisponding ip table entries for network - * - * Returns the `id` of the created nas client - */ -CREATE OR REPLACE FUNCTION netblock_add -( - netAddress CIDR, - poolName VARCHAR(32), - regionName VARCHAR(32) DEFAULT NULL, - netActive BOOLEAN DEFAULT TRUE, - netDesc VARCHAR(128) DEFAULT NULL, - addIpEntries BOOLEAN DEFAULT TRUE -) - RETURNS INTEGER - LANGUAGE plpgsql - SECURITY DEFINER - AS $$ -DECLARE - netRegionId INTEGER DEFAULT NULL; - netPoolId INTEGER; - returnId INTEGER DEFAULT 1; -BEGIN - - -- restricts access to IPv4 addresses - IF ( family(netAddress) != 4 ) THEN - RAISE EXCEPTION 'only IPv4 addresses are supported'; - END IF; - - -- searches for region id - if ( regionName IS NOT NULL ) THEN - SELECT id INTO netRegionId - FROM region - WHERE name LIKE lower(regionName); - END IF; - - -- searches for pool id - SELECT id INTO netPoolId - FROM pool - WHERE name LIKE lower(poolName); - IF ( netPoolId IS NULL ) THEN - RAISE EXCEPTION 'invalid pool'; - END IF; - - -- adds new pool - INSERT INTO netblock - ( network, - poolId, - regionId, - active, - description - ) - VALUES ( netAddress, - netPoolId, - netRegionId, - netActive, - netDesc - ) - RETURNING id INTO returnId; - - -- adds IP entries - if ( addIpEntries IS NOT FALSE ) THEN - PERFORM ip_add_network( ipNetblockId := returnId); - END IF; - - RETURN returnId; -END; -$$; - - -/* - * adds contraints to netblocks - * - * returns trigger + * trigger functions for netblock table */ CREATE OR REPLACE FUNCTION netblock_trigger_insert_or_update ( ) @@ -915,9 +379,7 @@ $$; /* - * updates timestamp of addresses - * - * returns trigger + * trigger functions for pool table */ CREATE OR REPLACE FUNCTION pool_trigger_insert_or_update ( ) @@ -936,9 +398,7 @@ $$; /* - * updates timestamp of addresses - * - * returns trigger + * trigger functions for region table */ CREATE OR REPLACE FUNCTION region_trigger_insert_or_update ( ) @@ -1016,4 +476,5 @@ CREATE TRIGGER region_trigger_insert_or_update FOR EACH ROW EXECUTE PROCEDURE region_trigger_insert_or_update(); + /* end of sql */ diff --git a/notes/projects/radius/sql/ippool.md b/notes/projects/radius/sql/ippool.md index d5c1223..ce39c38 100644 --- a/notes/projects/radius/sql/ippool.md +++ b/notes/projects/radius/sql/ippool.md @@ -12,12 +12,13 @@ Objectives Files ---- - * ippool.md - This file - * ippool-database.sql - Creates database - * ippool-schema.sql - Creates database schema (tables, indexes, etc) - * ippool-demo.sql - Loads demo data - * ippool-queries.sql - Common or useful queries - * radius-roles.sql - Common database roles for RADIUS + * ippool.md - This file + * ippool-database.sql - Creates database + * ippool-schema.sql - Creates database schema (tables, indexes, etc) + * ippool-functions.sql - Creates end-user/end-application functions + * ippool-demo.sql - Loads demo data + * ippool-queries.sql - Common or useful queries + * radius-roles.sql - Common database roles for RADIUS Initializing New Database @@ -31,6 +32,7 @@ Initializing New Database psql -h raddbtest.prv.acsalaska.net -f ippool-database.sql psql -h raddbtest.prv.acsalaska.net -f ippool-schema.sql radius_ippool + psql -h raddbtest.prv.acsalaska.net -f ippool-functions.sql radius_ippool * Create RADIUS pools using `pool_add()`. * Create RADIUS regions using `region_add()`. -- cgit v1.2.3