[RADIATOR] reusing Database connections efficiently
Hugh Irvine
hugh at open.com.au
Thu Oct 3 22:37:12 UTC 2019
Hello Bruno -
You don’t need to modify any code.
You can define an AuthBy SQL clause with an Identifier outside any Handler or Realm and call it like this:
sub
{
my $p = ${$_[0]}; # proxy reply packet
my $rp = ${$_[1]}; # reply packet to NAS
my $op = ${$_[2]}; # original request packet
my $sp = ${$_[3]}; # packet sent to proxy
# Get the Identifier for this Realm/Handler
my $identifier = Radius::Util::format_special('%{Handler:Identifier}', $op, $rp);
&main::log($main::LOG_DEBUG, "Using Identifier $identifier");
# Find the AuthBy clause with the same Identifier
my $authby = Radius::AuthGeneric::find($identifier);
&main::log($main::LOG_DEBUG, "Found AuthBy with Identifier $identifier”);
This example is from “goodies/hooks.txt” and derives the Identifier from the Realm or Handler, but you can set the Identifier in your hook code any way you like.
hope that helps
Hugh
> On 3 Oct 2019, at 21:21, Bruno Tiago Rodrigues <bruno.tiago.rodrigues at gmail.com> wrote:
>
> Thank you, Hugh.
>
> The load is already being shared between multiple instances and request type. It's been a best practice we've been following for a while.
>
> I will need to have a look at the RADIUS class attribute, but I'm afraid it might not work as expected, since the authentication 3GPP parameters stored in database I want to retrieve come from a different RADIUS client than the one really establishing the session and sending the accounting packets.
>
> The first authentication flow is sent from the mobile gateway (which sends the 3GPP attributes related to the mobile session I want to keep) to my Radiator.
> Radiator returns the tunnel endpoint for that specific connection (let's call it B)
>
> The second authentication flow is sent from B towards my Radiator server - once authenticated, it starts a session and will send accounting packets as needed - which I need to correlate with information from the first authentication flow.
>
> I'm feeling like I need to clone the AuthSQL.pm code, create a new AuthBy and change the handle_request to react on Accounting packets, fetch the relevant data from database and "enrich" the request packet. I was just curious if there was a direct way to access the database connection handles from within a hook.
>
> Thanks again.
>
> Bruno Tiago Rodrigues
>
>
> On Mon, Sep 30, 2019 at 11:55 PM Hugh Irvine <hugh at open.com.au> wrote:
>
> 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 <hugh at open.com.au> 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 <bruno.tiago.rodrigues at gmail.com> 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
> >> radiator at lists.open.com.au
> >> https://lists.open.com.au/mailman/listinfo/radiator
> >
> >
> > --
> >
> > Hugh Irvine
> > hugh at open.com.au
> >
> > 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
> > radiator at lists.open.com.au
> > https://lists.open.com.au/mailman/listinfo/radiator
>
>
> --
>
> Hugh Irvine
> hugh at open.com.au
>
> 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.
>
--
Hugh Irvine
hugh at open.com.au
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.
More information about the radiator
mailing list