pfSense Captive Portal + FreeRADIUS + SQLite Configuration Issues

[fakearia]

I am working on setting up a system combining pfSense's Captive Portal, FreeRADIUS, and SQLite. The goal is to allow users to log in via the Captive Portal, display their data usage and remaining quota, authenticate and track usage via FreeRADIUS, and store usage data in SQLite. The system is designed to allocate a set amount of data each month.

However, I am encountering the following issues:

1. On the Captive Portal login page, I want to display the user's data usage and the total amount of data available for the current month, but it shows 0MB. It seems like the username is not being recognized correctly. What should I check or modify to resolve this?
   <?php

$db = new SQLite3('/tmp/freeradius.db');
$username = $_SESSION['username'];

$result = $db->query("SELECT SUM(AcctInputOctets + AcctOutputOctets) AS total_usage FROM radacct WHERE username = '$username' AND AcctStartTime BETWEEN date('now', 'start of month') AND date('now', 'start of month', '+1 month', '-1 day')");

if ($result) {
$row = $result->fetchArray(SQLITE3_ASSOC);
$total_usage_mb = round($row['total_usage'] / (1024 * 1024), 2);
} else {
$total_usage_mb = 0;
}

$result = $db->query("SELECT value FROM radcheck WHERE username = '$username' AND attribute = 'Max-Monthly-Data'");

if ($result) {
$row = $result->fetchArray(SQLITE3_ASSOC);
$allocated_data_mb = round($row['value'] / (1024 * 1024), 2);
} else {
$allocated_data_mb = 0;
}

if ($allocated_data_mb > 0) {
$usage_percentage = round(($total_usage_mb / $allocated_data_mb) * 100, 2);
} else {
$usage_percentage = 0;
}
?>

in html

<?= gettext("The portal session is connected.") ?>

<p><?= gettext("Data Usage This Month: ") ?><?= $total_usage_mb ?> MB / <?= $allocated_data_mb ?> MB</p>

<div style="width: 100%; background-color: #f3f3f3; border-radius: 13px; padding: 3px;">
<div style="width: <?= $usage_percentage ?>%; background-color: #4CAF50; height: 24px; border-radius: 10px; text-align: center; color: white;">
<?= $usage_percentage ?>%
</div>
</div>

2. When I reboot the PC, the symbolic links in ./mod/available/sql and ./mod/enable disappear, and the sql module's comments in ./raddb/site-enable/default are re-enabled automatically. Why is this happening, and how can I prevent it?

[Gertjan]

Using SQL and chosing for SQLight ?
Didn't know that was possible / was an option.
I use FreeRadius, but use a 'SQL' server (MariaDB on my NAS).

"SELECT value FROM radcheck WHERE username = '$username' AND attribute = 'Max-Monthly-Data'");

Did you modify the FreeRadius config files manyally so it adds "attribute" in the radcheck table ?
I see just this :

= the user name and password. No other colums.

edit : wait : by default, this table is empty as pfSense uses the GUI to create a file ( this file : /usr/local/etc/raddb/mods-config/files/authorize ) that contains the users, passwords and some other stuff.

Be ware : FreeRadius can have thousands of options, pfSense uses (enables) just a few of them.
The rest is hard coded / not used.

@fakearia said in pfSense Captive Portal + FreeRADIUS + SQLite Configuration Issues:

Why is this happening, and how can I prevent it?

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)