1 files changed, 536 insertions, 0 deletions

diff --git a/notes/projects/radius/sql/ippool-functions.sql b/notes/projects/radius/sql/ippool-functions.sql
new file mode 100644
index 0000000..bbfd60f
--- /dev/null
+++ b/notes/projects/radius/sql/ippool-functions.sql
@@ -0,0 +1,536 @@
+/*
+ *  IP Engineering RADIUS Servers
+ *
+ *  ippool-schema.sql - builds database schema for RADIUS IP Pools
+ *
+ *  Usage:  psql -f ippool-schema.sql radius_ippool
+ */
+
+
+SET ROLE radius;
+SET client_min_messages TO WARNING;
+\set ON_ERROR_STOP on
+
+
+-- /////////////////
+-- //             //
+-- //  Functions  //
+-- //             //
+-- /////////////////
+
+/*
+ *  Creates IP entries for the given network block
+ *
+ *  % SELECT ip_add_network(
+ *       ipNetwork   := '198.51.100.0/24'
+ *    );
+ *  % SELECT ip_add_network(
+ *       ipNetblockId := (
+ *                         SELECT       id
+ *                            FROM      netblock
+ *                            WHERE     network = '198.51.100.0/24'
+ *                       )
+ *    );
+ *
+ *  ipNetwork
+ *  : network address and network size being added to database
+ *
+ *  ipNetblockId
+ *  : reference to netblock to add to database
+ *
+ *  Returns the number of new IP addresses registered
+ */
+CREATE OR REPLACE FUNCTION ip_add_network
+(
+   ipNetwork        CIDR            DEFAULT NULL,
+   ipNetblockId     INTEGER         DEFAULT NULL
+)
+   RETURNS          INTEGER
+   LANGUAGE         plpgsql
+   SECURITY         DEFINER
+   AS               $$
+DECLARE
+   ipAddress        INET;
+   resultAddress    INET;
+   ipAdded          INTEGER;
+   ipMask           INTEGER;
+BEGIN
+
+   -- verifies input
+   IF ( ( ipNetwork IS NULL ) AND ( ipNetblockId IS NULL ) ) THEN
+      RAISE EXCEPTION 'must specify either ipNetwork or ipNetblockId';
+   END IF;
+
+   -- determines netblock
+   if ( ipNetblockId IS NULL ) THEN
+      SELECT        id              INTO ipNetblockId
+        FROM        netblock
+        WHERE       network         >>= ipNetwork;
+   ELSE
+      SELECT        network         INTO ipNetwork
+         FROM       netblock
+         WHERE      id              = ipNetblockId;
+   END IF;
+   if ( ( ipNetblockId IS NULL ) OR ( ipNetwork IS NULL ) ) THEN
+      RAISE EXCEPTION 'specified netblock does not exist';
+   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 );
+
+      INSERT INTO   ip
+                    ( address,
+                      netblockId
+                    )
+         SELECT     ipAddress,
+                    ipNetblockId
+            WHERE   NOT EXISTS
+                    ( SELECT address FROM ip WHERE address = ipAddress )
+          RETURNING address INTO resultAddress;
+      IF ( resultAddress IS NOT NULL ) THEN
+         ipAdded    := ipAdded + 1;
+      END IF;
+      ipAddress     := ipAddress   + 1;
+
+   END LOOP;
+
+   RETURN ipAdded;
+END;
+$$;
+
+
+/*
+ *  Assigns specific IP address to specified user/session
+ *
+ *  % SELECT ip_assign(
+ *       assignIp         := '198.51.100.1/32',
+ *       assignUser       := 'jdoe@acsalaska.net'
+ *    );
+ *  % 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
+ *  : IP address to assign to user
+ *
+ *  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,
+   assignForce      BOOLEAN         DEFAULT FALSE
+)
+   RETURNS          BOOLEAN
+   LANGUAGE         plpgsql
+   SECURITY         DEFINER
+   AS               $$
+DECLARE
+   assignNasId      INTEGER;
+   assignPortCount  INTEGER;
+BEGIN
+
+   -- sets defaults
+   IF ( assignSess IS NULL ) THEN
+      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
+         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
+      RETURN FALSE;
+   END IF;
+
+   -- updates record
+   UPDATE           ip
+      SET           nasId         = ( SELECT id FROM nas WHERE name LIKE assignNas ),
+                    username      = assignUser,
+                    acctSession   = assignSess
+      WHERE         address       = set_masklen( assignIp, 32 )
+         AND        nasId         IS NULL;
+
+   RETURN FOUND;
+END;
+$$;
+
+
+/*
+ *  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
+ */
+CREATE OR REPLACE FUNCTION ip_release
+(
+   assignIp         INET
+)
+   RETURNS          BOOLEAN
+   LANGUAGE         plpgsql
+   SECURITY         DEFINER
+   AS               $$
+DECLARE
+BEGIN
+
+   -- updates record
+   UPDATE           ip
+      SET           nasId         = NULL,
+                    acctSession   = NULL
+      WHERE         address       = set_masklen( assignIp, 32 );
+
+   RETURN FOUND;
+END;
+$$;
+
+
+/*
+ *  Creates entry for NAS client
+ *
+ *  % SELECT nas_add(
+ *       nasName    := 'br1.cwc',
+ *       regionName := 'Anchorage'
+ *    );
+ *
+ *  nasName
+ *  : name of NAS being added to database
+ *
+ *  regionName
+ *  : the name of the region in which the NAS is physically located
+ *
+ *  Returns the `id` of the created nas client
+ */
+CREATE OR REPLACE FUNCTION nas_add
+(
+   nasName          VARCHAR(32),
+   regionName       VARCHAR(128)
+)
+   RETURNS          INTEGER
+   LANGUAGE         plpgsql
+   SECURITY         DEFINER
+   AS               $$
+DECLARE
+   nasRegionId      INTEGER;
+   returnId         INTEGER;
+BEGIN
+   -- searches for region id
+   SELECT           id      INTO nasRegionId
+      FROM          region
+      WHERE         name    LIKE lower(regionName);
+   IF ( nasRegionId IS NULL ) THEN
+      RAISE EXCEPTION 'invalid region';
+   END IF;
+
+   -- adds new pool
+   INSERT INTO      nas
+                    ( name,
+                      regionId
+                    )
+      VALUES        ( lower(nasName),
+                      nasRegionId
+                    )
+      RETURNING     id INTO returnId;
+
+   RETURN returnId;
+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',
+ *       addIpEntries  := TRUE
+ *    );
+ *
+ *  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
+ *
+ *  addIpEntries
+ *  : add coorisponding ip table entries for 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,
+   addIpEntries     BOOLEAN      DEFAULT TRUE
+)
+   RETURNS          INTEGER
+   LANGUAGE         plpgsql
+   SECURITY         DEFINER
+   AS               $$
+DECLARE
+   netRegionId      INTEGER      DEFAULT NULL;
+   netPoolId        INTEGER;
+   returnId         INTEGER      DEFAULT 1;
+BEGIN
+
+   -- restricts access to IPv4 addresses
+   IF ( family(netAddress) != 4 ) THEN
+      RAISE EXCEPTION 'only IPv4 addresses are supported';
+   END IF;
+
+   -- 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;
+
+   -- adds IP entries
+   if ( addIpEntries IS NOT FALSE ) THEN
+      PERFORM ip_add_network( ipNetblockId := returnId);
+   END IF;
+
+   RETURN returnId;
+END;
+$$;
+
+
+/* end of sql */