3.114. <AddressAllocator SQL>

<AddressAllocator SQL> works in conjunction with <AuthBy DYNADDRESS> (see Section 3.53. <AuthBy DYNADDRESS>) to allocate IPv4 addresses and IPv6 prefixes from an SQL database. During deallocation, the address is marked as unused. Addresses that remain in use for more than DefaultLeasePeriod seconds are automatically reclaimed (this protects against lost Accounting Stop requests).
<AddressAllocator SQL> supports all the common SQL configuration parameters. For more information about the SQL configuration parameters, see Section 3.8. SQL configuration.
The default FindQuery fetches the oldest unused address from the RADPOOL table. Change the allocation strategy by customising the SQL queries.
Starting from Radiator 4.14, you can define UpdateQuery to refresh address TIME_STAMP and EXPIRY when accounting Alive requests are received. This keeps the addresses from being automatically reclaimed while they are periodically updated by Alive requests.
The table definition of a sample RADPOOL table for a range of SQL databases can be found in the goodies/*.sql files in the Radiator distribution.
<AddressAllocator SQL> uses the PoolHint to determine which pool to use. It uses the pool hint in the SQL select statement that is used to find an available address. It does an exact match on the POOL column of the RADPOOL table.
When an address is allocated for a user, it is ‘leased’ to the user for a fixed period. It is available exclusively for that user until either they terminate their session (when an Accounting Stop is received) or until the lease expires. The purpose of this is to protect against ‘lost’ Accounting Stops such as might occur with poor network connectivity, or a crashed NAS. However it also means that you should set the lease period to be longer than the longest legitimate session time, otherwise users may find their addresses being reallocated to another user.
Since Radiator 4.14 you can use shorter lease period if accounting Alive messages are used and UpdateQuery is configured.
Starting with Radiator 4.18, you can use asynchronous SQL queries with some database servers. See AsynchronousSQL and goodies/addressallocator.sql for the details.
The default lease period is 1 day, and you can control this with DefaultLeasePeriod. Every LeaseReclaimInterval seconds, expired leases are reclaimed and made available for allocation again.
Acct-Status-Type values Accounting-On and Accounting-Off are by default accepted and replied to with no other action. You can configure AddressAllocator SQL with DeallocateByNASQuery to, for example, release all leases for the NAS when Accounting-On or Accounting-Off is received.
<AddressAllocator SQL> makes the following allocation variables available for replies. These names can be used in MapAttribute in <AuthBy DYNADDRESS>.
<AddressAllocator SQL> can also optionally populate the RADPOOL table at startup, by defining <AddressPool xxxxxx> clauses inside the <AddressAllocator SQL> clause.

3.114.1. Identifier

This mandatory parameter specifies an symbolic name for this AddressAllocator clause. It must exactly match the Allocator parameter in an AuthBy DYNADDRESS in order for it to be used to allocate addresses.
Identifier mySQLallocator

3.114.2. DefaultLeasePeriod

This optional parameter defines how long the default lease period is in seconds. The default is 86400 seconds (24 hours).
# We have long sessions, set the lease to be 2 days
DefaultLeasePeriod 172800

3.114.3. LeaseReclaimInterval

This optional parameter defines how often expired leases are looked for. Every LeaseReclaimInterval seconds, AddressAllocator SQL checks the RADPOOL table for expired leases, using the ReclaimQuery. If expired leases are found, they are marked as available again. The default is 86400 seconds (24 hours).
# Look for expired leases every hour
LeaseReclaimInterval 3600

3.114.4. FindQuery

This optional parameter allows you to define a custom SQL query to find an available address or prefix. The default is:
select TIME_STAMP, YIADDR, SUBNETMASK, DNSSERVER from RADPOOL where POOL='%0'
       and STATE=0 order by TIME_STAMP
The variables are replaced as follows:
  • %0 by the pool hint
  • %1 by the user name
  • %2 with the lease expiry time
  • %3 by SQL quoted NasId value
Tip
You can get a substantial speedup during address allocation with MySQL by adding 'limit 1' to the end of the FindQuery when using a database engine that supports LIMIT clause.
This example uses NAS_ID column to allocate an IPv6 prefix from the range assigned to the user's NAS. The pool has been previously initialised with NAS_IDs belonging to known NASes.
# Our NASes all send NAS-IPv6-Address, e.g. 2001:db8:2::2
NasId %{NAS-IPv6-Address}
FindQuery select TIME_STAMP, YIADDR, SUBNETMASK, DNSSERVER from RADPOOL
          where POOL='%0' and STATE=0 and NAS_ID=%3 order by TIME_STAMP

3.114.5. FindQueryBindVar

This optional parameter specifies a bind variable to be used with FindQuery. For more information about bind variables, see Section 3.8.1. SQL bind variables.

3.114.6. AllocateQuery

This optional parameter allows you to define a custom SQL query to allocate an address or prefix that was found with FindQuery. The default is:
update RADPOOL set STATE=1,TIME_STAMP=%0, EXPIRY=%1, USERNAME=%2
       where YIADDR='%3' and STATE=0 and TIME_STAMP %4
The variables are replaces as follows:
  • %0 by the current time in Unix epoch seconds
  • %1 by the lease expiry time (the current time + the lease period)
  • %2 by the user name
  • %3 by the address or prefix
  • %4 by a timestamp comparison operator
  • %5 by the SQL quoted NasId
If the AllocateQuery is an empty string, the query is not executed. This can be useful if the FindQuery does all the allocation work.

3.114.7. AllocateQueryBindVar

This optional parameter specifies a bind variable to be used with AllocateQuery. For more information about bind variables, see Section 3.8.1. SQL bind variables.

3.114.8. UpdateQuery

This optional parameter allows you to define a custom SQL query to update the address or prefix when accounting Start or Alive requests are received. There is no default.
UpdateQuery update RADPOOL set TIME_STAMP=?, EXPIRY=? where YIADDR=?
UpdateQueryBindVar %t
UpdateQueryBindVar %0
UpdateQueryBindVar %1
The variables are replaced as follows:
  • %0 by the lease expiry time (the current time + the lease period)
  • %1 by the address
  • %2 by the SQL quoted NasId
If the UpdateQuery is an empty string, the query is not executed.

3.114.9. UpdateQueryBindVar

This optional parameter specifies a bind variable to be used with UpdateQuery. For more information about bind variables, see Section 3.8.1. SQL bind variables.

3.114.10. CheckPoolQuery

This optional parameter allows you to define a custom SQL query to check whether an address or prefix exists in the pool. The default is:
select STATE from RADPOOL where YIADDR='%0'
%0 is replaced by the address.
If CheckPoolQuery is set to an empty string, no pool checking is done at startup. Delay the start of pool check with DelayedPoolCheckTime. For more information, see Section 3.114.22. DelayedPoolCheckTime.

3.114.11. CheckPoolQueryBindVar

This optional parameter specifies a bind variable to be used with CheckPoolQuery.For more information about bind variables, see Section 3.8.1. SQL bind variables.

3.114.12. AddAddressQuery

This optional parameter allows you to define a custom SQL query to add an address to the pool if it is not found by CheckPoolQuery. The default is:
insert into RADPOOL (STATE, TIME_STAMP, POOL, YIADDR, SUBNETMASK, DNSSERVER)
       values (0, %t, %0, '%1', '%2', '%3')
The variables are replaced as follows:
  • %t by the current time in Unix epoch seconds
  • %0 by the pool name
  • %1 by the address
  • %2 by the subnet mask
  • %3 by the DNS server address
  • %4 by the pool's PoolGroup
  • %5 by the pool's Priority
  • %6 by the pool's NasIdentifier
If AddAddressQuery is set to an empty string, addresses are not automatically added to the pool.

3.114.13. AddAddressQueryBindVar

This optional parameter specifies a bind variable to be used with AddAddressQuery. For more information about bind variables, see Section 3.8.1. SQL bind variables.

3.114.14. DeallocateQuery

This optional parameter allows you to define a custom SQL query to deallocate a previously allocated address. If the DeallocateQuery is an empty string, no deallocation query is executed. The default value is:
update RADPOOL set STATE=0,TIME_STAMP=%t where YIADDR='%0'
%0 is replaced the address and %1 by the SQL quoted NasId.

3.114.15. DeallocateQueryBindVar

This optional parameter specifies a bind variable to be used with DeallocateQuery. For more information about bind variables, see Section 3.8.1. SQL bind variables.

3.114.16. DeallocateByNASQuery

This optional parameter allows you to define a custom SQL query to deallocate all previously allocated addresses for a NAS. If the DeallocateByNASQuery is an empty string or not defined, no deallocation query is executed. This query is not defined by default currently. %0 is replaced by the SQL quoted NasId.
# Process Accounting-On and Accounting-Off
DeallocateByNASQuery update RADPOOL set STATE=0, TIME_STAMP=%t where NAS_ID=%0

3.114.17. DeallocateByNASQueryBindVar

This optional parameter specifies a bind variable to be used with DeallocateByNASQuery. For more information about bind variables, see Section 3.8.1. SQL bind variables.

3.114.18. ReclaimQuery

This optional parameter allows you to define a custom SQL query to reclaim an address whose lease has expired. If the ReclaimQuery is an empty string, no reclaim query will be executed. Defaults to
update RADPOOL set STATE=0 where STATE!=0 and EXPIRY < %0
%0 is replaced by the current time in Unix epoch seconds. By default reclaim query runs immediately when AddressAllocator SQL module is loaded. You can delay the start of reclaim check with DelayedPoolCheckTime. For more information, see Section 3.114.22. DelayedPoolCheckTime.

3.114.19. ReclaimQueryBindVar

This optional parameter specifies a bind variable to be used with ReclaimQuery. For more information about bind variables, see Section 3.8.1. SQL bind variables.

3.114.20. NoAddressHook

NoAddressHook is called when there are no addresses left or the allocation fails because of too many simultaneous tries.
NoAddressHook is passed with the following arguments:
  • Reference to the current request object
  • Reference to the current reply object
  • Reference to current authentication result
  • Reference to a string variable holding the reason for a reject
  • String variable holding value of pool hint
To change the type of reply, change the third argument from $main::REJECT to the desired value.

3.114.21. NasId

NasId defines the name of attribute in request or other value typically used for NAS_ID column. It is available as SQL quoted special value for the AddressAllocator SQL queries. The default is %{NAS-Identifier}.
# Our NASes use IPv6 addresses
NasId %{NAS-IPv6-Address}

3.114.22. DelayedPoolCheckTime

This parameter defines time in seconds Radiator delays checking address pools when AddressAllocatorSQL is activated. This is not set by default, which mean Radiator checks the pool immediately when AddressAllocator SQL loaded during the configuration. During the pool check CheckPoolQuery and ReclaimQuery are run to update the pool state. For more information, see Section 3.114.10. CheckPoolQuery and Section 3.114.18. ReclaimQuery.
# Postpone the check. Do it when the config has been loaded
DelayedPoolCheckTime 1

3.114.23. AddressPool

The AddressPool clause allows you to define which address pools are available. When Radiator is started, each AddressPool ensures there is an entry for each of its addresses and prefixes in the RADPOOL table.
AddressPool is a simple alternative for maintaining the contents of the RADPOOL table through other method. You can use another method for initialising and maintaining the RADPOOL table, in this case it is not necessary to have any AddressPool clauses.
AddressPool defines a range of available addresses or prefixes. Each address or prefix in the range has the same Subnet mask and DNS Server address. The Subnetmask and the DNS server address specify the values to use if an address or prefix is allocated from a range. The default for Subnetmask is 255.255.255.255. There is no default for DNSServer.
IPv4 address ranges can be specified either as lower and upper addresses (inclusive) within a class C block or as a CIDR block. IPv6 prefix ranges are specified as a CIDR block.
The step size between consecutive addresses is controlled with the Step parameter, which defaults to 1. Step of other than 1 can be useful where you need to allocate subnets of more than one address, rather than individual host addresses.
Advanced configuration for address pools is supported with optional parameters PoolGroup and Priority. A PoolGroup defines a name to group multiple pools with different priorities set by Priority. These parameters are available for AddAddressQuery (for more information, see Section 3.114.6. AllocateQuery) and the values inserted in SQL can later be used by customised allocation queries and procedures.
The following example defines two pools of addresses. The first pool is called 'pool1'. It contains addresses in the ranges 192.1.1.1 to 192.1.1.50 (inclusive) and 192.1.1.60 to 192.1.1.120 (inclusive) and the entire 192.1.2.0 class C block. The IP Subnet mask for each address is 255.255.255.255. The second pool is called ‘pool2’ and contains addresses in the range 192.2.2.62 to 192.2.2.99 (inclusive). The third pool is called 'pool3' and contains 256 IPv6 prefixes with prefix length of 64 bits.
PoolGroup, PoolPriority and NasIdentifier are made available for AddAddressQuery (for more information, see Section 3.114.12. AddAddressQuery) and can later be used by FindQuery (for more information, see Section 3.114.4. FindQuery) and other queries that need to up suitable addresses.
<AddressAllocator SQL>
      .....
      # Defines the addresses that we are prepared 
      # to allocate:
      <AddressPool pool1>
            Subnetmask 255.255.255.0
            DNSServer 10.1.1.1
            Range 192.1.1.1 192.1.1.50
            Range 192.1.1.60 192.1.1.120
            Range 192.1.2.0/24
      </AddressPool>
      <AddressPool pool2>
            Subnetmask 255.255.255.127
            Range 192.2.2.62 192.2.2.99
      </AddressPool>
      <AddressPool pool3>
            Subnetmask /56
            Range 2001:db8:100::/48
            #PoolGroup group1
            #PoolPriority 1
            NasIdentifier 2001:db8:2::2
      </AddressPool>
</AddressAllocator>