Adding triggers

3 files changed, 277 insertions, 32 deletions

diff --git a/notes/projects/radius/sql/= b/notes/projects/radius/sql/=
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/notes/projects/radius/sql/=
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
@@ -195,11 +195,79 @@ 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
@@ -403,6 +555,67 @@ $$;
 
 
 /*
+ *  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
  *
  *  % SELECT pool_add(
@@ -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 */