Create Radius / Easyhotspot Database
The FreeRadius radius_db must be the same as Easyhotspot database, so choose a name for the DB, here „easyhpradiusdb“ is used.
Version1:
Create DB named easyhpradiusdb, „fire and forget“ mode:
echo "create database easyhpradiusdb;" | mysql -u root -p Enter password: ??
Version2:
The way showed here is a bit more detailed concerning what is happening:
mysql -u root -p Enter password: ?? mysql> create database easyhpradiusdb; mysql> quit; Bye
FreeRadius needs to write to the radacct and radpostauth tables
Easyhotspot needs to write to its own tables plus radcheck, radgroupcheck, radreply, radgroupreply
Easyhotspot database, username and password system/application/config/database.php
Create Radius related DB tables with Free-Raduis Script
mysql -u root -p easyhpradiusdb < /etc/freeradius/sql/mysql/schema.sql Enter password: ??
Note: The script: /etc/freeradius/sql/mysql/admin.sql will not be executed as it only creates the radius user and DB permissions. This will be done manually in the next steps
mysql -u root -p easyhpradiusdb Enter password: 2= mysql> show tables; mysql> show tables; +------------------+ | Tables_in_radius | +------------------+ | radacct | | radcheck | | radgroupcheck | | radgroupreply | | radpostauth | | radreply | | radusergroup | +------------------+ 7 rows in set (0.00 sec)
Erase all Radius information from „ehs_noraddata_0.2p3.sql“ and use this modifyed file to
create the tables needed for EASY-Hotspot.
ehs_noraddata_0.2p3.sql can be found in: /opt/EasyHotspot/install
Copy it to e.g. database_HP-only-NoRadius-NoData.sql
and use vi to earse all radius related operations
Then use this file to create the EASY-Hotspot tables in the same DB that already contain the FreeRadius tabels.
root@raspberrypi:~# mysql -u root -p easyhpradiusdb < /home/pi/easyhotspot/easyhotspot/install/database_HP-only-NoRadius-NoData.sql Enter password: ????
Check tables
Check what tables have been created:
mysql -u root -p easyhpradiusdb Enter password:??? mysql> show tables; +--------------------------+ | Tables_in_easyhpradiusdb | +--------------------------+ | billingplan | | ci_sessions | | expiration_account | | expirationplan | | fa_country | | fa_user | | fa_user_profile | | fa_user_temp | | invoice | | invoice_detail | | nas | | postpaid_account | | postpaid_account_bill | | postpaid_account_list | | postplan | | radacct | | radcheck | | radchecked_out | | radgroupcheck | | radgroupreply | | radpostauth | | radreply | | radusergroup | | voucher | | voucher_list | +--------------------------+ 25 rows in set (0.00 sec) mysql>
Set DB-User Permissions
As we skipped Admin.sql no radius user has been created. To split responsibilities, easyhotspot needs to access all tables and radius will only be allowd to the radius tables (This are: radacct, radcheck, radgroupcheck, radgroupreply, radpostauth, radreply, radusergroup )
Note: First it is explained step by step a bit below all commands are listed as a Copy & paste block!!
Start with creating the users :
mysql -u root -p
Enter password: ???
mysql>CREATE USER 'easyhotspot'@'localhost';
mysql> select Host,User from mysql.user where User='easyhotspot';
+-----------+-------------+
| Host | User |
+-----------+-------------+
| localhost | easyhotspot |
+-----------+-------------+
1 row in set (0.00 sec)
mysql>CREATE USER 'radius'@'localhost';
mysql> select Host,User from mysql.user where User='radius';
+-----------+--------+
| Host | User |
+-----------+--------+
| localhost | radius |
+-----------+--------+
1 row in set (0.00 sec)
# Change PW for easyhotspot
#####mysql> SET PASSWORD FOR 'easyhotspot'@'localhost' = PASSWORD('MyPwd');
mysql> UPDATE mysql.user SET Password=PASSWORD('MyPwd') WHERE User='easyhotspot' AND Host='localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
# Change PW for radius
#####mysql> SET PASSWORD FOR 'radius'@'localhost' = PASSWORD('Radpass');
mysql> UPDATE mysql.user SET Password=PASSWORD('Radpass') WHERE User='radius' AND Host='localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> FLUSH PRIVILEGES;
# Grant usage for easyhotspot
mysql> GRANT ALL ON easyhpradiusdb . * TO 'easyhotspot'@'%' IDENTIFIED BY 'MyPass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
# Grant usage for radius
mysql> GRANT SELECT ON easyhpradiusdb . radcheck TO 'radius'@'%' IDENTIFIED BY 'Radpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
mysql> GRANT SELECT ON easyhpradiusdb . radgroupcheck TO 'radius'@'%' IDENTIFIED BY 'Radpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
mysql> GRANT SELECT ON easyhpradiusdb . radreply TO 'radius'@'%' IDENTIFIED BY 'Radpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
mysql> GRANT SELECT ON easyhpradiusdb . radgroupreply TO 'radius'@'%' IDENTIFIED BY 'Radpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
mysql> GRANT ALL ON easyhpradiusdb . radacct TO 'radius'@'%' IDENTIFIED BY 'Radpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
mysql> GRANT ALL ON easyhpradiusdb . radpostauth TO 'radius'@'%' IDENTIFIED BY 'Radpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
mysql> GRANT SELECT ON easyhpradiusdb . radusergroup TO 'radius'@'%' IDENTIFIED BY 'Radpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Copy and paste command from above:
mysql -u root -p
Enter password: ???
CREATE USER 'easyhotspot'@'localhost';
CREATE USER 'radius'@'localhost';
# Change PW for easyhotspot
####SET PASSWORD FOR 'easyhotspot'@'localhost' = PASSWORD('MyPass');
UPDATE mysql.user SET Password=PASSWORD('Easy54321') WHERE User='easyhotspot' AND Host='localhost';
FLUSH PRIVILEGES;
# Change PW for radius
#####SET PASSWORD FOR 'radius'@'localhost' = PASSWORD('Radpass');
UPDATE mysql.user SET Password=PASSWORD('Radpass12345') WHERE User='radius' AND Host='localhost';
mysql> FLUSH PRIVILEGES;
# Grant usage for easyhotspot
GRANT ALL ON easyhpradiusdb . * TO 'easyhotspot'@'%' IDENTIFIED BY 'MyPass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
FLUSH PRIVILEGES;
# Grant usage for radius
GRANT SELECT ON easyhpradiusdb . radcheck TO 'radius'@'%' IDENTIFIED BY 'Radpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
FLUSH PRIVILEGES;
GRANT SELECT ON easyhpradiusdb . radgroupcheck TO 'radius'@'%' IDENTIFIED BY 'Radpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
FLUSH PRIVILEGES;
GRANT SELECT ON easyhpradiusdb . radreply TO 'radius'@'%' IDENTIFIED BY 'Radpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
FLUSH PRIVILEGES;
GRANT SELECT ON easyhpradiusdb . radgroupreply TO 'radius'@'%' IDENTIFIED BY 'Radpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
FLUSH PRIVILEGES;
GRANT ALL ON easyhpradiusdb . radacct TO 'radius'@'%' IDENTIFIED BY 'Radpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
FLUSH PRIVILEGES;
GRANT ALL ON easyhpradiusdb . radpostauth TO 'radius'@'%' IDENTIFIED BY 'Radpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
FLUSH PRIVILEGES;
GRANT SELECT ON easyhpradiusdb . radusergroup TO 'radius'@'%' IDENTIFIED BY 'Radpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
FLUSH PRIVILEGES;
Test "radius" User login to Radius/EasyHotspot DB
Test on commandline that the new user is able to login to DB
mysql -u easyhotspot -p easyhpradiusdb Enter password: Rad
Test "easyHotspot" User login to Radius/EasyHotspot DB
Test on commandline that the new user is able to login to DB
mysql -u radius -p easyhpradiusdb Enter password: EAS
Create a user to test radius login
This user is not managed via Easy-Hotspot, so creation is optional. \\
echo "insert into radcheck (username, attribute, op, value) values ('user1','Cleartext-Password',':=','passwd1');" | mysql -u root -p easyhpradiusdb
Check new user entry in table radcheck\\
mysql> select * from radcheck where username='user1'; +----+----------+--------------------+----+-----------+ | id | username | attribute | op | value | +----+----------+--------------------+----+-----------+ | 1 | user1 | Cleartext-Password | := | passwd1 | +----+----------+--------------------+----+-----------+ 1 row in set (0.01 sec)
Create User via EasyHP Admin GUI
Log in to:EASY-Hotspot Admin GUI and create a (Post-Payed) User for the first tests.
Check / Change Freeradius shared secret
Default is „testing123“ In case of using a different secret change password, as we have configured above of radius user in mysql, change secret in Free-Radius config.
vi /etc/freeradius/sql.conf
# Connection info:
server = "localhost"
#port = 3306
login = "radius"
password = "Radpass
# Database table configuration for everything except Oracle
radius_db = "easyhpradiusdb"
NOTE: Restart MySQL and Freeradius to enshure the new password is used. Check /var/log/freeradius/radius.log for a successfull DB
connection in case of problems see:raspmysqladmin
Test Radius Server
Now the Radius Server can be tested as a valid user is available.
Start Radius in Debug-Mode:
freeradius -X
Dont panic, tons of output will run over the screen, but important is that after some time
a line named: Ready to process requests. is displayed
Now login on another terminal and test radius communication with MySQL by requesting login-In for for the user just created via EASY-Hotspot Admin GUI e.g. user1 with pw passwd1 on localhost 1812 and secret RaspiRadi:
root@raspberrypi:~# radtest user1 passwd123 localhost 0 RaspiRadi Sending Access-Request of id 35 to 127.0.0.1 port 1812 User-Name = "user1" User-Password = "passwd123" NAS-IP-Address = 127.0.1.1 NAS-Port = 0 Message-Authenticator = 0x00000000000000000000000000000000 rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=35, length=20 root@raspberrypi:~#
If you see the reply from Radius that an Access-Accept packet is received the Radius is operational.
Additional: Only if something went wrong and a restart is necessary: Drop DB and User:
mysql -u root -p Enter password: 2 drop user 'radius'@'%'; drop user 'easyhotspot'@'%'; drop database easyhpradiusdb; mysql> drop user 'radius'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> drop user 'easyhotspot'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> drop database easyhpradiusdb; Query OK, 25 rows affected (0.41 sec)
</code>
