path: root/notes/projects/radius/sql/ippool-functions.sql

/*
 *  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 */