Captive portal databases are reset!

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

[Grimson]

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

[prophet]

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

[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

[prophet]

@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

[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?

[prophet]

@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

[Gertjan]

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

[prophet]

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