|
Posted
about 15 years
ago
by
anonymous
Hey everyone, I've been running a wifidog server for a little while now for the guest network at the university where I work. Thanks to the locals, the number of user accounts quickly expanded to well over 600 in a matter of days (very few of the
... [More]
users ever validated an account, and rather just kept making new ones with fake emails after the validation period). Obviously, these excess accounts are quite annoying, and I've noticed there doesn't seem to be an easy way to delete accounts. I know a ticket or two has been opened previously mentioning this, and if I recall correctly, simply deleting a username from the database skews statistics?
I just wanted to share a possible solution with you guys. Unfortunately, the setup I run now uses only one network on one hotspot, distributed across many access points around the campus. The server is pretty much only used for having a user login/create an account, and then be redirected and on their way. For administrators, we just monitor the server and clean up users as necessary. Also, I would like to point out that my knowledge of PostgreSQL is probably no where near yours, and I hardly know anything about C/PHP.
That being said, here's what I've set up.
In the SQL database, I created three views (I had hoped for only two, but limited knowledge combined with some error involved in non-validated accounts not having a "timestamp_out" in some cases prevented that). The first view collected accounts from the database that have an "age" of 30 days or more ("age" referring to the number of days between "now" and the most recent "timestampe_out"). Something like this:
CREATE VIEW xxx AS SELECT u.username, to_char(max(c.timestamp_out), 'YYYY-MM-DD') AS last_used, date_part('day', (SELECT now() - max(c.timestamp_out)) AS age FROM users AS u JOIN connections AS c ON u.user_id=c.user_id GROUP BY u.username WHERE (SELECT now() - max(c.timestamp_out) > '30' ORDER BY date_part('day', (SELECT now() - max(c.timestamp_out)) DESC;
I know that looks a little messy. I actually cheated and made a "days_since_last_connection" view for all accounts, then just extracted the >30 aged accounts from it, but that should do it.
Then I made a view for accounts that haven't been validated:
CREATE VIEW xxx AS SELECT * FROM users WHERE account_status = 5 AND date_part('day', (SELECT now() - reg_date)) > '14';
This gives you accounts that were never validated older than 14 days (obviously, the numbers can be changed to whatever).
And finally, those weird accounts that don't have a timestamp_out. This one I did using the "days_since_last_connection" shortcut (it would display a number of usernames with no "age"). I created a view from it to display the age from reg_date where the age was null:
SELECT d.username, d.age, (SELECT now() - u.reg_date) AS real_age FROM days_since_last_connection AS d JOIN users AS U ON d.username=u.username WHERE d.age IS NULL AND (SELECT now() - u.reg_date) > '14 days';
After those views were created, I just wrote a simple PERL script, using the DBI module, that let me select the usernames from these views and delete them from the users table. Your constraints are set up fine to delete connections relative to usernames, which is where I imagine most of your statistical data comes from, but I'm not entirely sure. Anyway, I just have that script running from the crontab every morning and it keeps the database pretty clean.
Sorry for the long ticket, especially if this ends up being worthless. Hoping that this idea could help you guys come up with a simple way to delete accounts easily as I'm sure there is a much better way than what I have done.
Thanks, and thanks for your software. Despite a difficult set up for me, it's been pretty nice ever since it started working.
[Less]
|
