(RADIATOR) AuthBy DYNADDRESS / AddressAllocatorSQL

[Andy M]

Hi,

Sorry to reply to my own post - but I've set up a quick (& very hacky) proof of concept of how it might work:

In AddressAllocatorSQL.pm:

 - Add to the '%Radius::AddressAllocatorSQL::ConfigKeywords =' section the following keywords:
      'PLSQLFunction'          => 'string',
      'PLSQLBindVar'           => 'stringarray',
      'PLSQLBindVarName'       => 'stringarray',
 - In the 'prepareAndExecute' subroutine, add the following (just before '    # Find the oldest free address in this pool')
     if (defined($self->{PLSQLFunction})) {
         my $q = &Radius::Util::format_special ($self->{PLSQLFunction}, $p, undef, $hint, $username, $expiry);
         $details{yiaddr} = $self->prepareAndExecuteInOut
             ($q, \@{$self->{PLSQLBindVarName}}, map { &Radius::Util::format_special($_, $p, undef, $hint, $username, $expiry)}
              @{$self->{PLSQLBindVar}});

               return ($main::REJECT), if $details{yiaddr} eq 'NO_IP';
           # Call the callers allocateDone() function to process
           # the results
           $caller->allocateDone($p, \%details);
           # And tell the caller to accept immediately
           return ($main::ACCEPT);
     }


In AuthBySQL.pm, add a new function 'prepareAndExecuteInOut':

 sub prepareAndExecuteInOut
 {
     my ($self, $q, $bind_value_names_ref, @bind_values) = @_;
     my ($attempts, $sth, $rc);
     my ($param_ipaddress, $param_expiry);

     # Try to execute the query. If we fail due to database failure
     # try to reconnect and try again. If that also fails, give up
     while (!$sth && $attempts++ < 2)
     {
       if ($self->reconnect())
       {

           # We evaluate the execution
           # with an alarm for the timeout period
           # pending. If the alarm goes off, the eval will die
           &Radius::Util::exec_timeout($self->{Timeout},
               sub {
               $sth = $Radius::SqlDb::handles{$self->{dbname}}->prepare($q);
               $sth->bind_param_inout(":v_ipaddress", \$param_ipaddress, 20);
               foreach my $thisnum (0..(scalar(@bind_values)-1)) {
                       $sth->bind_param(${$bind_value_names_ref}[$thisnum], $bind_values[$thisnum]);
               }
           });

           # Some DBD drivers dont undef rc on DB failure
           return $param_ipaddress if $sth && $rc && !$DBI::err;

           # If we got here, something went wrong
           my $reason = $DBI::errstr;
           $reason = "SQL Timeout" if $@ && $@ =~ /timeout/;
           $self->log($main::LOG_ERR, "Execute failed for '$q': $reason");
       }
       # Hmm, failed prob due to database failure, try to reconnect
       # to an alternate
       $self->disconnect();
       $sth = undef;
     }
     return;
 }

- A sample config contains, in the AddressAllocator section:

        PLSQLFunction BEGIN :v_ipaddress := get_ip(:v_pool, :v_expiry); END;
        PLSQLBindVarName :v_pool
        PLSQLBindVarName :v_expiry
        PLSQLBindVar %0
        PLSQLBindVar %2


The above has been tested with successful and unsuccessful cases and works correctly. It is also a little quicker than using prepare...execute...fetch. Clearly it is very untidy, not integrated with Radiator, doesn't handle many errors correctly etc, but as a proof of concept it does demonstrate address allocation using stored functions/procedures and PL/SQL (which, due to better contention management, can significantly improve address allocation times when running with several Radiator instances each attempting to allocate from the same pool of IP addresses). As such - could something similar to it be included within the main Radiator code?

Regards,

Andy





  ----- Original Message ----- 
  From: Andy M 
  To: radiator at open.com.au 
  Sent: Tuesday, October 26, 2004 1:48 PM
  Subject: (RADIATOR) AuthBy DYNADDRESS / AddressAllocatorSQL


  Hi,

  We are using a Radiator configuration with AuthBy DYNADDRESS and AddressAllocator SQL in order to allocate IP addresses from an Oracle 9i database.

  We have a configuration where we don't use separate SelectQuery and UpdateQuery, but instead have a SelectQuery which calls an Oracle stored function that picks an IP address, marks it as unavailable (taking care of contention with other Radiator processes doing the same thing), records the username etc, and returns the IP address. We therefore have a blank UpdateQuery, as updates are done in the SelectQuery. See goodies/address-allocator-sp.sql and goodies/address-allocator-sp.cfg for an example of the sort of thing we are doing.

  The problem is that when the SelectQuery (which is something like "select 1, get_ip(?, ?) from dual", with bind variables of the poolhint and the expiry time) cannot return a valid IP address, it needs to return something, e.g. a NULL response, or an error. This might happen, for instance, if the address pool is completely full, or it is invalid pool hint, or there is just so much contention that it has given up trying to find an IP address.

  If it returns NULL, Radiator goes ahead and sends an Access-Accept to the client (and so not suitable).

  If it returns an error, Radiator sees this as an error in DBI 'fetch' and disconnects from the database. This does result in an Access-Reject being sent to the client, but the database disconnections/reconnections kill the DB performance whenever the problem is due to e.g. a completely full address pool.

  Some possibilities might be:

  1. Recode AddressAllocatorSQL to use PL/SQL with in/out bind variables (similar to AuthByPLSQL - although this doesn't handle address allocation), and to handle scenarios where no address could be allocated
  2. Recode AddressAllocatorSQL to recognise a response from the SelectQuery which isn't a valid IP (e.g. an IP address of '0.0.0.0' or 'NO_IP' could mean 'no IP address found') and return an Access-Reject to the client in this case.

  What are your thoughts on this? I will have a go at #1 as support for PL/SQL for address allocation seems to be a better generic solution - so if I get something working I'll send in the code. But would be interested to get opinions from anyone else on this.

  Regards,

  Andy
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.open.com.au/pipermail/radiator/attachments/20041026/9f1bec94/attachment.html>