summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB/Users.pm
AgeCommit message (Collapse)AuthorFilesLines
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.
2009-07-29Altered the way sessions are handled in some cases3dB1-0/+1
-- Deleting a user now deletes all sessions too. -- Changing a user password now deletes all stored sessions for that user.
2009-07-28Modified DB libraries and user handlers dealing with passwords to work with ↵3dB1-4/+4
new auth system. -- Modified all database insertion and edit subroutines to be able to work with the new 'salt' column. -- Modified all subroutines dealing with password manipulation to use authPreparePass to encrypt passwords and generate salts.
2009-07-27Applied small adjustments and reorganized DB library code.3dB1-1/+33
-- Removed "ON UPDATE" from sessions.uid column FK -- Fixed dbSessionCheck to work as described -- Merged DB/Sessions.pm into DB/Users.pm
2009-07-07Display thread count in user menuYorhel1-1/+3
This adds a small performance penalty, but should make it a lot easier for people to notice that someone is asking for his/her attention. Ideally, this count would be the number of *unread* *posts*, rather than *total* number of *threads*, but we don't store read the state (yet).
2009-07-03Search box on user listYorhel1-1/+4
2009-03-08Tag stats on user pages and listYorhel1-0/+2
2009-01-10Limit account creation to one account a day per IPYorhel1-3/+8
This would at least annoy the people who'll try to manipulate the rankings, and will make finding duplicate users a bit easier. (Not that it's really a problem at the moment)
2009-01-02Added custom CSS field to user profileYorhel1-1/+1
2008-12-23Added skin selector to profile pageYorhel1-1/+1
2008-12-11Forum & list stats to user pagesYorhel1-4/+15
They're relatively cheap on server resources and easy to create, so why not?
2008-12-112 Bugfixes with user registrationYorhel1-1/+1
2008-12-09Code cleanup: removed leading spaces and combined ↵Yorhel1-2/+2
DB/{Votes,VNList,WishList}.pm into ULists.pm
2008-11-11Removed users.flags and used two boolean columns insteadYorhel1-1/+1
I wasn't planning on really touching the database in the rewrite, but oh well, I've always wanted to do this, anyway.