[RADIATOR] Using SQL statements inside a PostAuthHook for COA

[Thomas Kurian]

Dear Heikki and friends,
The following radpwtst test for COA worked, I want to get this COA 
automated ,with the script i have provided below.
With this command i was able to change the speed of a user from 6Mbps to 
3Mbps.
_
Working Radpwtst __test_ (The device responded positively to the this 
command)
radpwtst -trace 4 -bind_address 0.0.0.0 -auth_port 3799 -noauth -noacct 
-s 10.25.1.28 -secret XXXXXX -time -code Change-Filter-Request 
User-Name="99774711" Acct-Session-Id="002E9100" 
Framed-IP-Address="94.187.153.84" cisco-Policy-Up="3Mbps" 
cisco-Policy-Down="3Mbps";

Can you please help me with the following script based on the above 
"working radpwtst test"

Script
-------------------------------------------------------------------------
#! /usr/bin/perl -w
use strict;
use warnings;
use diagnostics;

sub {

# OBJECT REF

         my $p = ${$_[0]};
         my $r = ${$_[1]};

# RETURN VOID

         return unless ($p->code() eq 'Accounting-Request')
                                 && (${$_[2]} == $main::ACCEPT);

         my $handler             = $p->{Handler};

         my $identifier          = $handler->{thomas};

         &main::log($main::LOG_DEBUG, "Running PostAuthHook: Using 
Identifier
$identifier");

         my $username            =
$p->getAttrByNum($Radius::Radius::USER_NAME);


         my $sess_handle         = Radius::SessGeneric::find($identifier);

         my $query               = undef;


         &main::log($main::LOG_DEBUG, "Running PostAuthHook sql query 
check for :
$username");

                 $query  = "select username from quotasubscribers where 
switched = 0 and type = 'Q' and monthlycounter >= maxquota ";
                 my $sth = $sess_handle->prepareAndExecute($query);
                 my @row = $sess_handle->getOneRow($sth);
                 $sth->finish;
                 my $db_user_name = $row[0];



if ( $db_user_name eq  $username )
{ &main::log($main::LOG_DEBUG, "Running PostAuthHook sql query check for 
speed2 ,the speed assigned after user exceeds allocated qouta");

                 $query  = "select speed2 from quotasubscribers where 
switched = 0 and type = 'Q' and monthlycounter >= maxquota ";
                 my $sth = $sess_handle->prepareAndExecute($query);
                 my @row = $sess_handle->getOneRow($sth);
                 $sth->finish;
                 my $speed2 = $row[0];

     main::log($main::LOG_DEBUG, 'Starting COA execution '); \

     my $user_name = $p->get_attr('User-Name'); \
     my $sess_id = $p->get_attr('Acct-Session-Id'); \
     my $framed_ipaddress = $p->get_attr('Framed-IP-Address'); \

     my @coa_attrs = ("User-Name=$user_name", 
"Acct-Session-Id=$sess_id", "Framed-IP-Address=$framed_ipaddress", 
"cisco-Policy-Up=$speed2", "cisco-Policy-Down=$speed2);\

            push @cmd_args, ("-trace", "4", "-bind_address", "0.0.0.0", 
"-auth_port", "3799", "-secret", "XXXXX", "-s", "10.20.1.25"); \

           my @cmd = ("perl", "radpwtst"); \

           main::log($main::LOG_DEBUG, "Running command: @cmd @cmd_args 
@coa_attrs"); \

           system (@cmd, @cmd_args, @coa_attrs); \





     &main::log($main::LOG_DEBUG, "The user $db_user_name has exceeded 
allocated quota and is been limited to $speed2");

}else

    &main::log($main::LOG_DEBUG, "The user $username either has not yet 
exceeded allocated quota or isnt a quota based user");


}
-------------------------------------------------------------------

-- 
Thanks & Best Regards,

Thomas Kurian
IT Security Engineer (B.Tech. -- Electrical)
Kuwaiti Canadian Consulting Group (www.kccg.com)
T: +965 22435566
F: +965 22415149
E: thomas at kccg.com

Date: Thu, 04 Apr 2013 23:13:46 +0300
From: Heikki Vatiainen<hvn at open.com.au>
Subject: Re: [RADIATOR] Using SQL statements inside a PostAuthHook for
	COA
To:radiator at open.com.au
Message-ID:<515DDEFA.9040706 at open.com.au>
Content-Type: text/plain; charset=windows-1252

On 04/04/2013 10:35 PM, Thomas Kurian wrote:

>   1. Can you  check if the COA part in the below script is configured the
>      right way ,advice me if there is anything extra that needs to be added .
This depends on the device you are sending CoA to.
>   2. I also require to get ($Radius::Radius::?  dictionary definitions
>      of) Acct-Session-Id and Framed-IP-Address from the Accounting packet
>      just like how it is done for User-Name (my
>      $username=$p->getAttrByNum($Radius::Radius::USER_NAME);  please
>      provide me with the similar script line for Acct-Session-Id and
>      Framed-IP-Address like
>      my $acctsessionid= $p->getAttrByNum($Radius::Radius::___________)
>      and my $framedipaddress =
>      $p->getAttrByNum($Radius::Radius::____________).
Using using attribute names should work. So you should be able to get 
rthe value with $p->get_attr('Framed-IP-Address'). If you still want to 
use getAttrByNum, see Radius/Radius.pm for the list of what is defined.
>   3. Please check if the below hook file as a whole and kindly see &
>      advice if it meets the requirements for the COA.
This depends on the device you are sending the CoA. You would need to 
see its manual and/or experiment e.g., with radpwtst. Thanks, Heikki
-- Heikki Vatiainen <hvn 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 etc. Full source on Unix, 
Windows, MacOSX, Solaris, VMS, NetWare etc. ------------------------------

  

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.open.com.au/pipermail/radiator/attachments/20130405/0ceb8f14/attachment.html