summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2016-11-27 13:20:06 +0100
committerYorhel <git@yorhel.nl>2016-11-27 13:20:06 +0100
commite75c79790ec102d041882479f3c5ebe4985a2ffc (patch)
tree86c9ca253bab4a600fb8206c29f9ca3099af96fb
parent6a04b3278bb6e2bedbe169870314eff7d5de33da (diff)
SQL: Use separate role for Multi2.26
-rw-r--r--lib/Multi/API.pm49
-rw-r--r--util/sql/func.sql2
-rw-r--r--util/sql/perms.sql69
3 files changed, 93 insertions, 27 deletions
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index da38b7b8..e43f42f3 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -13,6 +13,7 @@ use AnyEvent::Socket;
use AnyEvent::Handle;
use POE::Filter::VNDBAPI 'encode_filters';
use Encode 'encode_utf8', 'decode_utf8';
+use Crypt::URandom 'urandom';
use Crypt::ScryptKDF 'scrypt_raw';;
use VNDBUtil 'normalize_query', 'norm_ip';
use JSON::XS;
@@ -287,7 +288,7 @@ sub login_auth {
if $tm-AE::time() > $VNDB::S{login_throttle}[1];
# Fetch user info
- cpg $c, 'SELECT id, encode(passwd, \'hex\') FROM users WHERE username = $1', [ $arg->{username} ], sub {
+ cpg $c, 'SELECT id, encode(user_getscryptargs(id), \'hex\') FROM users WHERE username = $1', [ $arg->{username} ], sub {
login_verify($c, $arg, $tm, $_[0]);
};
};
@@ -298,31 +299,29 @@ sub login_verify {
my($c, $arg, $tm, $res) = @_;
return cerr $c, auth => "No user with the name '$arg->{username}'" if $res->nRows == 0;
-
- my $passwd = pack 'H*', $res->value(0,1);
my $uid = $res->value(0,0);
- my $accepted = 0;
-
- if(length $passwd == 46) { # scrypt
- my($N, $r, $p, $salt, $hash) = unpack 'NCCa8a*', $passwd;
- $accepted = $hash eq scrypt_raw($arg->{password}, $VNDB::S{scrypt_salt} . $salt, $N, $r, $p, 32);
- } else {
- return cerr $c, auth => "Account disabled";
- }
-
- if($accepted) {
- $c->{uid} = $uid;
- $c->{username} = $arg->{username};
- $c->{client} = $arg->{client};
- $c->{clientver} = $arg->{clientver};
- cres $c, ['ok'], 'Successful login by %s (%s) using client "%s" ver. %s', $arg->{username}, $c->{uid}, $c->{client}, $c->{clientver};
-
- } else {
- my @a = ( $tm + $VNDB::S{login_throttle}[0], norm_ip($c->{ip}) );
- pg_cmd 'UPDATE login_throttle SET timeout = to_timestamp($1) WHERE ip = $2', \@a;
- pg_cmd 'INSERT INTO login_throttle (ip, timeout) SELECT $2, to_timestamp($1) WHERE NOT EXISTS(SELECT 1 FROM login_throttle WHERE ip = $2)', \@a;
- cerr $c, auth => "Wrong password for user '$arg->{username}'";
- }
+ my $sargs = $res->value(0,1);
+ return cerr $c, auth => "Account disabled" if !$sargs || length($sargs) != 14*2;
+
+ my $token = urandom(20);
+ my($N, $r, $p, $salt) = unpack 'NCCa8', pack 'H*', $sargs;
+ my $passwd = pack 'NCCa8a*', $N, $r, $p, $salt, scrypt_raw($arg->{password}, $VNDB::S{scrypt_salt} . $salt, $N, $r, $p, 32);
+
+ cpg $c, 'SELECT user_login($1, decode($2, \'hex\'), decode($3, \'hex\'))', [ $uid, unpack('H*', $passwd), unpack('H*', $token) ], sub {
+ if($_[0]->nRows == 1 && ($_[0]->value(0,0)||'') =~ /t/) {
+ $c->{uid} = $uid;
+ $c->{username} = $arg->{username};
+ $c->{client} = $arg->{client};
+ $c->{clientver} = $arg->{clientver};
+ pg_cmd 'SELECT user_logout($1, decode($2, \'hex\'))', [ $uid, unpack('H*', $token) ];
+ cres $c, ['ok'], 'Successful login by %s (%s) using client "%s" ver. %s', $arg->{username}, $c->{uid}, $c->{client}, $c->{clientver};
+ } else {
+ my @a = ( $tm + $VNDB::S{login_throttle}[0], norm_ip($c->{ip}) );
+ pg_cmd 'UPDATE login_throttle SET timeout = to_timestamp($1) WHERE ip = $2', \@a;
+ pg_cmd 'INSERT INTO login_throttle (ip, timeout) SELECT $2, to_timestamp($1) WHERE NOT EXISTS(SELECT 1 FROM login_throttle WHERE ip = $2)', \@a;
+ cerr $c, auth => "Wrong password for user '$arg->{username}'";
+ }
+ };
}
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 20fc71c4..32dff5ea 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -162,7 +162,7 @@ BEGIN
UPDATE tags SET c_items = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id);
RETURN;
END;
-$$ LANGUAGE plpgsql;
+$$ LANGUAGE plpgsql SECURITY DEFINER;
diff --git a/util/sql/perms.sql b/util/sql/perms.sql
index fcfe47a1..015cc045 100644
--- a/util/sql/perms.sql
+++ b/util/sql/perms.sql
@@ -59,7 +59,7 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON traits_parents TO vndb_site;
-- 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;
+ UPDATE ( 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;
@@ -78,3 +78,70 @@ 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;
+
+
+
+
+-- vndb_multi
+-- (Assuming all modules are loaded)
+
+GRANT CONNECT, TEMP ON DATABASE :DBNAME TO vndb_multi;
+GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO vndb_multi;
+GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO vndb_multi;
+
+GRANT SELECT, INSERT, UPDATE ON affiliate_links TO vndb_multi;
+GRANT SELECT, UPDATE ON anime TO vndb_multi;
+GRANT SELECT ON changes TO vndb_multi;
+GRANT SELECT ON chars TO vndb_multi;
+GRANT SELECT ON chars_hist TO vndb_multi;
+GRANT SELECT ON chars_traits TO vndb_multi;
+GRANT SELECT ON chars_vns TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON login_throttle TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON notifications TO vndb_multi;
+GRANT SELECT, UPDATE ON producers TO vndb_multi;
+GRANT SELECT ON producers_hist TO vndb_multi;
+GRANT SELECT ON producers_relations TO vndb_multi;
+GRANT SELECT ON quotes TO vndb_multi;
+GRANT SELECT ON releases TO vndb_multi;
+GRANT SELECT ON releases_hist TO vndb_multi;
+GRANT SELECT ON releases_lang TO vndb_multi;
+GRANT SELECT ON releases_media TO vndb_multi;
+GRANT SELECT ON releases_platforms TO vndb_multi;
+GRANT SELECT ON releases_producers TO vndb_multi;
+GRANT SELECT ON releases_vn TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON relgraphs TO vndb_multi;
+GRANT SELECT ON screenshots TO vndb_multi;
+GRANT SELECT (lastused) ON sessions TO vndb_multi;
+GRANT DELETE ON sessions TO vndb_multi;
+GRANT SELECT ON staff TO vndb_multi;
+GRANT SELECT ON staff_alias TO vndb_multi;
+GRANT SELECT ON staff_alias_hist TO vndb_multi;
+GRANT SELECT ON staff_hist TO vndb_multi;
+GRANT SELECT, UPDATE ON stats_cache TO vndb_multi;
+GRANT SELECT ON tags TO vndb_multi;
+GRANT SELECT ON tags_aliases TO vndb_multi;
+GRANT SELECT ON tags_parents TO vndb_multi;
+GRANT SELECT ON tags_vn TO vndb_multi;
+GRANT SELECT ON tags_vn_inherit TO vndb_multi; -- tag_vn_calc() is SECURITY DEFINER due to index drop/create, so no extra perms needed here
+GRANT SELECT ON threads TO vndb_multi;
+GRANT SELECT ON threads_boards TO vndb_multi;
+GRANT SELECT ON threads_posts TO vndb_multi;
+GRANT SELECT, UPDATE ON traits TO vndb_multi;
+GRANT SELECT, INSERT, TRUNCATE ON traits_chars TO vndb_multi;
+GRANT SELECT ON traits_parents TO vndb_multi;
+GRANT SELECT (id, username, registered, c_votes, c_changes, c_tags, ign_votes, email_confirmed),
+ UPDATE ( c_votes, c_changes, c_tags ) ON users TO vndb_multi;
+GRANT DELETE ON users TO vndb_multi;
+GRANT SELECT ON users_prefs TO vndb_multi;
+GRANT SELECT, UPDATE ON vn TO vndb_multi;
+GRANT SELECT ON vn_anime TO vndb_multi;
+GRANT SELECT ON vn_hist TO vndb_multi;
+GRANT SELECT ON vn_relations TO vndb_multi;
+GRANT SELECT ON vn_screenshots TO vndb_multi;
+GRANT SELECT ON vn_screenshots_hist TO vndb_multi;
+GRANT SELECT ON vn_seiyuu TO vndb_multi;
+GRANT SELECT ON vn_staff TO vndb_multi;
+GRANT SELECT ON vn_staff_hist TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON vnlists TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON votes TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON wlists TO vndb_multi;