diff options
Diffstat (limited to 'util')
| -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 |
5 files changed, 206 insertions, 4 deletions
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; |