Rlm sqlcounter

From FreeRADIUS Wiki

The rlm_sqlcounter enables a packet counter using SQL.

Make sure to have radiusd running properly under sql and there must be a "sql" entry under accounting{ } section of radiusd.conf

NB: the current version of this file is kept in the FreeRADIUS server source at src/modules/rlm_sqlcounter/

1 Configuration
2 Implementation
- 2.1 Scenarios

Configuration

Default configuration

NB: the current version of this file is kept in the FreeRADIUS server source at raddb/modules/sqlcounter

	#
	#  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
	#  counter 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"
	#
	sqlcounter dailycounter {
		counter-name = Daily-Session-Time
		check-name = Max-Daily-Session
		sqlmod-inst = sql
		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
		# For mysql:
		query = "SELECT SUM(AcctSessionTime - \
                 GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) \
                 FROM radacct WHERE UserName='%{%k}' AND \
                 UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"

		# For postgresql:
#		query = "SELECT SUM(AcctSessionTime - \
#                GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) \
#                FROM radacct WHERE UserName='%{%k}' AND \
#                AcctStartTime::ABSTIME::INT4 + 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
		# For mysql:
#		query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE \
#                UserName='%{%k}' AND AcctStartTime > FROM_UNIXTIME('%b')"

		# For postgresql:
#		query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE \
#                UserName='%{%k}' AND AND AcctStartTime::ABSTIME::INT4 > '%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
		# For mysql:
#		query = "SELECT SUM(AcctSessionTime) FROM radacct \
#                WHERE UserName='%{%k}' AND AcctStartTime BETWEEN \
#                FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"

		# For postgresql:
#		query = "SELECT SUM(AcctSessionTime) FROM radacct \
#                WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 \
#                BETWEEN '%b' AND '%e'"
	}

	sqlcounter monthlycounter {
		counter-name = Monthly-Session-Time
		check-name = Max-Monthly-Session
		sqlmod-inst = sql
		key = User-Name
		reset = monthly

		# 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
		# The same notes above about the differences between mysql
		# versus postgres queries apply here.
		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')"
	}

Attributes

check-name: This is the name of the radius attribute that describes the limit of the sqlcounter.

sqlmod-inst: The instance of the rlm_sql that you wish to use to query.

key: The key field in the sql lookup - this is usually the username.

reset: Specifies the frequency that the sqlcounter should be reset. Available options are: hourly, daily, weekly, monthly and never.

query: The sql expresion that is queried - often from the radacct table. The resulting value can then be compared with the value from check-name.

Example Setup

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

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

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

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'"

}

If you are using postgresql then the query lines would have to be replaced by the following:

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'"

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';

Include the above file to radiusd.conf by adding a line in modules{ } section

modules {

$INCLUDE ${confdir}/sqlcounter.conf

...some other entries here...

}

Make sure to have the sqlcounter names under authorize section like the followings:

authorize {

...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

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, Cleartext-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',':=');

username test0002 have total time limit of 3 hours a day

test0002 Max-Daily-Session := 10800, Cleartext-Password := "blah"

Service-Type = Framed-User,

Framed-Protocol = PPP

or in sql:

> INSERT into radcheck VALUES ('','test0002','Max-Daily-Session','10800',':=');

username test0003 have total time limit of 90 hours a month

test0003 Max-Monthly-Session := 324000, Cleartext-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 defined in sqlcounter.conf

VERY IMPORTANT Accounting must be done via sql or this will not work.

Retrieved from "http://wiki.freeradius.org/Rlm_sqlcounter"

This page has been accessed 22,073 times. This page was last modified on 11 June 2010, at 21:54.

Rlm sqlcounter

Contents

Configuration

Default configuration

Attributes

Example Setup

Implementation

Scenarios

Find

Browse

Edit

This page

Context

My pages

Special pages

FreeRADIUS Wiki	Main Page \| About \| Help \| FAQ \| Special pages \| Log in
	Printable version \| Disclaimers \| Privacy policy