[HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step
-
I'm pretty sure that you have two instances of mysql running. Check with ps aux.
-
Hi deajan,
First thank you very much for this perfect HOWTO.
But I got a problem when i import the admin.sql script. Please have a look below:
mysql radius < admin.sql ERROR 1064 (42000) at line 31: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIVIELGES' at line 1
Do you have any idea how to fix it? I am running a brand new pfsense 2.3 on a PC. Thank you in advance.
-
There was a typo in admin.sql, should read PRIVILEGES and not PRIVIELGES.
Corrected on github. You may redownload the latest archive at https://github.com/deajan/pfSense-cp-auth-onestep/archive/master.zip -
There was a typo in admin.sql, should read PRIVILEGES and not PRIVIELGES.
Corrected on github. You may redownload the latest archive at https://github.com/deajan/pfSense-cp-auth-onestep/archive/master.zipMerci!!! Problem solved!!
-
Hello deajan,
First thank you a lot for your work, the system works great !
I try to use your template only with the "room number" field to log in on the portal.
I successfully disable the others fields (familyName, surName, emailAddress and newsletter) but when i validate the form, I've a blank page.
However, I pass the portal and I can access to the web but the result is not very clean
Could you please explain me which modifications I have to make in ozy-captive.php and perhaps in captiveportal-config.php .
Thanks in advance !
-
Hello xAm56,
As far as I remember, the username is a combination of emailAdress and roomNumber and password is a combination of familyName and surName, in order to get some degree of randomness.
Basically, only using room number would mean that Radius records would not be unique.I'll have a to push an update until monday which adds the language parameter in the url.
I'll try to check your usecase in the same time.Regards,
Orsiris. -
Have a look at the latest commits on github at https://github.com/deajan/pfSense-cp-auth-onestep/releases/tag/v0.44
-
Hi @deajan and thank you for your amazing work!
Unfortunately I'm not able to run mysql server.
I have made a clean install of pfSense 2.3, configured wan and lan, internet works. I'm also able to install all what do you mentioned on this guide.
Each time I try start mysql server it comes some error:[2.3.2-RELEASE][admin@pfSense.localdomain]/root: service mysql-server.sh start Starting mysql. Bad -c option /usr/local/etc/rc.d/mysql-server.sh: WARNING: failed to start mysql
I have already research on Google regarding to this error, but don't found nothing useful.
Please let me know if you need some additional info or log output. -
@saygon I remember having dealt with that error while developping this project.
Don't remember the exact root cause… probably a bad mysql version installed.Remove and reinstall the version corresponding to your setup.
If the problem still occurs, post the content of your mysql_server.sh file, and mysql -v output. -
+–--+----------+-------+---------------+---------------------+
| id | username | pass | reply | authdate |
+----+----------+-------+---------------+---------------------+
| 1 | testu | testp | Access-Accept | 2015-09-29 15:13:24 |
+----+----------+-------+---------------+---------------------+Hello deajan,
would you please tell me , how can I set MD5 encrypt here for user password?
-
@astatine Your question isn't in the scope of this howto. Please make your own question regarding FreeRADIUS + MySQL setup only.
-
Hello !
I'm new to pfSense and forum. I was tasked to set up this type of access for an organization and I'm having a small trouble that I probably think it's pretty stupid but can't make it work.
I followed the instructions to the letter and I got the captive portal working as is- No modification of the files except for mysql password etc.
On the LAN PC I get successfully forwarded to the Captive Portal but as soon as I press Connect nothing happens, the form is cleared (as if new connection) and remain there.
I tried to see if the PHP inserted something in the radius DB but I found nothing there and for the life of me I can't find in the logs, or where in the logs, I can tail if there are connectivity errors with mysql + php etc.
I do want t thank you though, the guide is very detailed and I was able to set everything up quick. I just now need to to make that work.
Thanks !
-
Hello joel.dq,
Can you activate debug mode in captiveportal-config.php ?
-
I think I did already. Where should I see that logging?
-
There's no logging, it's about getting the error messages onscreen.
What version of the captive portal code are you using ? What user input do you ask for to connect ? -
There's no logging, it's about getting the error messages onscreen.
What version of the captive portal code are you using ? What user input do you ask for to connect ?Hello
I'm using 2.3.2-RELEASE-p1 version.
The inputs I'm asking are
- Room # (renamed to Age) . I put it because I can read in the code that the room# is used to create the radius account.
- Name
- Surname
Debug is enabled
/usr/local/captiveportal: cat captiveportal-config.php | grep DEBUG
DEFINE("DEBUG", true);
DEFINE("DEBUG", true);What is happening is that I do get the Captive Portal. I fill the details and click Connect. Nothing happens, no error on the screen and simply clears the form as if first reaching the CP page.
http://i.imgur.com/a/TfTn4
and form gets cleared
http://imgur.com/a/Z8Udw
I normally try to debug myself, I have rudimentary php skills to understand what code does, but I'm lost when JS is involved.
-
Hello,
What version of captiveportal do you use ? Latest from git ?
Can you mail me your captiveportal-config.php file at ozy at netpower dot fr ?
Also, assuming you did a Spanish translation, can you give me that so I add it to git ?
-
Hello,
What version of captiveportal do you use ? Latest from git ?
Can you mail me your captiveportal-config.php file at ozy at netpower dot fr ?
Also, assuming you did a Spanish translation, can you give me that so I add it to git ?
Latest from GIT. Sending and many thanks for your assistance.
I'll send you the spanish translation a bit later. Modified it outside hotel for educative institute.
-
Your config file does not come from the latest git, there have been at least 7 commits since your version.
Please UPDATE ALL files before posting about this again. -
Your config file does not come from the latest git, there have been at least 7 commits since your version.
Please UPDATE ALL files before posting about this again.I found the issue.
When I put the terms of use as false, no matter what other options I try with true or false, it won't go on.
$askForTermsOfUse = false;
While the option to put it false is there, apparently it has to always be true to work.
Regards
-
Well this was more or less by design, as accepting Terms of use is mandatory.
In the meanwhile, I added askFor[all] parameters, without updating the fact that Terms of Use aren't mandatory anymore.I've commited a quick and dirty fix you may try.
Regards,
Ozy. -
Well this was more or less by design, as accepting Terms of use is mandatory.
In the meanwhile, I added askFor[all] parameters, without updating the fact that Terms of Use aren't mandatory anymore.I've commited a quick and dirty fix you may try.
Regards,
Ozy.I can confirm it now works perfect ! Thanks very much Ozy
-
Hello Ozy, great work. Thank you.
I have couple of questions about authentification and administration:
How do you disable "non" Hotel guests? You solution is free and open for all "participants".
Is it possible to check the room number, before accepting the registration?
Why does in pfSense in FrreRadius section not listing the registrated user? Only within mySQL-database.
Have you thinking about the process to validate "the login" with eMail confirmation by the user or by the hotel?Thanks so lot for a discussion.
Best regards
Treml -
Hi Ozy,
Great work on this, thank you! Just some question:
When I reboot my pfsense machine, all credentials are gone. Users will need to re-input and goes back to the landing page. Is that how it should be? Is there anyway the credentials are stored even after every reboot?
Thanks Ozy
-
1/ Non hotel guests are excluded via confirmation code (which is basically a stupid code you give the customers at the reception desk)
2/ This is a SELF REGISTERING solution. If you don't need this, you could remove the self registering part and create FreeRADIUS accounts with roomnumber and different passwords per room which you give to the customers
3/ Because pfSense FreeRADIUS UI does not know about the underlying SQL storage, you may open a ticket at redmine.pfsense.org
4/ Well… Confirm the email without having internet ? That's the point of providing wifi, isn't it ?@lienor:
That's the normal behavior. Unless you know how to play with pfSense's SQLite database where the CP stores identification, you won't be able to change this. -
@hsrtreml:
Thanks for your quick reply.1/ Non hotel guests are excluded via confirmation code (which is basically a stupid code you give the customers at the reception desk)
Good idea!2/ This is a SELF REGISTERING solution. If you don't need this, you could remove the self registering part and create FreeRADIUS accounts with roomnumber and different passwords per room which you give to the customers
The point above (1.) will substitute this point.3/ Because pfSense FreeRADIUS UI does not know about the underlying SQL storage, you may open a ticket at redmine.pfsense.org
ok.4/ Well… Confirm the email without having internet ? That's the point of providing wifi, isn't it ?
Perhaps an internal network messages to the hotel information system?
btw.
First the customer get internet access after self registration and if the validation is not correct or the time (5 minutes) for confirmation is over, we delete the entry in the SQL-Database?@lienor:
That's the normal behavior. Unless you know how to play with pfSense's SQLite database where the CP stores identification, you won't be able to change this. -
1/ Non hotel guests are excluded via confirmation code (which is basically a stupid code you give the customers at the reception desk)
2/ This is a SELF REGISTERING solution. If you don't need this, you could remove the self registering part and create FreeRADIUS accounts with roomnumber and different passwords per room which you give to the customers
3/ Because pfSense FreeRADIUS UI does not know about the underlying SQL storage, you may open a ticket at redmine.pfsense.org
4/ Well… Confirm the email without having internet ? That's the point of providing wifi, isn't it ?@lienor:
That's the normal behavior. Unless you know how to play with pfSense's SQLite database where the CP stores identification, you won't be able to change this.Thanks Ozy, I have found a way to store the entries by checking Enable Pass-through MAC automatic additions on Captive Portal. Somehow, the entries are still there even after reboot. My only problem is that FreeRADIUS is not starting automatically on restart, I tried all the possible fixes in forums but still failed. Seems like a bug on the new version of PFSense, but not that serious though.
-
Be careful with passthrough MAC entries, they are persistent, but will prevent ANY further logins, even if you try to force disconnect them after some period using the CP settings.
As for FreeRADIUS not starting, this is actually a known issue for my setup.Whenever FreeRADIUS starts before MySQL, it will fail to connect to the database (you'll have logs about this), and will simply fail to start.
A quick workaround is to install Watchdog service and let it check / restart FreeRADIUS.Btw, if someone knows how to improve the boot order I'd be happy.
-
2/ This is a SELF REGISTERING solution. If you don't need this, you could remove the self registering part and create FreeRADIUS accounts with roomnumber and different passwords per room which you give to the customers
@hsrtreml:
One question again: Do you have an easy way to check or validate the room number? Just between a range (100 to 300) or within an array (100, 101, 101, …).best regards
Treml -
@hrstreml Could be easily implemented on line 114 of main file, using a global variable containing a range or an array in config file.
-
Hi @deajan and thank you for your amazing work!
Unfortunately I'm not able to run mysql server.
I have made a clean install of pfSense 2.3, configured wan and lan, internet works. I'm also able to install all what do you mentioned on this guide.
Each time I try start mysql server it comes some error:[2.3.2-RELEASE][admin@pfSense.localdomain]/root: service mysql-server.sh start Starting mysql. Bad -c option /usr/local/etc/rc.d/mysql-server.sh: WARNING: failed to start mysql
I have already research on Google regarding to this error, but don't found nothing useful.
Please let me know if you need some additional info or log output.Hi @saygon I had the same issue… Allow me to write down what I did and works for me!
First I change the default shell access for user mysql
chsh -s /bin/sh mysqlThen edit /etc/rc.subr file
change "su -m" to "su -s" in the file I think you will find it two times in the file!I don't know if this is a correct way but it works for me..
Thanks
Ps. Many thanks to @deajan for his great work!! -
Hello,
if I install sql-server in a separate pfsense (Hardware), where are the entries of the sql-server IP to integrate free radius (of the basic pfsense) with sql-pfsense?
Thanks for your reply.
best regards
Treml -
some issue with pfsense 2.3.2-p1
i did an clean install
the redirect page was show (perfect)
i put all parameters (email, name, surname) agree newsletter and termsclicked connect
and a Blank Screen is presented
with this url http://192.168.1.1:8002/index.php?zone=&redirurl=&language=en
nothing is inserted on mysql tables
and log shows
php-fpm 58240 /index.php: Submission to captiveportal with unknown parameter zone:any glue ?
-
Hi deajan
Thanks for superb detail explanation.One question: How can i integrate separate MySQL server. I have already MySQL running on different PC (in LAN).
I know about point IP to MySQL server, buthow do i create table structure in my separate sql server.
I downloaded http://netpower.fr/sites/default/files/soft/bin/pfSense-cp-auth-onestep.gz file from step 2.2.2.
But it is not useful to create structure.Pls help me.
Thanks
Sanket -
@dhipo Did you set up a correct zone name in pfSense GUI ?
Also, which version have you tried ? Can you try with latest git master tree ?You asked for a "glue", I can't give you that, but the latest dev snapshot should stick toghether with 2.3.2 without problems :)
@sanketgroup
The archive contains some sql files (in sql directory) that you must run on your mysql server.
Btw, also use latest github sql files because of an error in previous versions. -
where can i find latest github sql files?
I got schema from https://wiki.freeradius.org/config/MySQL-DDL-script
However there were some mismatch in column names. -
See https://github.com/deajan/pfSense-cp-auth-onestep
-
perfect, this schema worked.
BUT
how can i add internet username and passwords in sql.for e.g In current setup, user 'testu' has been created PFSense>Services>FreeRadius>Users
if i delete this user here, it does not go to my sql to check the user.
And also i do not know in which table and now i can create users in MySQL.
Pls let me know.Thanks a lot
-
how can i add internet username and passwords in sql.
for e.g In current setup, user 'testu' has been created PFSense>Services>FreeRadius>Users
if i delete this user here, it does not go to my sql to check the user.
And also i do not know in which table and now i can create users in MySQL.
Pls let me know.Thanks a lot
That's not how this captive portal is supposed to work.
It creates the user accounts on the fly.
If you don't want that behavior, just check the code of ozy-captive.php that creates the user in order to disable it and create them manually in the same manner the code does. -
On ozy-captive.php page.
when i fill in Email, Family Name, Surname and click connect - I get following error.Fatal error: Uncaught Error: Call to undefined function mysql_real_escape_string() in C:\xampp\htdocs\PFSense-cp-auth\ozy-captive.php:35 Stack trace: #0 C:\xampp\htdocs\PFSense-cp-auth\ozy-captive.php(65): cleanInput('en') #1 {main} thrown in C:\xampp\htdocs\PFSense-cp-auth\ozy-captive.php on line 35
=== Mainly i want to store username password generated from my CRM application to radius database.
I have own CRM application which i can modify to store username password of hotspot users into radius database.
And then captive portal will verify that user in radius mysql database.
But I do not know in which table and column i have to insert usertname password.
And may be their plan like 1gb per day, or 2 hours access etcThanks