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}')"
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
