Hi again -
BTW - you should always be running separate Radiator instances for authenticaiton and accounting. regards Hugh > On 1 Oct 2019, at 07:44, Hugh Irvine <[email protected]> wrote: > > > 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 -- 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
