1 rlm_sqlcounter installation and running guide
2 by Ram Narula ram@princess1.net
3 Internet for Education (Thailand)
5 Make sure to have radiusd running properly under sql
6 and there must be a "sql" entry under accounting{ } section
11 The server has an example "dailycounter" in radiusd.conf. It can be
12 used for initial testing. Other examples are given below.
14 ---------------------------------------------------------------------
16 sqlcounter noresetcounter {
17 counter-name = Max-All-Session-Time
18 check-name = Max-All-Session
19 reply-name = Session-Timeout
24 query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}'"
29 # This is used to limit users per day, e.g. 3 hours/day
30 sqlcounter dailycounter {
31 counter-name = Daily-Session-Time
32 check-name = Max-Daily-Session
33 reply-name = Session-Timeout
38 query = "SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
43 # This is used to limit users per month, e.g. 10 hours/month
44 sqlcounter monthlycounter {
45 counter-name = Monthly-Session-Time
46 check-name = Max-Monthly-Session
47 reply-name = Session-Timeout
52 query = "SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
55 ----------------------------------------------------------------------
57 The respective queries for postgresql are:
59 query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}'"
60 query = "SELECT SUM(AcctSessionTime - GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'"
61 query = "SELECT SUM(AcctSessionTime - GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'"
63 If you are running postgres 7.x, you may not have a GREATER function.
67 CREATE OR REPLACE FUNCTION "greater"(integer, integer) RETURNS integer AS '
90 [2] Add the appropriate module configuration (as above) to radiusd.conf,
91 in the "modules" section.
93 [3] Make sure to have the sqlcounter names under authorize section
97 ...some entries here...
98 ...some entries here...
99 ...some entries here...
100 ...some entries here...
102 # You probably only want only one of these
106 ... other entries here ...
109 You can make your own names and directives for resetting the counter
110 by reading the sample sqlcounter configuration in
116 Add sqlcounter names to be used into radcheck or radgroupcheck
117 table appropriately for sql. For users file just follow the
120 Note: The users in the example below must be able to login
121 normally as the example will only show how to apply sqlcounter
125 [1] username test0001 have total time limit of 15 hours
126 (user can login as many times as needed but can be online for
127 total time of 15 hours which is 54000 seconds)
128 If using normal users file authenication the entry can look like:
130 test0001 Max-All-Session := 54000, User-Password == "blah"
131 Service-Type = Framed-User,
132 Framed-Protocol = PPP
134 or for sql make sure to have Max-All-Session entry under either
135 radcheck or radgroup check table:
136 > INSERT into radcheck VALUES ('','test0001','Max-All-Session','54000',':=');
138 [2] username test0002 have total time limit of 3 hours a day
140 test0002 Max-Daily-Session := 10800, User-Password == "blah"
141 Service-Type = Framed-User,
142 Framed-Protocol = PPP
144 > INSERT into radcheck VALUES ('','test0002','Max-Daily-Session','10800',':=');
147 [3] username test0003 have total time limit of 90 hours a month
149 test0003 Max-Monthly-Session := 324000, User-Password == "blah"
150 Service-Type = Framed-User,
151 Framed-Protocol = PPP
153 > INSERT into radcheck VALUES ('','test0003','Max-Monthly-Session','10800',':=');
156 Note that Max-All-Session, Max-Daily-Session and Max-Monthly-Session are
157 defined in sqlcounter.conf
160 Accounting must be done via sql or this will not work.