summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB/Users.pm
AgeCommit message (Collapse)AuthorFilesLines
2021-01-20v2rw: Rewrite done, time to clean up old v2 codeYorhel1-49/+0
Yay! There are no more request handlers in the VNDB::* namespace and no more Javascript in data/js/. This cleans up a lot of old legacy code that wasn't fun to maintain.
2020-04-15SQL: Split "perm" column into multiple booleansYorhel1-1/+1
This simplifies several actions and makes permission checks more obvious. This is also yack shaving for another feature I've been planning to add: boardmods should be able to set other users' "board" permission, tagmods for tags, etc. So that partial user bans don't need the full "usermod" permission.
2019-11-15Move pubskin fetching responsibility to VNWeb::HTML::framework_Yorhel1-2/+0
Requiring callers to fetch the right fields and pass it along to framework_() just seems brittle. This implementation requires an extra query but does make an effort to avoid fetching the skin and css when it's not needed.
2019-10-14v2rw: Convert user listingYorhel1-36/+4
2019-10-14Delete the user deletion featureYorhel1-8/+1
It's rather much code just to run a single SQL statement that I almost never need. Incidentally, the feature was also broken because the DELETE permission wasn't granted in perms.sql.
2019-10-12rewards: Fix applying the public skin even when disabledYorhel1-1/+1
2019-10-12v2rw: Convert user notifications interface (/u+/notifies)Yorhel1-70/+0
2019-10-10rewards: Honor public custom css/skinYorhel1-1/+2
2019-10-10rewards: Apply supporters badge and unicode name (almost) everywhereYorhel1-13/+3
Only place where this isn't applied (yet?): Sorting user lists still goes by the old username and board names don't use the new unicode names. I have to say, I quite like the sql_user() and user_() pattern. It's not without problems when applied to everything, but it's good enough for several use cases.
2019-10-03Disallow changing email address to one already used by another accountYorhel1-50/+1
2019-10-03Fix 500 when sorting userlist by vote countYorhel1-1/+1
Broken in 8795f8a55df40603e3e589b584cc5d4c66e78f3a
2019-10-03SQL: Get rid of the users_prefs table, store preferences in users tableYorhel1-33/+4
This bloats the users table a little bit, but that's fine. The main advantage of this change is that we now have a proper schema for user preferences, rather than the schemaless key-value mess we had before. This commit also splits the 'tags_cat' preference up into tags_cont, tags_ero and tags_tech bools, as that's more compact to store and easier to work with. This commit also changes the 'notify_nodbedit' preference to 'notify_dbedit' with inverted meaning. The reason the value was negated in the first place was because the old schemaless approach did not support positive defaults.
2019-09-18v2rw: Convert authentication code to VNWeb::AuthYorhel1-41/+5
More churn! Also converted v3 to use VNWeb::Auth, considering the API is pretty much the same. Converted VNWeb::* to use VNDB::Config directly rather than read from tuwf->{}, converted VNWeb::HTML to use VNWeb::Auth, and updated util/vndb.pl with the new code style. I tested as much as I could, but I'm sure I broke something.
2016-11-27SQL: Use separate role for the website + disallow access to user dataYorhel1-33/+65
Previously the website was connected to the database with a "database owner" user, which has far too many permissions. Now there's a special vndb_site user with only the necessary permissions. The primary reason to do this is to decrease the impact if the site process is compromised. E.g. it's now no longer possible to delete or modify old entry revisions. An attacker can still do a lot of damage, however. Additionally (and this was the main reason to implement this change in the first place), the user sessions, passwords and email data is now not easily accessible anymore. Hopefully, the new user management abstractions will prevent email and password dumps in case of an SQL injection or RCE vulnerability in the site code. Of course, this only works if my implementation is fully correct and there's no privilige escalation vulnerability somewhere. Furthermore, changing your password now invalidates any existing sessions, and the password reset function is disabled for 'usermods' (because usermods can list email addresses from the database, and the password reset function could still allow an attacker to gain access to anyone's account). I also changed the format of the password reset tokens, as they totally don't need to be salted.
2016-01-10DB::Users: Perform case-insensitive email matchingYorhel1-1/+1
This fixes two things: - It's not possible to create two accounts with the same mail address with different case (although the user+xyz@domain trick still works). - The password reset form is now case-insensitive as well. Some people had problems with the case-sensitive behavior in the past.
2015-06-15d7: Replace "top 5 contributors" with "special users" listingYorhel1-1/+4
Having a proper and up-to-date list of moderators is an often requested feature.
2014-10-16Hash session tokens with SHA-1 when storing in DBYorhel1-4/+4
This ensures that, if an attacker evers gets read access to the database, he will not be able to compromise any accounts. SHA-1 suffices here, because the data being hashed is a random 20 byte string. The search space is so damn large that you can't sanely brute force it, nor are rainbow tables any use at that scale. They're not salted. The password reset tokens are also hashed in the database and do include salt, but I've no idea why we did that.
2014-10-15SQL: Merge users.(passwd|salt) in one column + document valuesYorhel1-9/+7
It doesn't make a whole lot to separate the hashed password and the salt from each other, you need both to do anything with them, and from the database perspective they're both completely opaque strings only usable for direct comparison with other hashed strings. This change is mostly as preparation for switching to a proper key derivation function (sha256 isn't...) and to add support for longer and/or binary salt. Because the passwd field now needs to be interpreted in Perl, it's being passed around as a binary string rather than a hex-encoded value. API login is broken in this commit. I'll get to that.
2014-10-11SQL: Convert login_throttle.timeout to a timestamptzYorhel1-3/+3
Easier to work with in custom queries.
2014-08-29Throttle failed login attempts (10/day)Yorhel1-0/+16
2013-03-09Allow only one account registration per /48 IPv6 block per dayYorhel1-1/+1
I think this is the only thing necessary to add full IPv6 support to VNDB. It's not actually necessary, but without this modification it will become way too easy to flood the site with new accounts.
2011-08-23Added email confirmation to registration processYorhel1-3/+3
2011-04-30Replaced user ranks with a permission systemYorhel1-2/+2
This is far more flexible.
2011-04-03Bugfix: use the 'vnlists' table to calculate stats on user pageYorhel1-1/+1
2011-01-27Order "all notifications" with new notifications firstYorhel1-3/+3
That's more what you'd expect from a list that functions somewhat as a short-lived FIFO "archive".
2010-12-23Converted the notify_announce and notify_dbedit preferencesYorhel1-3/+3
And renamed notify_dbedit to notify_nodbedit, since the default is to provide a notify on a database edit. Also fixed a few bugs along the way.
2010-12-23Converted the show_list pref. to users_prefs and inverted the valueYorhel1-4/+8
In the users_prefs table, the default value should evaluate to 'false' in Perl, so show_list had to be inverted to hide_list.
2010-12-23Converted the show_nsfw preference to use the users_prefs tableYorhel1-2/+2
2010-12-23Added users_prefs table and removed users.(skin|customcss)Yorhel1-4/+27
Will convert the other preferences later.
2010-11-14SQL: Added ON DELETE clause to all foreign keys referencing users (id)Yorhel1-15/+1
This makes deleting user accounts less error prone. It also seems I forgot to git add update_2.14.sql in an earlier commit, sorry about that.
2010-11-06Bugfix: delete/update all references when deleting a userYorhel1-0/+5
Really need a cleaner solution for that. PostgreSQL actually provides a better solution, need to change to that.
2010-02-05Notifications: Added 'announce' notificationYorhel1-2/+2
This one is also configurable, but mainly because I want to avoid generating several thousands of notifications for a single action...
2010-02-05Notifications: Added 'dbedit' notificationYorhel1-2/+2
And added a settings window where you can disable this notification, which is something you really want to do if you're an active contributor...
2010-02-01Cache the title and userid of the notificationsYorhel1-9/+2
These aren't likely to change anyway, and things will become less easy to display when other types of notifications are added.
2010-01-26SQL: Replaced sessions.expiration with sessions.addedYorhel1-5/+2
An expiration date doesn't make much sense if it's both not used and if it can't be configured by the user, so just make this a timestamp to indicate when the session has been added, which, while still not really used, is more valuable.
2010-01-26Keep track of when a session has last been usedYorhel1-1/+9
If we're going to automatically remove older sessions, it would make more sense to remove unused sessions, rather than old sessions that are still in use. But we first need to keep track of when a session has last been used to do so...
2010-01-26Made a start on the notification systemYorhel1-16/+66
The current setup should be able to handle all kinds of notifications, though only PMs are implemented at this point. More to come.
2010-01-24Automatically generate the skin credits on d7Yorhel1-1/+3
Added a userid field in the skin config files, from which the credits are loaded. Now I don't have to constantly update d7 for every language when something changes in the skin files.
2009-11-27DB: Abstracted all ORDER BY clauses in the DB abstraction layerYorhel1-3/+11
The ORDER BY was previously specified using an 'order' argument, which would then be directly inserted into the query. The new method is the same as what I used for the public API: two 'sort' and 'reverse' arguments. This should be less error-prone and more readable. This changes quite a lot of code, so I hope I haven't forgotten to update something along the way.
2009-11-15Auth: Combined dbSessionCheck into dbUserGetYorhel1-14/+11
This one query is a bit faster than the two queries executed seperately, and with a query that is executed on each pageview it does matter. Ideally, the dbUserMessageCount() is cached and fetched with the same query, this should save another 1-2ms. But this is probably not worth the extra code it would require.
2009-10-10Greatly reduced default number of columns returned by dbUserGetYorhel1-3/+7
Most of the columns are only used in some rare situations, so fetching all that information is unecessary. To fetch this information, a what => 'extended' is now required. This change should be most noticable for the user list (now less than half of the previous data is fetched from the database).
2009-10-10Get unread posts count in htmlHeader() instead of Util::AuthYorhel1-10/+16
This way the counter resets itself immediately when opening the unread thread page, which is more intuitive.
2009-10-10Display number of unread posts in user menuYorhel1-1/+7
Instead of displaying the total number of threads. Posts are marked as read when the thread is opened.
2009-08-12Added global user votes ignore listYorhel1-2/+2
2009-08-08Converted users.registered to timestamptzYorhel1-5/+5
Just a few more to go...
2009-08-08Improved handling of the timestamp columns in anime and session tablesYorhel1-7/+2
Anything fetched from the DB to Perl should be converted to a UNIX timestamp, and everything that goes from Perl to the DB should be converted from a UNIX timestamp to a timestamptz data type. Also, when creating a session, don't rely on the fact that the expiration default happens to be the same as the cookie expiration time calculated in Perl. It's cleaner to calculate the date at one place and then use that everywhere else.
2009-08-01Don't try to DELETE FROM vnlists in dbUserDel()Yorhel1-1/+0
The vnlists table has been removed in an earlier update. Though I don't think I've explicitely removed it from an update script. For those who still have this table, check commit 9a07126464f4ca05f33e7375883ada66abe7dcf4 - it has some changes not present in any of the update scripts but which are done on the main (vndb.org & beta) databases.
2009-07-31Use bytea data type to store session tokensYorhel1-10/+8
To be consistent with users.passwd - hashes are stored in binary. All conversion from/to hex is done in the DB layer.
2009-07-31Fetch hex hashes from dbUserGet() and do comparison in hexYorhel1-3/+2
For consistency, it's best to do everything related to hashes in hexadecimal notation in Perl.
2009-07-29Changed sessions.expiration column to timestamp3dB1-3/+10
-- Updated SQL files to reflect column type change. -- Subroutine dbSessionAdd rewritten to accept an expiration timestap as an optional third argument.