Fix syntax error in new radippool table

[freeradius.git] / doc / rlm_sqlcounter

diff --git a/doc/rlm_sqlcounter b/doc/rlm_sqlcounter
index f778927..fd8b74f 100644 (file)
--- a/doc/rlm_sqlcounter
+++ b/doc/rlm_sqlcounter
@@ -2,29 +2,21 @@ 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:
+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
@@ -33,12 +25,13 @@ sqlcounter noresetcounter {
 
         }
 
-
+# Reset daily
+# This is used to limit users per day, e.g. 3 hours/day
 sqlcounter dailycounter {
-                driver = "rlm_sqlcounter"
                 counter-name = Daily-Session-Time
                 check-name = Max-Daily-Session
-                sqlmod-inst = sqlcca3
+               reply-name = Session-Timeout
+                sqlmod-inst = sql
                 key = User-Name
                 reset = daily
 
@@ -46,27 +39,56 @@ sqlcounter dailycounter {
 
         }
 
+# 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
-                sqlmod-inst = sqlcca3
+               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'"
 
        }
-
-#-----#
-
-[2] Include the above file to radiusd.conf by adding a line in
-modules{ } section
-
-modules {
-
-$INCLUDE  ${confdir}/sqlcounter.conf
-
-...some other entries here...
+----------------------------------------------------------------------
+
+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:
@@ -77,24 +99,16 @@ authorize {
 ...some entries here...
 ...some entries here...
 
-noresetcounter
-dailycounter
-monthlycounter
+# You probably only want only one of these
+       noresetcounter
+       dailycounter
+       monthlycounter
+... other entries here ...
 }
 
-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
-
+raddb/radiusd.conf
 
 
 *) Implementation:
@@ -140,7 +154,7 @@ in sql:
 
 
 Note that Max-All-Session, Max-Daily-Session and Max-Monthly-Session are
-definied in sqlcounter.conf
+defined in sqlcounter.conf
 
 VERY IMPORTANT:
 Accounting must be done via sql or this will not work.