[lms] LMS + freeradius, integracja mysql

9 Paź 2007


      Witam,
Szukam pomocy lub gotowej konfiguracji lms z freeradius.
Swego czasu znalazlem na tej liście gotowy config pod postgresql (jest w 
załączniku). Jednak z wielu względów muszę zostać przy mysql... Więc czy 
ktoś z Was mógłby podzielić się informacją jak za pomocą mysql odpytać 
bazę lmsa, żeby osiągnąć podobny efekt?
pozdr.
Sebastian
**
    ** http://www.postgresql.org/
CREATE TYPE radiusrow AS (
    id integer,
    UserName text,
    Attribute text,
    Value text,
    Op text
);
CREATE OR REPLACE FUNCTION radcheck (username text) RETURNS SETOF radiusrow AS $$
DECLARE
    result radiusrow;
BEGIN
    IF username ~* '^[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}$' 
    THEN 
    	SELECT 0, username, 'Password', '', '=='
    	INTO result
    	FROM nodes 
    	WHERE LOWER(mac) = LOWER(username);
    	IF FOUND THEN RETURN NEXT result; END IF;
SELECT 1, username, 'NAS-Port-Id', netdevices.name, '=='
    	INTO result
    	FROM nodes n 
    	LEFT JOIN netdevices ON (n.netdev = netdevices.id)
    	WHERE LOWER(mac) = LOWER(username);
    	IF FOUND THEN RETURN NEXT result; END IF; 
    ELSE 
    	SELECT 0, username, 'Password', passwd, '=='
    	INTO result
    	FROM nodes 
    	WHERE LOWER(name) = LOWER(username);
    	IF FOUND THEN RETURN NEXT result; END IF;
SELECT 1, username, 'NAS-Port-Id', netdevices.name, '=='
    	INTO result
    	FROM nodes n 
    	LEFT JOIN netdevices ON (n.netdev = netdevices.id)
    	WHERE LOWER(n.name) = LOWER(username);
    	IF FOUND THEN RETURN NEXT result; END IF; 
    END IF;
    RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION radreply (username text) RETURNS SETOF radiusrow AS $$
DECLARE
    result radiusrow;
    up integer := 0;
    down integer := 0;
    theip text; themac text; themask text; thename text;	
BEGIN
    IF username ~* '^[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}$' 
    THEN 
    	SELECT 0, username, 'NAS-Port-Id', netdevices.name, '=='
    	INTO result
    	FROM nodes n 
    	LEFT JOIN netdevices ON (n.netdev = netdevices.id)
    	WHERE LOWER(n.mac) = LOWER(username);
    	IF FOUND THEN RETURN NEXT result; END IF; 
    ELSE 
    	result.UserName := username;
    	result.Op := '==';
SELECT INET_NTOA(n.ipaddr), n.mac, netdevices.name, 
    		(SELECT mask FROM networks WHERE n.ipaddr & INET_ATON(mask) = address) AS mask
    	INTO theip, themac, thename, themask
    	FROM nodes n
    	LEFT JOIN netdevices ON (n.netdev = netdevices.id)
    	WHERE LOWER(n.name) = LOWER(username);
IF theip IS NOT NULL
    	THEN 
    		result.id := 0;
    		result.Attribute := 'Framed-Ip-Address';
    		result.Value := theip;
    		RETURN NEXT result; 
    	END IF; 
    	IF themac IS NOT NULL
    	THEN 
    		result.id := 1;
    		result.Attribute := 'Calling-Station-Id';
    		result.Value := themac;
    		RETURN NEXT result; 
    	END IF; 
    	IF themask IS NOT NULL
    	THEN 
    		result.id := 2;
    		result.Attribute := 'Framed-IP-Netmask';
    		result.Value := themask;
    		RETURN NEXT result; 
    	END IF; 
--		IF thename IS NOT NULL
--		THEN 
--			result.id := 3;
--			result.Attribute := 'NAS-Port-Id';
--			result.Value := thename;
--			RETURN NEXT result; 
--		END IF;
SELECT ROUND(SUM(upceil)/COUNT(DISTINCT n.id)),
    		ROUND(SUM(downceil)/COUNT(DISTINCT n.id)) * 2
    	INTO up, down
                FROM assignments
                LEFT JOIN tariffs ON (tariffid = tariffs.id) 
    	LEFT JOIN nodes n ON (customerid = n.ownerid)
    	WHERE EXISTS (SELECT 1 FROM nodes n2
    			WHERE LOWER(n2.name) = LOWER(username) 
    			AND n.ownerid = n2.ownerid
    			AND n2.access = 1)
    		AND n.access = 1 
    		AND (datefrom <= EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)) OR datefrom = 0) 
    		AND (dateto >= EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)) OR dateto = 0);
    	
    	result.id := 4;
    	result.Attribute := 'Mikrotik-Rate-Limit';
    	result.Value := up || 'k/' || down || 'k';
    	RETURN NEXT result; 
    END IF;
    RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION radgroupcheck (username text) RETURNS SETOF radiusrow AS $$
DECLARE
    result radiusrow;
BEGIN
    IF username !~* '^[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}$' 
    THEN 
    	SELECT 0, 'PPPOE', 'Auth-Type', 'MS-CHAP', ':='
    	INTO result;
    	RETURN NEXT result;
    END IF;
    RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION radgroupreply (username text) RETURNS SETOF radiusrow AS $$
DECLARE
    result radiusrow;
BEGIN
    IF username !~* '^[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}:[0-9a-f]{2}$' 
    THEN 
    	SELECT 0, 'PPPOE', 'Framed-Protocol', 'PPP', '=='
    	INTO result;
    	RETURN NEXT result;
---		SELECT 1, 'PPPOE', 'Framed-MTU', '1500', '=='
---		INTO result;
---		RETURN NEXT result;
    	SELECT 2, 'PPPOE', 'Service-Type', 'Framed-User', '=='
    	INTO result;
    	RETURN NEXT result;
    	SELECT 3, 'PPPOE', 'Port-Limit', '1', '=='
    	INTO result;
    		RETURN NEXT result;
    END IF;
    RETURN;
END;
$$ LANGUAGE plpgsql;
_______________________________________________
lms mailing list
lms@lists.lms.org.pl
http://lists.lms.org.pl/mailman/listinfo/lms

[lms] LMS + freeradius, integracja mysql

sqniC