diff options
-rw-r--r-- | data/global.pl | 1 | ||||
-rw-r--r-- | lib/VNDB/DB/Users.pm | 98 | ||||
-rw-r--r-- | lib/VNDB/Handler/Users.pm | 67 | ||||
-rw-r--r-- | lib/VNDB/Util/Auth.pm | 140 | ||||
-rw-r--r-- | util/sql/all.sql | 4 | ||||
-rw-r--r-- | util/sql/func.sql | 108 | ||||
-rw-r--r-- | util/sql/perms.sql | 80 | ||||
-rw-r--r-- | util/sql/schema.sql | 5 | ||||
-rw-r--r-- | util/sql/superuser_init.sql | 13 |
9 files changed, 377 insertions, 139 deletions
diff --git a/data/global.pl b/data/global.pl index 1e46053b..30e002be 100644 --- a/data/global.pl +++ b/data/global.pl @@ -43,6 +43,7 @@ our %S; ch_size => [ 256, 300 ], # max. w*h of char images cv_size => [ 256, 400 ], # max. w*h of cover images # bit flags (Flag 8 was used for staffedit, now free to re-use) + # The 'usermod' flag is hardcoded in sql/func.sql for user_* functions. permissions => {qw| board 1 boardmod 2 edit 4 tag 16 dbmod 32 tagmod 64 usermod 128 affiliate 256 |}, default_perm => 1+4+16, # Keep synchronised with the default value of users.perm default_tags_cat=> 'cont,tech', diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm index d6776b2b..84ff10f2 100644 --- a/lib/VNDB/DB/Users.pm +++ b/lib/VNDB/DB/Users.pm @@ -6,15 +6,16 @@ use warnings; use Exporter 'import'; our @EXPORT = qw| - dbUserGet dbUserEdit dbUserAdd dbUserDel dbUserPrefSet - dbSessionAdd dbSessionDel dbSessionUpdateLastUsed + dbUserGet dbUserEdit dbUserAdd dbUserDel dbUserPrefSet dbUserLogin dbUserLogout + dbUserUpdateLastUsed dbUserEmailExists dbUserGetMail dbUserSetMail dbUserSetPerm dbUserAdminSetPass + dbUserResetPass dbUserIsValidToken dbUserSetPass dbNotifyGet dbNotifyMarkRead dbNotifyRemove dbThrottleGet dbThrottleSet |; -# %options->{ username passwd mail session uid ip registered search results page what sort reverse notperm } -# what: notifycount stats extended prefs hide_list +# %options->{ username session uid ip registered search results page what sort reverse notperm } +# what: notifycount stats scryptargs extended prefs hide_list # sort: username registered votes changes tags sub dbUserGet { my $s = shift; @@ -26,6 +27,7 @@ sub dbUserGet { @_ ); + my $token = unpack 'H*', $o{session}||''; $o{search} =~ s/%// if $o{search}; my %where = ( $o{username} ? ( @@ -34,8 +36,6 @@ sub dbUserGet { 'SUBSTRING(username from 1 for 1) = ?' => $o{firstchar} ) : (), !$o{firstchar} && defined $o{firstchar} ? ( 'ASCII(username) < 97 OR ASCII(username) > 122' => 1 ) : (), - $o{mail} ? ( - 'LOWER(mail) = LOWER(?)' => $o{mail} ) : (), $o{uid} && !ref($o{uid}) ? ( 'id = ?' => $o{uid} ) : (), $o{uid} && ref($o{uid}) ? ( @@ -48,8 +48,8 @@ sub dbUserGet { 'registered > to_timestamp(?)' => $o{registered} ) : (), $o{search} ? ( 'username ILIKE ?' => "%$o{search}%") : (), - $o{session} ? ( - q|s.token = decode(?, 'hex')| => unpack 'H*', $o{session} ) : (), + $token ? ( + q|user_isloggedin(id, decode(?, 'hex')) IS NOT NULL| => $token ) : (), $o{notperm} ? ( 'perm & ~(?::smallint) > 0' => $o{notperm} ) : (), ); @@ -57,8 +57,9 @@ sub dbUserGet { my @select = ( qw|id username c_votes c_changes c_tags|, q|extract('epoch' from registered) as registered|, - $o{what} =~ /extended/ ? qw|mail perm ign_votes passwd| : (), + $o{what} =~ /extended/ ? qw|perm ign_votes| : (), # mail $o{what} =~ /hide_list/ ? 'up.value AS hide_list' : (), + $o{what} =~ /scryptargs/ ? 'user_getscryptargs(id) AS scryptargs' : (), $o{what} =~ /notifycount/ ? '(SELECT COUNT(*) FROM notifications WHERE uid = u.id AND read IS NULL) AS notifycount' : (), $o{what} =~ /stats/ ? ( @@ -69,11 +70,10 @@ sub dbUserGet { '(SELECT COUNT(DISTINCT tag) FROM tags_vn WHERE uid = u.id) AS tagcount', '(SELECT COUNT(DISTINCT vid) FROM tags_vn WHERE uid = u.id) AS tagvncount', ) : (), - $o{session} ? q|extract('epoch' from s.lastused) as session_lastused| : (), + $token ? qq|extract('epoch' from user_isloggedin(id, decode('$token', 'hex'))) as session_lastused| : (), ); my @join = ( - $o{session} ? 'JOIN sessions s ON s.uid = u.id' : (), $o{what} =~ /hide_list/ || $o{sort} eq 'votes' ? "LEFT JOIN users_prefs up ON up.uid = u.id AND up.key = 'hide_list'" : (), ); @@ -119,10 +119,7 @@ sub dbUserEdit { my %h; defined $o{$_} && ($h{$_.' = ?'} = $o{$_}) - for (qw| username mail perm ign_votes email_confirmed |); - $h{'passwd = decode(?, \'hex\')'} = unpack 'H*', $o{passwd} - if defined $o{passwd}; - + for (qw| username ign_votes email_confirmed |); return if scalar keys %h <= 0; return $s->dbExec(q| @@ -133,11 +130,9 @@ sub dbUserEdit { } -# username, pass(ecrypted), mail, [ip] +# username, mail, [ip] sub dbUserAdd { - my($s, @o) = @_; - $s->dbRow(q|INSERT INTO users (username, passwd, mail, ip) VALUES(?, decode(?, 'hex'), ?, ?) RETURNING id|, - $o[0], unpack('H*', $o[1]), $o[2], $o[3]||$s->reqIP)->{id}; + $_[0]->dbRow(q|INSERT INTO users (username, mail, ip) VALUES(?, ?, ?) RETURNING id|, $_[1], $_[2], $_[3]||$_[0]->reqIP)->{id}; } @@ -156,27 +151,64 @@ sub dbUserPrefSet { } -# Adds a session to the database -# uid, 40 character session token -sub dbSessionAdd { - $_[0]->dbExec(q|INSERT INTO sessions (uid, token) VALUES(?, decode(?, 'hex'))|, $_[1], unpack 'H*', $_[2]); +# uid, encpass, token +sub dbUserLogin { + $_[0]->dbRow( + q|SELECT user_login(?, decode(?, 'hex'), decode(?, 'hex')) AS r|, + $_[1], unpack('H*', $_[2]), unpack('H*', $_[3]) + )->{r}||0; } -# Deletes session(s) from the database -# If no token is supplied, all sessions for the uid are destroyed -# uid, token (optional) -sub dbSessionDel { - my($s, @o) = @_; - my %where = ('uid = ?' => $o[0]); - $where{"token = decode(?, 'hex')"} = unpack 'H*', $o[1] if $o[1]; - $s->dbExec('DELETE FROM sessions !W', \%where); +# uid, token +sub dbUserLogout { + $_[0]->dbExec(q|SELECT user_logout(?, decode(?, 'hex'))|, $_[1], unpack 'H*', $_[2]); } # uid, token -sub dbSessionUpdateLastUsed { - $_[0]->dbExec(q|UPDATE sessions SET lastused = NOW() WHERE uid = ? AND token = decode(?, 'hex')|, $_[1], unpack 'H*', $_[2]); +sub dbUserUpdateLastUsed { + $_[0]->dbExec(q|SELECT user_update_lastused(?, decode(?, 'hex'))|, $_[1], unpack 'H*', $_[2]); +} + + +sub dbUserEmailExists { + $_[0]->dbRow(q|SELECT user_emailexists(?) AS r|, $_[1])->{r}; +} + + +sub dbUserIsValidToken { + $_[0]->dbRow(q|SELECT user_isvalidtoken(?, decode(?, 'hex')) AS r|, $_[1], unpack 'H*', $_[2])->{r}; +} + + +sub dbUserResetPass { + $_[0]->dbRow(q|SELECT user_resetpass(?, decode(?, 'hex')) AS r|, $_[1], unpack 'H*', $_[2])->{r}; +} + + +sub dbUserSetPass { + $_[0]->dbRow(q|SELECT user_setpass(?, decode(?, 'hex'), decode(?, 'hex')) AS r|, $_[1], unpack('H*', $_[2]), unpack('H*', $_[3]))->{r}; +} + + +sub dbUserGetMail { + $_[0]->dbRow(q|SELECT user_getmail(?, ?, decode(?, 'hex')) AS r|, $_[1], $_[2], unpack 'H*', $_[3])->{r}; +} + + +sub dbUserSetMail { + $_[0]->dbExec(q|SELECT user_setmail(?, ?, decode(?, 'hex'), ?)|, $_[1], $_[2], unpack('H*', $_[3]), $_[4]); +} + + +sub dbUserSetPerm { + $_[0]->dbExec(q|SELECT user_setperm(?, ?, decode(?, 'hex'), ?)|, $_[1], $_[2], unpack('H*', $_[3]), $_[4]); +} + + +sub dbUserAdminSetPass { + $_[0]->dbExec(q|SELECT user_admin_setpass(?, ?, decode(?, 'hex'), decode(?, 'hex'))|, $_[1], $_[2], unpack('H*', $_[3]), unpack('H*', $_[4])); } diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm index 5b6888cb..d1f0df93 100644 --- a/lib/VNDB/Handler/Users.pm +++ b/lib/VNDB/Handler/Users.pm @@ -145,7 +145,7 @@ sub userpage { sub login { my $self = shift; - return $self->resRedirect('/') if $self->authInfo->{id}; + return $self->resRedirect('/', 'temp') if $self->authInfo->{id}; my $tm = $self->dbThrottleGet(norm_ip($self->reqIP)); if($tm-time() > $self->{login_throttle}[1]) { @@ -209,21 +209,18 @@ sub logout { sub newpass { my $self = shift; - return $self->resRedirect('/') if $self->authInfo->{id}; + return $self->resRedirect('/', 'temp') if $self->authInfo->{id}; - my($frm, $u); + my($frm, $uid, $token); if($self->reqMethod eq 'POST') { return if !$self->authCheckCode; $frm = $self->formValidate({ post => 'mail', template => 'email' }); if(!$frm->{_err}) { - $u = $self->dbUserGet(mail => $frm->{mail})->[0]; - $frm->{_err} = [ 'No user found with that email address' ] if !$u || !$u->{id}; + ($uid, $token) = $self->authResetPass($frm->{mail}); + $frm->{_err} = [ 'No user found with that email address' ] if !$uid; } if(!$frm->{_err}) { - my %o; - my $token; - ($token, $o{passwd}) = $self->authPrepareReset(); - $self->dbUserEdit($u->{id}, %o); + my $u = $self->dbUserGet(uid => $uid)->[0]; my $body = sprintf "Hello %s,\n\nYour VNDB.org login has been disabled, you can now set a new password by following the link below:\n\n" ."%s\n\nNow don't forget your password again! :-)\n\nvndb.org", @@ -253,7 +250,7 @@ sub newpass { sub newpass_sent { my $self = shift; - return $self->resRedirect('/') if $self->authInfo->{id}; + return $self->resRedirect('/', 'temp') if $self->authInfo->{id}; $self->htmlHeader(title => 'New password', noindex => 1); div class => 'mainbox'; h1 'New password'; @@ -267,14 +264,14 @@ sub newpass_sent { sub setpass { my($self, $uid) = @_; - return $self->resRedirect('/') if $self->authInfo->{id}; + return $self->resRedirect('/', 'temp') if $self->authInfo->{id}; my $t = $self->formValidate({get => 't', regex => qr/^[a-f0-9]{40}$/i }); return $self->resNotFound if $t->{_err}; $t = $t->{t}; - my $u = $self->dbUserGet(uid => $uid, what => 'extended')->[0]; - return $self->resNotFound if !$u || !$self->authValidateReset($u->{passwd}, $t); + my $u = $self->dbUserGet(uid => $uid)->[0]; + return $self->resNotFound if !$u || !$self->authIsValidToken($u->{id}, $t); my $frm; if($self->reqMethod eq 'POST') { @@ -286,10 +283,8 @@ sub setpass { push @{$frm->{_err}}, 'Passwords do not match' if $frm->{usrpass} ne $frm->{usrpass2}; if(!$frm->{_err}) { - my %o = (email_confirmed => 1); - $o{passwd} = $self->authPreparePass($frm->{usrpass}); - $self->dbUserEdit($uid, %o); - return $self->authCreateSession($u->{username}, "/u$uid"); + $self->dbUserEdit($uid, email_confirmed => 1); + return $self->authSetPass($uid, $frm->{usrpass}, "/u$uid", token => $t) } } @@ -306,7 +301,7 @@ sub setpass { sub register { my $self = shift; - return $self->resRedirect('/') if $self->authInfo->{id}; + return $self->resRedirect('/', 'temp') if $self->authInfo->{id}; my $frm; if($self->reqMethod eq 'POST') { @@ -323,7 +318,7 @@ sub register { push @{$frm->{_err}}, 'Someone already has this username, please choose another name' if $frm->{usrname} eq 'anonymous' || !$frm->{_err} && $self->dbUserGet(username => $frm->{usrname})->[0]{id}; push @{$frm->{_err}}, 'Someone already registered with that email address' - if !$frm->{_err} && $self->dbUserGet(mail => $frm->{mail})->[0]{id}; + if !$frm->{_err} && $self->dbUserEmailExists($frm->{mail}); # Use /32 match for IPv4 and /48 for IPv6. The /48 is fairly broad, so some # users may have to wait a bit before they can register... @@ -332,8 +327,8 @@ sub register { if !$frm->{_err} && $self->dbUserGet(ip => $ip =~ /:/ ? "$ip/48" : $ip, registered => time-24*3600)->[0]{id}; if(!$frm->{_err}) { - my($token, $pass) = $self->authPrepareReset(); - my $uid = $self->dbUserAdd($frm->{usrname}, $pass, $frm->{mail}); + my $uid = $self->dbUserAdd($frm->{usrname}, $frm->{mail}); + my(undef, $token) = $self->authResetPass($frm->{mail}); my $body = sprintf "Hello %s,\n\n" ."Someone has registered an account on VNDB.org with your email address. To confirm your registration, follow the link below.\n\n" ."%s\n\n" @@ -369,7 +364,7 @@ sub register { sub register_done { my $self = shift; - return $self->resRedirect('/') if $self->authInfo->{id}; + return $self->resRedirect('/', 'temp') if $self->authInfo->{id}; $self->htmlHeader(title => 'Account created', noindex => 1); div class => 'mainbox'; h1 'Account created'; @@ -416,9 +411,6 @@ sub edit { ); push @{$frm->{_err}}, 'Passwords do not match' if ($frm->{usrpass} || $frm->{usrpass2}) && (!$frm->{usrpass} || !$frm->{usrpass2} || $frm->{usrpass} ne $frm->{usrpass2}); - push @{$frm->{_err}}, 'Invalid password' - if !($self->authInfo->{id} != $u->{id} && $self->authCan('usermod')) - && ($frm->{usrpass} || $frm->{usrpass2}) && !$self->authCheck($u->{username}, $frm->{curpass}); if(!$frm->{_err}) { $frm->{skin} = '' if $frm->{skin} eq $self->{skin_default}; @@ -426,23 +418,34 @@ sub edit { my $tags_cat = join(',', sort @{$frm->{tags_cat}}) || 'none'; $self->dbUserPrefSet($uid, tags_cat => $tags_cat eq $self->{default_tags_cat} ? '' : $tags_cat); + my %o; if($self->authCan('usermod')) { $o{username} = $frm->{usrname} if $frm->{usrname}; - $o{perm} = 0; - $o{perm} |= $self->{permissions}{$_} for(@{ delete $frm->{perms} }); + $o{ign_votes} = $frm->{ign_votes} ? 1 : 0; + + my $perm = 0; + $perm |= $self->{permissions}{$_} for(@{ delete $frm->{perms} }); + $self->dbUserSetPerm($u->{id}, $self->authInfo->{id}, $self->authInfo->{token}, $perm); } - $o{mail} = $frm->{mail}; - $o{passwd} = $self->authPreparePass($frm->{usrpass}) if $frm->{usrpass}; - $o{ign_votes} = $frm->{ign_votes} ? 1 : 0 if $self->authCan('usermod'); + $self->dbUserSetMail($u->{id}, $self->authInfo->{id}, $self->authInfo->{token}, $frm->{mail}); $self->dbUserEdit($uid, %o); - return $self->resRedirect("/u$uid/edit?d=1", 'post'); + $self->authAdminSetPass($u->{id}, $frm->{usrpass}) if $frm->{usrpass} && $self->authInfo->{id} != $u->{id}; + + if($frm->{usrpass} && $self->authInfo->{id} == $u->{id}) { + # Bit ugly: On incorrect password, all other changes are still saved. + my $ok = $self->authSetPass($u->{id}, $frm->{usrpass}, "/u$uid/edit?d=1", pass => $frm->{curpass}); + return if $ok; + push @{$frm->{_err}}, 'Invalid password'; + } else { + return $self->resRedirect("/u$uid/edit?d=1", 'post'); + } } } # fill out default values $frm->{usrname} ||= $u->{username}; - $frm->{mail} ||= $u->{mail}; + $frm->{mail} ||= $self->dbUserGetMail($u->{id}, $self->authInfo->{id}, $self->authInfo->{token}); $frm->{perms} ||= [ grep $u->{perm} & $self->{permissions}{$_}, keys %{$self->{permissions}} ]; $frm->{$_} //= $u->{prefs}{$_} for(qw|skin customcss show_nsfw traits_sexual tags_all hide_list spoilers|); $frm->{tags_cat} ||= [ split /,/, $u->{prefs}{tags_cat}||$self->{default_tags_cat} ]; diff --git a/lib/VNDB/Util/Auth.pm b/lib/VNDB/Util/Auth.pm index 6e1dfa5d..e3ee20eb 100644 --- a/lib/VNDB/Util/Auth.pm +++ b/lib/VNDB/Util/Auth.pm @@ -14,8 +14,8 @@ use VNDB::Func; our @EXPORT = qw| - authInit authLogin authLogout authInfo authCan authPreparePass authCreateSession authCheck - authPrepareReset authValidateReset authGetCode authCheckCode authPref + authInit authLogin authLogout authInfo authCan authSetPass authAdminSetPass + authResetPass authIsValidToken authGetCode authCheckCode authPref |; @@ -28,7 +28,7 @@ sub randomascii { # Returns (uid, encrypted_token) on success, (0, '') on failure. sub parsecookie { # Earlier versions of the auth cookie didn't have the dot separator, so that's optional. - return ($_[0]->reqCookie('auth')||'') =~ /^([a-zA-Z0-9]{40})\.?(\d+)$/ ? ($2, sha1 pack 'H*', $1) : (0, ''); + return ($_[0]->reqCookie('auth')||'') =~ /^([a-fA-F0-9]{40})\.?(\d+)$/ ? ($2, sha1 pack 'H*', $1) : (0, ''); } @@ -38,9 +38,10 @@ sub authInit { my($uid, $token_e) = parsecookie($self); $self->{_auth} = $uid && $self->dbUserGet(uid => $uid, session => $token_e, what => 'extended notifycount prefs')->[0]; + $self->{_auth}{token} = $token_e if $self->{_auth}; # update the sessions.lastused column if lastused < now()-'6 hours' - $self->dbSessionUpdateLastUsed($uid, $token_e) if $self->{_auth} && $self->{_auth}{session_lastused} < time()-6*3600; + $self->dbUserUpdateLastUsed($uid, $token_e) if $self->{_auth} && $self->{_auth}{session_lastused} < time()-6*3600; # Drop the cookie if it's not valid $self->resCookie(auth => undef) if !$self->{_auth} && $self->reqCookie('auth'); @@ -52,29 +53,39 @@ sub authInit { sub authLogin { my($self, $user, $pass, $to) = @_; - if($self->authCheck($user, $pass)) { - $self->authCreateSession($user, $to); - return 1; - } + return 0 if !$user || !$pass; - return 0; + my $d = $self->dbUserGet(username => $user, what => 'scryptargs extended prefs notifycount')->[0]; + return 0 if !$d->{id} || !$d->{scryptargs} || length($d->{scryptargs}) != 14; + + my($N, $r, $p, $salt) = unpack 'NCCa8', $d->{scryptargs}; + my $encpass = _preparepass($self, $pass, $salt, $N, $r, $p); + + return _createsession($self, $d->{id}, $encpass, $to); } -# Args: user, url-to-redirect-to-on-success -# Should only be called if the user is already authenticated (i.e. after authCheck or when the user just confirmed his email address). -sub authCreateSession { - my($self, $user, $to) = @_; +# Prepares a plaintext password for database storage +# Arguments: pass, optionally: salt, N, r, p +# Returns: encrypted password (as a binary string) +sub _preparepass { + my($self, $pass, $salt, $N, $r, $p) = @_; + ($N, $r, $p) = @{$self->{scrypt_args}} if !$N; + $salt ||= urandom(8); + return pack 'NCCa8a*', $N, $r, $p, $salt, scrypt_raw($pass, $self->{scrypt_salt} . $salt, $N, $r, $p, 32); +} + - $self->{_auth} = $self->dbUserGet(username => $user, what => 'extended notifycount')->[0] if $user; - die "No valid user!" if !$self->{_auth}{id}; +# self, uid, encpass, url-to-redirect-to +sub _createsession { + my($self, $uid, $encpass, $url) = @_; my $token = urandom(20); - my $cookie = unpack('H*', $token).'.'.$self->{_auth}{id}; - $self->dbSessionAdd($self->{_auth}{id}, sha1 $token); + return 0 if !$self->dbUserLogin($uid, $encpass, sha1 $token); - $self->resRedirect($to, 'post'); - $self->resCookie(auth => $cookie, httponly => 1, expires => time + 31536000); # keep the cookie for 1 year + $self->resRedirect($url, 'post'); + $self->resCookie(auth => unpack('H*', $token).'.'.$uid, httponly => 1, expires => time + 31536000); # keep the cookie for 1 year + return 1; } @@ -83,82 +94,71 @@ sub authLogout { my $self = shift; my($uid, $token_e) = parsecookie($self); - $self->dbSessionDel($uid, $token_e) if $uid; + $self->dbUserLogout($uid, $token_e) if $uid; $self->resRedirect('/', 'temp'); $self->resCookie(auth => undef); } -# returns a hashref with information about the current loggedin user -# the hash is identical to the hash returned by dbUserGet -# returns empty hash if no user is logged in. -sub authInfo { - return shift->{_auth} || {}; +# Replaces the user's password with a random token that can be used to reset the password. +sub authResetPass { + my $self = shift; + my $mail = shift; + my $token = unpack 'H*', urandom(20); + my $id = $self->dbUserResetPass($mail, sha1(lc($token))); + return $id ? ($id, $token) : (); } -# returns whether the currently loggedin or anonymous user can perform -# a certain action. Argument is the action name as defined in global.pl -sub authCan { - my($self, $act) = @_; - return $self->{_auth} ? $self->{_auth}{perm} & $self->{permissions}{$act} : 0; +# uid, token +sub authIsValidToken { + $_[0]->dbUserIsValidToken($_[1], sha1(lc($_[2]))) } -# Checks for a valid login and writes information in _auth -# Arguments: user, pass -# Returns: 1 if login is valid, 0 otherwise -sub authCheck { - my($self, $user, $pass) = @_; - - return 0 if !$user || length($user) > 15 || length($user) < 2 || !$pass; +# uid, new_pass, url_to_redir_to, 'token'|'pass', $token_or_pass +# Changes the user's password, invalidates all existing sessions, creates a new +# session and redirects. +sub authSetPass { + my($self, $uid, $pass, $redir, $oldtype, $oldpass) = @_; - my $d = $self->dbUserGet(username => $user, what => 'extended notifycount')->[0]; - return 0 if !$d->{id}; + if($oldtype eq 'token') { + $oldpass = sha1(lc($oldpass)); - # scrypt format - if(length $d->{passwd} == 46) { - my($N, $r, $p, $salt) = unpack 'NCCa8', $d->{passwd}; - return 0 if $self->authPreparePass($pass, $salt, $N, $r, $p) ne $d->{passwd}; - $self->{_auth} = $d; - return 1; + } elsif($oldtype eq 'pass') { + my $u = $self->dbUserGet(uid => $uid, what => 'scryptargs')->[0]; + return 0 if !$u->{id} || !$u->{scryptargs} || length($u->{scryptargs}) != 14; + my($N, $r, $p, $salt) = unpack 'NCCa8', $u->{scryptargs}; + $oldpass = _preparepass($self, $oldpass, $salt, $N, $r, $p); } - return 0; + $pass = _preparepass($self, $pass); + return 0 if !$self->dbUserSetPass($uid, $oldpass, $pass); + return _createsession($self, $uid, $pass, $redir); } -# Prepares a plaintext password for database storage -# Arguments: pass, optionally: salt, N, r, p -# Returns: encrypted password (as a binary string) -sub authPreparePass { - my($self, $pass, $salt, $N, $r, $p) = @_; - ($N, $r, $p) = @{$self->{scrypt_args}} if !$N; - $salt ||= urandom(8); - return pack 'NCCa8a*', $N, $r, $p, $salt, scrypt_raw($pass, $self->{scrypt_salt} . $salt, $N, $r, $p, 32); +sub authAdminSetPass { + my($self, $uid, $pass) = @_; + $pass = _preparepass($self, $pass); + $self->dbUserAdminSetPass($uid, $self->authInfo->{id}, $self->authInfo->{token}, $pass); } -# Generates a random token that can be used to reset the password. -# Returns: token (hex string), token-encrypted (binary string) -sub authPrepareReset { - my $self = shift; - my $token = unpack 'H*', urandom(20); - my $salt = randomascii(9); - my $token_e = encode_utf8($salt) . sha1(lc($token).$salt); - return ($token, $token_e); +# returns a hashref with information about the current loggedin user +# the hash is identical to the hash returned by dbUserGet +# returns empty hash if no user is logged in. +sub authInfo { + return shift->{_auth} || {}; } -# Checks whether the password reset token is valid. -# Arguments: passwd (binary string), token (hex string) -sub authValidateReset { - my($self, $passwd, $token) = @_; - return 0 if length $passwd != 29; - my $salt = substr $passwd, 0, 9; - return 0 if $salt.sha1(lc($token).$salt) ne $passwd; - return 1; +# returns whether the currently loggedin or anonymous user can perform +# a certain action. Argument is the action name as defined in global.pl +sub authCan { + my($self, $act) = @_; + return $self->{_auth} ? $self->{_auth}{perm} & $self->{permissions}{$act} : 0; } diff --git a/util/sql/all.sql b/util/sql/all.sql index 2c47f63b..12852221 100644 --- a/util/sql/all.sql +++ b/util/sql/all.sql @@ -45,6 +45,10 @@ CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'sid \i util/sql/triggers.sql +-- permissions + +\i util/sql/perms.sql + -- Sequences used for ID generation of items not in the DB CREATE SEQUENCE covers_seq; CREATE SEQUENCE charimg_seq; diff --git a/util/sql/func.sql b/util/sql/func.sql index ec109692..20fc71c4 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -2,6 +2,7 @@ -- edit_* -> revision insertion abstraction functions -- *_notify -> functions issuing a PgSQL NOTIFY statement -- notify_* -> functions creating entries in the notifications table +-- user_* -> functions to manage users and sessions -- update_* -> functions to update a cache -- *_update ^ (I should probably rename these to -- *_calc ^ the update_* scheme for consistency) @@ -687,3 +688,110 @@ END; $$ LANGUAGE plpgsql; + + +---------------------------------------------------------- +-- user management -- +---------------------------------------------------------- +-- XXX: These functions run with the permissions of the 'vndb' user. + + +-- Returns the raw scrypt parameters (N, r, p and salt) for this user, in order +-- to create an encrypted pass. Returns NULL if this user does not have a valid +-- password. +CREATE OR REPLACE FUNCTION user_getscryptargs(integer) RETURNS bytea AS $$ + SELECT + CASE WHEN length(passwd) = 46 THEN substring(passwd from 1 for 14) ELSE NULL END + FROM users WHERE id = $1 +$$ LANGUAGE SQL SECURITY DEFINER; + + +-- Create a new session for this user (uid, scryptpass, token) +CREATE OR REPLACE FUNCTION user_login(integer, bytea, bytea) RETURNS boolean AS $$ + INSERT INTO sessions (uid, token) SELECT $1, $3 FROM users + WHERE length($2) = 46 AND length($3) = 20 + AND id = $1 AND passwd = $2 + RETURNING true +$$ LANGUAGE SQL SECURITY DEFINER; + + +CREATE OR REPLACE FUNCTION user_logout(integer, bytea) RETURNS void AS $$ + DELETE FROM sessions WHERE uid = $1 AND token = $2 +$$ LANGUAGE SQL SECURITY DEFINER; + + +CREATE OR REPLACE FUNCTION user_update_lastused(integer, bytea) RETURNS void AS $$ + UPDATE sessions SET lastused = NOW() WHERE uid = $1 AND token = $2 +$$ LANGUAGE SQL SECURITY DEFINER; + + +CREATE OR REPLACE FUNCTION user_isloggedin(integer, bytea) RETURNS timestamptz AS $$ + SELECT lastused FROM sessions WHERE uid = $1 AND token = $2 +$$ LANGUAGE SQL SECURITY DEFINER; + + +CREATE OR REPLACE FUNCTION user_emailexists(text) RETURNS boolean AS $$ + SELECT true FROM users WHERE lower(mail) = lower($1) LIMIT 1 +$$ LANGUAGE SQL SECURITY DEFINER; + + +CREATE OR REPLACE FUNCTION user_isvalidtoken(integer, bytea) RETURNS boolean AS $$ + SELECT true FROM users WHERE id = $1 AND passwd = $2 AND length($2) = 20 +$$ LANGUAGE SQL SECURITY DEFINER; + + +-- Replace password with a reset token. args: email, token. Returns: user id. +-- Doesn't work for usermods, otherwise an attacker could use this function to +-- gain access to all user's emails by obtaining a reset token of a usermod. +-- Ideally Postgres itself would send the user an email so that the application +-- calling this function doesn't even get the token, and thus can't get access +-- to someone's account. But alas, that'd require a separate process. +CREATE OR REPLACE FUNCTION user_resetpass(text, bytea) RETURNS integer AS $$ + UPDATE users SET passwd = $2 WHERE lower(mail) = lower($1) AND length($2) = 20 AND perm & 128 = 0 RETURNING id; +$$ LANGUAGE SQL SECURITY DEFINER; + + +-- Changes the user's password and invalidates all existing sessions. args: uid, old_pass_or_token, new_pass +CREATE OR REPLACE FUNCTION user_setpass(integer, bytea, bytea) RETURNS boolean AS $$ + WITH upd(id) AS ( + UPDATE users SET passwd = $3 WHERE id = $1 AND passwd = $2 AND length($2) IN(20,46) AND length($3) = 46 RETURNING id + ), del AS( -- Not referenced, but still guaranteed to run + DELETE FROM sessions WHERE uid IN(SELECT id FROM upd) + ) + SELECT true FROM upd +$$ LANGUAGE SQL SECURITY DEFINER; + + +-- Internal function, used to verify whether user ($2 with session $3) is +-- allowed to access data from user $1. +CREATE OR REPLACE FUNCTION user_isauth(integer, integer, bytea) RETURNS boolean AS $$ + SELECT true FROM users + WHERE id = $2 + AND EXISTS(SELECT 1 FROM sessions WHERE uid = $2 AND token = $3) + AND ($2 = $1 OR perm & 128 = 128) +$$ LANGUAGE SQL; + + +-- uid of user email to get, uid currently logged in, session token of currently logged in. +-- Ensures that only the user itself or a useradmin can get someone's email address. +CREATE OR REPLACE FUNCTION user_getmail(integer, integer, bytea) RETURNS text AS $$ + SELECT mail FROM users WHERE id = $1 AND user_isauth($1, $2, $3) +$$ LANGUAGE SQL SECURITY DEFINER; + + +CREATE OR REPLACE FUNCTION user_setmail(integer, integer, bytea, text) RETURNS void AS $$ + UPDATE users SET mail = $4 WHERE id = $1 AND user_isauth($1, $2, $3) +$$ LANGUAGE SQL SECURITY DEFINER; + + +CREATE OR REPLACE FUNCTION user_setperm(integer, integer, bytea, integer) RETURNS void AS $$ + UPDATE users SET perm = $4 WHERE id = $1 AND user_isauth(-1, $2, $3) +$$ LANGUAGE SQL SECURITY DEFINER; + + +CREATE OR REPLACE FUNCTION user_admin_setpass(integer, integer, bytea, bytea) RETURNS void AS $$ + WITH upd(id) AS ( + UPDATE users SET passwd = $4 WHERE id = $1 AND user_isauth(-1, $2, $3) AND length($4) = 46 RETURNING id + ) + DELETE FROM sessions WHERE uid IN(SELECT id FROM upd) +$$ LANGUAGE SQL SECURITY DEFINER; diff --git a/util/sql/perms.sql b/util/sql/perms.sql new file mode 100644 index 00000000..fcfe47a1 --- /dev/null +++ b/util/sql/perms.sql @@ -0,0 +1,80 @@ +-- vndb_site + +GRANT CONNECT, TEMP ON DATABASE :DBNAME TO vndb_site; +GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO vndb_site; +GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO vndb_site; + +GRANT SELECT, INSERT, UPDATE, DELETE ON affiliate_links TO vndb_site; +GRANT SELECT, INSERT ON anime TO vndb_site; +GRANT SELECT, INSERT ON changes TO vndb_site; +GRANT SELECT, INSERT, UPDATE ON chars TO vndb_site; +GRANT SELECT, INSERT ON chars_hist TO vndb_site; +GRANT SELECT, INSERT, DELETE ON chars_traits TO vndb_site; +GRANT SELECT, INSERT ON chars_traits_hist TO vndb_site; +GRANT SELECT, INSERT, DELETE ON chars_vns TO vndb_site; +GRANT SELECT, INSERT ON chars_vns_hist TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON login_throttle TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON notifications TO vndb_site; +GRANT SELECT, INSERT, UPDATE ON producers TO vndb_site; +GRANT SELECT, INSERT ON producers_hist TO vndb_site; +GRANT SELECT, INSERT, DELETE ON producers_relations TO vndb_site; +GRANT SELECT, INSERT ON producers_relations_hist TO vndb_site; +GRANT SELECT ON quotes TO vndb_site; +GRANT SELECT, INSERT, UPDATE ON releases TO vndb_site; +GRANT SELECT, INSERT ON releases_hist TO vndb_site; +GRANT SELECT, INSERT, DELETE ON releases_lang TO vndb_site; +GRANT SELECT, INSERT ON releases_lang_hist TO vndb_site; +GRANT SELECT, INSERT, DELETE ON releases_media TO vndb_site; +GRANT SELECT, INSERT ON releases_media_hist TO vndb_site; +GRANT SELECT, INSERT, DELETE ON releases_platforms TO vndb_site; +GRANT SELECT, INSERT ON releases_platforms_hist TO vndb_site; +GRANT SELECT, INSERT, DELETE ON releases_producers TO vndb_site; +GRANT SELECT, INSERT ON releases_producers_hist TO vndb_site; +GRANT SELECT, INSERT, DELETE ON releases_vn TO vndb_site; +GRANT SELECT, INSERT ON releases_vn_hist TO vndb_site; +GRANT SELECT ON relgraphs TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON rlists TO vndb_site; +GRANT SELECT, INSERT, UPDATE ON screenshots TO vndb_site; +-- No access to the 'sessions' table, managed by the user_* functions. +GRANT SELECT, INSERT, UPDATE ON staff TO vndb_site; +GRANT SELECT, INSERT, DELETE ON staff_alias TO vndb_site; +GRANT SELECT, INSERT ON staff_alias_hist TO vndb_site; +GRANT SELECT, INSERT ON staff_hist TO vndb_site; +GRANT SELECT, UPDATE ON stats_cache TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON tags TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON tags_aliases TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON tags_parents TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON tags_vn TO vndb_site; +GRANT SELECT ON tags_vn_inherit TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON threads TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON threads_boards TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON threads_poll_options TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON threads_poll_votes TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON threads_posts TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON traits TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON traits_chars TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON traits_parents TO vndb_site; + +-- users table is special; The 'perm', 'passwd' and 'mail' columns are +-- protected and can only be accessed through the user_* functions. +GRANT SELECT (id, username, registered, perm, c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed), + INSERT (id, username, mail, registered, c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed), + UPDATE (id, username, registered, c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed) ON users TO vndb_site; +GRANT DELETE ON users TO vndb_site; + +GRANT SELECT, INSERT, UPDATE, DELETE ON users_prefs TO vndb_site; +GRANT SELECT, INSERT, UPDATE ON vn TO vndb_site; +GRANT SELECT, INSERT, DELETE ON vn_anime TO vndb_site; +GRANT SELECT, INSERT ON vn_anime_hist TO vndb_site; +GRANT SELECT, INSERT ON vn_hist TO vndb_site; +GRANT SELECT, INSERT, DELETE ON vn_relations TO vndb_site; +GRANT SELECT, INSERT ON vn_relations_hist TO vndb_site; +GRANT SELECT, INSERT, DELETE ON vn_screenshots TO vndb_site; +GRANT SELECT, INSERT ON vn_screenshots_hist TO vndb_site; +GRANT SELECT, INSERT, DELETE ON vn_seiyuu TO vndb_site; +GRANT SELECT, INSERT ON vn_seiyuu_hist TO vndb_site; +GRANT SELECT, INSERT, DELETE ON vn_staff TO vndb_site; +GRANT SELECT, INSERT ON vn_staff_hist TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON vnlists TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON votes TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON wlists TO vndb_site; diff --git a/util/sql/schema.sql b/util/sql/schema.sql index b641c556..ca234d49 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -569,10 +569,7 @@ CREATE TABLE users ( mail varchar(100) NOT NULL, perm smallint NOT NULL DEFAULT 1+4+16, -- Interpretation of the passwd column depends on its length: - -- * 29 bytes: Password reset token - -- First 9 bytes: salt (ASCII) - -- Latter 20 bytes: sha1(hex(token) + salt) - -- 'token' is a sha1 digest obtained from random data. + -- * 20 bytes: Password reset token (sha1(lower_hex(20 bytes of random data))) -- * 46 bytes: scrypt password -- 4 bytes: N (big endian) -- 1 byte: r diff --git a/util/sql/superuser_init.sql b/util/sql/superuser_init.sql new file mode 100644 index 00000000..145ff30e --- /dev/null +++ b/util/sql/superuser_init.sql @@ -0,0 +1,13 @@ +-- This script should be run before all other scripts and as a PostgreSQL +-- superuser It will create the VNDB database and required users. +-- All other SQL scripts should be run by the 'vndb' user. + +-- In order to "activate" a user, i.e. to allow login, you need to manually run +-- the following for each user you want to activate: +-- ALTER ROLE rolename LOGIN UNENCRYPTED PASSWORD 'password'; + +CREATE ROLE vndb; +CREATE DATABASE vndb OWNER vndb; + +-- The website +CREATE ROLE vndb_site; |