My setup has been resetted.

fakearia

I am trying to use FreeRADIUS with SQLite, but the following settings are reset after a reboot:

In the /usr/local/etc/raddb/sites-enabled/default file,
I remove the comment from the
account { ### sql DISABLED ### }
section, but after rebooting, the comment is added back.

I create a symbolic link to the
/usr/local/etc/raddb/mods-available/sql
file in the
/usr/local/etc/raddb/mods-enabled/
directory, but this file gets deleted after a reboot.

It’s becoming very challenging to create a solution with pfSense, FreeRADIUS, and SQLite to monitor users' data usage.

stephenw10

You have to use the settings/config in the pfSense package. You may be able to add something as a custom field there but editing the files directly could well be overwritten at boot when the config is regenerated.

If you want to do something completely custom you may be better off running freeradius off the firewall.

Steve

Gertjan

@fakearia

Didn't I already explained this before ?

....
Yes I did : https://forum.netgate.com/topic/189749/pfsense-captive-portal-freeradius-sqlite-configuration-issues/2

pfSense controls the construction of config files of every and any process on the system.
The the core essence of what is pfSense all about.
If you want to have your own config files, you should modify the files that create these files (modifying pfSense, itself)

pfSense, on every radiusd = the Freeradius daemon, re creates the config files (many config files, as this is FreeRadius).
A reboot can be seen as a process restart

So, change the pfSense PHP files (FreeRadius package PHP script files) if you want to modify the process config files.

@fakearia said in My setup has been resetted.:

It’s becoming very challenging to create a solution with pfSense, FreeRADIUS, and SQLite to monitor users' data usage

The challenge isn't pfSense, but what you want to do with it.
pfSense can do what it can do. pfSense can do many things - imho : to much already.
But you (and me, don't worry) we want more !!

So here is the moment to leave the save "click click youpi, I'm a network admin" situation, and start 'coding' yourself.

Or, do what has been done for decades :
Get FreeBSD - or any other BSD Linux flavor, or why not, Linux.
Install the freeradius package.
And now you're in full config file control.
Don't forget to set up the rest, and begin with the firewall rules.

Btw : I've wrote a reply to this :

create a solution with pfSense, FreeRADIUS, and SQLite to monitor users' data usage.

Here it is :

@fakearia

When the user logs in, this info isn't accessible. After all, when the user didn't enter a username, related info can't be looked up.

So I presume that, after login, when the user comes back to the "login page", the login page isn't shown but some other page : the logout page.

edit : in reality, this page is seldom used by captive portal user. As they can't find it. It is possible to have the logout page popped up as soon as the portal user is logged in, but these days (2024) the "Allow popup pages" browser setting is set to "disabled". You know why.

This is the default logout page :

This default logout page, like the login page, can be re created by you. make one, and upload it as the "logout page" :

When the user sees this page, it has a portal session, which means that you, the admin can see it here as 'logged in' :

This list with logged in users is a local PHP database (SQLLite3), maintained by the portal script code.

I've written a small PHP script that dumps this database file to the console :

#!/usr/local/bin/php -q
<?php
        require_once("/etc/inc/util.inc");
        require_once("/etc/inc/functions.inc");
        require_once("/etc/inc/captiveportal.inc");
        /* Read in captive portal db */
        /* Determine number of logged in users for all zones */
        $count_cpusers = 0;
        /* Is portal activated ? */
        if (is_array($config['captiveportal']))
                /* For every zone, do */
                foreach ($config['captiveportal'] as $cpkey => $cp)
                        /* Sanity check */
                        if (is_array($config['captiveportal'][$cpkey]))
                                /* Is zone enabled ? */
                                if (array_key_exists('enable', $config['captiveportal'][$cpkey])) {
                                        $cpzone = $cpkey;
                                        /* Zone selected -> count users and add */
                                        $cpdb = captiveportal_read_db();
                                        foreach ($cpdb as $cpent) {
                                                print_r($cpent);
                                                echo date("m/d/Y H:i:s\n", $cpent[0]);
                                        echo "---------------\n";
                                        }
                                }
?>

Place it in the file /root/cap.php and call it like this :

php -q cap.php

Now, things get interesting.
The logout page is shown, because the session ( variable $cpsession) isn't empty, but set to the session ID. See here : https://github.com/pfsense/pfsense/blob/dc459dc91ccf7bd3f8d536e1571e1bdf2a59f896/src/usr/local/captiveportal/index.php#L90

The session is avaible in the captive portal connected user database, it's actually the database (file) with active session. It's the index '5', or 'sessionid' filed : example :

    [5] => b6e3b9a06ad72e21
    [sessionid] => b6e3b9a06ad72e21

You have your session, look it up in the portal/session database, and you have also, if set, to this :

    [11] => 1048576000
    [traffic_quota] => 1048576000

which is this :

multiplied by 1024 x 1024 to go from Mbytes to bytes : 1000 x 1024 x1024 = 1048576000.

What isn't stored in the session database : the forever, monthly weekly or daily indicator.

Btw : the same value is stored here :

/var/log/radacct/datacounter/daily/max-octets-x - it contains "1048576000".
Where 'x' is the connect user name, and 'daily' is the time period used.

The get your hands on the Time period .... I've found one location : /usr/local/etc/raddb/mods-config/files/authorize .... where you can find :

"x" Cleartext-Password := "abcdefge"

	Acct-Interim-Interval := 600,
	pfSense-Max-Total-Octets := 1048576000,

	Exec-Program-Wait = "/bin/sh /usr/local/etc/raddb/scripts/datacounter_auth.sh x daily"

Last, but not least : no need to change or edit /usr/local/captiveportal/index.php

When you lake your own logout page, you can use html and php !!

Here is a fragment of my own login page :

It will show an entry to type in the voucher code IF voucher usage is activated in the portal.

Some hints / suggestion :
Never ever use PHP variables without checking them first. (edit : lol : this is basic for any language)
When testing, use log lines to test your code. Here is an example : https://github.com/pfsense/pfsense/blob/dc459dc91ccf7bd3f8d536e1571e1bdf2a59f896/src/usr/local/captiveportal/index.php#L194

fakearia

@Gertjan How wonderful of the answer! Thank you for your good reply.
I am an amature. Perhaps I need long time for apply to my system.
I will check this step by step.

Thank you

fakearia

@Gertjan
I did it!
I didn't know how to program at all, but I got a lot of help from ChatGPT with the programming aspects, and from you with the structural aspects of pfSense. I am truly grateful. Thank you very much.

Gertjan

@fakearia

Nice !!

stephenw10

Good job! Perhaps you could document it for other users?