SQL Counter Module

The sqlcounter module provides a general framework to allow access based on accumulated usage of a resource, such as total time online in a given period, total data transferred in a given period, etc. This is very useful in a Prepaid Service situation, where a user has paid for a finite amount of usage and should not be allowed to use more than that service. Collection, monitoring, and replenishment of prepaid services are beyond the scope of this module.

Rather than maintaining separate (GDBM) databases of accounting info for each counter, this module uses the data stored in the radacct 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.

Syntax

Configuration explanation.

sql_module_instance: 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).

reset: Defines when the counters are all reset to zero.

It can be hourly, daily, weekly, monthly or never.

It can also be user defined as the form: num[hdwm]

where:

Parameter Description

Parameter	Description
`h`	hours
`d`	days
`w`	weeks
`m`	months

h

hours

d

days

w

weeks

m

months

If the latter is omitted days will be assumed.

In example:

reset = 10h (reset every 10 hours)
reset = 12 (reset every 12 days)

key

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.

reset_period_start_name: The name of the attribute which is used to store the time that the current reset period started.

The default is &control.${.:instance}-Reset-Start

Note because we are counting seconds, this attribute is of type uint64, and not date.

reset_period_end_name: The name of the attribute which is used to store the time that the current reset period will end.

The default is &control.${.:instance}-Reset-End

Note because we are counting seconds, this attribute is of type uint64, and not date.

counter_name: Name of the attribute to add to the control list with the current value of the counter.

After the sqlcounter module has run, the counter attribute will exist.

check_name: Name of the attribute containing the limit to compare the counter to.
reply_name: Name of the attribute to populate with the remaining session time. e.g. &reply.Session-Timeout. If the attribute already exists and contains a lower value, then it will not be updated.
reply_message_name: Name of the attribute into which a message should be placed if the limit has been exceeded for the counter.
auto_extend: If set to yes and the remaining session time goes past the time for the next counter reset, the value in the reply_name attribute will be set to the time to the next reset plus the value of the check_name attribute. This is most useful if the limit is a time based one, and, for example, Session-Timeout is the reply_name attribute. If there is sufficient allocation left for the session to get to the next counter period, the user will not have to re-authenticate before they have used their allocation for the next counter period.
utc: Use UTC for calculating the period start and end values.

Configuration Settings

Daily Counter

Monthly Counter

No Reset Counter

Expire on login

Set an account to expire T seconds after first login. Requires the Expire-After attribute to be set, in seconds.

You may need to edit raddb/dictionary to add the Expire-After attribute.

Default Configuration

#	sqlcounter <name> {
#	sql_module_instance = sql_foo
#	reset = 12h
#	key = "%{&Stripped-User-Name || &User-Name}"
#	if (&control.Daily-Session-Time > 3600) {
#		 &Reply-Message := "You've used up more than one hour today"
#		 reject
#	}
sqlcounter dailycounter {
	sql_module_instance = sql
	dialect = ${modules.sql.dialect}
#	reset_period_start_name = &control.${.:instance}-Reset-Start
#	reset_period_end_name = &control.${.:instance}-Reset-End
	counter_name = &control.Daily-Session-Time
	check_name = &control.Max-Daily-Session
	reply_name = &reply.Session-Timeout
	auto_extend = yes
	key = "%{&Stripped-User-Name || &User-Name}"
	reply_message_name = &Reply-Message
	reset = daily
	$INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf
}
sqlcounter monthlycounter {
	sql_module_instance = sql
	dialect = ${modules.sql.dialect}
	counter_name = &Monthly-Session-Time
	check_name = &control.Max-Monthly-Session
	reply_name = &reply.Session-Timeout
	auto_extend = yes
	key = &User-Name
	reset = monthly
	$INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf
}
sqlcounter noresetcounter {
	sql_module_instance = sql
	dialect = ${modules.sql.dialect}
	counter_name = &Max-All-Session-Time
	check_name = &control.Max-All-Session
	key = &User-Name
	reset = never
	$INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf
}
sqlcounter expire_on_login {
	sql_module_instance = sql
	dialect = ${modules.sql.dialect}
	counter_name = &Expire-After-Initial-Login
	check_name = &control.Expire-After
	key = &User-Name
	reset = never
	$INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf
}