From bef73ac3138fd12f32446973eba5dd1cdd3afa88 Mon Sep 17 00:00:00 2001 From: "David M. Syzdek" Date: Wed, 8 May 2019 23:44:24 -0800 Subject: Adding triggers --- notes/projects/radius/sql/= | 0 notes/projects/radius/sql/ippool-demo.sql | 42 +++-- notes/projects/radius/sql/ippool-schema.sql | 267 ++++++++++++++++++++++++++-- 3 files changed, 277 insertions(+), 32 deletions(-) create mode 100644 notes/projects/radius/sql/= diff --git a/notes/projects/radius/sql/= b/notes/projects/radius/sql/= new file mode 100644 index 0000000..e69de29 diff --git a/notes/projects/radius/sql/ippool-demo.sql b/notes/projects/radius/sql/ippool-demo.sql index 4775d5f..d00ebf1 100644 --- a/notes/projects/radius/sql/ippool-demo.sql +++ b/notes/projects/radius/sql/ippool-demo.sql @@ -13,11 +13,13 @@ SET ROLE radius_ippool; SELECT pool_add( poolName := 'DHCP' ); SELECT pool_add( poolName := 'PPPoE' ); +SELECT pool_add( poolName := 'reserved' ); SELECT region_add( regionName := 'Anchorage' ); SELECT region_add( regionName := 'Soldotna' ); SELECT region_add( regionName := 'Fairbanks' ); +SELECT region_add( regionName := 'No where' ); SELECT nas_add( nasName := 'br1.cwc', regionName := 'Anchorage' ); @@ -29,13 +31,15 @@ SELECT netblock_add ( netAddress := '209.112.192.0/19', poolName := 'DHCP', - regionName := 'Anchorage' + regionName := 'Anchorage', + addIpEntries := TRUE ); SELECT netblock_add ( netAddress := '209.112.128.0/18', poolName := 'DHCP', - regionName := 'Anchorage' + regionName := 'Anchorage', + addIpEntries := TRUE ); SELECT netblock_add ( @@ -43,20 +47,30 @@ SELECT netblock_add poolName := 'DHCP', regionName := 'Anchorage', netActive := FALSE, - netDesc := 'Admin Network' + netDesc := 'Admin Network', + addIpEntries := TRUE + ); +SELECT netblock_add + ( + netAddress := '209.193.4.0/28', + poolName := 'DHCP', + regionName := 'Anchorage', + netActive := FALSE, + netDesc := 'Admin Network', + addIpEntries := TRUE + ); +SELECT netblock_add + ( + netAddress := '209.193.4.0/29', + poolName := 'DHCP', + regionName := 'Anchorage', + netActive := FALSE, + netDesc := 'Admin Network', + addIpEntries := TRUE ); - - -INSERT INTO block ( poolId, regionId, net, description ) VALUES - ( ( SELECT id FROM pool WHERE name LIKE 'dhcp' ), - ( SELECT id FROM region WHERE name LIKE 'anchorage' ), - '209.112.156.0/23', - 'Admin Blocks' ), - ( ( SELECT id FROM pool WHERE name LIKE 'dhcp' ), - ( SELECT id FROM region WHERE name LIKE 'anchorage' ), - '209.112.128.0/24', - 'User Blocks' ); +SELECT ip_add_network( ipNetwork := '209.112.192.0/19' ); +SELECT ip_add_network( ipNetwork := '209.112.128.0/18' ); /* end of sql */ diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql index df2bfeb..6db3a0a 100644 --- a/notes/projects/radius/sql/ippool-schema.sql +++ b/notes/projects/radius/sql/ippool-schema.sql @@ -194,12 +194,80 @@ CREATE INDEX -- // // -- ///////////////// +/* + * Creates IP entries for the given network block + * + * % SELECT ip_add_network( + * ipNetwork := '198.51.100.0/24' + * ); + * + * ipNetwork + * : network address and network size being added to database + * + * Returns the number of new IP addresses registered + */ +CREATE OR REPLACE FUNCTION ip_add_network +( + ipNetwork CIDR +) + RETURNS INTEGER + LANGUAGE plpgsql + AS $$ +DECLARE + ipAddress INET; + resultCount INTEGER; + ipAdded INTEGER; + ipMask INTEGER; +BEGIN + + -- searches for a matching netblock + SELECT count(network) INTO resultCount + FROM netblock + WHERE network >>= ipNetwork; + IF ( resultCount = 0 ) THEN + RAISE EXCEPTION 'matching netblock not found'; + 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 ); + + -- searches for existing IP address + SELECT count( address ) INTO resultCount + FROM ip + WHERE address = ipAddress; + + -- defines IP address if not defined and counts results + IF ( resultCount = 0 ) THEN + INSERT INTO ip + ( address ) + VALUES ( ipAddress ); + ipAdded := ipAdded + 1; + END IF; + ipAddress := ipAddress + 1; + + END LOOP; + + RETURN ipAdded; +END; +$$; + + /* * updates timestamp of addresses * * returns trigger */ -CREATE OR REPLACE FUNCTION ip_timestamp +CREATE OR REPLACE FUNCTION ip_trigger ( ) RETURNS trigger LANGUAGE plpgsql @@ -208,12 +276,82 @@ DECLARE BEGIN -- prevents IP address from being modified - IF ( NEW.address != OLD.address ) THEN - RAISE EXCEPTION 'address cannot be modified'; + IF ( ( TG_OP = 'UPDATE' ) AND ( NEW.address != OLD.address ) ) THEN + RAISE EXCEPTION 'address cannot be modified'; + END IF; + + -- forces initial values + IF ( TG_OP = 'INSERT' ) THEN + NEW.username := NULL; + NEW.acctSession := NULL; + NEW.nasId := NULL; + END IF; + + -- determines mask for single IP address + IF ( family(NEW.address) != 4 ) THEN + RAISE EXCEPTION 'only IPv4 addresses are supported'; END IF; -- update timestamp - NEW.lastModified := current_timestamp; + IF ( TG_OP = 'INSERT' ) THEN + NEW.modified := current_timestamp; + ELSIF ( TG_OP = 'UPDATE' ) THEN + NEW.modified := TIMESTAMP 'epoch'; + END IF; + + RETURN NEW; +END; +$$; + + +/* + * updates timestamp of addresses + * + * returns trigger + */ +CREATE OR REPLACE FUNCTION ip_trigger_delete +( ) + RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE +BEGIN + -- verifies ip is not assigned to a RADIUS accounting session + IF ( OLD.acctSession IS NOT NULL ) THEN + RAISE EXCEPTION 'IP address is still assigned to accounting session'; + END IF; + + -- verifies ip is not assigned to a RADIUS accounting session + IF ( OLD.nasId IS NOT NULL ) THEN + RAISE EXCEPTION 'IP address is still assigned to NAS session'; + END IF; + + RETURN NEW; +END; +$$; + + +/* + * updates timestamp of addresses + * + * returns trigger + */ +CREATE OR REPLACE FUNCTION ip_trigger_truncate +( ) + RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE + resultCount INTEGER; +BEGIN + -- searches for assigned IP addresses + SELECT count( address ) INTO resultCount + FROM ip + WHERE acctSession IS NOT NULL + OR nasId IS NOT NULL; + IF ( resultCount != 0 ) THEN + RAISE EXCEPTION 'IP addresses are still assigned to sessions'; + END IF; RETURN NEW; END; @@ -275,15 +413,16 @@ $$; * Creates entry for network block * * % SELECT netblock_add( - * netAddress := '198.51.100.0/24', - * poolName := 'PPPoE' + * 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 := '203.0.113.0/24', + * poolName := 'DHCP', + * regionName := 'Soldotna', + * netActive := FALSE, + * netDesc := 'Lab IP Pool', + * addIpEntries := TRUE * ); * * netAddress @@ -302,6 +441,9 @@ $$; * 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 @@ -310,7 +452,8 @@ CREATE OR REPLACE FUNCTION netblock_add poolName VARCHAR(32), regionName VARCHAR(32) DEFAULT NULL, netActive BOOLEAN DEFAULT TRUE, - netDesc VARCHAR(128) DEFAULT NULL + netDesc VARCHAR(128) DEFAULT NULL, + addIpEntries BOOLEAN DEFAULT FALSE ) RETURNS INTEGER LANGUAGE plpgsql @@ -352,13 +495,18 @@ BEGIN ) RETURNING id INTO returnId; + -- adds IP entries + if ( addIpEntries IS NOT FALSE ) THEN + PERFORM ip_add_network( ipNetwork := netAddress ); + END IF; + RETURN returnId; END; $$; /* - * updates timestamp of addresses + * adds contraints to netblocks * * returns trigger */ @@ -370,6 +518,10 @@ CREATE OR REPLACE FUNCTION netblock_trigger DECLARE resultCount INTEGER; BEGIN + -- verifies network is IPv4 + IF ( family(NEW.network) != 4 ) THEN + RAISE EXCEPTION 'only IPv4 networks are supported'; + END IF; -- verifies that new region does not conflict with new network SELECT count(id) INTO resultCount @@ -402,6 +554,67 @@ END; $$; +/* + * adds contraints to netblocks + * + * returns trigger + */ +CREATE OR REPLACE FUNCTION netblock_trigger_delete +( ) + RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE + resultCount INTEGER; +BEGIN + -- checks for IP addresses which would be orphaned + SELECT count( address ) INTO resultCount + FROM ip + WHERE address NOT IN + ( + SELECT address + FROM ip + INNER JOIN netblock + ON ip.address <<= netblock.network + WHERE ip.address <<= OLD.network + AND netblock.network <> OLD.network + ) + AND address <<= OLD.network; + IF ( resultCount != 0 ) THEN + RAISE EXCEPTION 'IP addresses still defined within netblock'; + END IF; + + RETURN NEW; +END; +$$; + + +/* + * adds contraints to netblocks + * + * returns trigger + */ +CREATE OR REPLACE FUNCTION netblock_trigger_truncate +( ) + RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE + resultCount INTEGER; +BEGIN + -- checks for IP addresses which would be orphaned + SELECT count( address ) INTO resultCount + FROM ip; + IF ( resultCount != 0 ) THEN + RAISE EXCEPTION 'IP addresses still defined'; + END IF; + + RETURN NEW; +END; +$$; + + + /* * Creates entry for IP Pool * @@ -499,12 +712,21 @@ $$; -- //////////////// -- address table triggers -CREATE TRIGGER ip_timestamp - BEFORE UPDATE +CREATE TRIGGER ip_trigger + BEFORE INSERT OR UPDATE ON ip FOR EACH ROW - EXECUTE PROCEDURE ip_timestamp(); - + EXECUTE PROCEDURE ip_trigger(); +CREATE TRIGGER ip_trigger_delete + BEFORE DELETE + ON ip + FOR EACH ROW + EXECUTE PROCEDURE ip_trigger_delete(); +CREATE TRIGGER ip_trigger_truncate + BEFORE TRUNCATE + ON ip + FOR EACH STATEMENT + EXECUTE PROCEDURE ip_trigger_truncate(); -- netblock table triggers CREATE TRIGGER netblock_trigger @@ -512,6 +734,15 @@ CREATE TRIGGER netblock_trigger ON netblock FOR EACH ROW EXECUTE PROCEDURE netblock_trigger(); - +CREATE TRIGGER netblock_trigger_delete + BEFORE DELETE + ON netblock + FOR EACH ROW + EXECUTE PROCEDURE netblock_trigger_delete(); +CREATE TRIGGER netblock_trigger_truncate + BEFORE TRUNCATE + ON netblock + FOR EACH STATEMENT + EXECUTE PROCEDURE netblock_trigger_truncate(); /* end of sql */ -- cgit v1.2.3