[RADIATOR] Too much DB queries with SessionDatabase
Hugh Irvine
hugh at open.com.au
Tue Apr 14 17:52:00 CDT 2009
Hello Jan -
I think I would put the session checking in the Inner Handler(s)
rather than the outer Handler.
Otherwise, instead of using the standard session database you could
use an AuthBy SQL clause in the outer Handler, but I would prefer my
first suggestion.
regards
Hugh
On 15 Apr 2009, at 00:07, Jan Tomasek wrote:
> Hi,
>
> I'm setting up a Radiator server which should allow only one login per
> user. It is working, but Radiator execute delete query per each
> access-request. It does something like:
>
> Tue Apr 14 14:33:17 2009: DEBUG: do query is: 'DELETE FROM RadOnline
> WHERE NASIdentifier="195.113.150.2" AND
> lower(UserName)=lower("semik at tomasek.cz") AND AcctSession
> ID="NULL"':
>
> I'm worried about load this can put on SQL server. How should I
> configure Radiator to execute delete query only for stop accounting
> requests?
>
> Trace4 log file and configuration I'm using is attached.
>
> Thank you for help.
>
> --
> -----------------------
> Jan Tomasek aka Semik
> http://www.tomasek.cz/
>
> #!radius1.cesnet.cz
>
> Foreground
> LogStdout
> Trace 4
> LogDir /var/log/radiator
> DbDir /usr/share/radiator
>
> <AuthLog SYSLOG>
> Identifier authlogger
> Facility local7
> LogSuccess 1
> LogFailure 1
> SuccessFormat %U::OK
> FailureFormat %U::FAIL
> </AuthLog>
> <Log SYSLOG>
> Facility local7
> LogIdent radiator
> Trace 2
> </Log>
> <Log FILE>
> Filename /var/log/arch/radiator/radiator.%Y_%m_%d.log
> Trace 4
> </Log>
>
> AuthPort 1812
> AcctPort 1813
>
>
> <Client localhost>
> Secret XX
> DupInterval 0
> </Client>
>
> <Client DEFAULT>
> Secret XX
> DupInterval 2
> </Client>
>
> # -- Definition of local authentication
> ---------------------------------------
> <AuthBy FILE>
> Identifier CheckFILE
>
> Filename /etc/radiator/user_accounts
>
> EAPType MSCHAP-V2,LEAP,PEAP,TTLS,TLS,MD5,MD5-Challenge
>
> EAPTLS_CAFile /etc/ssl/certs/cesnet-ca.cz.crt.pem
> EAPTLS_CertificateFile /etc/ssl/certs/r1orgA.etest.cesnet.cz.crt
> EAPTLS_CertificateType PEM
> EAPTLS_PrivateKeyFile /etc/ssl/private/r1orgA.etest.cesnet.cz.key
> EAPTLS_MaxFragmentSize 1000
>
> AutoMPPEKeys
>
> EAPTLS_PEAPVersion 0
>
> SSLeayTrace 0
>
> AddToReplyIfNotExist Tunnel-Private-Group-ID=1:100
> AddToReply Tunnel-Type=1:VLAN,\
> Tunnel-Medium-Type=1:Ether_802
>
> # SessionDatabase SQL
> </AuthBy>
>
> # -- Local realms
> -------------------------------------------------------------
> <Handler Realm=/^tomasek.cz$/io>
> AuthBy CheckFILE
>
> SessionDatabase SessionDB
> #DefaultSimultaneousUse 2
> MaxSessions 1
>
> AuthLog authlogger
> </Handler>
>
> <Handler TunnelledByTTLS=1>
> AuthBy CheckFILE
> AuthLog authlogger
> SessionDatabase SessionDBNull
> </Handler>
>
> <Handler TunnelledByPEAP=1>
> AuthBy CheckFILE
> AuthLog authlogger
> SessionDatabase SessionDBNull
> </Handler>
> #
> ^
> ^
> ^
> ^
> ^
> ^
> ^
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
> <ServerRADSEC>
> Secret XX
>
> UseTLS
> TLS_CAPath /etc/ssl/certs
> TLS_CertificateFile /etc/ssl/certs/r1orgA.etest.cesnet.cz.crt
> TLS_CertificateType PEM
> TLS_PrivateKeyFile /etc/ssl/private/r1orgA.etest.cesnet.cz.key
> TLS_ExpectedPeerName .+
> </ServerRADSEC>
>
> <Handler>
> Identifier TOPLEVEL
> <AuthBy RADSEC>
> MaxFailedRequests 1000
> MaxFailedGraceTime 360
> FailureBackoffTime 1
>
> #UseExtendedIds
>
> Host radius1.cesnet.cz
> Secret XX
>
> UseTLS
> TLS_CAPath /etc/ssl/certs
> TLS_CertificateFile /etc/ssl/certs/r1orgA.etest.cesnet.cz.crt
> TLS_CertificateType PEM
> TLS_PrivateKeyFile /etc/ssl/private/r1orgA.etest.cesnet.cz.key
> TLS_ExpectedPeerName .+
> </AuthBy>
> </Handler>
>
> <SessionDatabase SQL>
> Identifier SessionDB
> DBSource DBI:mysql:radiator:localhost:3306
> DBUsername root
> DBAuth XX
>
> # AddQuery insert into RadOnline (UserName, NASIdentifier,
> NASPort, AcctSessionID, TimeStamp, FramedIPAddress, NASPortType,
> ServiceType) values \
> # ("%u", "%1", %2, "%3", %{Timestamp}, "%{Framed-IP-
> Address}", "%{NAS-Port-Type}", "%{Service-Type}")
> AddQuery insert into RadOnline (UserName, NASIdentifier,
> NASPort, AcctSessionID, TimeStamp, FramedIPAddress, NASPortType,
> ServiceType) values \
> (lower("%u"), "%1", %2, "%3", %{Timestamp}, "%{Framed-IP-
> Address}", "%{NAS-Port-Type}", "%{Service-Type}")
>
> DeleteQuery DELETE FROM RadOnline WHERE NASIdentifier="%1"
> AND lower(UserName)=lower("%u") AND AcctSessionID="%3"
>
> ClearNasQuery delete from RadOnline where NASIdentifier="%0"
>
> CountQuery select NASIdentifier, NASPort, AcctSessionID,
> FramedIPAddress from RadOnline where lower(UserName)=lower("%u")
> </SessionDatabase>
>
> <SessionDatabase NULL>
> Identifier SessionDBNull
> </SessionDatabase>
>
>
> <maxsession-log.bz2>_______________________________________________
> radiator mailing list
> radiator at open.com.au
> http://www.open.com.au/mailman/listinfo/radiator
NB:
Have you read the reference manual ("doc/ref.html")?
Have you searched the mailing list archive (www.open.com.au/archives/radiator)?
Have you had a quick look on Google (www.google.com)?
Have you included a copy of your configuration file (no secrets),
together with a trace 4 debug showing what is happening?
Have you checked the RadiusExpert wiki:
http://www.open.com.au/wiki/index.php/Main_Page
--
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. Available on *NIX, *BSD, Windows, MacOS X.
Includes support for reliable RADIUS transport (RadSec),
and DIAMETER translation agent.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.
-
CATool: Private Certificate Authority for Unix and Unix-like systems.
More information about the radiator
mailing list