Proposal for LNS load-balancing with Freeradius

[Mathieu Dessus]

Hello,

I was working on a solution for having round-robin LNS repartition with
Freeradius. Since I must replace several parameters and they must match
against each other (the Tunnel-Server-Auth-Id name must match the
Tunnel-Server-Endpoint IP), I had trouble with using an external script
returning random values.

So, my solutions consists in modifying the SQL groupreply request (in
sql.conf).
Instead of:

authorize_group_reply_query = "SELECT
${groupreply_table}.id,${groupreply_table}.GroupName,${groupreply_table}.Attribute,${groupreply_table}.Value,
${groupreply_table}.op  FROM ${groupreply_table},${usergroup_table}
WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' AND
${usergroup_table}.GroupName = ${groupreply_table}.GroupName ORDER BY
${groupreply_table}.id"

I put:

  authorize_group_reply_query = "SELECT
${groupreply_table}.id,${groupreply_table}.GroupName,${groupreply_table}.Attribute,substring_index(substring_index(${groupreply_table}.Value,
';', myrand.val),';',-1),${groupreply_table}.op  FROM
${groupreply_table},${usergroup_table},(select floor(1+rand()*2) as val)
as myrand WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' AND
${usergroup_table}.GroupName = ${groupreply_table}.GroupName ORDER BY
${groupreply_table}.id"


where the number in "rand()*2" must match the number of LNS.

The database value format must be either in the usual from (the value
itself) when only one value must be replied, either
"firstvalue;secondvalue..." separed by semicolon (in this case, it will
return randomly one of the values, and the same random number will be
used for all this request).

I saw only two drawbacks: you must restart Freeradius if you modify the
number of LNS and the separator character must no be used anywhere else.

Does anybody have some comments on this method, or a better method to do
the same thing ?

Sincerely,
Mathieu Dessus.