Netgate Discussion Forum
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Search
    • Register
    • Login

    [HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step

    Scheduled Pinned Locked Moved Captive Portal
    154 Posts 47 Posters 104.3k Views
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • D
      deajan
      last edited by

      @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

      1 Reply Last reply Reply Quote 0
      • H
        hsrtreml
        last edited by

        @deajan:

        @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 Reply Last reply Reply Quote 0
        • L
          lienor
          last edited by

          @deajan:

          @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.

          1 Reply Last reply Reply Quote 0
          • D
            deajan
            last edited by

            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

            1 Reply Last reply Reply Quote 0
            • H
              hsrtreml
              last edited by

              @deajan:

              @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

              1 Reply Last reply Reply Quote 0
              • D
                deajan
                last edited by

                @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

                1 Reply Last reply Reply Quote 0
                • G
                  geocbr600rr
                  last edited by

                  @saygon:

                  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 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!!

                  1 Reply Last reply Reply Quote 0
                  • H
                    hsrtreml
                    last edited by

                    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

                    1 Reply Last reply Reply Quote 0
                    • D
                      dhipo
                      last edited by

                      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

                      1 Reply Last reply Reply Quote 0
                      • S
                        sanketgroup
                        last edited by

                        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

                        1 Reply Last reply Reply Quote 0
                        • D
                          deajan
                          last edited by

                          @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

                          1 Reply Last reply Reply Quote 0
                          • S
                            sanketgroup
                            last edited by

                            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.

                            1 Reply Last reply Reply Quote 0
                            • D
                              deajan
                              last edited by

                              See https://github.com/deajan/pfSense-cp-auth-onestep

                              NetPOWER.fr - some opensource stuff for IT people

                              1 Reply Last reply Reply Quote 0
                              • S
                                sanketgroup
                                last edited by

                                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

                                1 Reply Last reply Reply Quote 0
                                • D
                                  deajan
                                  last edited by

                                  @sanketgroup:

                                  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.

                                  NetPOWER.fr - some opensource stuff for IT people

                                  1 Reply Last reply Reply Quote 0
                                  • S
                                    sanketgroup
                                    last edited by

                                    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 etc

                                    Thanks

                                    1 Reply Last reply Reply Quote 0
                                    • D
                                      dhipo
                                      last edited by

                                      @deajan

                                      i got the latest git files and works perfect ..

                                      i did some mods on sql tables(more fields, to administrative purposes) and working too

                                      i'm using pf 2.3.2p1

                                      i used toghther Squid and LightSquid to do Reports and work too

                                      great Job

                                      Dhix Networks
                                      Everything Secure

                                      http://www.dhix.com.br

                                      1 Reply Last reply Reply Quote 0
                                      • S
                                        sanketgroup
                                        last edited by

                                        Ok, i found the table name. It is radcheck.
                                        Captive Portal is successfully authenticating username password stored in this table.

                                        Now one question: how do i set usage type to users.

                                        for e.g I want some user to have
                                        1GB per day  or
                                        2 HR access  or
                                        100MB for 2 Hrs
                                        etc.

                                        1 Reply Last reply Reply Quote 0
                                        • D
                                          deajan
                                          last edited by

                                          @sanketgroup This is out of the captive portal discussion but after a short google request I found this one:

                                          INSERT INTO radcheck ( id , UserName , Attribute , op , Value ) VALUES ( NULL , 'user', 'user-password', '==', 'user');
                                          INSERT INTO radreply (username, attribute, op, value) VALUES ('user', 'Rate-Limit', '=', '1024k/1024k');
                                          

                                          It may be adapted to fit actual radius table scheme, you may find info on how to configure it on freeradius documentation.

                                          NetPOWER.fr - some opensource stuff for IT people

                                          1 Reply Last reply Reply Quote 0
                                          • A
                                            ardorin
                                            last edited by

                                            First of all, amazing job, it works perfectly.
                                            However I have a small problem: in french and in spanish accents dont work.
                                            I use UTF-8 in HTML header.
                                            It only works if for example I replace á with á in the config file
                                            Im also using the files from your github

                                            Is there anything that should be added?

                                            1 Reply Last reply Reply Quote 0
                                            • First post
                                              Last post
                                            Copyright 2025 Rubicon Communications LLC (Netgate). All rights reserved.