From e8e3fbe71941f28b0e94d2c8ed1382df4be86cf6 Mon Sep 17 00:00:00 2001 From: "David M. Syzdek" Date: Tue, 14 May 2019 12:23:17 -0800 Subject: Fixing ip assignment tallies --- notes/projects/radius/sql/ippool-functions.sql | 20 ++++++++++++++++---- 1 file changed, 16 insertions(+), 4 deletions(-) diff --git a/notes/projects/radius/sql/ippool-functions.sql b/notes/projects/radius/sql/ippool-functions.sql index bbfd60f..a879cf3 100644 --- a/notes/projects/radius/sql/ippool-functions.sql +++ b/notes/projects/radius/sql/ippool-functions.sql @@ -169,7 +169,7 @@ BEGIN END IF; -- tallies existing sessions - SELECT count(id) + SELECT count( address ) AS ipCount INTO assignPortCount FROM ip WHERE ip.username LIKE assignUser @@ -181,10 +181,10 @@ BEGIN -- verifies IP address is not inactive IF ( assignForce = FALSE ) THEN - SELECT network + PERFORM network FROM netblock WHERE netblock.network >>= assignIp - AND netblock.active IS FALSE; + AND netblock.active = FALSE; IF FOUND THEN RETURN FALSE; END IF; @@ -265,7 +265,7 @@ CREATE OR REPLACE FUNCTION ip_autoassign assignUser TEXT, assignNas TEXT, assignPool TEXT, - assignPortLimit INTEGER, DEFAULT 2, + assignPortLimit INTEGER DEFAULT 2, assignSess TEXT DEFAULT NULL, assignPrevIp BOOLEAN DEFAULT TRUE ) @@ -276,6 +276,7 @@ CREATE OR REPLACE FUNCTION ip_autoassign DECLARE assignedIp INET; assignNasId INTEGER; + assignPortCount INTEGER; BEGIN -- sets defaults @@ -283,6 +284,17 @@ BEGIN assignSess := 'user:' || assignUser; END IF; + -- tallies existing sessions + SELECT count( address ) + INTO assignPortCount + FROM ip + WHERE ip.username LIKE assignUser + AND ip.nasId IS NOT NULL + AND ip.address != assignIp; + IF ( assignPortCount >= assignPortLimit ) THEN + RETURN NULL; + END IF; + -- search for user's previous address IF ( assignPrevIp IS TRUE ) THEN SELECT ip.address, -- cgit v1.2.3