=== 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)