1 rlm_sqlcounter installation and running guide
2 by Ram Narula ram@princess1.net
3 Internet for Education (Thailand)
6 Make sure to have configured radiusd with rlm_sqlcounter
10 > ./configure --with-experimental-modules
14 Make sure to have radiusd running properly under sql
15 and there must be a "sql" entry under accounting{ } section
20 [1] Create a text file called sqlcounter.conf in the same
21 directory where radiusd.conf resides (usually /usr/local/etc/raddb)
22 with the following content (for mysql):
25 sqlcounter noresetcounter {
26 counter-name = Max-All-Session-Time
27 check-name = Max-All-Session
28 reply-name = Session-Timeout
33 query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}'"
38 sqlcounter dailycounter {
39 driver = "rlm_sqlcounter"
40 counter-name = Daily-Session-Time
41 check-name = Max-Daily-Session
42 reply-name = Session-Timeout
47 query = "SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
51 sqlcounter monthlycounter {
52 counter-name = Monthly-Session-Time
53 check-name = Max-Monthly-Session
54 reply-name = Session-Timeout
59 query = "SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
65 The respective lines for postgresql are:
67 query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}'"
68 query = "SELECT SUM(AcctSessionTime - GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'"
69 query = "SELECT SUM(AcctSessionTime - GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'"
71 If you are running postgres 7.x, you may not have a GREATER function.
75 CREATE OR REPLACE FUNCTION "greater"(integer, integer) RETURNS integer AS '
98 [2] Include the above file to radiusd.conf by adding a line in
103 $INCLUDE ${confdir}/sqlcounter.conf
105 ...some other entries here...
107 [3] Make sure to have the sqlcounter names under authorize section
111 ...some entries here...
112 ...some entries here...
113 ...some entries here...
114 ...some entries here...
121 noresetcounter: the counter that never resets, can be used
122 for real session-time cumulation
124 dailycounter: the counter that resets everyday, can be used
125 for limiting daily access time (eg. 3 hours a day)
127 monthlycounter: the counter that resets monthly, can be used for
128 limiting monthly access time (eg. 50 hours per month)
130 You can make your own names and directives for resetting the counter
131 by reading the sample sqlcounter configuration in
132 raddb/experimental.conf
138 Add sqlcounter names to be used into radcheck or radgroupcheck
139 table appropriately for sql. For users file just follow the
142 Note: The users in the example below must be able to login
143 normally as the example will only show how to apply sqlcounter
147 [1] username test0001 have total time limit of 15 hours
148 (user can login as many times as needed but can be online for
149 total time of 15 hours which is 54000 seconds)
150 If using normal users file authenication the entry can look like:
152 test0001 Max-All-Session := 54000, User-Password == "blah"
153 Service-Type = Framed-User,
154 Framed-Protocol = PPP
156 or for sql make sure to have Max-All-Session entry under either
157 radcheck or radgroup check table:
158 > INSERT into radcheck VALUES ('','test0001','Max-All-Session','54000',':=');
160 [2] username test0002 have total time limit of 3 hours a day
162 test0002 Max-Daily-Session := 10800, User-Password == "blah"
163 Service-Type = Framed-User,
164 Framed-Protocol = PPP
166 > INSERT into radcheck VALUES ('','test0002','Max-Daily-Session','10800',':=');
169 [3] username test0003 have total time limit of 90 hours a month
171 test0003 Max-Monthly-Session := 324000, User-Password == "blah"
172 Service-Type = Framed-User,
173 Framed-Protocol = PPP
175 > INSERT into radcheck VALUES ('','test0003','Max-Monthly-Session','10800',':=');
178 Note that Max-All-Session, Max-Daily-Session and Max-Monthly-Session are
179 definied in sqlcounter.conf
182 Accounting must be done via sql or this will not work.