c_radius_functions.sql

gnugk软交换产品

-- RADIUS ARQ/RRQ processing functions
--
-- VoIP Billing Platform for GnuGk
-- Copyright (c) 2004, Michal Zygmuntowicz
--
-- This work is published under the GNU Public License (GPL)
-- see file COPYING for details

-- single RADIUS attribute value pair
CREATE TABLE voipradattr (
	-- unused
	id INT,
	-- attribute name ('User-Name' for example)
	attrname TEXT,
	-- attribute value ('user1')
	attrvalue TEXT,
	-- operator ('==',':=','=',...) for the name-value pair
	attrop TEXT
);

-- build a list of RADIUS check attribute-value pairs for endpoint registration request
-- $1 - User-Name
-- $2 - Framed-IP-Address
-- $3 - h323-ivr-in
CREATE OR REPLACE FUNCTION radius_get_check_rrq_attrs(TEXT, INET, TEXT)
	RETURNS SETOF voipradattr AS
'
DECLARE
	framed_ip ALIAS FOR $2;
	username TEXT;
	h323ivrout ALIAS FOR $3;
	reject_attr voipradattr%ROWTYPE;
	check_attr voipradattr%ROWTYPE;
	query_result RECORD;
	userid INT;
	aliasnum INT;
	rrqalias TEXT;
BEGIN
	RAISE LOG ''sqlbill: RRQ(username: %; IP: %; aliases: %)'', $1, $2, $3;
	
	-- prepare Auth-Type := Reject avp, as it is referenced very often
	reject_attr.id := 0;
	reject_attr.attrname := ''Auth-Type'';
	reject_attr.attrvalue := ''Reject'';
	reject_attr.attrop := '':='';
	
	-- check input parameters
	IF $1 IS NULL OR $2 IS NULL THEN
		RETURN NEXT reject_attr;
		RETURN;
	END IF;
	
	-- remove RADIUS escapes
	username := radius_xlat($1);

	userid := match_user(username, framed_ip);
	IF userid IS NULL THEN
		RETURN NEXT reject_attr;
		RETURN;
	END IF;
		
	-- get user information
	SELECT INTO query_result h323id, chappassword, allowedaliases, framedip 
		FROM voipuser u JOIN voipaccount a ON u.accountid = a.id 
		WHERE a.closed IS NULL AND NOT a.disabled AND u.id = userid;
	IF NOT FOUND OR query_result.chappassword IS NULL THEN
		RETURN NEXT reject_attr;
		RETURN;
	END IF;
	
	-- check if the endpoint IP address matches
	IF query_result.framedip IS NOT NULL THEN
		IF NOT (query_result.framedip >>= framed_ip) THEN
			RETURN NEXT reject_attr;
			RETURN;
		END IF;
	END IF;

	-- check the list of aliases being registered, if it is present
	IF h323ivrout IS NOT NULL THEN
		aliasnum := 1;
		LOOP
			rrqalias := split_part(h323ivrout, '','', aliasnum);
			EXIT WHEN length(rrqalias) = 0;
			aliasnum := aliasnum + 1;
			IF NOT rrqalias = query_result.h323id AND NOT rrqalias ~ query_result.allowedaliases THEN
				RETURN NEXT reject_attr;
				RETURN;
			END IF;
		END LOOP;			
	END IF;
	
	-- return User-Password check avp
	check_attr.id := 0;
	check_attr.attrname := ''User-Password'';
	check_attr.attrvalue := query_result.chappassword;
	check_attr.attrop := ''=='';
	RETURN NEXT check_attr;
	
	RETURN;	
END;
' LANGUAGE 'plpgsql' CALLED ON NULL INPUT SECURITY INVOKER;

-- build a list of RADIUS reply attribute-value pairs for endpoint registration request
-- $1 - User-Name
-- $2 - Framed-IP-Address
-- $3 - h323-ivr-in
CREATE OR REPLACE FUNCTION radius_get_reply_rrq_attrs(TEXT, INET, TEXT)
	RETURNS SETOF voipradattr AS
'
DECLARE
	framed_ip ALIAS FOR $2;
	username TEXT;
	h323ivrout ALIAS FOR $3;
	rcode_attr voipradattr%ROWTYPE;
	reply_attr voipradattr%ROWTYPE;
	attr_num INT := 1;
	userid INT;
	query_result RECORD;
	aliasnum INT;
	rrqalias TEXT;
BEGIN
	-- prepare h323-return-code avp, as it is referenced very often
	rcode_attr.id := attr_num;
	rcode_attr.attrname := ''h323-return-code'';
	rcode_attr.attrvalue := ''h323-return-code='';
	rcode_attr.attrop := ''='';
	attr_num := attr_num + 1;
	
	-- check input parameters
	IF $1 IS NULL OR $2 IS NULL THEN
		rcode_attr.attrvalue := rcode_attr.attrvalue || ''11'';
		RETURN NEXT rcode_attr;
		RETURN;
	END IF;
	
	-- remove RADIUS escapes
	username := radius_xlat($1);

	userid := match_user(username, framed_ip);
	IF userid IS NULL THEN
		rcode_attr.attrvalue := rcode_attr.attrvalue || ''1'';
		RETURN NEXT rcode_attr;
		RETURN;
	END IF;
	
	-- get user information
	SELECT INTO query_result h323id, balance, currencysym, allowedaliases, 
			assignaliases, framedip 
		FROM voipuser u JOIN voipaccount a ON u.accountid = a.id
		WHERE a.closed IS NULL AND NOT a.disabled AND u.id = userid;
	IF NOT FOUND OR query_result.balance IS NULL THEN
		rcode_attr.attrvalue := rcode_attr.attrvalue || ''1'';
		RETURN NEXT rcode_attr;
		RETURN;
	END IF;
	
	-- check if the endpoint IP address matches
	IF query_result.framedip IS NOT NULL THEN
		IF NOT (query_result.framedip >>= framed_ip) THEN
			rcode_attr.attrvalue := rcode_attr.attrvalue || ''7'';
			RETURN NEXT rcode_attr;
			RETURN;
		END IF;
	END IF;
	
	-- check the list of aliases being registered, if it is present
	IF h323ivrout IS NOT NULL THEN
		aliasnum := 1;
		LOOP
			rrqalias := split_part(h323ivrout, '','', aliasnum);
			EXIT WHEN length(rrqalias) = 0;
			aliasnum := aliasnum + 1;
			IF NOT rrqalias = query_result.h323id AND NOT rrqalias ~ query_result.allowedaliases THEN
				rcode_attr.attrvalue := rcode_attr.attrvalue || ''7'';
				RETURN NEXT rcode_attr;
				RETURN;
			END IF;
		END LOOP;			
	END IF;

	rcode_attr.attrvalue := rcode_attr.attrvalue || ''0'';
	RETURN NEXT rcode_attr;
	
	reply_attr.id := attr_num;
	reply_attr.attrname := ''h323-credit-amount'';
	reply_attr.attrvalue := ''h323-credit-amount='' 
		|| to_char(query_result.balance,''FM9999990.00'');
	reply_attr.attrop := ''='';
	RETURN NEXT reply_attr;
	attr_num := attr_num + 1;
	
	reply_attr.id := attr_num;
	reply_attr.attrname := ''h323-currency'';
	reply_attr.attrvalue := ''h323-currency='' || query_result.currencysym;
	reply_attr.attrop := ''='';
	RETURN NEXT reply_attr;
	attr_num := attr_num + 1;
	
	reply_attr.id := attr_num;
	reply_attr.attrname := ''h323-billing-model'';
	reply_attr.attrvalue := ''h323-billing-model=2'';
	reply_attr.attrop := ''='';
	RETURN NEXT reply_attr;
	attr_num := attr_num + 1;

	IF length(query_result.assignaliases) > 0 THEN
		reply_attr.id := attr_num;
		reply_attr.attrname := ''Cisco-AVPair'';
		reply_attr.attrvalue := ''h323-ivr-in=terminal-alias:'' 
			|| query_result.assignaliases || '';'';
		reply_attr.attrop := ''='';
		RETURN NEXT reply_attr;
		attr_num := attr_num + 1;
	END IF;
	
	RETURN;	
END;
' LANGUAGE 'plpgsql' CALLED ON NULL INPUT SECURITY INVOKER;

-- build a list of RADIUS check attribute-value pairs for endpoint call admission request
-- $1 - User-Name
-- $2 - Framed-IP-Address
-- $3 - TRUE - the call is being answered, FALSE - the call is originated
-- $4 - calling station id
-- $5 - called station id
CREATE OR REPLACE FUNCTION radius_get_check_arq_attrs(TEXT, INET, BOOLEAN, TEXT, TEXT)
	RETURNS SETOF voipradattr AS
'
DECLARE
	framed_ip ALIAS FOR $2;
	answer_call ALIAS FOR $3;
	username TEXT;
	calling_station_id TEXT;
	called_station_id TEXT;
	reject_attr voipradattr%ROWTYPE;
	check_attr voipradattr%ROWTYPE;
	query_result RECORD;
	trf voiptariff%ROWTYPE;
	userid INT;
BEGIN
	RAISE LOG ''sqlbill: ARQ(username: %; IP: %; answer: %; calling: %; called: %)'', $1, $2, $3, $4, $5;
	
	-- prepare Auth-Type := Reject avp, as it is referenced very often
	reject_attr.id := 0;
	reject_attr.attrname := ''Auth-Type'';
	reject_attr.attrvalue := ''Reject'';
	reject_attr.attrop := '':='';
	
	-- check input arguments
	IF $1 IS NULL OR $2 IS NULL OR $3 IS NULL OR $4 IS NULL OR $5 IS NULL THEN
		RETURN NEXT reject_attr;
		RETURN;
	END IF;
	
	-- remove RADIUS escapes
	username := radius_xlat($1);
	calling_station_id := radius_xlat($4);
	called_station_id := radius_xlat($5);
	
	userid := match_user(username, framed_ip);
	IF userid IS NULL THEN
		RETURN NEXT reject_attr;
		RETURN;
	END IF;
	
	-- get user information
	SELECT INTO query_result a.id AS accid, balance, balancelimit, 
			currencysym, chappassword, allowedaliases, framedip 
		FROM voipuser u JOIN voipaccount a ON u.accountid = a.id 
		WHERE a.closed IS NULL AND NOT a.disabled AND u.id = userid;
	IF NOT FOUND OR query_result.balance IS NULL THEN
		RETURN NEXT reject_attr;
		RETURN;