summaryrefslogtreecommitdiff
path: root/sql/perms.sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql/perms.sql')
-rw-r--r--sql/perms.sql135
1 files changed, 89 insertions, 46 deletions
diff --git a/sql/perms.sql b/sql/perms.sql
index 406cb344..a67442be 100644
--- a/sql/perms.sql
+++ b/sql/perms.sql
@@ -1,14 +1,23 @@
+-- vndb
+-- these are used by util/dbdump.pl
+
+GRANT EXECUTE ON FUNCTION pg_wal_replay_pause TO vndb;
+GRANT EXECUTE ON FUNCTION pg_wal_replay_resume TO vndb;
+
-- vndb_site
DROP OWNED BY vndb_site;
GRANT CONNECT, TEMP ON DATABASE :DBNAME TO vndb_site;
+GRANT USAGE ON SCHEMA public TO vndb_site;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO vndb_site;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO vndb_site;
GRANT SELECT, INSERT ON anime TO vndb_site;
GRANT INSERT ON audit_log TO vndb_site;
GRANT SELECT, INSERT ON changes TO vndb_site;
+GRANT SELECT, INSERT, DELETE ON changes_patrolled TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON chars TO vndb_site;
+GRANT SELECT ON charst 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;
@@ -16,19 +25,29 @@ GRANT SELECT, INSERT, DELETE ON chars_vns TO vndb_site;
GRANT SELECT, INSERT ON chars_vns_hist TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON docs TO vndb_site;
GRANT SELECT, INSERT ON docs_hist TO vndb_site;
+GRANT SELECT, INSERT, UPDATE ON drm TO vndb_site;
+GRANT SELECT , UPDATE ON global_settings TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON images TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON image_votes TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON login_throttle TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON notification_subs TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON notifications TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON producers TO vndb_site;
+GRANT SELECT ON producerst 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, DELETE ON quotes TO vndb_site;
+GRANT SELECT, INSERT ON quotes_log TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON quotes_votes TO vndb_site;
+GRANT SELECT, INSERT, UPDATE ON registration_throttle TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON releases TO vndb_site;
+GRANT SELECT ON releasest 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_drm TO vndb_site;
+GRANT SELECT, INSERT ON releases_drm_hist TO vndb_site;
+GRANT SELECT, INSERT, DELETE ON releases_titles TO vndb_site;
+GRANT SELECT, INSERT ON releases_titles_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;
@@ -37,60 +56,62 @@ 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, INSERT, UPDATE ON reports TO vndb_site;
+GRANT SELECT, INSERT ON reports_log TO vndb_site;
+GRANT SELECT, INSERT, UPDATE ON reset_throttle TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON reviews TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON reviews_posts TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON reviews_votes TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON rlists TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON saved_queries TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON search_cache TO vndb_site;
-- No access to the 'sessions' table, managed by the user_* functions.
GRANT SELECT ON shop_denpa TO vndb_site;
GRANT SELECT ON shop_dlsite TO vndb_site;
+GRANT SELECT ON shop_jastusa TO vndb_site;
GRANT SELECT ON shop_jlist TO vndb_site;
GRANT SELECT ON shop_mg TO vndb_site;
GRANT SELECT ON shop_playasia TO vndb_site;
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 ON staff_aliast 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 ON tags TO vndb_site;
+GRANT SELECT, INSERT ON tags_hist TO vndb_site;
+GRANT SELECT, INSERT, DELETE ON tags_parents TO vndb_site;
+GRANT SELECT, INSERT ON tags_parents_hist 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 tags_vn_direct TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE 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 INSERT ON trace_log TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON traits TO vndb_site;
+GRANT SELECT, INSERT, UPDATE ON traits TO vndb_site;
+GRANT SELECT, INSERT ON traits_hist 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;
+GRANT SELECT, INSERT, DELETE ON traits_parents TO vndb_site;
+GRANT SELECT, INSERT ON traits_parents_hist TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_site;
-
--- users table is special; The 'perm_usermod', 'passwd' and 'mail' columns are
--- protected and can only be accessed through the user_* functions.
-GRANT SELECT ( id, username, registered, ip, ign_votes, email_confirmed
- , perm_board, perm_boardmod, perm_dbmod, perm_edit, perm_imgvote, perm_tag, perm_tagmod, perm_usermod, perm_imgmod
- , skin, customcss, show_nsfw, notify_dbedit, notify_announce
- , tags_all, tags_cont, tags_ero, tags_tech, spoilers, traits_sexual, max_sexual, max_violence
- , filter_vn, filter_release, vn_list_own, vn_list_wish
- , nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled
- , ulist_votes, ulist_vnlist, ulist_wish
- , c_vns, c_wish, c_votes, c_changes, c_imgvotes, c_tags),
- INSERT ( username, mail, ip),
- UPDATE ( username, ign_votes, email_confirmed
- , perm_board, perm_boardmod, perm_dbmod, perm_edit, perm_imgvote, perm_tag, perm_tagmod, perm_imgmod
- , skin, customcss, show_nsfw, notify_dbedit, notify_announce
- , tags_all, tags_cont, tags_ero, tags_tech, spoilers, traits_sexual, max_sexual, max_violence
- , filter_vn, filter_release, vn_list_own, vn_list_wish
- , nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled
- , ulist_votes, ulist_vnlist, ulist_wish
- , c_vns, c_wish, c_votes, c_changes, c_imgvotes, c_tags) ON users TO vndb_site;
-
+GRANT SELECT, INSERT, UPDATE ON users TO vndb_site;
+GRANT SELECT, INSERT, UPDATE ON users_prefs TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON users_prefs_tags TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON users_prefs_traits TO vndb_site;
+GRANT SELECT (id, perm_usermod, delete_at), INSERT (id, mail, ip) ON users_shadow TO vndb_site;
+GRANT SELECT, INSERT ON users_username_hist TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON users_traits 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, DELETE ON vn_editions TO vndb_site;
+GRANT SELECT, INSERT ON vn_editions_hist TO vndb_site;
GRANT SELECT, INSERT ON vn_hist TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON vn_length_votes 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;
@@ -99,6 +120,9 @@ 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, DELETE ON vn_titles TO vndb_site;
+GRANT SELECT, INSERT ON vn_titles_hist TO vndb_site;
+GRANT SELECT ON vnt TO vndb_site;
GRANT SELECT, INSERT ON wikidata TO vndb_site;
@@ -109,12 +133,14 @@ GRANT SELECT, INSERT ON wikidata TO vndb_site;
DROP OWNED BY vndb_multi;
GRANT CONNECT, TEMP ON DATABASE :DBNAME TO vndb_multi;
+GRANT USAGE ON SCHEMA public TO vndb_multi;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO vndb_multi;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO vndb_multi;
-GRANT SELECT, UPDATE ON anime TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE ON anime TO vndb_multi;
GRANT SELECT ON changes TO vndb_multi;
GRANT SELECT ON chars TO vndb_multi;
+GRANT SELECT ON charst 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;
@@ -125,21 +151,31 @@ GRANT SELECT ON image_votes 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 producerst 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, UPDATE ON quotes TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON registration_throttle TO vndb_multi;
GRANT SELECT ON releases TO vndb_multi;
+GRANT SELECT ON releasest TO vndb_multi;
GRANT SELECT ON releases_hist TO vndb_multi;
-GRANT SELECT ON releases_lang TO vndb_multi;
+GRANT SELECT ON releases_titles TO vndb_multi;
+GRANT SELECT ON releases_titles_hist 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 reset_throttle TO vndb_multi;
+GRANT SELECT, UPDATE ON reviews TO vndb_multi;
+GRANT SELECT ON reviews_posts TO vndb_multi;
+GRANT SELECT ON reviews_votes TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON rlists TO vndb_multi;
-GRANT SELECT (expires) ON sessions TO vndb_multi;
+GRANT SELECT ON search_cache TO vndb_multi;
+GRANT SELECT (expires, type) ON sessions TO vndb_multi;
GRANT DELETE ON sessions TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_denpa TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_dlsite TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON shop_jastusa TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_jlist TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_mg TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_playasia TO vndb_multi;
@@ -147,34 +183,41 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON shop_playasia_gtin 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_aliast 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, UPDATE ON tags TO vndb_multi;
+GRANT SELECT ON tags_hist 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 tags_parents_hist TO vndb_multi;
+GRANT SELECT, DELETE ON tags_vn TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON tags_vn_direct TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON tags_vn_inherit TO vndb_multi;
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 ON traits_chars TO vndb_multi; -- traits_chars_calc() is SECURITY DEFINER
+GRANT SELECT ON traits_hist TO vndb_multi;
+GRANT SELECT ON traits_chars TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON traits_chars TO vndb_multi;
GRANT SELECT ON traits_parents TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_multi;
-
-GRANT SELECT (id, username, registered, ign_votes, email_confirmed, notify_dbedit, notify_announce, c_vns, c_wish, c_votes, c_changes, c_imgvotes, c_tags, perm_imgvote),
- UPDATE ( c_vns, c_wish, c_votes, c_changes, c_imgvotes, c_tags ) ON users TO vndb_multi;
-GRANT DELETE ON users TO vndb_multi;
-
+GRANT SELECT, UPDATE, DELETE ON users TO vndb_multi;
+GRANT SELECT, UPDATE, DELETE ON users_prefs TO vndb_multi;
+GRANT SELECT (id, delete_at), DELETE ON users_shadow TO vndb_multi;
+GRANT SELECT, DELETE ON users_username_hist 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, INSERT, UPDATE, DELETE ON vn_length_votes 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 ON vn_titles TO vndb_multi;
+GRANT SELECT ON vn_titles_hist TO vndb_multi;
+GRANT SELECT ON vnt TO vndb_multi;
GRANT SELECT, INSERT, UPDATE ON wikidata TO vndb_multi;
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-20 05:34:01 +0000