The pfSense Store

Author Topic: [HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step  (Read 25232 times)

0 Members and 1 Guest are viewing this topic.

Offline deajan

  • Full Member
  • ***
  • Posts: 175
  • Karma: +25/-0
    • View Profile
Re: [HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step
« Reply #60 on: November 18, 2016, 10:29:13 am »
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.
NetPOWER.fr - some opensource stuff for IT people

Offline joel.dq

  • Newbie
  • *
  • Posts: 7
  • Karma: +0/-0
    • View Profile
Re: [HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step
« Reply #61 on: November 21, 2016, 04:03:48 pm »
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

Offline hsrtreml

  • Jr. Member
  • **
  • Posts: 44
  • Karma: +0/-0
    • View Profile
Re: [HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step
« Reply #62 on: November 25, 2016, 09:56:30 am »
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

Offline lienor

  • Newbie
  • *
  • Posts: 2
  • Karma: +0/-0
    • View Profile
Re: [HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step
« Reply #63 on: November 28, 2016, 03:55:47 am »
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

Offline deajan

  • Full Member
  • ***
  • Posts: 175
  • Karma: +25/-0
    • View Profile
Re: [HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step
« Reply #64 on: November 29, 2016, 03:13:30 am »
@hsrtreml:

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.
NetPOWER.fr - some opensource stuff for IT people

Offline hsrtreml

  • Jr. Member
  • **
  • Posts: 44
  • Karma: +0/-0
    • View Profile
Re: [HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step
« Reply #65 on: November 29, 2016, 07:34:45 am »
@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.

Offline lienor

  • Newbie
  • *
  • Posts: 2
  • Karma: +0/-0
    • View Profile
Re: [HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step
« Reply #66 on: November 29, 2016, 08:43:51 am »
@hsrtreml:

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.

Offline deajan

  • Full Member
  • ***
  • Posts: 175
  • Karma: +25/-0
    • View Profile
Re: [HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step
« Reply #67 on: November 29, 2016, 01:24:33 pm »
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.
NetPOWER.fr - some opensource stuff for IT people

Offline hsrtreml

  • Jr. Member
  • **
  • Posts: 44
  • Karma: +0/-0
    • View Profile
Re: [HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step
« Reply #68 on: December 02, 2016, 03:23:19 am »
@hsrtreml:

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

Offline deajan

  • Full Member
  • ***
  • Posts: 175
  • Karma: +25/-0
    • View Profile
Re: [HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step
« Reply #69 on: December 02, 2016, 06:21:21 am »
@hrstreml Could be easily implemented on line 114 of main file, using a global variable containing a range or an array in config file.
NetPOWER.fr - some opensource stuff for IT people

Offline geocbr600rr

  • Newbie
  • *
  • Posts: 1
  • Karma: +0/-0
    • View Profile
Re: [HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step
« Reply #70 on: December 14, 2016, 02:49:00 am »
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:
Code: [Select]
[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 mysql

Then 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!!
« Last Edit: December 14, 2016, 03:00:52 am by geocbr600rr »

Offline hsrtreml

  • Jr. Member
  • **
  • Posts: 44
  • Karma: +0/-0
    • View Profile
Re: [HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step
« Reply #71 on: December 16, 2016, 12:27:21 pm »
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

Offline dhipo

  • Full Member
  • ***
  • Posts: 113
  • Karma: +0/-0
  • Everything Secure
    • View Profile
    • Dhix Networks
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 terms

clicked 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 ?
Dhix Networks
Everything Secure

http://www.dhix.com.br

Offline sanketgroup

  • Newbie
  • *
  • Posts: 21
  • Karma: +0/-0
    • View Profile
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, but

how 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

Offline deajan

  • Full Member
  • ***
  • Posts: 175
  • Karma: +25/-0
    • View Profile
@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.
NetPOWER.fr - some opensource stuff for IT people