- # This module is an SQL enabled version of the counter module.
- #
- # Rather than maintaining seperate (GDBM) databases of accounting info
- # for each counter, this module uses the data stored in the raddacct
- # table by the sql modules. This module NEVER does any database
- # INSERTs or UPDATEs. It is totally dependent on the SQL module
- # to process Accounting packets.
- #
- # The 'sqlmod_inst' parameter holds the instance of the sql module to use
- # when querying the SQL database. Normally it is just "sql".
- # If you define more and one SQL module instance
- # (usually for failover situations), you can specify which module
- # has access to the Accounting Data (radacct table).
- #
- # The 'reset' parameter defines when the counters are all reset to
- # zero. It can be hourly, daily, weekly, monthly or never.
- # It can also be user defined. It should be of the form:
- # num[hdwm] where:
- # h: hours, d: days, w: weeks, m: months
- # If the letter is ommited days will be assumed. In example:
- # reset = 10h (reset every 10 hours)
- # reset = 12 (reset every 12 days)
- #
- # The 'key' parameter specifies the unique identifier for the counters
- # records (usually 'User-Name').
- #
- # The 'query' parameter specifies the SQL query used to get the
- # current Counter value from the database. There are 3 parameters
- # that can be used in the query:
- # %k 'key' parameter
- # %b unix time value of beginning of reset period
- # %e unix time value of end of reset period
- #
- # The 'check-name' parameter is the name of the 'check' attribute to use to access
- # the counter in the 'users' file or SQL radcheck or radcheckgroup
- # tables.
- #
- # DEFAULT Max-Daily-Session > 3600, Auth-Type = Reject
- # Reply-Message = "You've used up more than one hour today"
- #1
-
- sqlcounter dailycounter {
- counter-name = Daily-Session-Time
- check-name = Max-Daily-Session
- sqlmod-inst = sqlcca3
- key = User-Name
- reset = daily
-
- # This query properly handles calls that span from the previous reset period
- # into the current period but involves more work for the SQL server than those below
- query = "SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
-
- # This query ignores calls that started in a previous reset period and
- # continue into into this one. But it is a little easier on the SQL server
- # query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime > FROM_UNIXTIME('%b')"
-
- # This query is the same as above, but demonstrates an additional
- # counter parameter '%e' which is the timestamp for the end of the period
- # query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime BETWEEN FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"
- }
+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
- sqlcounter monthlycounter {
- counter-name = Monthly-Session-Time
- check-name = Max-Monthly-Session
- sqlmod-inst = sqlcca3
- key = User-Name
- reset = monthly
+Make sure to have radiusd running properly under sql
+and there must be a "sql" entry under accounting{ } section
+of radiusd.conf
- # This query properly handles calls that span from the previous reset period
- # into the current period but involves more work for the SQL server than those below
- query = "SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
+*) Configuration:
- # This query ignores calls that started in a previous reset period and
- # continue into into this one. But it is a little easier on the SQL server
- # query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime > FROM_UNIXTIME('%b')"
+[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 {
+ 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}'"
+
+ }
+
+
+sqlcounter dailycounter {
+ driver = "rlm_sqlcounter"
+ counter-name = Daily-Session-Time
+ check-name = Max-Daily-Session
+ reply-name = Session-Timeout
+ sqlmod-inst = sqlcca3
+ 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 {
+ counter-name = Monthly-Session-Time
+ check-name = Max-Monthly-Session
+ reply-name = Session-Timeout
+ sqlmod-inst = sqlcca3
+ 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'"
- # This query is the same as above, but demonstrates an additional
- # counter parameter '%e' which is the timestamp for the end of the period
- # query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime BETWEEN FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"
}
+#-----#
+
+The respective lines 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] 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:
-# Authorization. First preprocess (hints and huntgroups files),
-# then realms, and finally look in the "users" file.
-# The order of the realm modules will determine the order that
-# we try to find a matching realm.
-# Make *sure* that 'preprocess' comes before any realm if you
-# need to setup hints for the remote radius server
authorize {
- preprocess
-# attr_filter
-# eap
- suffix
- files
- group {
- sql1 {
- fail = 1
- notfound = return
- noop = 2
- ok = return
- updated = 3
- reject = return
- userlock = 4
- invalid = 5
- handled = 6
- }
- sql2 {
- fail = 1
- notfound = return
- noop = 2
- ok = return
- updated = 3
- reject = return
- userlock = 4
- invalid = 5
- handled = 6
- }
- }
- dailycounter
- monthlycounter
-# mschap
+...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
-# Authentication.
-#
-# This section lists which modules are available for authentication.
-# Note that it does NOT mean 'try each module in order'. It means
-# that you have to have a module from the 'authorize' section add
-# a configuration attribute 'Auth-Type := FOO'. That authentication type
-# is then used to pick the apropriate module from the list below.
-authenticate {
-# pam
-# unix
-# ldap
- mschap
-# pap
-# eap
-}
+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)
-# Pre-accounting. Look for proxy realm in order of realms, then
-# acct_users file, then preprocess (hints file).
-preacct {
- suffix
- files
- preprocess
-}
+You can make your own names and directives for resetting the counter
+by reading the sample sqlcounter configuration in
+raddb/experimental.conf
-# Accounting. Log to detail file, and to the radwtmp file, and maintain
-# radutmp.
-accounting {
- acct_unique
- detail
- sqlcca1
-# counter
-# unix
- radutmp
-# sradutmp
-}
+*) 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
+definied in sqlcounter.conf
+
+VERY IMPORTANT:
+Accounting must be done via sql or this will not work.
-# Session database, used for checking Simultaneous-Use. The radutmp module
-# handles this
-session {
- radutmp
-}
projects / freeradius.git / blobdiff