rlm_sqlippool allocating duplicate IPs

[Phil Mayers]

All,

I've just become aware of a rather annoying problem with our PPTP VPN 
server. Sometimes, a client will connect, disconnect and reconnect in 
quick succession. In these circumstances, there seems to be a window 
which an IP can remain allocated to a live VPN session, but is marked as 
free in the SQL table, causing subsequent clients to connect, be 
allocated the IP, and fail to get any connectivity.

Unfortunately at this time I don't have a debug trace or even detail 
logs of the accounting traffic for an instance of this, but I was 
wondering if anyone had seen something similar. We're running FreeRadius 
2.1.4 on RHEL5 against a postgres 8.3 database.

I am wondering if I've got a logic error in my sqlippool queries; 
however, they're the stock ones for postgres that come with the 
distribution with two additions:

  1. Any time a row is updated, two extra columns are added to the update:

   updatedby = '%{localopts.server}', updatedat = now()

  2. I've made the WHERE clause on the stop-clear and alive-update more 
specific, changing it from:

   WHERE nasipaddress = '%{NAS-IP-Address}' \
   AND pool_key = '${pool-key}'"

...to:

   WHERE nasipaddress = '%{Nas-IP-Address}' \
   AND pool_key = '${pool-key}' \
   AND username = '%{SQL-User-Name}' \
   AND callingstationid = '%{Calling-Station-Id}' \
   AND framedipaddress = '%{Framed-IP-Address}'"

Obviously the stock queries will try to allocate the same IP to 
subsequent connections for the same username, which is good and desired 
for us, but in this case it seems to be somehow allocating the IP, 
allocating the *same* IP to the 2nd connection, then marking it as 
unused when the first, short-lived client dies (and the accounting stop 
presumably comes in late)

I can't see how this is happening, since the postgres module has proper 
transaction support and rlm_sqlippool issues the "START TRANSACTION" 
queries around every allocate/update/free query, unless it's the 
postgres isolation level being wrong - in which case I'd expect others 
to see it.

Yours, baffled...