From 676530cdc7ca1a54a00b8594b678d5c36694c28d Mon Sep 17 00:00:00 2001 From: "David M. Syzdek" Date: Wed, 8 May 2019 16:02:23 -0800 Subject: Expanding functions and triggers --- notes/projects/radius/sql/ippool-database.sql | 2 + notes/projects/radius/sql/ippool-demo.sql | 21 ++++ notes/projects/radius/sql/ippool-schema.sql | 175 +++++++++++++++++++++++--- 3 files changed, 181 insertions(+), 17 deletions(-) diff --git a/notes/projects/radius/sql/ippool-database.sql b/notes/projects/radius/sql/ippool-database.sql index b7d0654..660e676 100644 --- a/notes/projects/radius/sql/ippool-database.sql +++ b/notes/projects/radius/sql/ippool-database.sql @@ -9,6 +9,8 @@ SET ROLE wheel; +SET client_min_messages TO WARNING; +\set ON_ERROR_STOP on CREATE ROLE radius_ippool diff --git a/notes/projects/radius/sql/ippool-demo.sql b/notes/projects/radius/sql/ippool-demo.sql index 601ac48..4775d5f 100644 --- a/notes/projects/radius/sql/ippool-demo.sql +++ b/notes/projects/radius/sql/ippool-demo.sql @@ -25,6 +25,27 @@ SELECT nas_add( nasName := 'bras.eng', regionName := 'Anchorage' ); SELECT nas_add( nasName := 'br1.sol', regionName := 'Soldotna' ); +SELECT netblock_add + ( + netAddress := '209.112.192.0/19', + poolName := 'DHCP', + regionName := 'Anchorage' + ); +SELECT netblock_add + ( + netAddress := '209.112.128.0/18', + poolName := 'DHCP', + regionName := 'Anchorage' + ); +SELECT netblock_add + ( + netAddress := '209.112.156.0/24', + poolName := 'DHCP', + regionName := 'Anchorage', + netActive := FALSE, + netDesc := 'Admin Network' + ); + INSERT INTO block ( poolId, regionId, net, description ) VALUES diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql index 5dfc5e1..df2bfeb 100644 --- a/notes/projects/radius/sql/ippool-schema.sql +++ b/notes/projects/radius/sql/ippool-schema.sql @@ -8,6 +8,8 @@ SET ROLE radius_ippool; +SET client_min_messages TO WARNING; +\set ON_ERROR_STOP on -- ////////////// @@ -103,15 +105,15 @@ CREATE INDEX * id * : The primary key of a specific entry * + * network + * : network address and network size of block + * * poolId * : reference to the pool type of the network * * regionId * : reference to the physical region served by the block * - * net - * : network address and network size of block - * * active * : assignable to customer if true, disabled/inactive if false * @@ -121,9 +123,9 @@ CREATE INDEX CREATE TABLE IF NOT EXISTS netblock ( id SERIAL, + network CIDR NOT NULL UNIQUE, poolId INTEGER NOT NULL, regionId INTEGER, - network CIDR NOT NULL UNIQUE, active BOOLEAN NOT NULL DEFAULT TRUE, description VARCHAR(32), PRIMARY KEY ( id ), @@ -150,39 +152,39 @@ CREATE INDEX * address * : user assignable IP address * - * poolId - * : reference to the pool type of the network - * - * assigned - * : TRUE if currently assigned, FALSE if available - * * modified * : timestamp generated when entry is modified * * username * : username currently assigned or last assigned address + * + * acctSession + * : RADIUS accounting session currently holding address + * + * nasId + * : reference to RADIUS NAS client currently holding address */ CREATE TABLE IF NOT EXISTS ip ( address INET, - poolId INTEGER NOT NULL, - assigned BOOLEAN NOT NULL DEFAULT FALSE, modified timestamp WITH TIME ZONE NOT NULL DEFAULT TIMESTAMP 'epoch', username VARCHAR(64), + acctSession VARCHAR(32), + nasId INTEGER, PRIMARY KEY ( address ), - FOREIGN KEY ( poolId ) - REFERENCES pool + FOREIGN KEY ( nasId ) + REFERENCES nas ( id ) ); CREATE INDEX IF NOT EXISTS ip_index ON ip ( address, - poolId, - assigned, modified, - username + username, + acctSession, + nasId ); @@ -269,6 +271,137 @@ 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' + * ); + * + * 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 + * + * 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 +) + RETURNS INTEGER + LANGUAGE plpgsql + AS $$ +DECLARE + netRegionId INTEGER DEFAULT NULL; + netPoolId INTEGER; + returnId INTEGER DEFAULT 1; +BEGIN + + -- 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; + + RETURN returnId; +END; +$$; + + +/* + * updates timestamp of addresses + * + * returns trigger + */ +CREATE OR REPLACE FUNCTION netblock_trigger +( ) + RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE + resultCount INTEGER; +BEGIN + + -- verifies that new region does not conflict with new network + SELECT count(id) INTO resultCount + FROM netblock + WHERE regionId != NEW.regionId + AND + ( NEW.network >>= network + OR + NEW.network <<= network + ); + IF ( resultCount != 0 ) THEN + RAISE EXCEPTION 'conflict with existing network regions'; + END IF; + + -- verifies that new region does not conflict with new network + SELECT count(id) INTO resultCount + FROM netblock + WHERE poolId != NEW.poolId + AND + ( NEW.network >>= network + OR + NEW.network <<= network + ); + IF ( resultCount != 0 ) THEN + RAISE EXCEPTION 'conflict with existing network pools'; + END IF; + + RETURN NEW; +END; +$$; + + /* * Creates entry for IP Pool * @@ -373,4 +506,12 @@ CREATE TRIGGER ip_timestamp EXECUTE PROCEDURE ip_timestamp(); +-- netblock table triggers +CREATE TRIGGER netblock_trigger + BEFORE INSERT OR UPDATE + ON netblock + FOR EACH ROW + EXECUTE PROCEDURE netblock_trigger(); + + /* end of sql */ -- cgit v1.2.3