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

    Captive portal databases are reset!

    Scheduled Pinned Locked Moved Captive Portal
    captive portalsqlite3database
    9 Posts 4 Posters 1.7k 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.
    • P
      prophet
      last edited by prophet

      Hi everybody,
      i was planning a way to programatically disconnect inactive users (last activity greater or equal to one month ago).
      I just made a little script to simulate users deletion from captive portal, so just to test i ended doing a select (instead of delete) on sqlite db for every IP found in ipfw auth_up table with old last activity timestamp:

      sqlite3 captiveportalname "SELECT * FROM captiveportal WHERE ip = '$ip_db';"
      

      I just ran this and suddenly all of my users got disconnected! Looks like captive portal databases have been reset, while ipfw rules are still there, i searched all kind of logs but found nothing... can someone please explain?

      Thank you!

      1 Reply Last reply Reply Quote 0
      • GrimsonG
        Grimson Banned
        last edited by

        https://www.sqlite.org/docs.html manually messing up a database is not a pfSense problem.

        1 Reply Last reply Reply Quote 0
        • P
          prophet
          last edited by

          I'm not going to argue, but doing a select means "messing up a database"?

          1 Reply Last reply Reply Quote 0
          • F
            free4 Rebel Alliance
            last edited by free4

            I'm also going to argue, but you are trying to re-code exactly what the "Idle Timeout" is doing?

            removing an user in the database isn't enough, you also need to remove the ipfw rules and the ipfw pipes associated to this user

            i have no idea why your users have been disconnected...that isn't logical, a select should not be harmful. however, you should have a look to "Idle Timeout" setting, it does exactly what you are looking for

            P 1 Reply Last reply Reply Quote 0
            • P
              prophet @free4
              last edited by

              @free4 thanks for reply. I am aware of those timeout settings and I know ipfw is involved as well. I need to keep users authenticated as long as possible, basically with manual handling of deauthentication, that's why I'm working in a custom way. But I'm not asking about that, I won't ask the forum about personal and out of standard efforts, I'm just asking what kind of process, if any, could silently reset captive portal users databases like happened to me.

              Thanks

              F 1 Reply Last reply Reply Quote 0
              • F
                free4 Rebel Alliance @prophet
                last edited by free4

                @prophet I really don't have a clue...or maybe one :
                could it be related to this ? https://redmine.pfsense.org/issues/8616

                Did you reconfigured your captive portal while users were connected?

                P 1 Reply Last reply Reply Quote 0
                • P
                  prophet @free4
                  last edited by

                  @free4 i did a "select" query in sqlite captive portal db.
                  i made a script that loops every ip listed in ipfw captiveportal_auth_up zone, with last updated timestamp, and then goes on for deletion of "aged" ip in sqlite database.

                  BUT before proceeding with deletion, just to check if everything was ok i changed "DELETE" statement with a simple "SELECT":

                  sqlite3 $cp "SELECT * FROM captiveportal WHERE ip = '$ip_db';"
                  

                  I still can't understand how it happened to reset my captiveportal auth database, maybe some kind of lock made by pfSense processes?

                  Thanks

                  GertjanG 1 Reply Last reply Reply Quote 0
                  • GertjanG
                    Gertjan @prophet
                    last edited by

                    @prophet said in Captive portal databases are reset!:

                    ... maybe some kind of lock made by pfSense processes?

                    It's probably something like this :

                    Line 1703 - file /etc/inc/captiveportal.inc - function function captiveportal_opendb()

                    When your code manipulates the data base file, it get's locked.
                    At the same moment, a user logs in, logs out, the prune process is called : pfSense calls indirectly - the first "catch" triggers and is followed by a

                    unlink_if_exists($db_path);
                    

                    $db_path was your data base file ;)

                    Look for the word "lock" in the file /etc/inc/captiveportal.inc : pfSense does its best to sync the access to the database (among others) to minimize race conditions.

                    Test : You code would work fine if you shut down the portal fist.

                    No "help me" PM's please. Use the forum, the community will thank you.
                    Edit : and where are the logs ??

                    P 1 Reply Last reply Reply Quote 0
                    • P
                      prophet @Gertjan
                      last edited by

                      @Gertjan thanks, it looks like a solid explanation for my case

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