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,
   assignable       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,
                      assignable
                    );


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


-- /////////////////////////
-- //                     //
-- //  Trigger Functions  //
-- //                     //
-- /////////////////////////

/*
 *  trigger functions for ip table
 */
CREATE OR REPLACE FUNCTION ip_before_delete
( )
   RETURNS          trigger
   LANGUAGE         plpgsql
   AS               $$
DECLARE
BEGIN

   -- verifies ip is not assigned to a RADIUS accounting session
   IF ( OLD.nasId IS NOT NULL ) THEN
      RETURN NULL;
   END IF;

   RETURN OLD;
END;
$$;


CREATE OR REPLACE FUNCTION ip_before_insert
( )
   RETURNS          trigger
   LANGUAGE         plpgsql
   AS               $$
DECLARE
BEGIN

   -- forces initial values
   NEW.username     := NULL;
   NEW.nasId        := NULL;
   NEW.modified     := TIMESTAMP 'epoch';

   -- verifies netblock contains IP address
   PERFORM          id
      FROM          netblock
      WHERE         id = NEW.netblockId
         AND        network >>= NEW.address;
  IF NOT FOUND THEN
      RAISE EXCEPTION 'invalid netblock';
  END IF;

   -- determines mask for single IP address
   IF ( family(NEW.address) != 4 ) THEN
      RAISE EXCEPTION 'only IPv4 addresses are supported';
   END IF;

   RETURN NEW;
END;
$$;


CREATE OR REPLACE FUNCTION ip_before_truncate
( )
   RETURNS          trigger
   LANGUAGE         plpgsql
   AS               $$
DECLARE
BEGIN
   RETURN NULL;
END;
$$;


CREATE OR REPLACE FUNCTION ip_before_update
( )
   RETURNS          trigger
   LANGUAGE         plpgsql
   AS               $$
DECLARE
   ipAssignable     BOOLEAN;
BEGIN

   -- prevents IP address from being modified
   IF ( NEW.address != OLD.address ) THEN
      RAISE EXCEPTION 'address cannot be modified';
   END IF;

   -- verifies netblock contains IP address
   IF ( NEW.netblockId != NEW.netblockId ) THEN
      SELECT        assignable      INTO ipAssignable
         FROM       netblock
         WHERE      id      =   NEW.netblockId
            AND     network >>= NEW.address;
      IF ipAssignable IS NULL THEN
         RAISE EXCEPTION 'invalid netblock';
      END IF;
      IF ipAssignable IS TRUE THEN
         PERFORM    id
            FROM    netblock
            WHERE   assignable IS TRUE
               AND  network >>= NEW.address;
         IF FOUND THEN
            RAISE EXCEPTION 'cannot associate assignable netblock if an unassignable netblock contains IP address';
         END IF;
      END IF;
   END IF;

   -- update timestamp
   IF ( ( OLD.nasId != NEW.nasId ) OR ( OLD.username != NEW.username ) ) THEN
      NEW.modified := current_timestamp;
   ELSE
      NEW.modified := OLD.modified;
   END IF;

   RETURN NEW;
END;
$$;


/*
 *  trigger functions for nas table
 */
CREATE OR REPLACE FUNCTION nas_before_insert_or_update
( )
   RETURNS          trigger
   LANGUAGE         plpgsql
   AS               $$
DECLARE
BEGIN

   -- normalizes name
   NEW.name = lower( NEW.name );

   RETURN NEW;
END;
$$;


/*
 *  trigger functions for netblock table
 */
CREATE OR REPLACE FUNCTION netblock_before_insert_or_update
( )
   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 pool 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;

   -- verifies that IP address will not be orphaned
   PERFORM          netblockId
      FROM          ip
      WHERE         ip.netblockId = NEW.id
         AND        NOT ip.address <<= NEW.network;
   IF FOUND THEN
      RAISE EXCEPTION 'changing network will orphan existing IP addresses';
   END IF;

   -- updates IP Association of netblock if an update and is now assignable
   IF ( ( OLD.assignable IS FALSE ) AND ( NEW.assignable IS TRUE ) ) THEN
      UPDATE        ip
         SET        netblockId      =   nb.id
         FROM       ip
         INNER JOIN netblock        AS  nb
            ON      nb.network      >>= ip.address
         WHERE      ip.address      <<= NEW.network
            AND     nb.assignable   IS  TRUE;
   END IF;

   RETURN NEW;
END;
$$;


CREATE OR REPLACE FUNCTION netblock_after_insert_or_update
( )
   RETURNS          trigger
   LANGUAGE         plpgsql
   AS               $$
DECLARE
BEGIN
   -- updates IP association of netblock is not assignable
   IF ( NEW.assignable IS FALSE ) THEN
      UPDATE        ip
         SET        netblockId = NEW.id
         WHERE      address <<= NEW.network;
   END IF;

   RETURN NEW;
END;
$$;


/*
 *  trigger functions for pool table
 */
CREATE OR REPLACE FUNCTION pool_before_insert_or_update
( )
   RETURNS          trigger
   LANGUAGE         plpgsql
   AS               $$
DECLARE
BEGIN

   -- normalizes name
   NEW.name = lower( NEW.name );

   RETURN NEW;
END;
$$;


/*
 *  trigger functions for region table
 */
CREATE OR REPLACE FUNCTION region_before_insert_or_update
( )
   RETURNS          trigger
   LANGUAGE         plpgsql
   AS               $$
DECLARE
BEGIN

   -- normalizes name
   NEW.name = lower( NEW.name );

   RETURN NEW;
END;
$$;


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

-- address table triggers
CREATE TRIGGER      ip_before_delete
   BEFORE           DELETE
   ON               ip
   FOR EACH         ROW
   EXECUTE          PROCEDURE ip_before_delete();
CREATE TRIGGER      ip_before_insert
   BEFORE           INSERT
   ON               ip
   FOR EACH         ROW
   EXECUTE          PROCEDURE ip_before_insert();
CREATE TRIGGER      ip_before_truncate
   BEFORE           TRUNCATE
   ON               ip
   FOR EACH         STATEMENT
   EXECUTE          PROCEDURE ip_before_truncate();
CREATE TRIGGER      ip_before_update
   BEFORE           UPDATE
   ON               ip
   FOR EACH         ROW
   EXECUTE          PROCEDURE ip_before_update();


-- netblock table triggers
CREATE TRIGGER      netblock_before_insert_or_update
   BEFORE           INSERT OR UPDATE
   ON               netblock
   FOR EACH         ROW
   EXECUTE          PROCEDURE netblock_before_insert_or_update();
CREATE TRIGGER      netblock_after_insert_or_update
   AFTER            INSERT OR UPDATE
   ON               netblock
   FOR EACH         ROW
   EXECUTE          PROCEDURE netblock_after_insert_or_update();


-- nas table triggers
CREATE TRIGGER      nas_before_insert_or_update
   BEFORE           INSERT OR UPDATE
   ON               nas
   FOR EACH         ROW
   EXECUTE          PROCEDURE nas_before_insert_or_update();


-- pool table triggers
CREATE TRIGGER      pool_before_insert_or_update
   BEFORE           INSERT OR UPDATE
   ON               pool
   FOR EACH         ROW
   EXECUTE          PROCEDURE pool_before_insert_or_update();


-- region table triggers
CREATE TRIGGER      region_before_insert_or_update
   BEFORE           INSERT OR UPDATE
   ON               region
   FOR EACH         ROW
   EXECUTE          PROCEDURE region_before_insert_or_update();


/* end of sql */