/*
 *  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
 *
 *  netblockId
 *  : reference to owning netblock
 *
 *  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,
   netblockId       INTEGER         NOT NULL,
   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 ),
   FOREIGN KEY      ( netblockId )
      REFERENCES    netblock
                    ( id )
);
CREATE INDEX
   IF NOT EXISTS    ip_index
   ON               ip
                    ( address,
                      netblockId,
                      modified,
                      username,
                      acctSession,
                      nasId
                    );


-- /////////////////
-- //             //
-- //  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 IP address to specific user/session
 *
 *  % 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
(
   assignIp         INET,
   assignUser       TEXT,
   assignNas        TEXT            DEFAULT NULL,
   assignSess       TEXT            DEFAULT NULL
)
   RETURNS          BOOLEAN
   LANGUAGE         plpgsql
   SECURITY         DEFINER
   AS               $$
DECLARE
   assignNasId      INTEGER;
BEGIN

   -- sets defaults
   IF ( assignSess IS NULL ) THEN
      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 ),
                    username      = assignUser,
                    acctSession   = assignSess
      WHERE         address       = set_masklen( assignIp, 32 );

   RETURN FOUND;
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
      WHERE         address       = set_masklen( assignIp, 32 );

   RETURN FOUND;
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.nasId IS NOT NULL ) THEN
      RAISE EXCEPTION 'IP address is still assigned to NAS session';
   END IF;

   RETURN NEW;
END;
$$;


/*
 *  forces initial values on insert
 *
 *  returns trigger
 */
CREATE OR REPLACE FUNCTION ip_trigger_insert
( )
   RETURNS          trigger
   LANGUAGE         plpgsql
   AS               $$
DECLARE
BEGIN

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

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

   RETURN NEW;
END;
$$;


/*
 *  verifies no assigned IP addresses before truncating
 *
 *  returns trigger
 */
CREATE OR REPLACE FUNCTION ip_trigger_truncate
( )
   RETURNS          trigger
   LANGUAGE         plpgsql
   AS               $$
DECLARE
BEGIN
   RAISE EXCEPTION 'DO NOT TRUNCATE IP TABLE';
   RETURN NEW;
END;
$$;


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

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

   -- update timestamp
   NEW.modified := current_timestamp;

   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;
$$;


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

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

   RETURN NEW;
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;
$$;


/*
 *  adds contraints to netblocks
 *
 *  returns trigger
 */
CREATE OR REPLACE FUNCTION netblock_trigger_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 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;
$$;


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

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

   RETURN NEW;
END;
$$;


/*
 *  updates timestamp of addresses
 *
 *  returns trigger
 */
CREATE OR REPLACE FUNCTION region_trigger_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_trigger_delete
   BEFORE           DELETE
   ON               ip
   FOR EACH         ROW
   EXECUTE          PROCEDURE ip_trigger_delete();
CREATE TRIGGER      ip_trigger_insert
   BEFORE           INSERT
   ON               ip
   FOR EACH         ROW
   EXECUTE          PROCEDURE ip_trigger_insert();
CREATE TRIGGER      ip_trigger_truncate
   BEFORE           TRUNCATE
   ON               ip
   FOR EACH         STATEMENT
   EXECUTE          PROCEDURE ip_trigger_truncate();
CREATE TRIGGER      ip_trigger_update
   BEFORE           UPDATE
   ON               ip
   FOR EACH         ROW
   EXECUTE          PROCEDURE ip_trigger_update();


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


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


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


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

/* end of sql */