patch for sqlcounter, please test!

[Markus Krause]

hi list!

part of a radius-related project is to allow access to our guests and visitors
at our institute to parts of our network, of course only with username and
password, and do some logging. the idea is that guests get an account and a
password which they can use for a period of one to seven days (according to
setup). to achieve this i am using the sqlcounter module using the following
query:

query = "SELECT TO_DAYS(NOW()) - MIN(TO_DAYS(AcctStartTime)) FROM radacct WHERE
UserName = '%{%k}' LIMIT 1";

this actually works very well, a user logs in and is allowed to access to the
network until the date changes e.g. the second time if he is allowed access for
two days. but as i am saving the days as "days" in the mysql database, i run
into trouble with "Session-Timeout" because rlm_sqlcounter assumes that the
query returns seconds and the user gets a session timeout of the remaining days
as seconds (a value between 1 and 7!). putting the day limit as seconds into
the database does (in my case/opinion) not make any sense here.

as i posted a question about this some two months ago to the freeradius users
list unfortunately nobody had a solution available, but i can remember several
answers (to the list an by private mail) that this feature would be nice!

so i finaly tried some coding today and now here is my proposal:

add an optional parameter 'timeunit' to sqlcounter.conf that represents the time
unit used in the query and the check value (in the sql db), my config then
reads:

+++++ /etc/raddb/sqlcounter.conf
sqlcounter shorttermaccounts {
        counter-name = Short-Term-Account
        check-name = Max-Days-Passed
        sqlmod-inst = sql
        key = User-Name
        reset = never
        timeunit = days
        query = "SELECT TO_DAYS(NOW()) - MIN(TO_DAYS(AcctStartTime)) FROM
radacct WHERE UserName = '%{%k}' LIMIT 1";
}
----- /etc/raddb/sqlcounter.conf

in rlm_sqlcounter.c i added some lines which (if 'timeunit' is set) multiply the
value of 'res' (i assume this is the remaining time of allowd success) by an
appropriate value to get seconds. in this case Session-Timeout is returned
"correctly".

possible values for timeunit are minutes, hours, days, and weeks (for now).

a patch to version 1.0.5 can be found as attachment to this email.

i ran several tests and it is also working with different queries (like the ones
in the doc), but i would appreciate others to do some testing if it really
works.

but as it seems that nobody has made a proposal/code for this yet, what do think
about it?

i put this also on bugs.freeradius.org but could not find the component
"rlm_sqlcounter" so i put it in "modules", i hope i did not mess up things!

hoping for many feedback! ;-)

with best regards,
  markus

--
Markus Krause                           email: krause at biochem.mpg.de
Computing Center                        Tel.: 089 - 89 40 85 99
Group Lottspeich / Proteomics           Fax.: 089 - 89 40 85 98

---------------------------------------------------------------------
     This message was sent using https://webmail.biochem.mpg.de
If you encounter any problems please report to rz-linux at biochem.mpg.de
-------------- next part --------------
A non-text attachment was scrubbed...
Name: rlm_sqlcounter_patch_for_timeunit.diff
Type: text/diff
Size: 1903 bytes
Desc: not available
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20051223/bc6fa1bf/attachment.bin>