From efbbc604314735edbf4bd0edd23017c05d680961 Mon Sep 17 00:00:00 2001 From: "David M. Syzdek" Date: Fri, 24 May 2019 14:12:16 -0800 Subject: Updating documentation --- notes/projects/radius/sql/README.ippool.md | 84 +++++++++++++- notes/projects/radius/sql/ippool-functions.sql | 148 ++++++++++++++++++++++--- 2 files changed, 215 insertions(+), 17 deletions(-) diff --git a/notes/projects/radius/sql/README.ippool.md b/notes/projects/radius/sql/README.ippool.md index b463df8..d6ed50f 100644 --- a/notes/projects/radius/sql/README.ippool.md +++ b/notes/projects/radius/sql/README.ippool.md @@ -5,7 +5,7 @@ IP Engineering RADIUS IP Pool Database Objectives ---------- - * Psuedo static IP addresses + * Psuedo static IP addresses (i.e. "sticky" IP addresses) * Per NAS Preference for geographic regions * Ability to deavtivate/busy out specific netblocks @@ -77,10 +77,88 @@ The following PostgreSQL (psql) commands will purge RADIUS roles from Postgresql DROP ROLE radius; -Network Block Management ------------------------- +Auditing Reports +---------------- +The following displays IP usage by IP pool: + + SELECT * FROM pool_audit() ORDER BY poolname, regionType; + +The following displays IP usage by IP pool and region: + + SELECT * FROM region_audit() ORDER BY poolName, regionName; + +The following displays IP usage by defined netblocks: + + SELECT * FROM netblock_audit() ORDER BY poolName, network; + +The following displays the state of each IP address: + + SELECT * FROM ip_audit() ORDER BY ipAddress; IP Address Assignments ---------------------- + +IP addresses can be manually assigned or auto-assigned based upon the NAS and +pool type specified. + +The following is an example of manually specifing an IP address assignment: + + SELECT ip_assign + ( assignIp := '209.193.4.8/32', + assignNas := 'br1.sol', + assignUser := 'jdoe8@example.net' + ); + +The above assignment will fail if a netblock containing '209.193.4.8/32' has +been marked as 'busyout'. A assignment to a busy netblock can be forced using +the 'assignForce' option: + + SELECT ip_assign + ( assignIp := '209.193.4.3/32', + assignNas := 'br1.sol', + assignUser := 'busy1@example.net', + assignForce := TRUE + ); + +The following is an example of auto-assigning an IP address based upon a client +session's NAS and pool type: + + SELECT ip_autoassign + ( assignUser := 'autoassign1@example.net', + assignNas := 'br1.sol', + assignPool := 'dhcp' + ); + +By default, `ip_autoassign()` will attempt to assign "sticky" IP addresses to a +session. A sticky address is a best effort attempt to re-assign the IP address +previously used by the subscriber. Sticky IP address assignments use the +following logic to assign an IP address to a user session: + + * Attempt to assign regional pool address which was last used by subscriber. + * Attempt to assign regional pool address which has been idle the longest. + * Attempt to assign non-regional pool address which was last used by subscriber. + * Attempt to assign non-regional pool address which has been idle the longest. + * Fail address assignment. + +Dynamic IP addresses can be requested by passing 'assignPrevIp' with a value of +FALSE to `ip_autoassign()`: + + SELECT ip_autoassign + ( assignUser := 'autoassign2@example.net', + assignNas := 'br1.sol', + assignPool := 'dhcp', + assignPrevIp := FALSE + ); + +When assigning auto-assigning dynamic IP addresses, the following logic is used: + + * Attempt to assign regional pool address which has been idle the longest. + * Attempt to assign non-regional pool address which has been idle the longest. + * Fail address assignment. + +Both manual and auto-assigned IP addresses are freed using `ip_release()`: + + SELECT ip_release( '209.193.4.3' ); + diff --git a/notes/projects/radius/sql/ippool-functions.sql b/notes/projects/radius/sql/ippool-functions.sql index f7cc8c5..3bbabdf 100644 --- a/notes/projects/radius/sql/ippool-functions.sql +++ b/notes/projects/radius/sql/ippool-functions.sql @@ -223,7 +223,35 @@ $$; * * % SELECT * FROM ip_audit(); * - * Returns table of ip + * Returns table with the following columns: + * + * poolName + * : name of pool which owns IP address + * + * regionname + * : name of region which owns IP address or NULL if not assigned to a region + * + * ipAddress + * : the IP address of the current record + * + * busyout + * : if TRUE, IP address cannot be assigned without being forced + * + * assigned + * : if true, IP address is assigend to a session. If false, IP address is + * : available + * + * modified + * : time stamp of last assignment or release + * + * nasName + * : NAS name of current assignment, NULL if IP address is not assigned to a + * : session + * + * username + * : username of current subscriber if IP address is assigned or username of + * : prevsious subscriber if IP address is not currently assigned. + * */ CREATE OR REPLACE FUNCTION ip_audit ( @@ -447,9 +475,16 @@ $$; /* - * updates timestamp of addresses + * Releases IP address from current assignment + * + * % SELECT ip_release( + * assignIp := '209.193.4.1' + * ) + * + * assignIp + * : IP address to release * - * returns trigger + * returns TRUE if sucessful and FALSE if unsuccessful */ CREATE OR REPLACE FUNCTION ip_release ( @@ -631,7 +666,36 @@ $$; * * % SELECT * FROM netblock_audit(); * - * Returns table of netblocks + * Returns table with the following columns: + * + * poolName + * : name of pool which owns IP address + * + * regionname + * : name of region which owns IP address or NULL if not assigned to a region + * + * network + * : network address and CIDR of netblock + * + * busyout + * : if TRUE, IP address cannot be assigned without being forced + * + * ipAssigned + * : number of IP addresses assigned from netblock + * + * ipUnassigned + * : number of IP addresses in netblock which are not assigned + * + * ipAvailable + * : number of IP addresses in netblock available for assignment (i.e. not + * : busyout and not currently assigend) + * + * ipTotal + * : total number of IP addresses in netblock + * + * description + * : human readable description of netblock + * */ CREATE OR REPLACE FUNCTION netblock_audit ( @@ -697,11 +761,39 @@ $$; /* - * audits netblocks + * audits pools * - * % SELECT * FROM netblock_audit(); + * % SELECT * FROM netblock_pool(); + * + * Returns table with the following columns: + * + * poolName + * : name of pool which owns IP address + * + * regionType + * : one of 'regional', 'non-regional', and 'TOTAL' + * + * ipAssignedFree + * : number of IP addresses which are currently not busyout and are assigend + * : to sessions + * + * ipAssignedBusy + * : number of IP addresses which are currently busyout and are assigend to + * : sessions + * + * ipAvailable + * : number of IP addresses which are currently not busyout and not assigned to + * : a session + * + * ipBusyout + * : number of IP addresses currently marked as busyout + * + * ipTotal + * : total number of IP addresses + * + * description + * : human readable description of record * - * Returns table of netblocks */ CREATE OR REPLACE FUNCTION pool_audit ( @@ -709,7 +801,7 @@ CREATE OR REPLACE FUNCTION pool_audit RETURNS TABLE ( poolName VARCHAR(32), - addressType TEXT, + regionType TEXT, ipAssignedFree BIGINT, ipAssignedBusy BIGINT, ipAvailable BIGINT, @@ -790,7 +882,7 @@ BEGIN INNER JOIN netblock AS nb2 ON nb2.id = ip.netblockId WHERE nb2.regionId IS NULL - AND nb2.poolId IS NULL + AND nb2.poolId = pool.id AND nb2.busyout IS FALSE AND ip.nasId IS NOT NULL ) AS ipAssignFree, @@ -839,7 +931,7 @@ BEGIN FROM netblock INNER JOIN pool ON pool.id = netblock.poolId - WHERE netblock.regionId IS NULL; + WHERE netblock.regionId IS NULL UNION ALL SELECT DISTINCT pool.name AS poolName, 'ALL' AS addressType, @@ -848,7 +940,7 @@ BEGIN FROM ip INNER JOIN netblock AS nb2 ON nb2.id = ip.netblockId - WHERE nb2.poolId IS NULL + WHERE nb2.poolId = pool.id AND nb2.busyout IS FALSE AND ip.nasId IS NOT NULL ) AS ipAssignFree, @@ -900,11 +992,39 @@ $$; /* - * audits netblocks + * audits regions * - * % SELECT * FROM netblock_audit(); + * % SELECT * FROM netblock_region(); + * + * Returns table with the following columns: + * + * poolName + * : name of pool which owns IP address + * + * regionName + * : name of region + * + * ipAssignedFree + * : number of IP addresses which are currently not busyout and are assigend + * : to sessions + * + * ipAssignedBusy + * : number of IP addresses which are currently busyout and are assigend to + * : sessions + * + * ipAvailable + * : number of IP addresses which are currently not busyout and not assigned to + * : a session + * + * ipBusyout + * : number of IP addresses currently marked as busyout + * + * ipTotal + * : total number of IP addresses + * + * description + * : human readable description of record * - * Returns table of netblocks */ CREATE OR REPLACE FUNCTION region_audit ( -- cgit v1.2.3