Hello all, Ive been using Freeradius for a long time and about 5 months ago I made a change in my default layout so I could use Freeradius SQL-IPPool running over PostgreSQL. This is working almost fine but for some reason I cant figure out its giving the same IP address to two different clients. So, my network layout is something like this: [ freeradius ] -- [ external* mysql ] -- [ external* postgresql ] * external I mean as the databases are running in separate hardware but in the same physical network My customers are authenticating over PPPoE interfaces on two parallel servers that work as a backup for each other but they are running simultaneously. This would be layout for my clients: (internet) [ auth01/auth02 ] (clients) Auth01 and Auth02 can both communicate with Freeradius in the same physical network and they are getting there with no more than 1 ms (its a gigabit network that fairly is using 50 mbit). I am using MySQL and PostgreSQL for a reason: somehow the software I use to bill my customers can only work with MySQL radacct table and it was not able to read the radacct from PostgreSQL. So I am running accounting over MySQL and Authentication/Authorization over PostgreSQL (I will post the configuration files in the end of this message). So, returning to the problem, this is what is happening: http://www.cnett.com.br/nata/imagens/Capturar.JPG There is two clients using the SAME IP which is a problem. Looking into my Freeradius log file I could not find when cjunior got the IP I have JUST taise allocation: Fri Jan 27 12:20:03 2012 : Auth: Login OK: [cjunior] (from client LJS.AUTH.02 port 69822 cli 74:EA:3A:D1:FD:17) Fri Jan 27 12:20:03 2012 : Info: Allocated IP: 186.251.148.216 from main_pool (did cnett_pppoe cli 74:EA:3A:D1:FD:17 port 69822 user cjunior) Fri Jan 27 14:41:38 2012 : Info: Released IP 186.251.148.216 (did cnett_pppoe cli 74:EA:3A:D1:FD:17 user cjunior) Fri Jan 27 14:45:47 2012 : Auth: Login OK: [taise] (from client LJS.AUTH.02 port 69530 cli 00:02:72:89:34:C5) Fri Jan 27 14:45:47 2012 : Info: Allocated IP: 186.251.150.130 from main_pool (did cnett_pppoe cli 00:02:72:89:34:C5 port 69530 user taise) Fri Jan 27 15:18:01 2012 : Auth: Login OK: [cjunior] (from client LJS.AUTH.02 port 69668 cli 74:EA:3A:D1:FD:17) Fri Jan 27 15:18:01 2012 : Info: Allocated IP: 186.251.150.226 from main_pool (did cnett_pppoe cli 74:EA:3A:D1:FD:17 port 69668 user cjunior) Fri Jan 27 12:19:14 2012 : Info: Allocated IP: 186.251.150.136 from main_pool (did cnett_pppoe cli 00:02:72:89:34:C5 port 69799 user taise) Fri Jan 27 23:22:04 2012 : Info: Released IP 186.251.150.130 (did cnett_pppoe cli 00:02:72:89:34:C5 user taise) Mon Jan 30 08:19:05 2012 : Info: Released IP 186.251.150.5 (did cnett_pppoe cli 00:02:72:89:34:C5 user taise) Mon Jan 30 08:20:23 2012 : Auth: Login OK: [taise] (from client LJS.AUTH.02 port 69525 cli 00:02:72:89:34:C5) Mon Jan 30 08:20:23 2012 : Info: Allocated IP: 186.251.151.65 from main_pool (did cnett_pppoe cli 00:02:72:89:34:C5 port 69525 user taise) Mon Jan 30 08:23:54 2012 : Info: Released IP 186.251.151.65 (did cnett_pppoe cli 00:02:72:89:34:C5 user taise) Mon Jan 30 08:24:05 2012 : Auth: Login OK: [taise] (from client LJS.AUTH.01 port 350112 cli 00:02:72:89:34:C5) Mon Jan 30 08:24:05 2012 : Info: Allocated IP: 186.251.150.118 from main_pool (did cnett_pppoe cli 00:02:72:89:34:C5 port 350112 user taise) This is really strange. Bellow I am showing my PostgreSQL radippool table and there is just the taise record (ok, I know, it could not have both). http://www.cnett.com.br/nata/imagens/Capturar1.JPG Looking for problems I also checked the usage of CPU and RAM on PostgreSQL box and MySQL box but there is nothing I could use. For those who ask I change the pool_key because I was having duplicated pool_key from my NAS (Auth01 and Auth02) so I just made a change where the name of the server is showing. The problem with the duplicate IPs is older than the change so the pool_key is discarded. http://www.cnett.com.br/nata/imagens/cpu_usage_postgres-mysql.JPG Is it possible to someone help me finding a solution? -- Descrição: Descrição: 111027-nata_at_cnett
On Mon, Jan 30, 2012 at 7:43 PM, Nataniel Klug <listas.nata@cnett.com.br> wrote:
I’ve been using Freeradius for a long time and about 5 months ago I made a change in my default layout so I could use Freeradius SQL-IPPool running over PostgreSQL.
So you have ONLY one instance of sqlipool, backed by postgresql?
I am using MySQL and PostgreSQL for a reason: somehow the software I use to bill my customers can only work with MySQL radacct table and it was not able to read the radacct from PostgreSQL. So I am running accounting over MySQL and Authentication/Authorization over PostgreSQL (I will post the configuration files in the end of this message).
If you don't use mysql for sqlpipool then it's not relevant for this discussion. Focus on what you use for sqlippool
So, returning to the problem, this is what is happening:
http://www.cnett.com.br/nata/imagens/Capturar.JPG
There is two clients using the SAME IP which is a problem. Looking into my Freeradius log file I could not find when “cjunior” got the IP I have JUST “taise” allocation:
And what does freeradius debug log say? -- Fajar
Hello Fajar, Thank you for your reply. I will answer bellow:
So you have ONLY one instance of sqlipool, backed by postgresql?
If you don't use mysql for sqlpipool then it's not relevant for this discussion. Focus on what you use for sqlippool
[Nataniel Klug] It's true, I just use PostgreSQL for ippool: --------- /etc/raddb/sql.conf --------- sql sqlps { database = "postgresql" driver = "rlm_sql_${database}" server = "186.251.144.XX" port = 5432 login = "radius" password = "XX" radius_db = "radius" acct_table1 = "radacct" acct_table2 = "radacct" postauth_table = "radpostauth" authcheck_table = "radcheck" authreply_table = "radreply" groupcheck_table = "radgroupcheck" groupreply_table = "radgroupreply" usergroup_table = "radusergroup" deletestalesessions = yes sqltrace = no sqltracefile = ${logdir}/sqltrace.sql num_sql_socks = 30 connect_failure_retry_delay = 60 lifetime = 0 max_queries = 0 readclients = yes nas_table = "nas" $INCLUDE sql/${database}/dialup.conf } sql sqlmy { database = "mysql" driver = "rlm_sql_${database}" server = "186.251.144.XX" port = 3306 login = "radius" password = "XX" radius_db = "radius" acct_table1 = "radacct" acct_table2 = "radacct" postauth_table = "radpostauth" authcheck_table = "radcheck" authreply_table = "radreply" groupcheck_table = "radgroupcheck" groupreply_table = "radgroupreply" usergroup_table = "usergroup" deletestalesessions = yes sqltrace = no sqltracefile = ${logdir}/sqltrace.sql num_sql_socks = 60 connect_failure_retry_delay = 60 lifetime = 0 max_queries = 0 readclients = no nas_table = "nas" $INCLUDE sql/${database}/dialup.conf } --------- /etc/raddb/sqlippool.conf --------- sqlippool { sql-instance-name = "sqlps" ippool_table = "radippool" lease-duration = 720 pool-key = "%{NAS-Identifier}-%{NAS-Port}" $INCLUDE sql/postgresql/ippool.conf sqlippool_log_exists = "Existing IP: %{reply:Framed-IP-Address} \ (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})" sqlippool_log_success = "Allocated IP: %{reply:Framed-IP-Address} from %{control:Pool-Name} \ (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})" sqlippool_log_clear = "Released IP %{Framed-IP-Address}\ (did %{Called-Station-Id} cli %{Calling-Station-Id} user %{User-Name})" sqlippool_log_failed = "IP Allocation FAILED from %{control:Pool-Name} \ (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})" sqlippool_log_nopool = "No Pool-Name defined \ (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})" }
And what does freeradius debug log say?
-- Fajar
[Nataniel Klug] This is a production server and I can't run it in debug mode (-X) and as it's not been a common problem it appear just once or twice during a day I can't be monitoring it all the time. Is there any other way to make the debug mode?
On Mon, Jan 30, 2012 at 8:47 PM, Nataniel Klug <listas.nata@cnett.com.br> wrote:
And what does freeradius debug log say?
[Nataniel Klug] This is a production server and I can't run it in debug mode (-X) and as it's not been a common problem it appear just once or twice during a day I can't be monitoring it all the time. Is there any other way to make the debug mode?
Try "man radmin", look for "debug". That's assuming you setup control-socket correctly (i.e. edit the file, symlink to sites-enabled, etc). Last time I check the output is slightly different (e.g. you can't see request attributes), so if you can't make it work with that then your only option is to run FR in debug mode. It's important because it can tell whether the attribute came from sqlipool, or whether some other module overwrite it (e.g. files, sql), or whether FR is sending the correct ip address, but the client simply ignores it. -- Fajar
Fajar, I found the problem... It's Acct-Interim-Time that is not set for some groups and I can't find why... I am solving the problem now... Thank you for your help! --
-----Mensagem original----- De: freeradius-users-bounces+listas.nata=cnett.com.br@lists.freeradius.org [mailto:freeradius-users- bounces+listas.nata=cnett.com.br@lists.freeradius.org] Em nome de Fajar A. Nugraha Enviada em: segunda-feira, 30 de janeiro de 2012 09:53 Para: FreeRadius users mailing list Cc: Agner Vainer [ CNett ] Assunto: Re: Problems using SQL IP Pool
On Mon, Jan 30, 2012 at 8:47 PM, Nataniel Klug <listas.nata@cnett.com.br> wrote:
And what does freeradius debug log say?
[Nataniel Klug] This is a production server and I can't run it in debug mode (-X) and as it's not been a common problem it appear just once or twice during a day I can't be monitoring it all the time. Is there any other way to make the debug mode?
Try "man radmin", look for "debug". That's assuming you setup control-socket correctly (i.e. edit the file, symlink to sites-enabled, etc).
Last time I check the output is slightly different (e.g. you can't see request attributes), so if you can't make it work with that then your only option is to run FR in debug mode. It's important because it can tell whether the attribute came from sqlipool, or whether some other module overwrite it (e.g. files, sql), or whether FR is sending the correct ip address, but the client simply ignores it.
-- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (2)
-
Fajar A. Nugraha -
Nataniel Klug