FreeRadius with mysql Backend

What is Freeradius:

FreeRADIUS is a yet another service that we can setup on Linux and the protocol by which – the RADIUS – we can take advantage of providing functionalities of authentication, authorization and accounting. It has been developing very long time back and yet its very powerful and modern enough to provide authentication facility to systems & applications, specially in networking.

In this article we focus on its authentication ability and even beyond taking “mysql” database as the source of database to retrieve authentication credentials. However, there are other sources you could integrated as well, such as openLDAP, simple flat file and etc.

Now lets install FreeRadius with mysql Backend

Getting Started.

Note that through out the document, I will stick to Ubuntu 18.04 OS version.

Step 01 — Required Package Installation

# apt-get update
# apt-get install -y freeradius freeradius-mysql freeradius-utils php-common php-gd php-curl php-mysql mysql-server mysql-client

Step 02 — Setting up password for mysql own ROOT user

# mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Please set the password for root here.

New password: 

Re-enter new password: 

Estimated strength of the password: 50 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 

Step 03 — Create ‘radius’ database and import required Schema which already available.

# mysql -uroot -p

mysql> uninstall plugin validate_password;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
mysql> CREATE DATABASE radius;
mysql> exit

# cd /etc/freeradius/3.0/mods-config/sql/main/mysql/

# mysql -uroot -pYourMysqlPass radius < schema.sql
# mysql -uroot -pYourMysqlpass radius < setup.sql

Step 04 — Enable ‘sql’ module to be used with

# cd /etc/freeradius/3.0/mods-enabled
# ln -s ../mods-available/sql sql

Step 05 — Instruct freeRadius to use SQL as the backend store, rather local File

To achieve this, for all the below sections, remove the “file” directive and add the “sql” instead

authorize {
...
}
accounting {
...
}
post-auth {
...
}
session{
...
}

Step 06 — Reflect the correct details in Radius SQL module’s config

# vim /etc/freeradius/3.0/mods-available/sql

driver = "rlm_sql_mysql"
 
dialect = "mysql"
 
 server = "localhost"
 port = 3306
 login = "root"
 password = "password-which-we-setup-in-step2"
 radius_db = "radius"
read_clients = yes

Step 07 — Running freeRADIUS in foreground to check the status..

# freeradius -X

Listening on auth address 127.0.0.1 port 18120 bound to server inner-tunnel
Listening on auth address * port 1812 bound to server default
Listening on acct address * port 1813 bound to server default
Listening on auth address :: port 1812 bound to server default
Listening on acct address :: port 1813 bound to server default
Listening on proxy address * port 59791
Listening on proxy address :: port 36140
Ready to process requests

The output above is a good indication of a working configuration..

Step 08 — For a testing purpose, add basic authentication details, such as username, password, NAS ip address, etc..

# mysql -uroot -p

mysql> INSERT INTO nas VALUES (NULL , '0.0.0.0/0', 'myNAS', 'other', NULL , 'mysecret', NULL , NULL , 'RADIUS Client');
mysql> INSERT INTO radcheck (username, attribute, op, value) VALUES ('testuser', 'Cleartext-Password', ':=', 'testpassword');
mysql> INSERT INTO radusergroup (username, groupname, priority) VALUES ('testuser', 'testgroup', '1');
mysql> INSERT INTO radgroupreply (groupname, attribute, op, value) VALUES ('testgroup', 'Service-Type', ':=', 'Framed-User'), ('testgroup', 'Framed-Protocol', ':=', 'PPP'), ('testgroup', 'Framed-Compression', ':=', 'Van-Jacobsen-TCP-IP');

Lets check updated details by going through each mysql tables

mysql> select * from nas;
+----+-----------+-----------+-------+-------+----------+--------+-----------+---------------+
| id | nasname   | shortname | type  | ports | secret   | server | community | description   |
+----+-----------+-----------+-------+-------+----------+--------+-----------+---------------+
|  1 | 0.0.0.0/0 | myNAS     | other |  NULL | mysecret | NULL   | NULL      | RADIUS Client |
+----+-----------+-----------+-------+-------+----------+--------+-----------+---------------+

mysql> select * from radcheck;
+----+----------+--------------------+----+--------------+
| id | username | attribute          | op | value        |
+----+----------+--------------------+----+--------------+
|  1 | testuser | Cleartext-Password | := | testpassword |
+----+----------+---------------+----+-------------------+

mysql> select * from radusergroup;
+----------+-----------+----------+
| username | groupname | priority |
+----------+-----------+----------+
| testuser | testgroup |        1 |
+----------+-----------+----------+

mysql> select * from radgroupreply;
+----+-----------+--------------------+----+---------------------+
| id | groupname | attribute          | op | value               |
+----+-----------+--------------------+----+---------------------+
|  1 | testgroup | Service-Type       | := | Framed-User         |
|  2 | testgroup | Framed-Protocol    | := | PPP                 |
|  3 | testgroup | Framed-Compression | := | Van-Jacobsen-TCP-IP |
+----+-----------+--------------------+----+---------------------+

Step 09 — Ok. Finally lets verify the configured user using a “radclient” command

# echo "User-Name=testuser,User-Password=testpassword" | radclient 127.0.0.1:1812 auth mysecret

Sent Access-Request Id 65 from 0.0.0.0:43879 to 172.17.0.55:1812 length 48
Received Access-Accept Id 65 from 172.17.0.55:1812 to 0.0.0.0:0 length 38

Congradulations.! you have now working RADIUS service.

In order to run the service in background

# systemctl start freeradius
# systemctl enable freeradius

“I hope this has been informative”