2 files changed, 70 insertions, 6 deletions

diff --git a/notes/projects/radius/sql/ippool-queries.sql b/notes/projects/radius/sql/ippool-queries.sql
index 1009d53..f2daa21 100644
--- a/notes/projects/radius/sql/ippool-queries.sql
+++ b/notes/projects/radius/sql/ippool-queries.sql
@@ -36,8 +36,6 @@ SELECT DISTINCT      pool.name         AS poolName,
                      )                 AS active,
                      (
                        nasId           IS NOT NULL
-                       OR
-                       acctSession     IS NOT NULL
                      )                 AS assigned,
                      ip.modified       AS modified,
                      nas.name          AS nasName,
@@ -67,8 +65,6 @@ SELECT DISTINCT      pool.name         AS poolName,
                      )                 AS active,
                      (
                        nasId           IS NOT NULL
-                       OR
-                       acctSession     IS NOT NULL
                      )                 AS assigned,
                      ip.modified       AS modified,
                      nas.name          AS nasName,
diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql
index ea50e5d..5372bc6 100644
--- a/notes/projects/radius/sql/ippool-schema.sql
+++ b/notes/projects/radius/sql/ippool-schema.sql
@@ -325,9 +325,32 @@ $$;
 
 
 /*
- *  updates timestamp of addresses
+ *  Assigns IP address to specific user/session
  *
- *  returns trigger
+ *  % SELECT ip_add_network(
+ *       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'
+ *    );
+ *
+ *  assignIp
+ *  : IP address to assign to user
+ *
+ *  assignUser
+ *  : user to assign IP address
+ *
+ *  assignNas
+ *  : session NAS (attempt to guess if not specified)
+ *
+ *  assignSess
+ *  : RADIUS session
+ *
+ *  Returns TRUE if assigned and FALSE if not assigned
  */
 CREATE OR REPLACE FUNCTION ip_assign
 (
@@ -341,6 +364,7 @@ CREATE OR REPLACE FUNCTION ip_assign
    SECURITY         DEFINER
    AS               $$
 DECLARE
+   assignNasId      INTEGER;
 BEGIN
 
    -- sets defaults
@@ -348,6 +372,24 @@ BEGIN
       assignSess   := 'user:' || assignUser;
    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
+      RAISE EXCEPTION 'NAS not found';
+   END IF;
+
    -- updates record
    UPDATE           ip
       SET           nasId         = ( SELECT id FROM nas WHERE name LIKE assignNas ),
@@ -365,6 +407,32 @@ $$;
  *
  *  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
+      WHERE         address       = set_masklen( assignIp, 32 );
+
+   RETURN FOUND;
+END;
+$$;
+
+
+/*
+ *  updates timestamp of addresses
+ *
+ *  returns trigger
+ */
 CREATE OR REPLACE FUNCTION ip_trigger_delete
 ( )
    RETURNS          trigger