Hauptmenü

Dies ist eine alte Version des Dokuments!

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: 2=

Version2:

The way showed here is a bit more detailed concerning what is happening:

mysql -u root -p
Enter password: 2=
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: 2=

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)

Get Easy-Hotspot Files

Download easyhotspot web from https://github.com/rafeequl.

cd /opt
git clone https://github.com/rafeequl/EasyHotspot

Erase all Radius information from „ehs_noraddata_0.2p3.sql“ and use this modifyed file to
create the tables needed for EASY-Hotspot.

root@raspberrypi:~# mysql -u root -p easyhpradiusdb < /home/pi/easyhotspot/easyhotspot/install/No_rad_ehs_noraddata_0.2p3.sql
Enter password: 2=

Check tables

mysql -u root -p easyhpradiusdb 
Enter password: 2=


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 ( 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 easyhotspot user:

mysql -u root -p 
Enter password: 2

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('Easy54321');
mysql> UPDATE mysql.user SET Password=PASSWORD('Easy54321') 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('Radpass12345');
mysql> UPDATE mysql.user SET Password=PASSWORD('Radpass12345') 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  'Easy54321' 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 . radcheck TO  'radius'@'%' IDENTIFIED BY  'Radpass12345' 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  'Radpass12345' 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  'Radpass12345' 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  'Radpass12345' 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  'Radpass12345' 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  'Radpass12345' 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  'Radpass12345' 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: 2

CREATE USER 'easyhotspot'@'localhost';

CREATE USER 'radius'@'localhost';




# Change PW for easyhotspot
####SET PASSWORD FOR 'easyhotspot'@'localhost' = PASSWORD('Easy54321');
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('Radpass12345');
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  'Easy54321' 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 . radcheck TO  'radius'@'%' IDENTIFIED BY  'Radpass12345' 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  'Radpass12345' 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  'Radpass12345' 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  'Radpass12345' 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  'Radpass12345' 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  'Radpass12345' 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  'Radpass12345' 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

mysql -u easyhotspot -p easyhpradiusdb
Enter password: Rad

Test "easyHotspot" User login to Radius/EasyHotspot 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',':=','passwd123');" | 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 | := | passwd123 |
+----+----------+--------------------+----+-----------+
1 row in set (0.01 sec)

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 = "Radpass12345
        
       # 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

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)