=== 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:[[f11:technik:raspberrypi:hotspotdm3kb:rasp1logeasyhp|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 [[f11:technik:raspberrypi:hotspotdm3kb:rasp1logeasyhp|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.
[[f11:technik:raspberrypi:hotspotdm3kb|Back to HotSpot Overview-Page]]
**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)