IP Pools Question

[Nathan Ward]

> On 23/03/2021, at 9:53 AM, Richard J Palmer <richard at merula.net> wrote:
> 
> 
> Good Evening
> 
> 
> We have FreeRadius 3.0.20 running here quite happily - but have a very small issue we' like some help with
> 
> Some of our connections use ippools (running from a mssql server)
> 
> As a general rule this works exactly as it should
> 
> Very occasionally the sql server will time out - maybe once or twice a day due to transactions clashing
> 
> When this happens the session comes on-line but is not issued a IP.
> 
> What I'd ideally like to do is simply reject that connection IF that happens which will force the router to log in again - it will then get an IP and will be happy
> 
> Is there an easy way to catch this and reject the connection (or even re-try the ippool module) ?

Hi,

Firstly, I would suggest working on the database and queries so that you don’t get timeouts in the database because of transactions clashing. You should be able to use things like row based locking and SELECT .. FOR UPDATE (or equivalent) and other things to avoid deadlocks etc. (I presume that deadlocks are what you are getting when you say “transactions clashing”). This is a vastly superior solution in my view - if the database is causing problems, fix the database rather than patching over it elsewhere.

I did work on this for postgresql a year or so back, and I note that there are now stored procedures for mssql in 3.0.21 (mssql lacks SELECT .. FOR UPDATE and the stored procedure says it’s done to achieve the same thing) - so maybe it’s worth upgrading to 3.0.21 and see if that fixes it. You can probably just pull over the contents of raddb/sql/ippool/mssql rather than upgrade the server, but of course you should upgrade to 3.0.21 if you can. Note that the stored procedures are disabled by default, so check out queries.conf for how to use them, and you’ll need to load them from procedure.sql.


If that’s not possible for some reason, you can deal with this in the way you’re asking. sqlippool returns noop if it doesn’t assign an address. It also doesn’t set the attribute (presumably Framed-IP-Address).

So, there’s several things you can do:
1) Check for the return code of sqlippool, and if it’s noop then reject:

sqlippool {
  noop = reject
}

or

sqlippool
if (noop) {
  reject
}

2) Check to see if Framed-IP-Address is set:

sqlippool
if (reply:Framed-IP-Address !* ANY) {
  reject
}

3) Define two sqlippool module instances with the same parameters, and use module failover to try one then the other and then reject (if you think that two is enough):

redundant {
  sqlippool1
  sqlippool2
  reject
}


Perhaps the 3rd option is the best, because it means that if you can retry immediately and get a good answer, you don’t trouble the NAS - which might mean a better experience for the end user.

--
Nathan Ward