starting on IP assignment functions

1 files changed, 181 insertions, 15 deletions

diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql
index 5372bc6..59914d3 100644
--- a/notes/projects/radius/sql/ippool-schema.sql
+++ b/notes/projects/radius/sql/ippool-schema.sql
@@ -325,17 +325,19 @@ $$;
 
 
 /*
- *  Assigns IP address to specific user/session
+ *  Assigns specific IP address to specified user/session
  *
- *  % SELECT ip_add_network(
- *       assignIp    := '198.51.100.1/32',
- *       assignUser  := 'jdoe@acsalaska.net'
+ *  % SELECT ip_assign(
+ *       assignIp         := '198.51.100.1/32',
+ *       assignUser       := 'jdoe@acsalaska.net'
  *    );
- *  % SELECT ip_add_network(
- *       assignIp    := '198.51.100.1/32',
- *       assignUser  := 'jdoe@acsalaska.net',
- *       assignNas   := 'br1.cwc',
- *       assignSess  := '596c3b82-75df-11e9-bcbb-6003089b9f24'
+ *  % 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
@@ -344,20 +346,28 @@ $$;
  *  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
+   assignSess       TEXT            DEFAULT NULL,
+   assignForce      BOOLEAN         DEFAULT FALSE
 )
    RETURNS          BOOLEAN
    LANGUAGE         plpgsql
@@ -365,6 +375,7 @@ CREATE OR REPLACE FUNCTION ip_assign
    AS               $$
 DECLARE
    assignNasId      INTEGER;
+   assignPortCount  INTEGER;
 BEGIN
 
    -- sets defaults
@@ -372,13 +383,37 @@ BEGIN
       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
+      SELECT        id
+         INTO       assignNasId
          FROM       nas
          WHERE      name              LIKE assignNas;
    ELSE
-      SELECT        nas.id            INTO assignNasId
+      SELECT        nas.id
+         INTO       assignNasId
          FROM       nas
          INNER JOIN region
             ON      region.id         = nas.regionId
@@ -387,7 +422,7 @@ BEGIN
          WHERE      netblock.network  >>= assignIp;
    END IF;
    IF ( assignNasId IS NULL ) THEN
-      RAISE EXCEPTION 'NAS not found';
+      RETURN FALSE;
    END IF;
 
    -- updates record
@@ -395,7 +430,8 @@ BEGIN
       SET           nasId         = ( SELECT id FROM nas WHERE name LIKE assignNas ),
                     username      = assignUser,
                     acctSession   = assignSess
-      WHERE         address       = set_masklen( assignIp, 32 );
+      WHERE         address       = set_masklen( assignIp, 32 )
+         AND        nasId         IS NULL;
 
    RETURN FOUND;
 END;
@@ -403,6 +439,135 @@ $$;
 
 
 /*
+ *  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
@@ -420,7 +585,8 @@ BEGIN
 
    -- updates record
    UPDATE           ip
-      SET           nasId         = NULL
+      SET           nasId         = NULL,
+                    acctSession   = NULL
       WHERE         address       = set_masklen( assignIp, 32 );
 
    RETURN FOUND;