summaryrefslogtreecommitdiff
path: root/util/sql
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 /util/sql
parent6a04b3278bb6e2bedbe169870314eff7d5de33da (diff)
SQL: Use separate role for Multi2.26
Diffstat (limited to 'util/sql')
-rw-r--r--util/sql/func.sql2
-rw-r--r--util/sql/perms.sql69
2 files changed, 69 insertions, 2 deletions
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;