Hello Bruno -
Have you considered using the RADIUS Class attribute for this? You can add whatever information you require for accounting to a Class attribute that is returned when you process the authentication. The Class attribute will then be included in all accounting requests for the session and you can use the data directly without having to hit the DB again. Let me know if you need any further information. regards Hugh > On 30 Sep 2019, at 23:31, Bruno Tiago Rodrigues > <[email protected]> wrote: > > Our organization has a Radiator server handling a large volume of requests > and we're trying to squeeze ops and optimize as much as possible. > > On one of our recent audits we ran to the production server, we found out > that for each accounting request we need to get data from the underlying > database from the related authentication packet before processing the > accounting itself. > > There's a PreAuthHook running for each Handler which basically clones the > Radiator database connection properties to establish a connection and fetch > data from the authentication table. > > #!/usr/bin/perl > > sub > { > use DBI; > my $p = ${$_[0]}; > my $username = $p->get_attr('User-Name'); > my $anumber = $p->get_attr('Calling-Station-Id'); > > my $dbconn = &main::getVariable('dbconn'); > my $dbuser = &main::getVariable('dbuser'); > my $dbpass = &main::getVariable('dbpass'); > > my $dbh = DBI->connect($dbconn, $dbuser, $dbpass); > > ($country_code, $imei, $rat) = &get_location_info($username, $anumber, $dbh); > > $p->add_attr('3GPP-SGSN-MCC-MNC', $country_code); > $p->add_attr('3GPP-IMEISV', $imei); > $p->add_attr('3GPP-RAT-TYPE', $rat); > > &main::log($main::LOG_INFO,"Got extra RADIUS parameters from database for > user: $username"); > } > > sub get_location_info { > my $query = 'SELECT * FROM (SELECT country_code, imei, rat, row_number() > over(order by timestamp desc) rn FROM authentication WHERE username = > \''.$_[0].'\' and anumber = \''.$_[1].'\' order by TIMESTAMP desc) tbl WHERE > tbl.rn <= 1'; > my $sth = $_[2]->prepare($query); > $sth->execute(); > my @result=$sth->fetchrow_array(); > $sth->finish(); > return @result; > } > } > > According to our DBAs, however, this is hurting the database because it > requires establishing a connection to the database, logging in and fetching > data. Logging in, for auditing purposes, is taking a huge toll on the > response times. > > Is there any efficient way to reuse an existing handle bound to the instance > or persistently get a DBI->connect handle on a Startup Hook for each instance > and then reuse it inside the PreAuthHook (eventually reconnecting if > necessary)? > > > > Bruno Tiago Rodrigues > _______________________________________________ > radiator mailing list > [email protected] > https://lists.open.com.au/mailman/listinfo/radiator -- Hugh Irvine [email protected] Radiator: the most portable, flexible and configurable RADIUS server anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald, Platypus, Freeside, TACACS+, PAM, external, Active Directory, EAP, TLS, TTLS, PEAP, TNC, WiMAX, RSA, Vasco, Yubikey, MOTP, HOTP, TOTP, DIAMETER, SIM, etc. Full source on Unix, Linux, Windows, macOS, Solaris, VMS, NetWare etc. _______________________________________________ radiator mailing list [email protected] https://lists.open.com.au/mailman/listinfo/radiator
