Re: Not real account stop records ,
On Mon, 2008-09-29 at 05:49 +0200, AHMED KHIDR wrote:
> Hi All
>
> Any one have an Idea about this problem ,
>
>
>
> I changed the account priodic update in my NASes to be 25 Min , and
> all works good except
>
>
> when a session starts ,
> I found in Acc Start time : 2008-09-28 03:58:56
> and Acc Stopt time : -00-00 00:00:00
>
> And this is good ,
>
> After a period I found the below
>
> Acc Start time : 2008-09-28 03:58:56
> and Acc Stopt time : 2008-09-28 03:58:56
>
> although the customer is connected and the accounts work good ,
>
What is the problem?
You have enabled Periodic (interim) updates from your NAS to be sent to
you every 25 minutes - and its trying to do that.
It saves the latest value (from an SQL point of view) in the Acct Stop
record, along with the current Bytes-in, Bytes-out - etc.
In my system - I have a variable in my DB called "AcctLastSeen" - which
is defined the same as "AcctStop" - and have the following in my
sql.conf file:-
accounting_update_query = "UPDATE ${acct_table1} \
SET FramedIPAddress = '%{Framed-IP-Address}', \
AcctSessionTime = '%{Acct-Session-Time}', \
AcctInputOctets = '%{Acct-Input-Gigawords:-0}' << 32 |
'%{Acct-Input-Octets:-0}', \
AcctOutputOctets = '%{Acct-Output-Gigawords:-0}' << 32 |
'%{Acct-Output-Octets:-0}', \
AcctLastSeen = current_timestamp, \
flag = 'C' \
WHERE AcctSessionId = '%{Acct-Session-Id}' \
AND UserName = '%{SQL-User-Name}' \
AND NASIPAddress= '%{NAS-IP-Address}'"
...thus keeping the Start, Stop and Interim values separated.
Initially, AcctStop will be blank and AcctStart and AcctLastSeen have
the same value. Then, about once an hour (my Update Interval),
AcctLastSeen will increase - until there is a STOP record - when
AcctLastSeen will be the same as AcctStop.
(My "flag-'C'" is to indicate there has been an Update on this record -
so I can check whether this ADSL customer has reached their cap or not.)
> This is what appears on the radius radaccount txt files ,
>
> Sun Sep 28 03:58:56 2008
> Acct-Session-Id = "08C7"
> Framed-Protocol = PPP
> Acct-Authentic = RADIUS
> User-Name = "testuser"
> Acct-Status-Type = Start
> NAS-Port-Type = Virtual
> NAS-Port = 0 Service-Type = Framed-User
> NAS-IP-Address = 10.1.1.158
> Acct-Delay-Time = 0
> Client-IP-Address = 10.1.1.158
> Acct-Unique-Session-Id = "5fe4d7463e8b93f8"
> Timestamp = 1222567136
>
>
> Sun Sep 28 04:23:24 2008
> Acct-Session-Id = "08C7"
> Framed-Protocol = PPP
> Framed-IP-Address =62.139.42.151
> Acct-Session-Time = 1468
> Acct-Input-Octets = 23642
> Acct-Output-Octets = 18730
> Acct-Input-Packets = 536
> Acct-Output-Packets = 367
> Acct-Authentic = RADIUS
> User-Name = "0402882105-assas"
> Acct-Status-Type = Interim-Update
> NAS-Port-Type = Virtual
> NAS-Port = 0
> Service-Type = Framed-User
> NAS-IP-Address = 10.1.1.158
> Acct-Delay-Time = 0
> Client-IP-Address = 10.1.1.158
> Acct-Unique-Session-Id = "5fe4d7463e8b93f8"
> Timestamp = 1222568604
>
>
>
> Notice :
> I didn't make any changes in radacct table
> as the default value for all accstart time and accstop time is
> -00-00 00:00:00
>
> AcctStartTime
> datetime
>
>
>
>
> No
> -00-00 00:00:00
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> AcctStopTime
> datetime
>
>
>
>
> No
> -00-00 00:00:00
>
>
>
> Waiting your reply as soon as possible ,
>
> Regards
>
>
>
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
--
. . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready
/| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE
/ |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Class attribute in accounting record.
On Mon, 2007-10-29 at 16:45 +0200, Mark J Elkins wrote:
> My access provider is setting and sending me the "Class" attribute in an
> accounting record...
>
> I use MySQL to store such info in... and I'm using freeradius 1.1.6
Wisdom prevails.. (touching the dictionaries is probably a bad* thing to do...)
I'm using ...
accounting_stop_query_alt = "INSERT , UNHEX(SUBSTR('%{Class}',3)))"
.. which keeps personal changes to one place (sql.conf and files
in /etc/raddb) and saves me from upsetting Alan DeKok's karma* - a bad
thing to do.
--
. . ___. .__ Posix Systems - Sth Africa
/| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE
/ |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: How to control users traffic ?
On Sat, 2009-07-11 at 12:29 +0430, Eric wrote: > The NAS server is poptop vpn server. It seems that pppd can support > Session-Octets-Limit. > How can freeradius send Session-Octets-Limit instead of > Session-Timeout in auth-reply? > > > On Tue, Jul 7, 2009 at 12:23 PM, Eric wrote: > Hi, > Which is conventional way for checking online users traffic > volume and disconnecting who reach to the limit of every user > in freeradius: > 1- using acct-interim packets to update output or input > octets in sql and if user reach to the max of its accounting > permission disconnect him/her.(Is there any patch to do > this ?) I do this. Whenever an interim accounting packet comes in - I also set an extra field (new_data=1) on that row of data. >From 'cron' (every 10 mins) - I run a script looking for accounting packets with that flag (new_data==1) set - and update those customers records accordingly - clearing the (new_data=0) flag. If a customer is over their data limit - I send a PoD (Packet of Disconnect. Next time they try and validate - I restrict them accordingly. > 2- freeradius sends Session-Octets-Limit to the NAS and NAS > can does this? > 3- using billing softwares such as nibs . > 4-other ways? > > - > List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- . . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready /| /| / /__ m...@posix.co.za - Mark J Elkins, Cisco CCIE / |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
icradius to freeradius with mysql - do I need all the tables...
I'm in need of some advice - I'm playing with a quite old icradius setup
and also looking to move to freeradius...
In my current MySql structure - I have a master user table (useracct) -
containing fields like...
| userid | usertype | username | fullname | password | contact | uid |
gid | phone | fax | cell | idnum | vat | email | dateadded | datemod |
substype | subsperiod | subsrate | billaddr1 | billaddr2 | billaddr3 |
billaddr4 | paytype | status | ostatus | agent | maildrop | ip | arpa |
company | comments | flag | quota | acctno |
| 8248 | T | wduser | Mr A N User | mypass | | 99 | 12 |
myyphone | myfax | mymobile | 1234 | 4321 | [EMAIL PROTECTED] |
1998-01-01 00:00:00 | 2001-02-26 13:23:21 | 3 | 12 | 895.00 | Address1
| Address2 | Address3 | Address4 | 1 | 7 | 0 | MyAgent
| /var/spool/mail/wduser | 255.255.255.254 | | | | | 0 |
MYACCTNO |
I then populated tables with appropriate fields from this 'master' data
- such as...
mysql> select * from radcheck where username='wduser';
| id | UserName | Attribute | Value |
| 1 | wduser | Password | mypass |
As freeradius has the SQL Query as part of the config file (sql.conf) -
rather than rebuilding the seperate tables - would it not be more
efficient to alter the 'authorize_check_query' SQL to something like...
authorize_check_query = "SELECT id, UserName, 'Password' as Attribute,
password as Value, '==' as op FROM useracct WHERE Username =
'%{SQL-User-Name}' AND status>2 ORDER BY id"
(status>2 would imply the user is paid up - so can get dialup access)
... and do similar things for other SQL lines
ie - authorize_group_reply_query is used to provide a static IP
(Framed-IP-Address) - if one was allocated - otherwise does nothing...
The authorize_group_check_query looks like it will return multiple lines
and looks more challenging ... otherwise something similar.
... or is is more efficient to stick bits of my master user table into
various tables and have freeradius select from there.
Obviously accounting info will be in its own table...
--
I also want to be able to host multiple realms in a single database - by
authenticating on 'email addresses' (which happen to look like a realm)
- so wouldn't do any stripping - anything wrong with this?
---
I'm already using the table for e-mail/pop and user administration - be
nice to simplify and make the logic of adding/suspending/deleting users
more simple...
--
. . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready
/| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE
/ |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: icradius to freeradius with mysql - do I need all the tables...
On Tue, 2005-01-11 at 17:47 +0200, Mark Elkins wrote:
> I'm in need of some advice - ... move to freeradius...
> In my current MySql structure - I have a master user table (useracct) -
> containing fields like...
>
> | userid | usertype | username | fullname | password | contact | uid |
> gid | phone | fax | cell | idnum | vat | email | dateadded | datemod |
> substype | subsperiod | subsrate .
> authorize_check_query = "SELECT id, UserName, 'Password' as Attribute,
> password as Value, '==' as op FROM useracct WHERE Username =
> '%{SQL-User-Name}' AND status>2 ORDER BY id"
I implemented sql.conf like above - and it seems fine this has
allowed me to get rid of usergroup, radcheck, radgroupcheck and radreply
- as all the info was in my useracct structure. I see this as a Good
Thing.
I also set 'readclients = yes' and had a couple of problems...
there is no 'nas_query' string that one can set with an appropriate
query structure. The query reply expects id, nasname, shortname &
secret... in that order. My current nas table is similar but not
identical - and 'secret' kept coming back as '1812' - what I have in my
ports column.
Has this functionality been implemented in any of the CVS code yet?
I also store the NAS's IP address - this code rather brings back the NAS
name and does a DNS lookup. My problem here is that some NAS Clients use
a 'Loopback' address to send out radius info on - but have different
IP's in the DNS
I'd prefer to pick the DNS statically out of the NAS table...
---
When does radiusd send an SNMP packet to the NAS to check whether a user
is still active or not? Only when its found in the radutmp file and the
user count is the same as simultaneous use? Does it use the 'community'
variable as found in the NAS table? Is this SQL query configurable yet?
Where would radcheck read this from?
--
. . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready
/| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE
/ |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: NAS from db - add without restart
On Wed, 2005-01-19 at 23:05 +0200, Kostas Kalevras wrote: > On Wed, 19 Jan 2005, Alan DeKok wrote: > > > "Neil Craig" <[EMAIL PROTECTED]> wrote: > >> Is it possible to add a NAS to SQL and have Freeradius recognise and > >> use it without restarting radius? The only way I can get this to work is > >> by adding in the entry and restarting, otherwise it thinks it's an > >> invalid NAS > > > > You can HUP the server. > > > > Or, if you're willing to edit the source, have it periodically > > re-read the NAS list form SQL. > > > > Reading the NAS data from SQL for every request is a bad idea. > > There's also snmp, don't forget that. It's the same as a HUP signal but at > least > it can be done remotely and without giving out shell access to the radius > server. Whats to stop one from reading the NAS Table - giving it a key (NAS Name) and doing an SQL lookup - in the same way as one does with user info?... ie potentially on every request. (Not the full table - just the required row entry). Make it more complicated - cache the NAS result (internal linked list) and only do an SQL lookup when its not in the cache. Arrange so that a HUP clears the NAS cache. Go back a step - load the full NAS table into memory, if an internal lookup does not work - then do an (indexed) search just for that NAS - and add the result to the table thats in memory. -- . . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready /| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE / |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: NAS from db - add without restart
On Fri, 2005-01-21 at 08:00 +, Neil Craig wrote: > Could use cron to HUP server every so often > > >>> [EMAIL PROTECTED] 21/01/2005 00:05:09 >>> > Just floating an idea... > > Is it worth considering adding a "periodic" section to radiusd.conf > and > the radius server? Rather than retrofitting reload this, reload that, To Add: Who would need a built-in periodic reload/refresh of tables? After the usertables - which can be read live out of (My)SQL, the next most-changeable category is probably the NAS information. NAS's are usually appended to - so add that ability to SQL. What else is lightly to be changed on a daily/weekly/monthly basis?... that shouldn't really be done without the current functionality of a HUP? -- . . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready /| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE / |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SUSPENSION OF ACCOUNT
On Tue, 2005-10-25 at 09:47 +0100, maruna wrote:
> I use FreeRadius v 1.0.5 with postgresql 7.3.4 on redhat Linux ES3 and
> its been working well. However, I want to create accounts where these
> accounts will be suspended after let say a month and this suspension
> continue until the account is renewed manually.
I use MySQL as my backend...
My MySQL "Useracct" table contains fields like...
userid username password dateadded ip status realm expiredate
(and others..)
This is all managed by Web and PHP (and some shell scripts)
Rather than create all the normal table names, I modified my SQL
commands...
ie
authorize_check_query = "SELECT userid as id,UserName,'Password' as
Attribute,password as Value,'==' as op FROM useracct WHERE
Username='%{Stripped-User-Name}' AND realm='%{Realm}' AND status>2 AND
expiredate>now() ORDER BY id"
authorize_reply_query = "SELECT userid as
id,UserName,'Framed-IP-Address' as Attribute,ip as Value,':=' as op
FROM useracct WHERE Username='%{Stripped-User-Name}' AND
realm='%{Realm}' AND status>2 AND ip != '255.255.255.254' AND
expiredate>now() ORDER BY id"
ie - this should not authenticate if the user has a status of less than
two (Deleted, Suspended) or his expire day has been reached (ie - I gave
him a 30 day test account).
This is for a typical Dial-up type situation - where the user will not
stay on-line continuously for days on end...
Also authorize_group_check_query, authorize_group_reply_query (and
others) are also somewhat customised.
--
. . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready
/| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE
/ |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Duplicate AccSessionId + AcctUniqueId from NAS (which I don't want)
I've got a problem where sometimes I'm getting duplicate accounting records entered into my Accounts file. The customer is on an ADSL line and the NAS is owned by the local monopoly operator. We need accurate traffic counts for each user - as thats what they are billed on. The Telco has sent multiple accounting records for the same person - due to some error on their side - and this seems to be happening more frequently. The Accounting Data is an attached TXT file - cause its wide... The IP's, Realm and UserName have been changed to protect the innocent. What I don't understand is that the AcctUniqueId sometimes changes for the same AcctSessionId. To fix the problem of getting duplicate accounting entries - I was thinking about making either AcctUniqueId or AcctSessionId a unique key in the MySQL table?... but which should I use - listening to the news that some NAS's don't always seny unique AcctUniqueId info. Does setting AcctSessionId to a unique value make more sense? Does it ever duplicate itself by accident? If I make AcctSessionId a unique key (index?) and radius tries to add a duplicate - what will happen? (I'm hoping the answer is it'll be silently ignored - and not that the whole accounting table will be corrupted as radius dies...). Still - it would seem more useful to have AcctSessionId as a unique index - as its actually used as a value for updating the accounting info when a STOP record arrives. Suggestions? Being really lazy - what would the appropriate MySQL command be? .. and wouldn't this generally be a "Good Thing" to do anyway??? -- . . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready /| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE / |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496 | RadAcctId | AcctSessionId | AcctUniqueId | UserName | Realm | NASIPAddress | NASPortId | AcctStartTime | AcctStopTime| AcctSessionTime | AcctInputOctets | AcctOutputOctets | FramedIPAddress | +---+---+--+--+-+--++-+-+-+-+--+-+ |427677 | 7/0/3/17.509_00C25E67 | f9508e528569bd2c | customer | MyRealm | 399.11.22.3 | 1930494461 | 2006-02-02 23:13:45 | 2006-02-03 03:33:23 | 15577 | 172473 | 1459496 | 388.123.120.62 | |427734 | 7/0/3/17.509_00C5DFFE | eda24884bc069f7b | customer | MyRealm | 399.11.22.3 | 1930494461 | 2006-02-03 03:32:44 | 2006-02-03 08:08:15 | 16528 | 499484 | 5991314 | 388.123.102.15 | |427881 | 7/0/3/17.509_00C9C9AC | 87f09acec85e566f | customer | MyRealm | 399.11.22.3 | 1930494461 | 2006-02-03 08:07:20 | 2006-02-03 08:09:40 | 140 | 67344 | 246529 | 388.123.116.151 | |427894 | 7/0/3/17.509_00C9DE73 | c1de91cd203f3b25 | customer | MyRealm | 399.11.22.3 | 1930494461 | 2006-02-03 08:11:59 | 2006-02-04 08:12:09 | 86400 |24863988 |443227614 | 388.123.101.190 | |429953 | 7/0/3/17.509_00DA02CD | 2ba4448b895c4670 | customer | MyRealm | 399.11.22.3 | 1930494461 | 2006-02-04 08:14:22 | 2006-02-04 11:48:24 | 12842 | 148913 | 855850 | 388.123.95.212 | |429988 | 7/0/3/17.509_00DD577A | e680e74be5c30719 | customer | MyRealm | 399.11.22.3 | 1930494461 | 2006-02-04 12:12:39 | 2006-02-04 12:37:45 | 1506 | 59240 | 149849 | 388.123.104.248 | |429990 | 7/0/3/17.509_00DD577A | e680e74be5c30719 | customer | MyRealm | 399.11.22.3 | 1930494461 | 2006-02-04 12:12:43 | 2006-02-04 12:37:45 | 1506 | 59240 | 149849 | 388.123.104.248 | |429992 | 7/0/3/17.509_00DD577A | e680e74be5c30719 | customer | MyRealm | 399.11.22.3 | 1930494461 | 2006-02-04 12:12:45 | 2006-02-04 12:37:45 | 1506 | 59240 | 149849 | 388.123.104.248 | |429994 | 7/0/3/17.509_00DD577A | e680e74be5c30719 | customer | MyRealm | 399.11.22.3 | 1930494461 | 2006-02-04 12:12:47 | 2006-02-04 12:37:45 | 1506 | 59240 | 149849 | 388.123.104.248 | |429996 | 7/0/3/17.509_00DD577A | e680e74be5c30719 | customer | MyRealm | 399.11.22.3 | 1930494461 | 2006-02-04 12:12:49 | 2006-02-04 12:37:45 | 1506 | 59240 | 149849 | 388.123.104.248 | |429998 | 7/0/3/17.509_00DD577A | e680e74be5c30719 | customer | MyRealm | 399.11.22.3 | 1930494461 | 2006-02-04 12:12:50 | 2006-02-04 12:37:45 | 1506 | 59240 | 149849 | 388.123.104.248 | |430003 | 7/0/3/17.509_00DD577A | e680e74be5c30719 | customer | MyRealm | 399.11.22.3 | 1930494461 | 2006-02-04 12:12:52 | 2006-02-04 12:37:45 | 1506 | 59240 | 149849 | 388.123.104.248 | |430004 | 7/0/3/17.509_00DD577A | e680e74be5c
Secondary SQL accounting instance needed
Before I go forth and break my radius and have a few thousand people
looking for me
I want to collect the current allocate IP address and username into a
separate MySql table - if it (the user (=key)) exists - update the IP,
if the user does not exist, add user and IP.
I (think that I) understand that I need to have a second instance of
'sql'..
So, inside the default 'sql.conf' file - I need to change a line near
the top of the file from "sql {" to something like "sql sql_main {"
and then add another named section such as
sql sql_catch_ip {
driver = "rlm_sql_mysql"
server = "DBserver.mydomain.com"
login = "radius-login"
password = "radius-password"
radius_db = "radius"
sqltrace = yes
sqltracefile = ${logdir}/sqltrace_catch_ip.sql
num_sql_socks = 5
connect_failure_retry_delay = 60
accounting_start_query = "INSERT into ip-table (UserName, Realm,
FramedIPAddress) values('%{Stripped-User-Name}', '%{Realm}',
'%{Framed-IP-Address}')"
accounting_start_query_alt = "UPDATE ip-table SET
FramedIPAddress = '%{Framed-IP-Address}' WHERE UserName =
'%{Stripped-User-Name}'"
}
-
Then... in radiusd.conf - where ever I currently have 'sql' - I change
that to the (new) instance name "sql_main" , and in addition, in the
accounting section, also add a line "sql_catch_ip"...
Am I missing anything else
--
. . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready
/| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE
/ |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Want to add details to MySQL
I want to put the contents of radius.log into a MySQL Database - in **real time**. ie - lines that read something like... Fri Feb 25 18:50:37 2005 : Auth: Login OK: [EMAIL PROTECTED] (from client adsl1 port 123456789) How do I do this? This is so various support type people can see when things go wrong... eg... Mon Feb 21 17:50:04 2005 : Auth: Login incorrect (rlm_pap: User password not available): [EMAIL PROTECTED]/testpass] (from client adsl1 port 123456789) ... and fix the problem. All my current interfaces are in web/php/apache - on different machines from where the flat file info is currently logged... -- . . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready /| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE / |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Secondary SQL accounting instance needed
On Wed, 2005-03-02 at 13:19 +0200, Mark Elkins wrote: > I want to collect the current allocate IP address and username into a > separate MySql table - if it (the user (=key)) exists - update the IP, > if the user does not exist, add user and IP. > > I (think that I) understand that I need to have a second instance of > 'sql'.. Phew - it works (exactly as I documented :) There are so many comments in the default radiusd.conf (and sql.conf) that it can become confusing. Better in than out though... Perhaps the default configs should use the full 'instance name' rather than just 'sql' ?? I know that then there will be even more info in the config file - but then adding multiple instances would be less dramatic and more intuitive for others to do? Someone also asked "why are you ignoring stop records",... This is for ADSL in South Africa, where our Telcom Monopoly currently refuses to give static IP's on ADSL services - and to make sure, drops all connections as soon as they get to 24hours of age, making sure that they get a new IP every 24 hours. Usually, most ADSL users who would need a DDNS service try and make sur ethat their link goes back up within seconds.. I'm intending to use the info as a 'free DDNS' for those that authenticate via me, and having a "DNS Lookup OK, route to host failed" is probably better than a "No DNS Record" - and quite often, after the 24 hour drop, the IP is the same anyway... Back to multiple instances... now I'm running another bunch of connections to the same SQL Database. perhaps a bit inefficient?? Maybe there should be some magical keyword that allows multiple instances to the same Database to use the same "connections"?? -- . . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready /| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE / |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Secondary SQL accounting instance needed
On Fri, 2005-03-04 at 16:56 +0200, Mark Elkins wrote:
> On Wed, 2005-03-02 at 13:19 +0200, Mark Elkins wrote:
>
> > I want to collect the current allocate IP address and username into a
> > separate MySql table - if it (the user (=key)) exists - update the IP,
> > if the user does not exist, add user and IP.
> >
> > I (think that I) understand that I need to have a second instance of
> > 'sql'..
>
> Phew - it works (exactly as I documented :)
Might have spoken too soon I'm getting... (in radius.log)
Error: rlm_sql (sql_catch_ip): Couldn't insert SQL accounting START
record - Duplicate entry 'mcsa' for key 2
mcsa is a username - my table looks like
CREATE TABLE dnstab (
dnsid int(10) unsigned NOT NULL auto_increment,
username varchar(64) NOT NULL default '',
realm varchar(64) NOT NULL default '',
ip varchar(16) NOT NULL default '',
dated timestamp(14) NOT NULL,
PRIMARY KEY (dnsid),
UNIQUE KEY username (username)
) TYPE=MyISAM;
Table dump includes
INSERT INTO dnstab VALUES
(4,'mcsa','myddns.com','160.160.100.100',20050304175854);
My 'sql_catch_ip' (in 'sql.conf') has two SQL statements...
accounting_start_query = "INSERT into dnstab (username, realm, ip)
values('%{Stripped-User-Name}', '%{Realm}', '%{Framed-IP-Address}')"
accounting_start_query_alt = "UPDATE dnstab SET
ip='%{Framed-IP-Address}' WHERE username='%{Stripped-User-Name}' and
realm='%{Realm}'"
My understanding is that if there is an SQL error from
accounting_start_query - that accounting_start_query_alt would be
automatically tried - without any error report - or should I be using
the username as the key instead? (or probably the username and realm
concatenated), again I could probably use one of those cute new
mysql commands that does "update" instead of "insert" if a key
exists..but what is FreeRadius meant to do?
--
. . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready
/| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE
/ |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
sql _alt query - when?
I'm happily running FreeRadius with SQL for storing users - etc... In the accounting sections - there are entries for :- accounting_update_query -and- accounting_update_query_alt Under what conditions does Radius run the '_alt' version of the SQL query?? (Where is it documented?) I ask because I ave added a second instance of sql - to capture IP addresses.. I have accounting_start_query and accounting_start_query_alt The "accounting_start_query" sometimes gets errors - but the "_alt" version never seems to run... In radius.log - I get.. Error: rlm_sql (sql_catch_ip): Couldn't insert SQL accounting START record - Duplicate entry 'acars' for key 2 sql_catch_ip is the name I've given to the second sql instance.. -- . . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready /| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE / |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Post-Auth-Typeq REJECT and sql
I'm sure Kostas Kalevras pointed me to a file - which included the
section
--
In the following example, 2 different sql modules are used
to store accepted requests and rejected requests.
post-auth {
my_sql_accept
Post-Auth-Type REJECT {
my_sql_reject
}
}
--
I think this means - set up two extra instances of sql (in sql.conf) -
using the names "my_sql_accept" and "my_sql_reject".
So in these instances - what should the names of the "queries" be
called?
Will this be an accounting query - such as "accounting_update_query"???
What Variables will give me info like "Login incorrect (rlm_pap: User
password not available)" ???
Would some kind soul point me in the right direction?
--
. . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready
/| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE
/ |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Multiple logins Freeradius/Mysql
We've set up Simultaneous-Use := 1 - and this seems fine.
However, I have my first user - who is really off the net and 'radzap'
seems to do nothing (radwho still gives the user as being there).
What is the code... in sql.conf...
simul_count_query = "SELECT COUNT(*) FROM ${acct_table1} WHERE
UserName='%{SQL-User-Name}' AND AcctStopTime = 0"
...for
In our previous Radius - we used to simply provide a valid AcctStopTime
time - and the user could then login again.
What do I need to do to change freeradius to do Simultaneous-Use control
using mysql and not using radwho??
I've read doc/Simultaneous-Use - which doesn't use/mention [my]sql.
I'm guessing (looking at where radutmp is mentioned)... radiusd.conf
has...
session {
radutmp
}
Should this be changed to...
session {
sql
}
???
--
. . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready
/| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE
/ |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Multiple logins Freeradius/Mysql
On Mon, 2005-06-13 at 17:27 +0200, Mark Elkins wrote:
> We've set up Simultaneous-Use := 1 - and this seems fine.
> However, I have my first user - who is really off the net and 'radzap'
> seems to do nothing (radwho still gives the user as being there).
>
> What is the code... in sql.conf...
> simul_count_query = "SELECT COUNT(*) FROM ${acct_table1} WHERE
> UserName='%{SQL-User-Name}' AND AcctStopTime = 0"
> ...for
>
> In our previous Radius - we used to simply provide a valid AcctStopTime
> time - and the user could then login again.
>
> What do I need to do to change freeradius to do Simultaneous-Use control
> using mysql and not using radwho??
>
> I've read doc/Simultaneous-Use - which doesn't use/mention [my]sql.
I think this Document should be updated to tell people who are using an
SQL to change the 'session' bit in radiusd.conf
> I'm guessing (looking at where radutmp is mentioned)... radiusd.conf
> has...
>
> session {
> radutmp
> }
>
> Should this be changed to...
> session {
> sql
> }
The above seems to work - grepping through old email also seems to
suggest that now checkrad won't work...unless we use radutmp...
or does checkrad run with the sql...
simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName,
NASIPAddress, NASPortId, FramedIPAddress, CallingStationId,
FramedProtocol FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND
AcctStopTime = 0"
???
Anyway - testing the above seemed to block simultaneous use - like
things worked with radutmp - except now it does seem to be using the SQL
code..
--
. . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready
/| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE
/ |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: How to solve alive user who actually has loged off
On Mon, 2005-06-20 at 18:54 +0800, ??? wrote:
> Hello everybody,
>
> I am using freeradius-1.0.4.
>
> In my situation, user "testuser" from domain "domain" logged from NAS
> 192.168.1.68,
>
> After a while,user "testuser" logged out,but the NAS did not send Accout-Stop
> packet(for some reason) and freeradius still
>
> thought that user "testuser" is alive,but Actually user "testuser" has
> logged off.
>
> I have set the Simulate-Use to 1 and now "testuser" can not log in any more
> from anywhere.
>
> I use radwho and it output as follows:
Rather than just telling you to read the Documentation...I had a problem
with this and feel the documentation could do with some refinement.
First: Look at:- /usr/src/freeradius-1.0.4/doc/Simultaneous-Use
With flat files:
logged-in users are in the 'radutmp' file (something
like /usr/local/var/log/radius/radutmp). 'radwho' prints the file.
If 'checkrad' is set up to run, it should 'snmp' (or however its been
told to run) the NAS where the user was last seen - and if the NAS says
the user is no longer there, clear the radutmp entry and allow login.
With MySQL (which I use):
logged-in users are determined by SQL asking the accounting table for
the user where the 'stoptime' is Zero (ie - no stop record received).
The SQL looks like:
"SELECT COUNT(*) FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}'
AND AcctStopTime = 0"
If 'checkrad' is programmed to run, It runs the SQL:
"SELECT RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId,
FramedIPAddress, CallingStationId, FramedPr
otocol FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND
AcctStopTime = 0"
...
which gives it the NAS to go and ask.
Whether you use radutmp or SQL for checking Simultaneous-Use is also
determined by the setting for 'session' in 'radiusd.conf'. Mine looks
like:
session {
# radutmp<-- commented out.
sql
}
--
. . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready
/| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE
/ |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Need to conditionally update user data in authentication process (mysql) - Clues?
Hi
I need to conditionally update some user data whilst in the
authentication process - I'm looking for clues as how to do this.
Background: I'm providing vouchers with a username and password on them.
The first time the voucher details are used - I need to update that
users details (change their group, add an expiry date - etc). All my
users are in groups and its only users in a particular group that I want
this to happen too...
Everything is in MySQL.
Sort of...
pre_authorize_check = "UPDATE useracct SET groupname='newgroup',
expireaccount='now() + 30 days' IF
(SELECT groupname FROM useracct WHERE Username='%{Stripped-User-Name}'
AND realm='%{Realm}' ) == 'voucher_group';
ie - If the person trying to login is a member of my 'voucher_group',
make some changes to their SQL details first then authenticate them
as normal...
How would I do something like this?
This process would only ever happen once to a user in the lifetime of
their account, unlike repetitively logging in and using the service
afterwards.
ps - I also want to send them some welcoming e-mail at the same time and
as their [EMAIL PROTECTED] is actually their e-mail address .. the address
is easy...but the process of doing so?
--
. . ___. .__ Posix Systems - Sth Africa. e.164 VOIP ready
/| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, Cisco CCIE
/ |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
