Cannot get MySQL backend to work in Ubuntu 17.04
Hiya, I've been trying for a while to get MySQL radius backend to work but only had success with the basic auth mode. select * from radpostauth; ^ Shows nothing, for some reason freeRADIUS doesn't even write there even though the log claims it does so successfully. Here is everything I tried, step by step. You can either open this link and read it somehow formatted on my PrivateBin instance, or you can read it in plain text posted below the link. https://paste.c0rn3j.com/?44f340705be383c4#6nPNN39P98jGng38weWx0223pyGU7UQOY... Create new LXC container running Ubuntu 17.04(I doubt this has any effect on my problems, but noting it anyways) and login as root. apt update apt install freeradius-mysql Package: freeradius Version: 3.0.12+dfsg-4ubuntu1 Go by https://wiki.freeradius.org/guide/Basic-configuration-HOWTO add >>> c0rn3jj Cleartext-Password := "123" <<< to the first line of /etc/freeradius/3.0/users systemctl restart freeradius radtest c0rn3jj 123 localhost 0 testing123 ^ Access-Accept. go by https://wiki.freeradius.org/guide/SQL-HOWTO-for-freeradius-3.x-on-Debian-Ubu... apt install mariadb-server Package: mariadb-server Version: 10.1.22-3 mysql -uroot -p ^default install leaves root with empty password CREATE DATABASE radius; exit mysql -uroot -p radius < /etc/freeradius/3.0/mods-config/sql/main/mysql/schema.sql /etc/freeradius/3.0/mods-config/sql/main/mysql/setup.sql ^ change password from radpass to V9WcNpFEfY69MhuL mysql -uroot -p radius < /etc/freeradius/3.0/mods-config/sql/main/mysql/setup.sql /etc/freeradius/3.0/mods-available/sql ^ uncomment server/port/login/password and change password to V9WcNpFEfY69MhuL, change dialect to mysql ln -s /etc/freeradius/3.0/mods-available/sql /etc/freeradius/3.0/mods-enabled/sql /etc/freeradius/3.0/sites-available/default ^ guide says to uncomment sql in the authorize block, however it is already uncommented but there's a - sign before it? -sql? I did nothing here. Same story with the accounting block. Same stuff with inner-tunnel. "Your radiusd.conf should then look something like this" "then"? I did nothing with the radiusd.conf, moreover there's no authoriSe or authorize block either. Do nothing with the file. Now I get to the Populating SQL section insert into radcheck (username,attribute,op,value) values("c0rn3j", "Cleartext-Password", ":=", "123"); select * from radcheck; radtest c0rn3j 123 localhost 0 testing123 ^ fail insert into radreply (username,attribute,op,value) values("c0rn3j", "Framed-IP-Address", ":=", "1.2.3.4"); radtest c0rn3j 123 localhost 0 testing123 ^fail Client output - https://paste.c0rn3j.com/?0af6cf0b0aa86d67#Lo53mu+IQ2RQPQOvMl/B0nxH+0/Fal4cq... systemctl stop freeradius /usr/sbin/freeradius -X > /tmp/radlog -X log either on https://paste.c0rn3j.com/?f6dc7f8decd19541#QheezTe5NftT6qj1furUsZRL4TL90QSmC... or https://ptpb.pw/n7Re
On 24 Apr 2017, at 11:47, Martin Rys <spleefer90@gmail.com> wrote:
Here is everything I tried, step by step.
That's a little hard to follow. You included the debug output, which is what we need though. The relevant section is this - your SQL queries aren't returning data:
(1) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id (1) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'c0rn3j' ORDER BY id (1) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'c0rn3j' ORDER BY id (1) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority (1) sql: --> SELECT groupname FROM radusergroup WHERE username = 'c0rn3j' ORDER BY priority (1) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'c0rn3j' ORDER BY priority (1) sql: User not found in any groups
If you run the query manually (SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'c0rn3j' ORDER BY id), does it return a user? Did you intend to use groups? Regards, Adam Bishop gpg: E75B 1F92 6407 DFDF 9F1C BF10 C993 2504 6609 D460 jisc.ac.uk Jisc is a registered charity (number 1149740) and a company limited by guarantee which is registered in England under Company No. 5747339, VAT No. GB 197 0632 86. Jisc’s registered office is: One Castlepark, Tower Hill, Bristol, BS2 0JA. T 0203 697 5800. Jisc Services Limited is a wholly owned Jisc subsidiary and a company limited by guarantee which is registered in England under company number 2881024, VAT number GB 197 0632 86. The registered office is: One Castle Park, Tower Hill, Bristol BS2 0JA. T 0203 697 5800.
If you run the query manually does it work?
Yup! MariaDB [radius]> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'c0rn3j' ORDER BY id; +----+----------+--------------------+-------+----+ | id | username | attribute | value | op | +----+----------+--------------------+-------+----+ | 1 | c0rn3j | Cleartext-Password | 123 | := | +----+----------+--------------------+-------+----+ 1 row in set (0.00 sec)
Did you intend to use groups? I actually don't know if I will need them, I do not think I will, which is why I didn't assign the user to a group or create any group. As far as I understand RADIUS I only want simple user auth, for which I don't need groups?
Thanks a lot for replying! On Mon, Apr 24, 2017 at 12:55 PM, Adam Bishop <Adam.Bishop@jisc.ac.uk> wrote:
On 24 Apr 2017, at 11:47, Martin Rys <spleefer90@gmail.com> wrote:
Here is everything I tried, step by step.
That's a little hard to follow. You included the debug output, which is what we need though.
The relevant section is this - your SQL queries aren't returning data:
(1) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id (1) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'c0rn3j' ORDER BY id (1) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'c0rn3j' ORDER BY id (1) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority (1) sql: --> SELECT groupname FROM radusergroup WHERE username = 'c0rn3j' ORDER BY priority (1) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'c0rn3j' ORDER BY priority (1) sql: User not found in any groups
If you run the query manually (SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'c0rn3j' ORDER BY id), does it return a user?
Did you intend to use groups?
Regards,
Adam Bishop
gpg: E75B 1F92 6407 DFDF 9F1C BF10 C993 2504 6609 D460
jisc.ac.uk
Jisc is a registered charity (number 1149740) and a company limited by guarantee which is registered in England under Company No. 5747339, VAT No. GB 197 0632 86. Jisc’s registered office is: One Castlepark, Tower Hill, Bristol, BS2 0JA. T 0203 697 5800.
Jisc Services Limited is a wholly owned Jisc subsidiary and a company limited by guarantee which is registered in England under company number 2881024, VAT number GB 197 0632 86. The registered office is: One Castle Park, Tower Hill, Bristol BS2 0JA. T 0203 697 5800.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Apr 24, 2017, at 6:47 AM, Martin Rys <spleefer90@gmail.com> wrote:
Hiya, I've been trying for a while to get MySQL radius backend to work but only had success with the basic auth mode.
select * from radpostauth; ^ Shows nothing, for some reason freeRADIUS doesn't even write there even though the log claims it does so successfully.
The debug log shows it writing to radpostauth?
Here is everything I tried, step by step.
All we need is the debug output. It doesn't matter what OS you're running. a) configure the SQL module b) put data into SQL c) run the server in debug mode d) read the output. Or post it here. The server will print out what it's doing, and what's going wrong. If the server is inserting into SQL and you can't find the records in SQL... something is wrong on the SQL server. Or, there's a cron job which deletes records from SQL. Alan DeKok.
The debug log shows it writing to radpostauth?
(0) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'c0rn3jj', '123', 'Access-Accept', '2017-04-24 10:18:48') (0) sql: SQL query returned: success (0) sql: 1 record(s) updated There's no cron job or anything interacting with the SQL database, this is a completely new container.
All we need is the debug output. It doesn't matter what OS you're running.
You can find the debug output at the end of my first message. On Mon, Apr 24, 2017 at 3:22 PM, Alan DeKok <aland@deployingradius.com> wrote:
On Apr 24, 2017, at 6:47 AM, Martin Rys <spleefer90@gmail.com> wrote:
Hiya, I've been trying for a while to get MySQL radius backend to work but only had success with the basic auth mode.
select * from radpostauth; ^ Shows nothing, for some reason freeRADIUS doesn't even write there even though the log claims it does so successfully.
The debug log shows it writing to radpostauth?
Here is everything I tried, step by step.
All we need is the debug output. It doesn't matter what OS you're running.
a) configure the SQL module
b) put data into SQL
c) run the server in debug mode
d) read the output. Or post it here.
The server will print out what it's doing, and what's going wrong.
If the server is inserting into SQL and you can't find the records in SQL... something is wrong on the SQL server. Or, there's a cron job which deletes records from SQL.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Apr 24, 2017, at 9:34 AM, Martin Rys <spleefer90@gmail.com> wrote:
The debug log shows it writing to radpostauth?
(0) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'c0rn3jj', '123', 'Access-Accept', '2017-04-24 10:18:48') (0) sql: SQL query returned: success (0) sql: 1 record(s) updated
There's no cron job or anything interacting with the SQL database, this is a completely new container.
If FreeRADIUS thinks the information is in SQL, then it's in SQL. Something else is going wrong. Alan DeKok.
I was originally running freeradius against a mariaDB running on 16.04 Ubuntu, where other services were running just fine, but it didn't work for freeRADIUS so I switched to a local one. I can still write to the db manually, so I don't see how the database is at fault, how should I debug this if it is the case?
On Apr 24, 2017, at 11:15 AM, Martin Rys <spleefer90@gmail.com> wrote:
I was originally running freeradius against a mariaDB running on 16.04 Ubuntu, where other services were running just fine, but it didn't work for freeRADIUS so I switched to a local one.
FreeRADIUS works with a local or remote SQL database. It doesn't matter.
I can still write to the db manually, so I don't see how the database is at fault, how should I debug this if it is the case?
Find out why your SQL server is returning SUCCESS, and then failing to write the data. FreeRADIUS works for everyone else with SQL. There's no magic here. Something on your local system is broken. It's either the database, *or* you have some locally butchered version of FreeRADIUS, or the SQL library used by FreeRADIUS is lying to the server. Again, there's no magic here. If FreeRADIUS says it wrote data to SQL, then it's been told by the SQL server and the SQL client library that the data was written. Alan DeKok.
you didnt correctly configure the SQL module.. you left the driver as rlm_sql_null - which is a fake driver used to just simulate SQL "Dummy SQL driver for logging SQL queries to disk, but not executing them" change your config so the SQL module is actually using your MySQL (MariaDB) server driver = rlm_sql_mysql (as per the 6 lines above the rlm_sql_null config line! ;-) ) alan On 24 April 2017 at 11:47, Martin Rys <spleefer90@gmail.com> wrote:
Hiya, I've been trying for a while to get MySQL radius backend to work but only had success with the basic auth mode.
select * from radpostauth; ^ Shows nothing, for some reason freeRADIUS doesn't even write there even though the log claims it does so successfully.
Here is everything I tried, step by step.
You can either open this link and read it somehow formatted on my PrivateBin instance, or you can read it in plain text posted below the link.
https://paste.c0rn3j.com/?44f340705be383c4#6nPNN39P98jGng38weWx0223pyGU7UQOY...
Create new LXC container running Ubuntu 17.04(I doubt this has any effect on my problems, but noting it anyways) and login as root.
apt update
apt install freeradius-mysql
Package: freeradius Version: 3.0.12+dfsg-4ubuntu1
Go by https://wiki.freeradius.org/guide/Basic-configuration-HOWTO
add >>> c0rn3jj Cleartext-Password := "123" <<<
to the first line of /etc/freeradius/3.0/users
systemctl restart freeradius
radtest c0rn3jj 123 localhost 0 testing123 ^ Access-Accept.
go by https://wiki.freeradius.org/guide/SQL-HOWTO-for-freeradius-3.x-on-Debian-Ubu...
apt install mariadb-server
Package: mariadb-server Version: 10.1.22-3
mysql -uroot -p ^default install leaves root with empty password
CREATE DATABASE radius; exit
mysql -uroot -p radius < /etc/freeradius/3.0/mods-config/sql/main/mysql/schema.sql
/etc/freeradius/3.0/mods-config/sql/main/mysql/setup.sql ^ change password from radpass to V9WcNpFEfY69MhuL
mysql -uroot -p radius < /etc/freeradius/3.0/mods-config/sql/main/mysql/setup.sql
/etc/freeradius/3.0/mods-available/sql ^ uncomment server/port/login/password and change password to V9WcNpFEfY69MhuL, change dialect to mysql
ln -s /etc/freeradius/3.0/mods-available/sql /etc/freeradius/3.0/mods-enabled/sql
/etc/freeradius/3.0/sites-available/default ^ guide says to uncomment sql in the authorize block, however it is already uncommented but there's a - sign before it? -sql? I did nothing here. Same story with the accounting block. Same stuff with inner-tunnel.
"Your radiusd.conf should then look something like this"
"then"? I did nothing with the radiusd.conf, moreover there's no authoriSe or authorize block either. Do nothing with the file.
Now I get to the Populating SQL section
insert into radcheck (username,attribute,op,value) values("c0rn3j", "Cleartext-Password", ":=", "123");
select * from radcheck;
radtest c0rn3j 123 localhost 0 testing123 ^ fail
insert into radreply (username,attribute,op,value) values("c0rn3j", "Framed-IP-Address", ":=", "1.2.3.4");
radtest c0rn3j 123 localhost 0 testing123 ^fail
Client output - https://paste.c0rn3j.com/?0af6cf0b0aa86d67#Lo53mu+IQ2RQPQOvMl/B0nxH+0/Fal4cq...
systemctl stop freeradius /usr/sbin/freeradius -X > /tmp/radlog
-X log either on https://paste.c0rn3j.com/?f6dc7f8decd19541#QheezTe5NftT6qj1furUsZRL4TL90QSmC... or https://ptpb.pw/n7Re - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Thank you so much! That's missing from the wiki page I used, I assume anyone can make an account and edit it which is what I'm gonna do soon. On Mon, Apr 24, 2017 at 7:56 PM, Alan Buxey <alan.buxey@gmail.com> wrote:
you didnt correctly configure the SQL module.. you left the driver as rlm_sql_null - which is a fake driver used to just simulate SQL "Dummy SQL driver for logging SQL queries to disk, but not executing them"
change your config so the SQL module is actually using your MySQL (MariaDB) server
driver = rlm_sql_mysql
(as per the 6 lines above the rlm_sql_null config line! ;-) )
alan
Hiya, I've been trying for a while to get MySQL radius backend to work but only had success with the basic auth mode.
select * from radpostauth; ^ Shows nothing, for some reason freeRADIUS doesn't even write there even though the log claims it does so successfully.
Here is everything I tried, step by step.
You can either open this link and read it somehow formatted on my PrivateBin instance, or you can read it in plain text posted below the link.
https://paste.c0rn3j.com/?44f340705be383c4#6nPNN39P98jGng38weWx0223pyGU7UQOY...
Create new LXC container running Ubuntu 17.04(I doubt this has any effect on my problems, but noting it anyways) and login as root.
apt update
apt install freeradius-mysql
Package: freeradius Version: 3.0.12+dfsg-4ubuntu1
Go by https://wiki.freeradius.org/guide/Basic-configuration-HOWTO
add >>> c0rn3jj Cleartext-Password := "123" <<<
to the first line of /etc/freeradius/3.0/users
systemctl restart freeradius
radtest c0rn3jj 123 localhost 0 testing123 ^ Access-Accept.
go by https://wiki.freeradius.org/guide/SQL-HOWTO-for-freeradius-3.x-on-Debian-Ubu...
apt install mariadb-server
Package: mariadb-server Version: 10.1.22-3
mysql -uroot -p ^default install leaves root with empty password
CREATE DATABASE radius; exit
mysql -uroot -p radius < /etc/freeradius/3.0/mods-config/sql/main/mysql/schema.sql
/etc/freeradius/3.0/mods-config/sql/main/mysql/setup.sql ^ change password from radpass to V9WcNpFEfY69MhuL
mysql -uroot -p radius < /etc/freeradius/3.0/mods-config/sql/main/mysql/setup.sql
/etc/freeradius/3.0/mods-available/sql ^ uncomment server/port/login/password and change password to V9WcNpFEfY69MhuL, change dialect to mysql
ln -s /etc/freeradius/3.0/mods-available/sql /etc/freeradius/3.0/mods-enabled/sql
/etc/freeradius/3.0/sites-available/default ^ guide says to uncomment sql in the authorize block, however it is already uncommented but there's a - sign before it? -sql? I did nothing here. Same story with the accounting block. Same stuff with inner-tunnel.
"Your radiusd.conf should then look something like this"
"then"? I did nothing with the radiusd.conf, moreover there's no authoriSe or authorize block either. Do nothing with the file.
Now I get to the Populating SQL section
insert into radcheck (username,attribute,op,value) values("c0rn3j", "Cleartext-Password", ":=", "123");
select * from radcheck;
radtest c0rn3j 123 localhost 0 testing123 ^ fail
insert into radreply (username,attribute,op,value) values("c0rn3j", "Framed-IP-Address", ":=", "1.2.3.4");
radtest c0rn3j 123 localhost 0 testing123 ^fail
Client output - https://paste.c0rn3j.com/?0af6cf0b0aa86d67#Lo53mu+IQ2RQPQOvMl/B0nxH+0/Fal4cq...
systemctl stop freeradius /usr/sbin/freeradius -X > /tmp/radlog
-X log either on https://paste.c0rn3j.com/?f6dc7f8decd19541#QheezTe5NftT6qj1furUsZRL4TL90QSmC... or https://ptpb.pw/n7Re - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On 24 April 2017 at 11:47, Martin Rys <spleefer90@gmail.com> wrote: - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (4)
-
Adam Bishop -
Alan Buxey -
Alan DeKok -
Martin Rys