path: root/notes/projects/radius/sql/ippool-schema.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


-- //////////////////
-- //              //
-- //  Privileges  //
-- //              //
-- //////////////////


-- GRANT               CONNECT
--    ON DATABASE      radius_ippool
--    TO               radius_user;


GRANT               USAGE
   ON SCHEMA        public
   TO               radius_user;


ALTER DEFAULT PRIVILEGES
   IN SCHEMA        public
   GRANT            SELECT
      ON            TABLES
      TO            radius_user;


ALTER DEFAULT PRIVILEGES
   IN SCHEMA        public
   GRANT            EXECUTE
      ON            FUNCTIONS
      TO            radius_admin;


-- //////////////
-- //          //
-- //  Tables  //
-- //          //
-- //////////////


/*
 *  RADIUS Pool Type
 *
 *  id
 *  : The primary key of a specific entry
 *
 *  name
 *  : the pool name/type
 *
 *  description
 *  : human readable description of pool
 */
CREATE TABLE IF NOT EXISTS pool
(
   id               SERIAL,
   name             VARCHAR(32)     NOT NULL UNIQUE,
   description      VARCHAR(128),
   PRIMARY KEY      ( id )
);
CREATE INDEX
   IF NOT EXISTS    pool_index
   ON               pool
                    ( id );


/*
 *  RADIUS geographic region
 *
 *  id
 *  : The primary key of a specific entry
 *
 *  name
 *  : the region's name/locality
 *
 *  description
 *  : human readable description of region
 */
CREATE TABLE IF NOT EXISTS region
(
   id               SERIAL,
   name             VARCHAR(32)     NOT NULL,
   description      VARCHAR(128),
   PRIMARY KEY      ( id )
);
CREATE INDEX
   IF NOT EXISTS    region_index
   ON               region
                    ( id );


/*
 *  RADIUS NAS client
 *
 *  id
 *  : The primary key of a specific entry
 *
 *  name
 *  : the NAS truncated DNS name (i.e. br1.cwc)
 *
 *  regionId
 *  : reference to the region in which the NAS is physically located
 */
CREATE TABLE IF NOT EXISTS nas
(
   id               SERIAL,
   name             VARCHAR(32)     NOT NULL,
   regionId         INTEGER         NOT NULL,
   PRIMARY KEY      ( id ),
   FOREIGN KEY      ( regionId )
      REFERENCES    region
                    ( id )
);
CREATE INDEX
   IF NOT EXISTS    nas_index
   ON               nas
                    ( id,
                      regionId
                    );


/*
 *  RADIUS network block
 *
 *  id
 *  : The primary key of a specific entry
 *
 *  network
 *  : network address and network size of block
 *
 *  poolId
 *  : reference to the pool type of the network
 *
 *  regionId
 *  : reference to the physical region served by the block
 *
 *  active
 *  : assignable to customer if true, disabled/inactive if false
 *
 *  description
 *  : human readable description of network block
 */
CREATE TABLE IF NOT EXISTS netblock
(
   id               SERIAL,
   network          CIDR            NOT NULL UNIQUE,
   poolId           INTEGER         NOT NULL,
   regionId         INTEGER,
   active           BOOLEAN         NOT NULL DEFAULT TRUE,
   description      VARCHAR(256),
   PRIMARY KEY      ( id ),
   FOREIGN KEY      ( poolId )
      REFERENCES    pool
                    ( id ),
   FOREIGN KEY      ( regionId )
      REFERENCES    region
                    ( id )
);
CREATE INDEX
   IF NOT EXISTS    netblock_index
   ON               netblock
                    ( id,
                      poolId,
                      network,
                      active
                    );


/*
 *  RADIUS user-assignable IP address
 *
 *  address
 *  : user assignable IP address
 *
 *  modified
 *  : timestamp generated when entry is modified
 *
 *  username
 *  : username currently assigned or last assigned address
 *
 *  acctSession
 *  : RADIUS accounting session currently holding address
 *
 *  nasId
 *  : reference to RADIUS NAS client currently holding address
 */
CREATE TABLE IF NOT EXISTS ip
(
   address          INET,
   modified         timestamp       WITH TIME ZONE NOT NULL
                                    DEFAULT TIMESTAMP 'epoch',
   username         VARCHAR(64),
   acctSession      VARCHAR(32),
   nasId            INTEGER,
   PRIMARY KEY      ( address ),
   FOREIGN KEY      ( nasId )
      REFERENCES    nas
                    ( id )
);
CREATE INDEX
   IF NOT EXISTS    ip_index
   ON               ip
                    ( address,
                      modified,
                      username,
                      acctSession,
                      nasId
                    );


-- /////////////////
-- //             //
-- //  Functions  //
-- //             //
-- /////////////////

/*
 *  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
   SECURITY         DEFINER
   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_assign
(
   assignIp         INET,
   assignUser       TEXT,
   assignNas        TEXT            DEFAULT NULL,
   assignSess       TEXT            DEFAULT NULL
)
   RETURNS          BOOLEAN
   LANGUAGE         plpgsql
   SECURITY         DEFINER
   AS               $$
DECLARE
BEGIN

   -- sets defaults
   IF ( assignSess IS NULL ) THEN
      assignSess   := 'user:' || assignUser;
   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 );

   RETURN FOUND;
END;
$$;


/*
 *  updates timestamp of addresses
 *
 *  returns trigger
 */
CREATE OR REPLACE FUNCTION ip_trigger
( )
   RETURNS          trigger
   LANGUAGE         plpgsql
   AS               $$
DECLARE
BEGIN

   -- prevents IP address from being 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
   IF ( TG_OP = 'UPDATE' ) THEN
      NEW.modified := current_timestamp;
   ELSIF ( TG_OP = 'INSERT' ) 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;
$$;


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

   -- 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( ipNetwork := netAddress );
   END IF;

   RETURN returnId;
END;
$$;


/*
 *  adds contraints to netblocks
 *
 *  returns trigger
 */
CREATE OR REPLACE FUNCTION netblock_trigger
( )
   RETURNS          trigger
   LANGUAGE         plpgsql
   AS               $$
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
      FROM          netblock
      WHERE         regionId != NEW.regionId
                    AND
                    ( NEW.network >>= network
                      OR
                      NEW.network <<= network
                    );
   IF ( resultCount != 0 ) THEN
     RAISE EXCEPTION 'conflict with existing network regions';
   END IF;

   -- verifies that new region does not conflict with new network
   SELECT           count(id) INTO resultCount
      FROM          netblock
      WHERE         poolId != NEW.poolId
                    AND
                    ( NEW.network >>= network
                      OR
                      NEW.network <<= network
                    );
   IF ( resultCount != 0 ) THEN
     RAISE EXCEPTION 'conflict with existing network pools';
   END IF;

   RETURN NEW;
END;
$$;


/*
 *  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(
 *       poolName  := 'dhcp'
 *    );
 *  % SELECT pool_add(
 *       poolName  := 'dhcp',
 *       poolDesc  := 'Pool of IP addresses for PPPoE connections'
 *    );
 *
 *  poolName
 *  : name of pool being created in the database
 *
 *  poolDesc
 *  : human readable description of the pool
 *
 *  Returns the `id` of the created pool
 */
CREATE OR REPLACE FUNCTION pool_add
(
   poolName         VARCHAR(32),
   poolDesc         VARCHAR(128) DEFAULT NULL
)
   RETURNS          INTEGER
   LANGUAGE         plpgsql
   SECURITY         DEFINER
   AS               $$
DECLARE
   returnId         INTEGER;
BEGIN
   -- adds new pool
   INSERT INTO      pool
                    ( name,
                      description
                    )
      VALUES        ( lower(poolName),
                      poolDesc
                    )
      RETURNING     id INTO returnId;

   RETURN returnId;
END;
$$;


/*
 *  Creates entry for geographic region
 *
 *  % SELECT region_add(
 *       regionName  := 'Anchorage'
 *    );
 *  % SELECT region_add(
 *       regionName  := 'Anchorage',
 *       regionDesc  := 'Anchorage Area'
 *    );
 *
 *  regionName
 *  : name of region being created in the database
 *
 *  regionDesc
 *  : human readable description of the region
 *
 *  Returns the `id` of the created pool
 */
CREATE OR REPLACE FUNCTION region_add
(
   regionName       VARCHAR(32),
   regionDesc       VARCHAR(128) DEFAULT NULL
)
   RETURNS          INTEGER
   LANGUAGE         plpgsql
   SECURITY         DEFINER
   AS               $$
DECLARE
   returnId         INTEGER;
BEGIN
   -- adds new pool
   INSERT INTO      region
                    ( name,
                      description
                    )
      VALUES        ( lower(regionName),
                      regionDesc
                    )
      RETURNING     id INTO returnId;

   RETURN returnId;
END;
$$;


-- ////////////////
-- //            //
-- //  Triggers  //
-- //            //
-- ////////////////

-- address table triggers
CREATE TRIGGER      ip_trigger
   BEFORE           INSERT OR UPDATE
   ON               ip
   FOR EACH         ROW
   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
   BEFORE           INSERT OR UPDATE
   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 */