Multiple Customer Databases
Hi, I'm using freeradius 3.0.4 on a Red Hat Centos 7 and it seems I missed a fundamental point. I have several customers with their own database. I do logging accounting data to sql. My problem is that any accounting data of any customers is logged to any radacct tables. E.g. accounting data of customer1 is logged to (sql) customer1.radacct, but also to customer2.radacct and vise versa. /etc/raddb/mods-enabled/sql: sql customer1 { driver = "rlm_sql_mysql" dialect = "mysql" server = "localhost" port = 3306 login = "radius" password = "ffgfdfdgfdgdg" radius_db = "customer1" acct_table1 = "radacct" acct_table2 = "radacct" postauth_table = "radpostauth" authcheck_table = "radcheck" groupcheck_table = "radgroupcheck" authreply_table = "radreply" groupreply_table = "radgroupreply" usergroup_table = "radusergroup" delete_stale_sessions = yes pool { start = 5 min = 4 max = ${thread[pool].max_servers} spare = 3 uses = 0 lifetime = 0 idle_timeout = 60 } read_clients = yes client_table = "nas" $INCLUDE ${modconfdir}/${.:name}/main/${dialect}/queries.conf } sql customer2 { driver = "rlm_sql_mysql" dialect = "mysql" server = "localhost" port = 3306 login = "radius" password = "fgfdgdg" radius_db = "customer2" acct_table1 = "radacct" acct_table2 = "radacct" postauth_table = "radpostauth" authcheck_table = "radcheck" groupcheck_table = "radgroupcheck" authreply_table = "radreply" groupreply_table = "radgroupreply" usergroup_table = "radusergroup" delete_stale_sessions = yes pool { start = 5 min = 4 max = ${thread[pool].max_servers} spare = 3 uses = 0 lifetime = 0 idle_timeout = 60 } read_clients = yes client_table = "nas" $INCLUDE ${modconfdir}/${.:name}/main/${dialect}/queries.conf } ... /etc/raddb/sites-enabled/default: [..] accounting { .. customer1 customer2 .. } Any help would be appreciated. -- MrLINK
On 25/07/16 15:38, Oliver Kutscher wrote:
accounting { .. customer1 customer2 .. }
How are your customers distinguished? Different realms in the username? Other attributes in the RADIUS packet? You could try something like this: accounting { if (User-Name =~ /@customer1.com$/) { customer1 } elsif (User-Name =~ /@customer2.com$/) { customer2 } } This is a fairly clumsy approach, though. The *right* way of fixing it is to use a different virtual server for each customer so different customers' information can not be accidentally contaminated. http://freeradius.org/features/virtual_servers.html Cheers, Jonathan -- Jonathan Gazeley Senior Systems Administrator IT Services University of Bristol
Hi,
I have several customers with their own database. I do logging accounting data to sql. My problem is that any accounting data of any customers is logged to any radacct tables. E.g. accounting data of customer1 is logged to (sql) customer1.radacct, but also to customer2.radacct and vise versa.
because thats how you've configured it
accounting { customer1 customer2 }
so when your server goes through accounting section with a packet it does both of those. you will need to write some policy/unlang to ensure that customer1 SQL only gets called when relevant etc eg if (%{Called-Station-ID =~ /10\.90\.5/}) { customer1 } elsif (%{Called-Station-ID =~ /192\.168\.1/}) { customer2 } or somesuch decision alternatively (and much better!) use virtual-servers in the clients.conf and ensure that each customer gets their own V-S instance and then just call customer1 or cusotmer2 etc in the accounting section. alan
Hi, tahnk you very much! Using virtual servers solved my issue. Mit freundlichen Grüßen, i.A. Oliver Kutscher -- Postanschrift: ADDIX Internet Services GmbH Postfach 1225 D-24011 Kiel Tel: +49 431 7755 140 Fax: +49 431 7755 105 ok@addix.net www.addix.net Am 25.07.2016 um 17:23 schrieb A.L.M.Buxey@lboro.ac.uk:
Hi,
I have several customers with their own database. I do logging accounting data to sql. My problem is that any accounting data of any customers is logged to any radacct tables. E.g. accounting data of customer1 is logged to (sql) customer1.radacct, but also to customer2.radacct and vise versa.
because thats how you've configured it
accounting { customer1 customer2 }
so when your server goes through accounting section with a packet it does both of those.
you will need to write some policy/unlang to ensure that customer1 SQL only gets called when relevant etc eg
if (%{Called-Station-ID =~ /10\.90\.5/}) { customer1 } elsif (%{Called-Station-ID =~ /192\.168\.1/}) { customer2 }
or somesuch decision
alternatively (and much better!) use virtual-servers in the clients.conf and ensure that each customer gets their own V-S instance and then just call customer1 or cusotmer2 etc in the accounting section.
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (3)
-
A.L.M.Buxey＠lboro.ac.uk -
Jonathan Gazeley -
Oliver Kutscher