Hello Harrison -
Further to my previous mail, here is a patched version of
AddressAllocatorSQL.pm for you to test.
Please let me know if it works correctly and I will roll the fix into the
next release.
regards
Hugh
On Tuesday 21 August 2001 12:59, Harrison Ng wrote:
> > Hello,
>
> Does anyone know is it valid to include %{attribute-name} in AllocateQuery
> of <AddressAllocator SQL>.
> We've append a new column called MSISDN in RADPOOL to record
> Calling-Station-Id. Then add custom AllocateQuery as below.
> Under trace 4, it seems that radiator return empty value for
> %{Calling-Station-Id}.
> However we tried similar things in AuthSelect of <AuthBy SQL>,
> %{Calling-Station-Id} works properly.
> Can anyone give me a hint.
>
> Harrison
> SmarTone Mobile Communications Ltd.
>
>
>
>
>
>
> This is an extract of radius.cfg
> --------------------------------------------
>
> <AuthBy SQL>
>
> Identifier getpoolhint
> DBSource dbi:mysql:radius:10.25.157.33
> DBUsername dbuser1
> DBAuth netra323
>
> AuthSelect select POOLHINT from APN where ROAMDIGIT =
> left("%{Calling-Station-Id}",3)
> AuthColumnDef 0, PoolHint, reply
>
> AccountingTable ACCOUNTING
>
> AcctColumnDef USERNAME,User-Name
> AcctColumnDef CLASS,Class
> AcctColumnDef ACCTSESSIONID,Acct-Session-Id
> AcctColumnDef NASIPADDRESS,NAS-IP-Address
> AcctColumnDef ACCTSTATUSTYPE,Acct-Status-Type
> AcctColumnDef NASPORT,NAS-Port,integer
> AcctColumnDef ACCTAUTHENTIC,Acct-Authentic
> AcctColumnDef NASIDENTIFIER,NAS-Identifier
> AcctColumnDef FRAMEDPROTOCOL,Framed-Protocol
> AcctColumnDef CALLINGSTATIONID,Calling-Station-Id
> AcctColumnDef FRAMEDIPADDRESS,Framed-IP-Address
> AcctColumnDef TIMESTAMP,Timestamp,integer
>
> AcctFailedLogFileName %L/grad1.%c.missing.%Y%m%d
>
> </AuthBy SQL>
>
>
>
> <AddressAllocator SQL>
>
> Identifier myallocator
>
> DBSource dbi:mysql:radius:10.25.157.33
> DBUsername dbuser1
> DBAuth netra323
>
> AllocateQuery update RADPOOL set
> STATE=1,TIME_STAMP=%0,EXPIRY=%1,USERNAME='%2',MSISDN='%{Calling-Station-Id}
>' \
> where YIADDR='%3' and TIME_STAMP%4
>
> <AddressPool local>
> Subnetmask 255.255.255.0
> Range 202.140.74.11 202.140.74.20
> </AddressPool>
>
> <AddressPool roam>
> Subnetmask 255.255.255.0
> Range 10.25.155.11 10.25.155.20
> </AddressPool>
>
> </AddressAllocator SQL>
>
>
>
> This is radiator trace 4
> --------------------------------
>
> Tue Aug 21 10:48:09 2001: DEBUG: Packet dump:
> *** Received from 202.140.74.1 port 2859 ....
> Code: Access-Request
> Identifier: 199
> Authentic: <0><0>'_<0><0><7><249><0><0>bm<0><0><7>T
> Attributes:
> User-Name = "aaaa"
> User-Password =
> "A<228><31><204><205>(<27><166>-v<150>Z<220><180><207>U"
> NAS-IP-Address = 202.140.74.1
> Service-Type = Framed-User
> Framed-Protocol = PPP
> NAS-Port = 1
> Calling-Station-Id = "85298699517"
> NAS-Identifier = "radius"
>
> Tue Aug 21 10:48:09 2001: DEBUG: Check if Handler Client-Id =
> 202.140.74.1,NAS-Identifier = "radius" should be used to handle this
> request Tue Aug 21 10:48:09 2001: DEBUG: Handling request with Handler
> 'Client-Id = 202.140.74.1,NAS-Identifier = "radius"'
> Tue Aug 21 10:48:09 2001: DEBUG: simultaneous Deleting session for aaaa,
> 202.140.74.1, 1
> Tue Aug 21 10:48:09 2001: DEBUG: do query is: delete from RADONLINE where
> NASIDENTIFIER='202.140.74.1' and NASPORT=1
>
> Tue Aug 21 10:48:09 2001: DEBUG: Handling with Radius::AuthFILE
> Tue Aug 21 10:48:09 2001: DEBUG: Radius::AuthFILE looks for match with aaaa
> Tue Aug 21 10:48:09 2001: DEBUG: Radius::AuthFILE looks for match with
> DEFAULT
> Tue Aug 21 10:48:09 2001: DEBUG: Radius::AuthFILE ACCEPT: Accept explicitly
> by Auth-Type=Accept
> Tue Aug 21 10:48:09 2001: DEBUG: Handling with Radius::AuthSQL
> Tue Aug 21 10:48:09 2001: DEBUG: Handling with Radius::AuthSQL
> Tue Aug 21 10:48:09 2001: DEBUG: Query is: select POOLHINT from APN where
> ROAMDIGIT = left("85298699517",3)
>
> Tue Aug 21 10:48:09 2001: DEBUG: Radius::AuthSQL looks for match with aaaa
> Tue Aug 21 10:48:09 2001: DEBUG: Radius::AuthSQL ACCEPT:
> Tue Aug 21 10:48:09 2001: DEBUG: Handling with Radius::AuthDYNADDRESS
> Tue Aug 21 10:48:09 2001: DEBUG: Query is: select TIME_STAMP, YIADDR,
> SUBNETMASK, DNSSERVER from RADPOOL
> where POOL='local' and STATE=0 order by TIME_STAMP
>
> Tue Aug 21 10:48:09 2001: DEBUG: do query is: update RADPOOL set
> STATE=1,TIME_STAMP=998362089,EXPIRY=998448489,USERNAME='aaaa',MSISDN=''
> where YIADDR='202.140.74.11' and TIME_STAMP=998362069
>
> Tue Aug 21 10:48:09 2001: DEBUG: Access accepted for aaaa
> Tue Aug 21 10:48:09 2001: DEBUG: Packet dump:
> *** Sending to 202.140.74.1 port 2859 ....
> Code: Access-Accept
> Identifier: 199
> Authentic: <0><0>'_<0><0><7><249><0><0>bm<0><0><7>T
> Attributes:
> Framed-IP-Netmask = 255.255.255.0
> Framed-IP-Address = 202.140.74.11
> Class = "SI=Testing"
>
> Tue Aug 21 10:48:09 2001: DEBUG: Packet dump:
> *** Received from 202.140.74.1 port 2860 ....
> Code: Accounting-Request
> Identifier: 200
> Authentic: <181><135>T<224><207>t<172>mc<239>$<206>c*W<182>
> Attributes:
> User-Name = "aaaa"
> Class = "SI=Testing"
> Acct-Session-Id = ""000000Testing""
> NAS-IP-Address = 202.140.74.1
> Acct-Status-Type = Start
> NAS-Port = 1
> Acct-Authentic = RADIUS
> NAS-Identifier = "radius"
> Framed-Protocol = PPP
> Calling-Station-Id = "85298699517"
> Framed-IP-Address = 202.140.74.11
>
> Tue Aug 21 10:48:09 2001: DEBUG: Check if Handler Client-Id =
> 202.140.74.1,NAS-Identifier = "radius" should be used to handle this
> request Tue Aug 21 10:48:09 2001: DEBUG: Handling request with Handler
> 'Client-Id = 202.140.74.1,NAS-Identifier = "radius"'
> Tue Aug 21 10:48:09 2001: DEBUG: simultaneous Adding session for aaaa,
> 202.140.74.1, 1
> Tue Aug 21 10:48:09 2001: DEBUG: do query is: delete from RADONLINE where
> NASIDENTIFIER='202.140.74.1' and NASPORT=1
>
> Tue Aug 21 10:48:09 2001: DEBUG: do query is: insert into RADONLINE
> (USERNAME,NASIDENTIFIER,NASPORT,ACCTSESSIONID,TIME_STAMP,FRAMEDIPADDRESS,NA
>S PORTTYPE,SERVICETYPE) values
> ('aaaa','202.140.74.1',1,'"000000Testing"',998362089,'202.140.74.11','','')
>
> Tue Aug 21 10:48:09 2001: DEBUG: Handling with Radius::AuthFILE
> Tue Aug 21 10:48:09 2001: DEBUG: Handling with Radius::AuthSQL
> Tue Aug 21 10:48:09 2001: DEBUG: Handling accounting with Radius::AuthSQL
> Tue Aug 21 10:48:09 2001: DEBUG: do query is: insert into ACCOUNTING
> (USERNAME, CLASS, ACCTSESSIONID, NASIPADDRESS,
> ACCTSTATUSTYPE, NASPORT, ACCTAUTHENTIC, NASIDENTIFIER, FRAMEDPROTOCOL,
> CALLINGSTATIONID, FRAMEDIPADDRESS, TIMESTAMP)
> values
> ('aaaa', 'SI=Testing', '"000000Testing"', '202.140.74.1',
> 'Start', 1, 'RADIUS', 'radius', 'PPP', '85298699517', '202.140.74.11',
> 998362089)
>
> Tue Aug 21 10:48:09 2001: DEBUG: Handling with Radius::AuthDYNADDRESS
> Tue Aug 21 10:48:09 2001: DEBUG: Accounting accepted
> Tue Aug 21 10:48:09 2001: DEBUG: Packet dump:
> *** Sending to 202.140.74.1 port 2860 ....
> Code: Accounting-Response
> Identifier: 200
> Authentic: <181><135>T<224><207>t<172>mc<239>$<206>c*W<182>
> Attributes:
>
> Tue Aug 21 10:49:54 2001: DEBUG: Packet dump:
> *** Received from 202.140.74.1 port 2863 ....
> Code: Accounting-Request
> Identifier: 201
> Authentic: <157><156>W<220>8k<9><26>H<181><203>a<133><229><130><24>
> Attributes:
> User-Name = "aaaa"
> Class = "SI=Testing"
> Acct-Session-Id = ""000000Testing""
> NAS-IP-Address = 202.140.74.1
> Acct-Status-Type = Stop
> NAS-Port = 1
> Acct-Authentic = RADIUS
> NAS-Identifier = "radius"
> Framed-Protocol = PPP
> Calling-Station-Id = "85298699517"
> Framed-IP-Address = 202.140.74.11
>
> Tue Aug 21 10:49:54 2001: DEBUG: Check if Handler Client-Id =
> 202.140.74.1,NAS-Identifier = "radius" should be used to handle this
> request Tue Aug 21 10:49:54 2001: DEBUG: Handling request with Handler
> 'Client-Id = 202.140.74.1,NAS-Identifier = "radius"'
> Tue Aug 21 10:49:54 2001: DEBUG: simultaneous Deleting session for aaaa,
> 202.140.74.1, 1
> Tue Aug 21 10:49:54 2001: DEBUG: do query is: delete from RADONLINE where
> NASIDENTIFIER='202.140.74.1' and NASPORT=1
>
> Tue Aug 21 10:49:54 2001: DEBUG: Handling with Radius::AuthFILE
> Tue Aug 21 10:49:54 2001: DEBUG: Handling with Radius::AuthSQL
> Tue Aug 21 10:49:54 2001: DEBUG: Handling accounting with Radius::AuthSQL
> Tue Aug 21 10:49:54 2001: DEBUG: do query is: insert into ACCOUNTING
> (USERNAME, CLASS, ACCTSESSIONID, NASIPADDRESS,
> ACCTSTATUSTYPE, NASPORT, ACCTAUTHENTIC, NASIDENTIFIER, FRAMEDPROTOCOL,
> CALLINGSTATIONID, FRAMEDIPADDRESS, TIMESTAMP)
> values
> ('aaaa', 'SI=Testing', '"000000Testing"', '202.140.74.1',
> 'Stop', 1, 'RADIUS', 'radius', 'PPP', '85298699517', '202.140.74.11',
> 998362194)
>
> Tue Aug 21 10:49:54 2001: DEBUG: Handling with Radius::AuthDYNADDRESS
> Tue Aug 21 10:49:54 2001: DEBUG: do query is: update RADPOOL set STATE=0,
> TIME_STAMP=998362194 where YIADDR='202.140.74.11'
>
> Tue Aug 21 10:49:54 2001: DEBUG: Accounting accepted
> Tue Aug 21 10:49:54 2001: DEBUG: Packet dump:
> *** Sending to 202.140.74.1 port 2863 ....
> Code: Accounting-Response
> Identifier: 201
> Authentic: <157><156>W<220>8k<9><26>H<181><203>a<133><229><130><24>
> Attributes:
----------------------------------------
Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description:
----------------------------------------
--
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. Available on *NIX, *BSD, Windows 95/98/2000, NT, MacOS X.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.
# AddressAllocatorSQL
#
# Implements IP address allocation from an SQL database.
# Called by AuthDYNADDRESS.pm
#
# Author: Mike McCauley ([EMAIL PROTECTED])
# Copyright (C) 2000 Open System Consultants
# $Id: AddressAllocatorSQL.pm,v 1.8 2001/06/07 03:52:14 mikem Exp $
package Radius::AddressAllocatorSQL;
use Radius::AddressAllocatorGeneric;
use Radius::SqlDb;
use strict;
use vars qw($VERSION @ISA);
BEGIN
{
@ISA = qw(Radius::AddressAllocatorGeneric Radius::SqlDb);
}
#####################################################################
# Contruct a new Dynamic address allocator
sub new
{
my ($class, $file) = @_;
my $self = $class->SUPER::new($file);
# Make sure all the addresses in all the
# pools are present in the database.
my $pool;
foreach $pool (@{$self->{AddressPools}})
{
$self->createPool($pool);
}
$self->reclaimExpired();
return $self;
}
#####################################################################
# Do per-instance default initialization
# This is called by Configurabel during Configurable::new before
# the config file is parsed. Its a good place initalze
# instance variables
# that might get overridden when the config file is parsed.
sub initialize
{
my ($self) = @_;
$self->Radius::AddressAllocatorGeneric::initialize;
$self->Radius::SqlDb::initialize;
$self->{FindQuery} = "select TIME_STAMP, YIADDR, SUBNETMASK, DNSSERVER from RADPOOL
where POOL='%0' and STATE=0 order by TIME_STAMP";
$self->{AllocateQuery} = "update RADPOOL set STATE=1,
TIME_STAMP=%0,
EXPIRY=%1, USERNAME='%2' where YIADDR='%3' and TIME_STAMP %4";
$self->{CheckPoolQuery} = "select STATE from RADPOOL where YIADDR='%0'";
$self->{AddAddressQuery} = "insert into RADPOOL (STATE, TIME_STAMP,
POOL, YIADDR, SUBNETMASK, DNSSERVER) values (0, %t, '%0', '%1',
'%2', '%3')";
$self->{DeallocateQuery} = "update RADPOOL set STATE=0,
TIME_STAMP=%t where YIADDR='%0'";
$self->{ReclaimQuery} = "update RADPOOL set STATE=0
where state!=0 and EXPIRY < %0";
$self->{DefaultLeasePeriod} = 86400; # 1 day
$self->{LeaseReclaimInterval} = 86400; # 1 day
}
#####################################################################
# Override the object function in Configurable
sub object
{
my ($self, $file, $keyword, $args) = @_;
if ($keyword eq 'AddressPool')
{
my $pool = Radius::AddressPool->new($file, $args);
push(@{$self->{AddressPools}}, $pool);
}
else
{
return 0;
}
}
#####################################################################
# Override the keyword function in Configurable
sub keyword
{
my ($self, $file, $keyword, $value) = @_;
$self->match_keyword
($keyword, $value,
'DefaultLeasePeriod' => 'integer',
'LeaseReclaimInterval' => 'integer',
'FindQuery' => 'string',
'AllocateQuery' => 'string',
'DeallocateQuery' => 'string',
'CheckPoolQuery' => 'string',
'AddAddressQuery' => 'string',
'ReclaimQuery' => 'string') && return 1;
# Multiple inheritance
return $self->Radius::AddressAllocatorGeneric::keyword
($file, $keyword, $value)
|| $self->Radius::SqlDb::keyword
($file, $keyword, $value);
}
#####################################################################
# Allocate an address for username with the given pool hint
# return a hash of interesting values for AuthBy DYNADDRESS
# to do stuff with
sub allocate
{
my ($self, $caller, $username, $hint, $p, $rp) = @_;
my %details;
# Find the oldest free address in this pool
my $q = &Radius::Util::format_special
($self->{FindQuery},
$p, $rp, $hint, $username);
my $iterations = 20; # Permit up to 20 collissions
my ($last_time_stamp, $sth, $update, $now, $expiry,
$lease_period, $timestamp_compare);
while ($iterations-- > 0)
{
$sth = $self->prepareAndExecute($q);
return ($main::REJECT,
"Address pool database not available")
unless $sth;
if (($last_time_stamp,
$details{yiaddr}, $details{subnetmask},
$details{dnsserver})
= $self->getOneRow($sth))
{
# Got a new address, update the state and timestamp
# and expiry time
$now = time;
# Use the Session-Timeout if its available, else
# the DefaultLeasePeriod
$lease_period = $rp->getAttrByNum
($Radius::Radius::SESSION_TIMEOUT);
$lease_period = $self->{DefaultLeasePeriod}
unless defined $lease_period;
$expiry = $now + $lease_period;
$timestamp_compare = $last_time_stamp eq '' ?
'is NULL' : "=$last_time_stamp";
$update = &Radius::Util::format_special
($self->{AllocateQuery},
$p, $rp,
$now, $expiry, $username, $details{yiaddr},
$timestamp_compare);
# If this fails, then its prob because someone else
# got the same address before us. Try again
next unless $self->do($update) == 1;
# Call the callers allocateDone() function to process
# the results
$caller->allocateDone($p, $rp, \%details);
# And tell the caller to accept immediately
return ($main::ACCEPT);
}
else
{
# Hmmm, no spare addresses
return ($main::REJECT, "No available addresses");
}
}
# Hmmmm, should not happen: too many other people trying
# to get addresses at the same time
return ($main::REJECT, "Too many simultaneous address requests");
}
#####################################################################
# Confirm a previously allocated address is in use
sub confirm
{
my ($self, $caller, $address, $p, $rp) = @_;
return ($main::ACCEPT);
}
#####################################################################
# Free a previously allocated address
sub deallocate
{
my ($self, $caller, $address, $p, $rp) = @_;
my $q = &Radius::Util::format_special
($self->{DeallocateQuery},
$p, $rp, $address);
$self->do($q);
return ($main::ACCEPT);
}
#####################################################################
# Glue between the timeout callback and the reclaimExpired function
sub reclaimExpiredTimeout
{
my ($handle, $self) = @_;
$self->reclaimExpired();
}
#####################################################################
# Arrange for reclamation of expired leases every
# LeaseReclaimInterval seconds
sub reclaimExpired
{
my ($self) = @_;
&main::log($main::LOG_DEBUG, "Reclaiming expired leases");
my $now = time;
my $q = &Radius::Util::format_special
($self->{ReclaimQuery}, undef, undef, $now);
$self->do($q);
# Arrange to be called again in LeaseReclaimInterval seconds
&Radius::Select::add_timeout($now + $self->{LeaseReclaimInterval},
\&reclaimExpiredTimeout, $self);
}
#####################################################################
# Ensure all the addresses in an address pool are present
# in the database
sub createPool
{
my ($self, $pool) = @_;
# For each range, and for each address in the range
# make sure it exists in the database
my ($range, $i4, $address, $state);
foreach $range (@{$pool->{Range}})
{
if ($range =~ /(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})\s+(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})/)
{
# 2 addresses a.b.c.d e.f.g.h
my ($l1, $l2, $l3, $l4) = ($1, $2, $3, $4);
my ($u1, $u2, $u3, $u4) = ($5, $6, $7, $8);
# Some error checking. We only permit the
# last octet to vary
if ( $l1 != $u1 || $l1 > 255
|| $l2 != $u2 || $l2 > 255
|| $l3 != $u3 || $l3 > 255
|| $l4 > $u4)
{
&main::log($main::LOG_ERR, "Invalid Range $range. Ignored");
next;
}
my $lower = ($l1 << 24) + ($l2 << 16) + ($l3 << 8) + $l4;
my $upper = ($u1 << 24) + ($u2 << 16) + ($u3 << 8) + $u4;
$self->checkAddressRange($pool, $lower, $upper);
}
elsif ($range =~ /(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})\/(\d{1,2})/)
{
# CIDR block a.b.c.d/x
my ($l1, $l2, $l3, $l4, $bs) = ($1, $2, $3, $4, $5);
# Some error checking. We only permit the
# last octet to vary
if ($l1 > 255 || $l2 > 255 || $l3 > 255 || $l4 > 255
|| $bs > 32)
{
&main::log($main::LOG_ERR, "Invalid CIDR block $range. Ignored");
next;
}
my $lower = ($l1 << 24) + ($l2 << 16) + ($l3 << 8) + $l4;
my $mask = (1 << (32 - $bs)) - 1;
$lower &= ~$mask;
my $upper = $lower | $mask;
$self->checkAddressRange($pool, $lower, $upper);
}
else
{
&main::log($main::LOG_WARNING,
"Range $range, unknown Range format. Ignored");
}
}
}
#####################################################################
# Ensure that a single address exists, else create it
# Return 0 if it was created, else 0
# Address is in the form of an integer
sub checkAddress
{
my ($self, $pool, $address) = @_;
# Convert integer address to a dotted quad
my $dquad = sprintf("%d.%d.%d.%d",
$address >> 24 & 0xff,
$address >> 16 & 0xff,
$address >> 8 & 0xff,
$address & 0xff);
# Check whether it already exists
&main::log($main::LOG_DEBUG, "Checking address $dquad");
my $q = &Radius::Util::format_special
($self->{CheckPoolQuery},
undef, undef, $dquad);
my $sth = $self->prepareAndExecute($q);
last unless $sth;
if (!$self->getOneRow($sth))
{
# Not there, add it
$q = &Radius::Util::format_special
($self->{AddAddressQuery},
undef, undef,
$pool->{Name}, $dquad, $pool->{Subnetmask},
$pool->{DNSServer});
return $self->do($q);
}
return;
}
#####################################################################
# Check that all the addresses in the range $lower to $upper
# inclusive exist in the pool, else create them
sub checkAddressRange
{
my ($self, $pool, $lower, $upper) = @_;
my $i;
for ($i = $lower; $i <= $upper; $i++)
{
$self->checkAddress($pool, $i);
}
}
#####################################################################
#####################################################################
#####################################################################
# This is where we define the companion class AddressPool
# which allows us to tell the database what our pools are
# each AddressPool will be checked out at startup time, to ensure
# it is in the database
package Radius::AddressPool;
use vars qw($VERSION @ISA);
BEGIN
{
@ISA = qw(Radius::Configurable);
}
#####################################################################
# Contruct a new AddressPool
sub new
{
my ($class, $file, $name) = @_;
my $self = $class->SUPER::new($file);
$self->{Name} = $name;
&main::log($main::LOG_ERR, "No Ranges defined for AddressPool at '$main::config_file' line $.")
if !defined $self->{Range} && $file;
return $self;
}
#####################################################################
# Do per-instance default initialization
# This is called by Configurabel during Configurable::new before
# the config file is parsed. Its a good place initalze
# instance variables
# that might get overridden when the config file is parsed.
sub initialize
{
my ($self) = @_;
$self->SUPER::initialize;
$self->{Subnetmask} = '255.255.255.255';
}
#####################################################################
# Override the keyword function in Configurable
sub keyword
{
my ($self, $file, $keyword, $value) = @_;
$self->match_keyword
($keyword, $value,
'Range' => 'stringarray',
'Subnetmask' => 'string',
'DNSServer' => 'string') && return 1;
return $self->SUPER::keyword($file, $keyword, $value);
}
1;
