rlm_sqlcounter installation and running guide
by Ram Narula ram@princess1.net
Internet for Education (Thailand)
Make sure to have radiusd running properly under sql
and there must be a "sql" entry under accounting{ } section
of radiusd.conf
*) Configuration:
The server has an example "dailycounter" in radiusd.conf. It can be
used for initial testing. Other examples are given below.
---------------------------------------------------------------------
# Never reset
sqlcounter noresetcounter {
counter-name = Max-All-Session-Time
check-name = Max-All-Session
reply-name = Session-Timeout
sqlmod-inst = sql
key = User-Name
reset = never
query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}'"
}
# Reset daily
# This is used to limit users per day, e.g. 3 hours/day
sqlcounter dailycounter {
counter-name = Daily-Session-Time
check-name = Max-Daily-Session
reply-name = Session-Timeout
sqlmod-inst = sql
key = User-Name
reset = daily
query = "SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
}
# Reset monthly
# This is used to limit users per month, e.g. 10 hours/month
sqlcounter monthlycounter {
counter-name = Monthly-Session-Time
check-name = Max-Monthly-Session
reply-name = Session-Timeout
sqlmod-inst = sql
key = User-Name
reset = monthly
query = "SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
}
----------------------------------------------------------------------
The respective queries for postgresql are:
query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}'"
query = "SELECT SUM(AcctSessionTime - GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'"
query = "SELECT SUM(AcctSessionTime - GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'"
If you are running postgres 7.x, you may not have a GREATER function.
An example of one is:
CREATE OR REPLACE FUNCTION "greater"(integer, integer) RETURNS integer AS '
DECLARE
res INTEGER;
one INTEGER := 0;
two INTEGER := 0;
BEGIN
one = $1;
two = $2;
IF one IS NULL THEN
one = 0;
END IF;
IF two IS NULL THEN
two = 0;
END IF;
IF one > two THEN
res := one;
ELSE
res := two;
END IF;
RETURN res;
END;
' LANGUAGE 'plpgsql';
[2] Add the appropriate module configuration (as above) to radiusd.conf,
in the "modules" section.
[3] Make sure to have the sqlcounter names under authorize section
like the followings:
authorize {
...some entries here...
...some entries here...
...some entries here...
...some entries here...
# You probably only want only one of these
noresetcounter
dailycounter
monthlycounter
... other entries here ...
}
You can make your own names and directives for resetting the counter
by reading the sample sqlcounter configuration in
raddb/radiusd.conf
*) Implementation:
Add sqlcounter names to be used into radcheck or radgroupcheck
table appropriately for sql. For users file just follow the
example below.
Note: The users in the example below must be able to login
normally as the example will only show how to apply sqlcounter
counters.
Scenarios
[1] username test0001 have total time limit of 15 hours
(user can login as many times as needed but can be online for
total time of 15 hours which is 54000 seconds)
If using normal users file authenication the entry can look like:
test0001 Max-All-Session := 54000, User-Password == "blah"
Service-Type = Framed-User,
Framed-Protocol = PPP
or for sql make sure to have Max-All-Session entry under either
radcheck or radgroup check table:
> INSERT into radcheck VALUES ('','test0001','Max-All-Session','54000',':=');
[2] username test0002 have total time limit of 3 hours a day
test0002 Max-Daily-Session := 10800, User-Password == "blah"
Service-Type = Framed-User,
Framed-Protocol = PPP
or in sql:
> INSERT into radcheck VALUES ('','test0002','Max-Daily-Session','10800',':=');
[3] username test0003 have total time limit of 90 hours a month
test0003 Max-Monthly-Session := 324000, User-Password == "blah"
Service-Type = Framed-User,
Framed-Protocol = PPP
in sql:
> INSERT into radcheck VALUES ('','test0003','Max-Monthly-Session','10800',':=');
Note that Max-All-Session, Max-Daily-Session and Max-Monthly-Session are
defined in sqlcounter.conf
VERY IMPORTANT:
Accounting must be done via sql or this will not work.