(RADIATOR) Session database with mysql
Hugh Irvine
hugh at open.com.au
Fri Jan 17 16:38:45 CST 2003
Hello -
Thanks for sending the configuration file and trace.
It is almost certainly the case that you are not receiving the stop
records at all, which of course makes it very difficult to maintain to
coherent session database.
You can always send a "Reply-Message = ...." in the access reject,
unfortuantely the vast majority of diallers (Microsoft) do not display
the message even if you send it. You can use the "RejectHasReason"
parameter in your Handler's, but as I say, it probably won't get
displayed to the user in any case.
regards
Hugh
On Saturday, Jan 18, 2003, at 00:18 Australia/Melbourne,
<tdn at tdn.co.ke> wrote:
>> I will need to see the complete configuration file (no secrets)
>> together with a trace 4 debug from Radiator showing what is happening.
>
> Below find the cfg file and a trace 4 log
>
> FYI
> ----
> This radius server receives request from a proxy radius server, and
> from it
> appears most
> of the stale sessions are from the remote-out-of-town Nases.
>
> Another question, is there any way i can configure radius to give a
> more
> meaningful message to the person
> trying to connect and fails because of Max sessions exeeded?
>
>
> CFG file
> ======================================
> LogDir /usr/local/radiator
> DbDir /usr/local/radiator/raddb
> Trace 4
> LogFile %L/logfile/logfile.%Y%m%d
>
> <SessionDatabase SQL>
> Identifier InSQL
>
> DBSource dbi:mysql:radonline:1.1.1.1
> DBUsername xx
> DBAuth xxx
>
>
> AddQuery insert into radonline (username, \
> nasidentifier, \
> nasipaddress, \
> nasport, \
> acctsessionid, \
> time_stamp, \
> framedipaddress, \
> nasporttype, \
> servicetype, \
> calledstationid, \
> callingstationid, \
> usrchassiscallslot, \
> usrchassiscallchannel) \
> values ('%u', \
> '%N', \
> '%{NAS-IP-Address}', \
> 0%{NAS-Port}, \
> '%{Acct-Session-Id}', \
> %{Timestamp}, \
> '%{Framed-IP-Address}', \
> '%{NAS-Port-Type}', \
> '%{Service-Type}', \
> '%{Called-Station-Id}', \
> '%{Calling-Station-Id}', \
> 0%{USR-Chassis-Call-Slot}, \
> 0%{USR-Chassis-Call-Channel} )
>
> DeleteQuery delete from radonline where
> acctsessionid='%{Acct-Session-Id}' and username='%u'
>
> ClearNasQuery delete from radonline where nasidentifier='%N'
>
> CountQuery select
> nasidentifier,nasport,acctsessionid,framedipaddress from radonline
> where
> username='%u'
> </SessionDatabase>
>
> #Primary Proxy Radius Server
> <Client 1.1.1.1>
> Secret xxx
> DefaultRealm domain.dom
> </Client>
>
> #Secondary Proxy Radius Server
> <Client 2.2.2.2>
> Secret xxx
> DefaultRealm domain.dom
> </Client>
>
> # Mainly for the radpwtst utility
> <Client DEFAULT>
> Secret mysecret
> DupInterval 0
> DefaultRealm domain.dom
> </Client>
>
> <AuthBy FILE>
> Identifier myISDN
> Filename /usr/local/radiator/raddb/users.isdn
> </AuthBy>
>
> <AuthBy GROUP>
> Identifier myAsync
> AuthByPolicy ContinueUntilAccept
> <AuthBy FILE>
> Filename
> /usr/local/radiator/raddb/users-to-migrate
> </AuthBy>
> <AuthBy FILE>
> Filename /usr/local/radiator/raddb/users
> </AuthBy>
> </AuthBy>
>
>
> <Handler Realm=domain.dom,NAS-Port-Type = Async>
> # Remove spaces - spaces are generally not allowed
> # but lusers type them anyway, and are a pain
> RewriteUsername s/\s+//g
> # Strip the realm
> RewriteUsername s/\@adomain\.dom$//
> AuthBy myAsync
> AcctLogFileName %L/detail/detail.%Y%m%d
> PasswordLogFileName %L/logfile/passwords/logfile.%Y%m%d
> MaxSessions 1
> </Handler>
>
>
> <Handler Realm=africaonline.co.ke,NAS-Port-Type = ISDN>
> # Remove spaces - spaces are generally not allowed
> # but lusers type them anyway, and are a pain
> RewriteUsername s/\s+//g
> # Strip the realm
> RewriteUsername s/\@domain\.dom$//
> AuthBy myISDN
> AcctLogFileName %L/detail/detail.%Y%m%d
> PasswordLogFileName %L/logfile/passwords/logfile.%Y%m%d
> </Handler>
>
>
> Trace 4 logs
> ==================================================================
> Fri Jan 17 15:45:54 2003: DEBUG: do query is: insert into radonline
> (username, nasidentifier, nasipaddress, nasport, acctsessionid,
> time_stamp,
> framedipaddress, nasporttype, servicetype, calledstationid,
> callingstationid, usrchassiscallslot, usrchassiscallchannel) values
> ('ec143pop', 4.4.4.4, 4.4.4.4,' 01025, '67109142', 1042807857,
> 8.8.8.8,
> 'Async', 'Framed-User', '', '', 05, 01 )
>
> Fri Jan 17 15:45:54 2003: DEBUG: Handling request with Handler
> 'Realm=domain.dom,NAS-Port-Type = Async'
> Fri Jan 17 15:45:54 2003: DEBUG: Rewrote user name to
> ec143pop at domain.dom
> Fri Jan 17 15:45:54 2003: DEBUG: Rewrote user name to ec143pop
> Fri Jan 17 15:45:54 2003: DEBUG: InSQL Adding session for ec143pop,
> 3.3.3.3,
> 1025
> Fri Jan 17 15:45:54 2003: DEBUG: do query is: delete from radonline
> where
> acctsessionid='67109142' and username='ec143pop'
>
>
>
>
> ----- Original Message -----
> From: "Hugh Irvine" <hugh at open.com.au>
> To: <tdn at tdn.co.ke>
> Cc: <radiator at open.com.au>
> Sent: 16-01-2003 5:08 AM
> Subject: Re: (RADIATOR) Session database with mysql
>
>
>>
>> Hello -
>>
>> The usual reason for stale records being left in the session database
>> is missing stop records. In other words, if you do not receive a stop
>> record when a user session completes, then you will not delete that
>> users record from the session database.
>>
>> It is also possible that the delete query that you show below is not
>> correct. Have you checked a trace 4 to make sure that this is doing
>> the
>> right thing and deleting the correct record?
>>
>> I will need to see the complete configuration file (no secrets)
>> together with a trace 4 debug from Radiator showing what is happening.
>>
>> regards
>>
>> Hugh
>>
>>
>> On Wednesday, Jan 15, 2003, at 21:22 Australia/Melbourne,
>> <tdn at tdn.co.ke> wrote:
>>
>>> Hello,
>>>
>>> Am trying to implement a session database using a mysql database.
>>> My problem is that i notice that there are so many cases when people
>>> have
>>> actually logged out, but not
>>> deleted from the database.
>>>
>>> (see my database structure and part of my cfg file)
>>>
>>> create table radonline (
>>> username char(50) NOT NULL,
>>> nasidentifier char(50),
>>> nasipaddress char(15),
>>> nasport int,
>>> acctsessionid char(30) NOT NULL,
>>> time_stamp int,
>>> framedipaddress char(15),
>>> nasporttype char(10),
>>> servicetype char(20),
>>> calledstationid char(16),
>>> callingstationid char(16),
>>> usrchassiscallslot int,
>>> usrchassiscallchannel int,
>>>
>>> UNIQUE RADONLINE_I (nasidentifier, username),
>>> INDEX RADONLINE_I2 (username)
>>> );
>>>
>>>
>>>
>>> cfg file
>>> =========
>>>
>>>
>>> SessionDatabase SQL>
>>> Identifier InSQL
>>>
>>> DBSource dbi:mysql:radonline:1.1.1.1
>>> DBUsername xxxx
>>> DBAuth xxx
>>>
>>>
>>> AddQuery insert into radonline (username, \
>>> nasidentifier, \
>>> nasipaddress, \
>>> nasport, \
>>> acctsessionid, \
>>> time_stamp, \
>>> framedipaddress, \
>>> nasporttype, \
>>> servicetype, \
>>> calledstationid, \
>>> callingstationid, \
>>> usrchassiscallslot, \
>>> usrchassiscallchannel) \
>>> values ('%u', \
>>> '%N', \
>>> '%{NAS-IP-Address}', \
>>> 0%{NAS-Port}, \
>>> '%{Acct-Session-Id}', \
>>> %{Timestamp}, \
>>> '%{Framed-IP-Address}', \
>>> '%{NAS-Port-Type}', \
>>> '%{Service-Type}', \
>>> '%{Called-Station-Id}', \
>>> '%{Calling-Station-Id}', \
>>> 0%{USR-Chassis-Call-Slot}, \
>>> 0%{USR-Chassis-Call-Channel} )
>>>
>>> DeleteQuery delete from radonline where acctsessionid='%4' and
>>> nasipaddress='%2' and username='%u'
>>>
>>> ClearNasQuery delete from radonline where
>>> nasidentifier='%N'
>>>
>>> CountQuery select
>>> nasidentifier,nasport,acctsessionid,framedipaddress from radonline
>>> where
>>> username='%u'
>>> </SessionDatabase>
>>>
>>> TDN
>>>
>>> ===
>>> Archive at http://www.open.com.au/archives/radiator/
>>> Announcements on radiator-announce at open.com.au
>>> To unsubscribe, email 'majordomo at open.com.au' with
>>> 'unsubscribe radiator' in the body of the message.
>>>
>>>
>>
>> --
>> Radiator: the most portable, flexible and configurable RADIUS server
>> anywhere. Available on *NIX, *BSD, Windows 95/98/2000, NT, MacOS X.
>> -
>> Nets: internetwork inventory and management - graphical, extensible,
>> flexible with hardware, software, platform and database independence.
>>
>> ===
>> Archive at http://www.open.com.au/archives/radiator/
>> Announcements on radiator-announce at open.com.au
>> To unsubscribe, email 'majordomo at open.com.au' with
>> 'unsubscribe radiator' in the body of the message.
>>
>
>
--
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. Available on *NIX, *BSD, Windows 95/98/2000, NT, MacOS X.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.
===
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.
More information about the radiator
mailing list