Using multiple databases on the same freeRadius instance
Hi Guys, I am wondering if there are any way to use two different databases within the same freeRadius instance? Currently, I am using the default MySQL schema for AAA. I am inserting additional attributes in the pre-proxy configuration of my site. The attribute information comes from the same database. This works perfectly. I now have a site where I need to do the same but the additional attribute information needs to come from a different database (existing database on the client side), other than the AAA database. Is there a way to do this? Regards Gabriel
Hi, The quickest way is to create two named instances of the sql module like this: sql sql_first_database { driver = "..." .... #rest of the db config } sql sql_second_database { driver = "..." .... #rest of the db config } and then, instead of calling 'sql' module call your own ones: sql_first_database sql_second_database you can also use them in xlat: %{sql_first_database:INSERT INTO .... } kind regards Pshem On Tue, 14 Feb 2017 at 20:09 Gabriel Marais <gabriel.j.marais@gmail.com> wrote:
Hi Guys,
I am wondering if there are any way to use two different databases within the same freeRadius instance?
Currently, I am using the default MySQL schema for AAA. I am inserting additional attributes in the pre-proxy configuration of my site. The attribute information comes from the same database. This works perfectly.
I now have a site where I need to do the same but the additional attribute information needs to come from a different database (existing database on the client side), other than the AAA database.
Is there a way to do this?
Regards
Gabriel - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On 2017/02/14 10:26 AM, Pshem Kowalczyk wrote:
> Hi,
>
> The quickest way is to create two named instances of the sql module like
> this:
>
> sql sql_first_database {
> driver = "..."
> .... #rest of the db config
> }
i left the initial config in place.
>
> sql sql_second_database {
> driver = "..."
> .... #rest of the db config
> }
i then created a new config file with the second db connection details
and included the file in radiusd.conf
from the radius debug, i can see it is loading the second config file:-
Wed Feb 15 14:00:18 2017 : Debug: including configuration file
/etc/freeradius/sql_admindb.conf
This is the contents of the file:-
sql_admin_db {
database = "mysql"
driver = "rlm_sql_${database}"
server = "localhost"
login = "radius"
password = "xxx"
radius_db = "xxx_adminportal"
# Print all SQL statements when in debug mode (-x)
sqltrace = no
sqltracefile = ${logdir}/sqltrace.sql
num_sql_socks = ${thread[pool].max_servers}
connect_failure_retry_delay = 60
lifetime = 0
max_queries = 0
}
Debug output shows it loads the initial sql config and makes the
connections to the db:-
Wed Feb 15 14:00:18 2017 : Debug: Module: Linked to module rlm_sql
Wed Feb 15 14:00:18 2017 : Debug: Module: Instantiating module "sql"
from file /etc/freeradius/sql.conf
Wed Feb 15 14:00:18 2017 : Debug: sql {
Wed Feb 15 14:00:18 2017 : Debug: driver = "rlm_sql_mysql"
Wed Feb 15 14:00:18 2017 : Debug: server = "localhost"
Wed Feb 15 14:00:18 2017 : Debug: port = ""
Wed Feb 15 14:00:18 2017 : Debug: login = "radius"
Wed Feb 15 14:00:18 2017 : Debug: password = "xxx"
Wed Feb 15 14:00:18 2017 : Debug: radius_db = "radius"
Wed Feb 15 14:00:18 2017 : Info: rlm_sql (sql): Driver rlm_sql_mysql
(module rlm_sql_mysql) loaded and linked
Wed Feb 15 14:00:18 2017 : Info: rlm_sql (sql): Attempting to connect to
radius@localhost:/radius
Wed Feb 15 14:00:18 2017 : Debug: rlm_sql (sql): starting 0
Wed Feb 15 14:00:18 2017 : Info: rlm_sql (sql): Attempting to connect
rlm_sql_mysql #0
Wed Feb 15 14:00:18 2017 : Info: rlm_sql_mysql: Starting connect to
MySQL server for #0
Wed Feb 15 14:00:18 2017 : Info: rlm_sql (sql): Connected new DB handle, #0
However, no connections are made or no reference is made to the second
db config.
>
> and then, instead of calling 'sql' module call your own ones:
>
> sql_first_database
> sql_second_database
>
> you can also use them in xlat:
>
> %{sql_first_database:INSERT INTO .... }
I am using xlat statements in the proxy areas of the config where i need
to call and insert the custom attributes.
I'm not sure why the db connections are not made to the second db...
>
> kind regards
> Pshem
>
>
>
>
> On Tue, 14 Feb 2017 at 20:09 Gabriel Marais <gabriel.j.marais@gmail.com>
> wrote:
>
>> Hi Guys,
>>
>> I am wondering if there are any way to use two different databases
>> within the same freeRadius instance?
>>
>> Currently, I am using the default MySQL schema for AAA. I am inserting
>> additional attributes in the pre-proxy configuration of my site. The
>> attribute information comes from the same database. This works perfectly.
>>
>> I now have a site where I need to do the same but the additional
>> attribute information needs to come from a different database (existing
>> database on the client side), other than the AAA database.
>>
>>
>> Is there a way to do this?
>>
>>
>> Regards
>>
>> Gabriel
>> -
>> List info/subscribe/unsubscribe? See
>> http://www.freeradius.org/list/users.html
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
--
Regards
Gabriel Marais
Office: +27 861 466 546 x 7001
Mobile: +27 83 663 2222
Mail: gabriel.j.marais@gmail.com
Unit 11, Ground Floor, Berkley Office Park
Cnr Bauhinia & Witch Hazel Str,
Highveld, Centurion, South-Africa
0157
PO Box 15846, Lyttelton, South Africa, 0140
On Feb 15, 2017, at 7:11 AM, Gabriel Marais <gabriel.j.marais@gmail.com> wrote:
i then created a new config file with the second db connection details and included the file in radiusd.conf
Good..
from the radius debug, i can see it is loading the second config file:- Wed Feb 15 14:00:18 2017 : Debug: including configuration file /etc/freeradius/sql_admindb.conf
That's good.
However, no connections are made or no reference is made to the second db config.
Because you didn't *use* the module anywhere. List "sql_admin_db" in a virtual server. If you don't use the module anywhere, the server notices that, and doesn't open connections to SQL. Because they would never be used. Alan DeKok.
On 2017/02/15 2:16 PM, Alan DeKok wrote:
On Feb 15, 2017, at 7:11 AM, Gabriel Marais <gabriel.j.marais@gmail.com> wrote:
i then created a new config file with the second db connection details and included the file in radiusd.conf
Good..
from the radius debug, i can see it is loading the second config file:- Wed Feb 15 14:00:18 2017 : Debug: including configuration file /etc/freeradius/sql_admindb.conf
That's good.
However, no connections are made or no reference is made to the second db config.
When testing with radclient, i see the following in the debug:- Wed Feb 15 14:13:49 2017 : Info: +++update proxy-request { Wed Feb 15 14:13:49 2017 : Info: WARNING: Unknown module "sql_admin_db" in string expansion "%" Wed Feb 15 14:13:49 2017 : Info: WARNING: Unknown module "sql_admin_db" in string expansion "%" Wed Feb 15 14:13:49 2017 : Info: WARNING: Unknown module "sql_admin_db" in string expansion "%" Wed Feb 15 14:13:49 2017 : Info: WARNING: Unknown module "sql_admin_db" in string expansion "%" Wed Feb 15 14:13:49 2017 : Info: WARNING: Unknown module "sql_admin_db" in string expansion "%" Wed Feb 15 14:13:49 2017 : Info: WARNING: Unknown module "sql_admin_db" in string expansion "%" The config file for that part looks like :- --snip if(Acct-Status-Type == Start){ update proxy-request { subscriberID += "%{sql_admin_db:SELECT account_name FROM app_dslaccount WHERE account_name = '%{User-Name}'}" productID += "%{sql_admin_db:SELECT current_product_id FROM app_dslaccount WHERE account_name = '%{User-Name}'}" --snip My DB Connections file looks like :- sql_admin_db { database = "mysql" driver = "rlm_sql_${database}" server = "localhost" login = "radius" password = "L0g1T3ch2017!" radius_db = "ionline_adminportal" # Print all SQL statements when in debug mode (-x) sqltrace = no sqltracefile = ${logdir}/sqltrace.sql num_sql_socks = ${thread[pool].max_servers} connect_failure_retry_delay = 60 lifetime = 0 max_queries = 0 } Thanks for the help...
Because you didn't *use* the module anywhere.
List "sql_admin_db" in a virtual server.
If you don't use the module anywhere, the server notices that, and doesn't open connections to SQL. Because they would never be used.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Feb 15, 2017, at 7:34 AM, Gabriel Marais <gabriel.j.marais@gmail.com> wrote:
My DB Connections file looks like :- sql_admin_db {
That's wrong. Please follow the examples You need to use: sql sql_admin_db { Which means "use the SQL module, but we're making a variant of it, called sql_admin_db" Alan DeKok.
On 2017/02/15 2:36 PM, Alan DeKok wrote:
On Feb 15, 2017, at 7:34 AM, Gabriel Marais <gabriel.j.marais@gmail.com> wrote:
My DB Connections file looks like :- sql_admin_db {
That's wrong. Please follow the examples
You need to use:
sql sql_admin_db {
Changed my sql connection file to :- sql sql_admin_db { database = "mysql" driver = "rlm_sql_${database}" server = "localhost" login = "radius" My queries still looks like:- if(Acct-Status-Type == Start){ update proxy-request { subscriberID += "%{sql_admin_db:SELECT account_name FROM app_dslaccount WHERE account_name = '%{User-Name}'}" productID += "%{sql_admin_db:SELECT current_product_id FROM app_dslaccount WHERE account_name = '%{User-Name}'}" Error in debug:- Wed Feb 15 14:47:04 2017 : Info: WARNING: Unknown module "sql_admin_db" in string expansion "%" Wed Feb 15 14:47:04 2017 : Info: WARNING: Unknown module "sql_admin_db" in string expansion "%"
Which means "use the SQL module, but we're making a variant of it, called sql_admin_db"
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- Regards Gabriel Marais Office: +27 861 466 546 x 7001 Mobile: +27 83 663 2222 Mail: gabriel.j.marais@gmail.com Unit 11, Ground Floor, Berkley Office Park Cnr Bauhinia & Witch Hazel Str, Highveld, Centurion, South-Africa 0157 PO Box 15846, Lyttelton, South Africa, 0140
On Feb 15, 2017, at 7:50 AM, Gabriel Marais <gabriel.j.marais@gmail.com> wrote:
Error in debug:-
Wed Feb 15 14:47:04 2017 : Info: WARNING: Unknown module "sql_admin_db" in string expansion "%" Wed Feb 15 14:47:04 2017 : Info: WARNING: Unknown module "sql_admin_db" in string expansion "%"
Did you tell the server to *use* the sql_admin_db module anywhere, as I suggested? If not, that's the problem. Read radiusd.conf. Look for "instantiate". Follow the instructions. Alan DeKok.
On 2017/02/15 2:53 PM, Alan DeKok wrote:
On Feb 15, 2017, at 7:50 AM, Gabriel Marais <gabriel.j.marais@gmail.com> wrote:
Error in debug:-
Wed Feb 15 14:47:04 2017 : Info: WARNING: Unknown module "sql_admin_db" in string expansion "%" Wed Feb 15 14:47:04 2017 : Info: WARNING: Unknown module "sql_admin_db" in string expansion "%"
Did you tell the server to *use* the sql_admin_db module anywhere, as I suggested?
If not, that's the problem.
Read radiusd.conf. Look for "instantiate". Follow the instructions.
Thanks Alan, I got it working by adding it under the instantiate section. Thanks to everyone for the help! Gabriel
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- Regards Gabriel Marais Office: +27 861 466 546 x 7001 Mobile: +27 83 663 2222 Mail: gabriel.j.marais@gmail.com Unit 11, Ground Floor, Berkley Office Park Cnr Bauhinia & Witch Hazel Str, Highveld, Centurion, South-Africa 0157 PO Box 15846, Lyttelton, South Africa, 0140
Hi,
i then created a new config file with the second db connection details and included the file in radiusd.conf
<snip>
from the radius debug, i can see it is loading the second config file:- Wed Feb 15 14:00:18 2017 : Debug: including configuration file /etc/freeradius/sql_admindb.conf
err, yes. just wondering WHY you are doing it like that? just put the module config into the mods-enabled directory (3.x) or modules/ directory (2.x) any wierd ways and you are on your own! ;-) alan
Hi,
I am wondering if there are any way to use two different databases within the same freeRadius instance?
yes, of course.
Currently, I am using the default MySQL schema for AAA. I am inserting additional attributes in the pre-proxy configuration of my site. The attribute information comes from the same database. This works perfectly.
I now have a site where I need to do the same but the additional attribute information needs to come from a different database (existing database on the client side), other than the AAA database.
just instantiate a new database connector - copy the DB config/type you want, give it a name eg instead of sql { .... } it is sql other_db { .... } and then use that name in the places you need %{other_db:select name from table} etc alan
participants (4)
-
A.L.M.Buxey＠lboro.ac.uk -
Alan DeKok -
Gabriel Marais -
Pshem Kowalczyk