rlm_sqlippool

[Chris Knipe]

> It is ONLY tested on Postgresql 8.1.x (On 32bit and 64bit SUSE Linux) 
> although
> I expect it should work fine on any 8.X version of postgresql and probably
> earlier versions as well. If you want to run it on a different database, 
> the
> driver itself should support it, but you will need to modify the schema 
> and
> the queries. If you get it working please let us know (And send us your
> schema and queries)

See below.  It can more than likely do with more indexes though.  I'm at 
this stage obviously only experimenting...   I'm still checking, but I'm 
*baffled* as to why the rlm_sqlippool won't reconnect to the database then! 
As you said, it uses the SQL driver, whether it's PostGRE, mySQL, MSSQL, 
Oracle, surely, the reconnections are handled in the sql driver itself and 
not the module...   Alan, anything I can look at perhaps???

My structures below should be quick and easy to understand.  I'm sure 
there's mistakes in it as well (which I hope will be pointed out to me), and 
I hope other SQL servers will support INET_ATON() and INET_NTOA. Perhaps add 
these as variables in FreeRadius (Alan?).  Considering pools are moving to 
SQL as well now  -  which is VERY good IMHO, I think it's a major waiste of 
space to allocate a VARCHAR(16) (at the minimum) to hold a IP Address in a 
database, when we can do it as a integer...

Cheers
Chris



CREATE TABLE `IPPools` (
  `EntryID` bigint(21) NOT NULL auto_increment,
  `GroupName` varchar(64) NOT NULL,
  `IPAddress` int(5) unsigned NOT NULL,
  `NASIPAddress` int(5) unsigned NOT NULL,
  `CallingStationID` varchar(50) default NULL,
  `PoolKey` char(33) NOT NULL,
  `ExpireTime` datetime default NULL,
  PRIMARY KEY  (`EntryID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


# This series of queries allocates an IP address
allocate-clear = "UPDATE GroupIPPools SET NASIPAddress = '0',
                                      PoolKey = MD5('0'),
                                      CallingStationID = NULL,
                                      ExpireTime = NOW() - INTERVAL 1 SECOND
                                WHERE PoolKey = 
MD5(CONCAT('%{NAS-IP-Address}', '%{Calling-Station-Id}'))"
allocate-find = "SELECT INET_NTOA(IPAddress)
                   FROM GroupIPPools
                  WHERE GroupID = '%{check:Pool-Name}' AND
                        ExpireTime < NOW()
                  ORDER BY GroupID, (CallingStationID <> 
'%{Calling-Station-Id}'), ExpireTime, RAND()
                  LIMIT 1 FOR UPDATE"
allocate-update = "UPDATE GroupIPPools SET NASIPAddress = 
INET_ATON('%{NAS-IP-Address}'),
                                           PoolKey = 
MD5(CONCAT('%{NAS-IP-Address}',
                                           '%{Calling-Station-Id}')),
                                           CallingStationID = 
'%{Calling-Station-Id}',
                                           ExpireTime = NOW() + INTERVAL 
${lease-duration} SECOND
                                     WHERE IPAddress = INET_ATON('%I')"

# This series of queries frees an IP number when an accounting START record 
arrives
start-update = "UPDATE GroupIPPools SET ExpireTime = NOW() + INTERVAL %J 
SECOND
                                  WHERE NASIPAddress = INET_ATON('%n') AND
                                        IPAddress = 
INET_ATON('%{Framed-IP-Address}')"

# This series of queries frees an IP number when an accounting STOP record 
arrives
stop-clear = "UPDATE GroupIPPools SET NASIPAddress = '0',
                                      PoolKey = MD5('0'),
                                      CallingStationID = NULL,
                                      ExpireTime = NOW() - INTERVAL 1 SECOND
                                WHERE NASIPAddress = 
INET_ATON('%{Nas-IP-Address}') AND
                                      PoolKey = 
MD5(CONCAT('%{NAS-IP-Address}', '${pool-key}')) AND
                                      CallingStationID = 
'%{Calling-Station-Id}' AND
                                      IPAddress = 
INET_ATON('%{Framed-IP-Address}')"

# This series of queries frees an IP number when an accounting ALIVE record 
arrives
alive-update = "UPDATE GroupIPPools SET ExpireTime = NOW() + INTERVAL 
${lease-duration} SECOND
                                  WHERE NASIPAddress = 
INET_ATON('%{Nas-IP-Address}') AND
                                        PoolKey = 
MD5(CONCAT('%{NAS-IP-Address}', '%{Calling-Station-Id}')) AND
                                        CallingStationID = 
'%{Calling-Station-Id}' AND
                                        IPAddress = 
INET_ATON('%{Framed-IP-Address}')"

# This series of queries frees the IP numbers allocate to a NAS when an 
accounting ON record arrives
on-clear = "UPDATE GroupIPPools SET NASIPaddress = '0',
                                    PoolKey = MD5('0'),
                                    CallingStationID = NULL,
                                    ExpireTime = NOW() - INTERVAL 1 SECOND
                              WHERE NASIPaddress = 
INET_ATON('%{Nas-IP-Address}') AND
                                    CallingStationId = 
'%{Calling-Station-Id}' AND
                                    IPAddress = 
INET_ATON('%{Framed-IP-Address}')"

# This series of queries frees the IP numbers allocate to a NAS when an 
accounting OFF record arrives
off-clear = "UPDATE GroupIPPools SET NASIPAddress = '0',
                                     PoolKey = MD5('0'),
                                     CallingStationID = NULL,
                                     ExpireTime = NOW() - INTERVAL 1 SECOND
                              WHERE  NASIPAddress = 
INET_ATON('%{Nas-IP-Address}') AND
                                     CallingStationID = 
'%{Calling-Station-Id}' AND
                                     IPAddress = 
INET_ATON('%{Framed-IP-Address}')"