I had a similar problem but a larger install (13-20K potential users) ... I did a few things: 1. setup wpa with freeRadius asking Active Directory (via Samba) about users -- Those windows people 2. created a sql table for cross reference to a "sponsor", which is a user from AD, to a guest account. The things I track in the guest users table are YMMV: who the sponsor was the random guest ID given guest users real email address the time and date created along with a duration 3. Made a web form to utilize step 2 and create the entries in radcheck. It verifies the sponsor by using the AD creds. 4. I created a script that runs every 15 mins -- cron is my hero -- or so and queries the guest users table for account that have expired... If an account has expired, and it the user is still in the radcheck I remove the password created by the web form from the radcheck table.... Here is where things get fancy, all guest users belong to a group that has a vlan tag for a reply item for guest access... this allows us to segment "guest" users from real users.... making it easy to only allow them to certain resources (like only access to the internet and not internal resources). This didn't require any patches to radius. Just some time and energy in some scripting (perl). I do alot of bounds checking and data sanitizing in the web form. Just thought you'd like to know how others are dealing with that problem. ----- Original Message ---- From: Markus Krause <krause@biochem.mpg.de> To: freeradius-devel@lists.freeradius.org Sent: Friday, December 16, 2005 10:07:35 AM Subject: proposal and patch for time unit in rlm_sqlcounter.c hello freeradius developers, let me introduce myself first so you know who is talking here ;-) my name is markus an i am currently working on a radius-related project at the max-planck-institute of biochemistry in martinsried/munich/germany. the aim of the project is to get more control over the network access of the ~900 users and >1200 devices. an important part of the project is to allow access to our guests and visitors 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". the code for this is (definitions and config items left out, i put them in the diff file created with 'diff -Naur' in the attachement): +++++ part of rlm_sqlcounter.c (approx. starting at line 616) /* * Check if check item > counter */ res=check_vp->lvalue - counter; if( data->timeunit != NULL ) { if(strcmp(data->timeunit, "minutes") == 0 ) { timemultiplier = 60; } else if(strcmp(data->timeunit, "hours") == 0 ) { timemultiplier = 3600; } else if(strcmp(data->timeunit, "days") == 0 ) { timemultiplier = 86400; } else if(strcmp(data->timeunit, "weeks") == 0 ) { timemultiplier = 604800; } else { radlog(L_ERR, "rlm_sqlcounter: Unknown value for timeuni t \"%s\" in sqlcounter.conf", data->timeunit); return -1; } } if( timemultiplier) res *= timemultiplier; if (res > 0) { ----- end part of rlm_sqlcounter.c i ran several tests and it is also working with different queries (like the ones in the doc) there surely are other and certainly better ways to achieve this (and i do not have an idea if this type of code (style, variable names etc.) does fit into the "philosophie" of freeradius) so any feedback is welcome!) but as it seems that nobody has made a proposal for this yet, what do think about it? as i got some mails that others may need this too what are the chances that something like this will be added to the freeradius code? hoping for many feedback! ;-) with best regards, markus -- Markus Krause email: krause@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@biochem.mpg.de -----Inline Attachment Follows----- --- rlm_sqlcounter-original.c 2005-12-16 15:05:54.725659800 +0100 +++ rlm_sqlcounter.c 2005-12-16 15:20:10.761522568 +0100 @@ -72,6 +72,7 @@ char *sqlmod_inst; /* instance of SQL module to use, usually just 'sql' */ char *query; /* SQL query to retrieve current session time */ char *reset; /* daily, weekly, monthly, never or user defined */ + char *timeunit; /* minutes, hours, days or weeks */ time_t reset_time; time_t last_reset; int key_attr; /* attribute number for key field */ @@ -94,6 +95,7 @@ { "sqlmod-inst", PW_TYPE_STRING_PTR, offsetof(rlm_sqlcounter_t,sqlmod_inst), NULL, NULL }, { "query", PW_TYPE_STRING_PTR, offsetof(rlm_sqlcounter_t,query), NULL, NULL }, { "reset", PW_TYPE_STRING_PTR, offsetof(rlm_sqlcounter_t,reset), NULL, NULL }, + { "timeunit", PW_TYPE_STRING_PTR, offsetof(rlm_sqlcounter_t,timeunit), NULL, NULL }, { NULL, -1, 0, NULL, NULL } }; @@ -544,6 +546,7 @@ int ret=RLM_MODULE_NOOP; int counter=0; int res=0; + int timemultiplier=1; DICT_ATTR *dattr; VALUE_PAIR *key_vp, *check_vp; VALUE_PAIR *reply_item; @@ -612,6 +615,27 @@ * Check if check item > counter */ res=check_vp->lvalue - counter; + + /* + * If timeunit is set in sqlcounter.conf set timemultiplier + */ + if( data->timeunit != NULL ) { + if(strcmp(data->timeunit, "minutes") == 0 ) { + timemultiplier = 60; + } else if(strcmp(data->timeunit, "hours") == 0 ) { + timemultiplier = 3600; + } else if(strcmp(data->timeunit, "days") == 0 ) { + timemultiplier = 86400; + } else if(strcmp(data->timeunit, "weeks") == 0 ) { + timemultiplier = 604800; + } else { + radlog(L_ERR, "rlm_sqlcounter: Unknown value for timeunit \"%s\" in sqlcounter.conf", data->timeunit); + return -1; + } + } + if( timemultiplier ) + res *= timemultiplier; + if (res > 0) { DEBUG2("rlm_sqlcounter: (Check item - counter) is greater than zero"); /* - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/devel.html