MYSQL for windows tables for freeradius
-
What tables would I need to configure users in MYSQL for windows to be used by freeradius in Pfsense ?
-
Look here : /usr/local/etc/raddb/mods-config/sql/main/mysql/ - where you can find the files needed for a SQL database (+tables) setup.
-
Thank you sir.
-
I know how to create the tables, I don't know what tables to create. Eg. username, password, description, etc.
-
@bahamasacademy said in MYSQL for windows tables for freeradius:
I don't know what tables to create
Strange.
Didn't you saw have a look at the /usr/local/etc/raddb/mods-config/sql/main/mysql/schema.sql ?Creating all the tables with their fields and settings, or 'execute' the schema.sql file when logged into the SQL server, and have the script file schema.sql do it for you.
The other file, setup.sql, will create a mysql user called 'radius' with password 'radpass' who has access rights to the crated database and tables. Edit the file to, at least, change the password, or change it later on, manually.It not only FreeRdius who works like this. Every process out there that uses sql comes with exact the same files, for easy database, user and table creation, so everything can be scripted.
-
Here is what I see
/usr/local/etc/raddb/mods-enabled/sqlsql sql1 {
database = "mysql"
driver = "rlm_sql_${database}"
dialect = "${database}"
server = "192.168.2.142"
port = 3306
login = "radius"
password = "Patric@2108"
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"
read_groups = yes
delete_stale_sessions = yes
logfile = ${logdir}/sqltrace.sql
read_clients = yes
client_table = "nas"
pool {
start = ${thread[pool].start_servers}
min = ${thread[pool].min_spare_servers}
max = 5
spare = ${thread[pool].max_spare_servers}
uses = 0
retry_delay = 60
lifetime = 0
idle_timeout = 60
}
group_attribute = "${.:instance}-SQL-Group"
$INCLUDE ${modconfdir}/${.:name}/main/${dialect}/queries.conf
}Here is the error message I get when I execute the file in MySQL
Error Code: 1064. You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near '{ database = "radius" driver = "rlm_sql_${database}" dialect = "${database}" ' at line 1 -
@bahamasacademy said in MYSQL for windows tables for freeradius:
/usr/local/etc/raddb/mods-enabled/sql
That's a FreeRadius config file. It doesn't contain any SQL commands ....
Why would you want to execute a config file ?Read again :
@Gertjan said in MYSQL for windows tables for freeradius:
.... look at the /usr/local/etc/raddb/mods-config/sql/main/mysql/schema.sql
?
-
Thank you for your patience in assisting me. I got everting working. One more thing, how do i export the clients table to a csv file so that i can add clients?
-
@bahamasacademy said in MYSQL for windows tables for freeradius:
how do i export the clients table to a csv file so that i can add clients?
You mean : how to import a csv ?
Or, export a table, edit it and re import again ?Probably the official way : MariaDB how to import csv file ?
I installed also the PHP based phpmyadmin on the 'sql (mariadb) server, so I can export, imprt, edit, etc my tables.
i can add clients?
The sql tables are not used for the clients.
Only one table is used, 'radacct'.
The clients are still stored in a flat file on pfSense : this one : /usr/local/etc/raddb/mods-config/files/authorize.
This file is created and maintained by the GUI, this page : Services > FreeRADIUS > Users, so if you want to add or modify a Freeradius user, you have to use that pfSense GUI page.See here for more info : same forum, a couple of threads lower : Updating tables with SQL and data usage.
This means you have to change the pfSense (the GUI) FreRadius core files ..... -
I got it now. Thank you very much.
