summaryrefslogtreecommitdiffstats
path: root/doc/modules/rlm_sqlcounter
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-28 09:49:46 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-28 09:49:46 +0000
commit50b37d4a27d3295a29afca2286f1a5a086142cec (patch)
tree9212f763934ee090ef72d823f559f52ce387f268 /doc/modules/rlm_sqlcounter
parentInitial commit. (diff)
downloadfreeradius-upstream.tar.xz
freeradius-upstream.zip
Adding upstream version 3.2.1+dfsg.upstream/3.2.1+dfsgupstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/modules/rlm_sqlcounter')
-rw-r--r--doc/modules/rlm_sqlcounter182
1 files changed, 182 insertions, 0 deletions
diff --git a/doc/modules/rlm_sqlcounter b/doc/modules/rlm_sqlcounter
new file mode 100644
index 0000000..54ad170
--- /dev/null
+++ b/doc/modules/rlm_sqlcounter
@@ -0,0 +1,182 @@
+rlm_sqlcounter installation and running guide
+by Ram Narula ram@princess1.net
+Internet for Education (Thailand)
+
+*) Pre-requisites:
+Make sure to have configured radiusd with rlm_sqlcounter
+installed
+
+> make clean
+> ./configure --with-experimental-modules
+> make
+> make install
+
+Make sure to have radiusd running properly under sql
+and there must be a "sql" entry under accounting{ } section
+of radiusd.conf
+
+*) Configuration:
+
+[1] Create a text file called sqlcounter.conf in the same
+directory where radiusd.conf resides (usually /usr/local/etc/raddb)
+with the following content (for mysql):
+
+#-----#
+sqlcounter noresetcounter {
+ sql_module_instance = sqlcca3
+ counter_name = Max-All-Session-Time
+ check_name = Max-All-Session
+ reply_name = Session-Timeout
+ key = User-Name
+ reset = never
+
+ query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}'"
+
+ }
+
+
+sqlcounter dailycounter {
+ sql_module_instance = sqlcca3
+ driver = "rlm_sqlcounter"
+ counter_name = Daily-Session-Time
+ check_name = Max-Daily-Session
+ reply_name = Session-Timeout
+ 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'"
+
+ }
+
+sqlcounter monthlycounter {
+ sql_module_instance = sqlcca3
+ counter_name = Monthly-Session-Time
+ check_name = Max-Monthly-Session
+ reply_name = Session-Timeout
+ 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 lines for postgresql are:
+
+query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}'"
+query = "SELECT SUM(AcctSessionTime - GREATEST((%b - EXTRACT(epoch FROM AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND EXTRACT(epoch FROM AcctStartTime) + AcctSessionTime > '%b'"
+query = "SELECT SUM(AcctSessionTime - GREATEST((%b - EXTRACT(epoch FROM AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND EXTRACT(epoch FROM AcctStartTime) + AcctSessionTime > '%b'"
+
+If you are running postgres 7.x, you may not have a GREATEST 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] Include the above file to radiusd.conf by adding a line in
+modules{ } section
+
+modules {
+
+$INCLUDE ${confdir}/sqlcounter.conf
+
+...some other entries here...
+
+[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...
+
+noresetcounter
+dailycounter
+monthlycounter
+}
+
+noresetcounter: the counter that never resets, can be used
+for real session-time cumulation
+
+dailycounter: the counter that resets everyday, can be used
+for limiting daily access time (eg. 3 hours a day)
+
+monthlycounter: the counter that resets monthly, can be used for
+limiting monthly access time (eg. 50 hours per month)
+
+You can make your own names and directives for resetting the counter
+by reading the sample sqlcounter configuration in
+raddb/experimental.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 authentication 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
+definied in sqlcounter.conf
+
+VERY IMPORTANT:
+Accounting must be done via sql or this will not work.