Updating tables with SQL and data usage
-
Hello Netgate Forum,
I've recently setup Captive portal on my PFSENSE. It is correctly authenticating with FreeRadius and assigning the VLAN and giving access. So that's great. However, I'm having two problems that may or may not be related.
-
I setup a SQL (MariaDB) db on my Synology. I was able to import the schema, the tables all seem to be there and I don't see any connection errors on a verbose radiusd startup. But the tables don't seem to populate with anything at all. After connecting I do see a "Ignoring "sql" message, though I'm guessing this is for the secondary server, which I don't have enabled. I feel like there's a connection or a flag in the config file somewhere that isn't visible in the GUI, but I'm lost on where to look. I do have one row in one of my sql tables which was definitely added by FR in the postauth table, but that's it. Seems like there should be a lot more, even though I'm still in testing and have only got a couple users testing for me.
-
I set up the "Enable Pass-Through MAC Additions" since it seemed like a more convenient option for the users rather than entering a u:p over and over. But it seems like since I've done this the data usage values stopped working. I'm not sure if this is an expected function of pass-through (it does say that a MAC in this list "skips" auth, so maybe it just doesn't test for data usage any more). But the problems with no data being added to my SQL database feel like they could also be related. But when I turn off SQL and go back to Radius doing its own data I still see the issue.
So some questions:
Is anyone aware of the behavior of the Pass Through MAC addition? Is it correct that this bypasses auth and thus data usage or does someone having it working with both and its just something in my setup?
I don't have to use SQL, it just seemed like a more convenient way for me to track data usage and see if people are hitting the limits regularly or not if I could set up some queries I can run daily. I don't see a lot of options for this in PFSENSE itself, apart from doing it indirectly with BandwidthD or similar. It also seems from some other threads on here that I might be able to add sql queries to the login page to show people how much data they have left for the day, which would also be nice.
From reading on here some replies by Gertjan and others, it seems that the Freeradius enabled by the GUI skips some steps (reading users form the SQL DB, possibly adding other data to the tables rather than with their own files). I'm willing to dig into the shell if needed, but I could benefit from a bit of a map as to where to look. Any assistance or pointers would be appreciated.
Quick Description: Merchant ship Entertainment network. VLAN for Movie server with Smart TVs, Unifi system for Wifi on its own VLAN, both into PFSENSE with three WAN connections. I would like to be able to allow users to access the Starlink for 1.5-2GB / day resetting daily so we stay under our caps, and have them log in so I can limit it to individual users.
Thanks,
KT -
-
"FreeRadius" can optionally use an SQL database.
An true, only one table is actually used :
as that table contains the start, stop events, updated every 300 (?) seconds where the 'bytes used' for an open session are updated.
The other tables : not used.
That said,the radpostauth table contains a lot of lines like :
where 'cuisine' is a logged in user.
I'm not sure if this is done because I modded something, or if it's done by default.The biggest bummer (imho) is that the table "radcheck", which could (should ?) contain the 'user' and 'password', like :
isn't used.
This page : Services > FreeRADIUS > Users is stored in a flat text file : /usr/local/etc/raddb/mods-config/files/authorize
I would prefer it if the "Services > FreeRADIUS > Users" was placed into the radcheck table, and that FreeRadius uses that table.
This means you could, by changing the table, change the portal login behavior, without accessing pfSense at all.FreeRadius has a lot of options/settings/possibilities. Only a small subset is supported by the pfSense GUI.
The main 'master' FreeRadius config file is here : /usr/local/etc/raddb/sites-enabled/default
If you control the generation of that file, you could instruct FreeRadius to do more with the SQL database.
This means you need some time, as you need to rad/learn about world's most messy open source source project : (Free)Radius.
Imho : apache2/nginx is so/so, bind is nasty. postfix is worse, and FreeRadius is ... well, it will take a lot of your time.Most of the fun stuff happens here : /usr/local/pkg/freeradius.inc
@reo-kmh said in Updating tables with SQL and data usage:
"Enable Pass-Through MAC Additions"
Because these MAC-added devices won't use the FreeRadius authentication and accounting, FreeRadius can't do the accounting for you when "Enable Pass-Through MAC Additions" is used.
That doesn't means that the conso info doesn't exist, it does.
See here : Diagnostics > Limiter Info : for every connected user, using Radius or not, IP or MAC listed, there is a Limiter/Schedulers pair. These queues contain the number of bytes send/received.
I while ago, on this forum, I've saw how to access these. It needs some scripting of course.Btw : be ware : if your pfSense is on a boat on the other side of the planet and you mess up .... what do you do ? Rent a helicopter ?
-
Thanks for the reply. For some reason I'm seeing the radpostauth update, but nothing in radacct (literally zero rows). So something seems to be messed up on my side, but its weird because if I just wasn't connecting I wouldn't see anything. I set up accounting pretty basic as described in all the tutorials, but maybe something is messed up there.
I agree that it would be great to have access to users through SQL. As I described, I'm managing the network on the ship, and as such we have new users coming on every 4-5 weeks where I need to update passwords, and it would be lovely to create a CSV I could upload to update rather than going one by one in the GUI. I've seen there are a few solutions for this out there using the users file, but it seems prone to error, and I'm not always fully confident that the people who relieve me will be able to make these changes without clicking the "wrong" thing. If nothing else it would be great to be able to do a user "import from file" in the GUI, so maybe I should write something up to request it.
Thanks very much for the info about the Pass-Through MAC Addition. I was starting to suspect, but knowing saves me a lot of time. (I think I'll use that only for the Captains, who don't use much data but are the most likely to call me if they can't connect.)
@Gertjan said in Updating tables with SQL and data usage:
Btw : be ware : if your pfSense is on a boat on the other side of the planet and you mess up .... what do you do ? Rent a helicopter ?
Yes, this is a concern. Its only the crew entertainment, so the ship keeps moving either way, but it makes me very unpopular if the movie server goes down and people can't call home. So I'm trying to do this slowly and test a lot before moving anything into "production". Based on what I've read from you and others, I think I'm going to try to get it working well without SQL for now, and as long as the data caps are kicking people and resetting daily and the CP is allowing logins, then I can figure out the rest when I get back from vacation and have more time to really dig into the freeradius setup. As convenient as it is, I wonder if I'm better off installing freeradius standalone so I can set it up the way I want without the weird little exceptions. Of course, from your description of the complexity, that may be a miserable project. Anyway, thanks.
-
@reo-kmh said in Updating tables with SQL and data usage:
I agree that it would be great to have access to users through SQL
I wasn't sure if it was a lot of work, so I tried it again.
You have to edit /
The file is here : /usr/local/pkg/freeradius.inc (start by making a backup of this file !)
There a 3 places where you can find :
authorize {Every time, a little bit lower you'll find
... files -sql ...change it for :
... # files -sql ...Add a user like this to the "radcheck" database :
Go to Services > FreeRADIUS > Settings and just hit save at the bottom.
Now test :
=> this user was SQL authorized !
Now all you need is a 6+ year old Chinese or Indian boy (girl) who writes you a web server page that allows you to edit the SQL table.
Europe or the States : I'm not sure. BASIC, Pascal and C (PHP etc) was mandatory in the past (the eighties ?!) but since then actually using a program language has been put back in the "rocket science" category for "some reason". Let's say the government doesn't want the public to know how 'computers' work
Or install phpmyadmin on your SQL server, that's what I did.edit :
I just copied the entire user list ( this one : Services > FreeRADIUS > Users ) into the radcheck.
Just a user name and a password. I'm pretty sure you can add other stuff - use /usr/local/etc/raddb/mods-config/files/authorize as an example.I could now login into the captive portal, and it worked.
Ones logged in, when I changed the password, and because I've set this :
the user was thrown of the portal within a minute
-
This is beautiful.
I've managed to get things working good enough to accomplish my first-level goals and turn it over to my relief so I get to go on vacation without getting emails about radius. And I noticed from my attempts earlier that as I was making changes trying to get SQL to update the Portal would stop working every so often and need to be restarted, so I'm going to leave things here for now. I was able to brute force a bash script that could calculate daily data usage as a percentage of the cap by poking around the datacounter directory and scp it to my desktop, and my relief will just have to live with the GUI user manager for a few trips.
But when I get back and have more than a couple days I'm going to dig into why radacct isn't updating then work on these changes you've outlined. Being able to view and edit all this through SQL will be a huge advance. (No smart children onboard so I added pHPmyadmin to my synology immediately after MariaDB.)
Thanks so much for this, I really appreciate it.