Re: freeradius & postgreSQL - stored procedures

[Graeme Lee]

Manda Costin wrote:
Pe 27 Jan 2005, la 03:13, Graeme Lee <[EMAIL PROTECTED]> a scris:
 

Siderite wrote:
   

Hello... I am trying to make freeradius authenticate some access
packets using the output of SQL stored procedures (that eventually would
do the billing as well). Can it be done? And if yes, how?
 thank you

     

Give an example of what you're trying to do. 
   

 Well, I was thinking of something like putting in the radcheck table the 
result of a pgsql procedure. like:
username=USER,attribute=%{pgsql_stored procedure output},op='>',value=0
 Can it be done?
 

I'm going to say yes, even though I'm unclear of exactly what you are 
trying to do.  Here's my get_simul_sessions() function for you to have a 
squiz at.

Some things to note...
whos_on is a VIEW
session_log is a log of the current number of ports in use
This function allows me to allocate say 30 ports to a school 
(arbitrarily called the 'owner'), and users within the school can get 2 
lines (allowing a max of 15 users with 2 lines, 10 with 2, 10 with 1) etc.
This was my first attempt just to get something working.  I'm sure it 
needs more work.


-- Determine if a user is logged on already, and if so, if they are 
allowed any further sessions
-- Returns 0 for permission, 1 to disallow
-- Simultaneous-Use MUST be set to 1 for the user's GROUP in 
radgroupcheck to function
-- if no Simultaneous-Use for the user's group is defined, the radius 
server doesn't check
-- if Simultaneous-Use is set highter than 1, then it won't work correctly

CREATE OR REPLACE FUNCTION get_simul_sessions(varchar) RETURNS integer AS '
       DECLARE
               _user ALIAS for $1;
               user_results record;
               current_user_sessions integer;
               current_group_sessions integer;
               current_owner_sessions integer;
               max_user_sessions integer;
               max_group_sessions integer;
               max_owner_sessions integer;
       BEGIN
               SELECT INTO user_results usergroup.username, 
usergroup.groupname, owneruser.ownername
                               WHERE usergroup.username = _user AND 
owneruser.username = _user;
               IF NOT FOUND THEN
                       RAISE EXCEPTION ''User % does not exist'', _user;
               END IF;
               SELECT count(whos_on.username) INTO 
current_user_sessions FROM whos_on where username = _user;
               SELECT count(whos_on.username) INTO 
current_owner_sessions FROM whos_on, owneruser
                       WHERE whos_on.username = owneruser.username AND 
owneruser.ownername = user_results.ownername;

               SELECT INTO max_owner_sessions value FROM radownercheck 
WHERE attribute = ''Simultaneous-Use''
                       AND ownername = user_results.ownername;
               IF NOT FOUND THEN
                       max_owner_sessions := 0;
               END IF;

               SELECT INTO max_user_sessions value FROM radcheck WHERE 
attribute = ''Simultaneous-Use''
                       AND username = _user;
               IF NOT FOUND THEN
                       max_user_sessions := 1;
               END IF;

               IF current_owner_sessions >= max_owner_sessions AND
                       max_owner_sessions != 0 THEN RETURN 1;
               END IF;
               IF current_user_sessions >= max_user_sessions THEN
                       RETURN 1;
               END IF;
               INSERT INTO session_log (time, usercount) VALUES
                       (now(), (SELECT count(username) FROM whos_on) + 1);
               RETURN 0;
       END;
' LANGUAGE plpgsql;
- 
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html