summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2016-11-27 10:15:19 +0100
committerYorhel <git@yorhel.nl>2016-11-27 10:15:19 +0100
commit6a04b3278bb6e2bedbe169870314eff7d5de33da (patch)
treeef0b66773270f15e87ac5ee46c844af1c2f1459e
parenta9df5c8d7e22874d37938b27913f239ce31f9414 (diff)
SQL: Use separate role for the website + disallow access to user data
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.
-rw-r--r--data/global.pl1
-rw-r--r--lib/VNDB/DB/Users.pm98
-rw-r--r--lib/VNDB/Handler/Users.pm67
-rw-r--r--lib/VNDB/Util/Auth.pm140
-rw-r--r--util/sql/all.sql4
-rw-r--r--util/sql/func.sql108
-rw-r--r--util/sql/perms.sql80
-rw-r--r--util/sql/schema.sql5
-rw-r--r--util/sql/superuser_init.sql13
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;