SQL: Split "perm" column into multiple booleans

This simplifies several actions and makes permission checks more
obvious. This is also yack shaving for another feature I've been
planning to add: boardmods should be able to set other users' "board"
permission, tagmods for tags, etc. So that partial user bans don't need
the full "usermod" permission.

3 files changed, 35 insertions, 11 deletions

diff --git a/util/devdump.pl b/util/devdump.pl
index 1973a7be..e9ff5314 100755
--- a/util/devdump.pl
+++ b/util/devdump.pl
@@ -109,15 +109,15 @@ sub copy_entry {
     # A few pre-defined users
     # This password is 'hunter2' with the default salt
     my $pass = '000100000801ec4185fed438752d6b3b968e2b2cd045f70005cb7e10cafdbb694a82246bd34a065b6e977e0c3dcc';
-    printf "INSERT INTO users (id, username, mail, perm, passwd, email_confirmed) VALUES (%d, '%s', '%s', %d, decode('%s', 'hex'), true);\n", @$_, $pass for(
-        [ 2, 'admin', 'admin@vndb.org', 503 ],
-        [ 3, 'user1', 'user1@vndb.org', 21 ],
-        [ 4, 'user2', 'user2@vndb.org', 21 ],
-        [ 5, 'user3', 'user3@vndb.org', 21 ],
-        [ 6, 'user4', 'user4@vndb.org', 21 ],
-        [ 7, 'user5', 'user5@vndb.org', 21 ],
-        [ 8, 'user6', 'user6@vndb.org', 21 ],
-        [ 9, 'user7', 'user7@vndb.org', 21 ],
+    printf "INSERT INTO users (id, username, mail, perm_usermod, passwd, email_confirmed) VALUES (%d, '%s', '%s', %d, decode('%s', 'hex'), true);\n", @$_, $pass for(
+        [ 2, 'admin', 'admin@vndb.org', 'true' ],
+        [ 3, 'user1', 'user1@vndb.org', 'false'],
+        [ 4, 'user2', 'user2@vndb.org', 'false'],
+        [ 5, 'user3', 'user3@vndb.org', 'false'],
+        [ 6, 'user4', 'user4@vndb.org', 'false'],
+        [ 7, 'user5', 'user5@vndb.org', 'false'],
+        [ 8, 'user6', 'user6@vndb.org', 'false'],
+        [ 9, 'user7', 'user7@vndb.org', 'false'],
     );
     print "SELECT ulist_labels_create(id) FROM users;\n";
 
diff --git a/util/updates/2020-04-15-users-permflags.sql b/util/updates/2020-04-15-users-permflags.sql
new file mode 100644
index 00000000..f33daf25
--- /dev/null
+++ b/util/updates/2020-04-15-users-permflags.sql
@@ -0,0 +1,26 @@
+ALTER TABLE users ADD COLUMN perm_board    boolean NOT NULL DEFAULT true;
+ALTER TABLE users ADD COLUMN perm_boardmod boolean NOT NULL DEFAULT false;
+ALTER TABLE users ADD COLUMN perm_dbmod    boolean NOT NULL DEFAULT false;
+ALTER TABLE users ADD COLUMN perm_edit     boolean NOT NULL DEFAULT true;
+ALTER TABLE users ADD COLUMN perm_imgvote  boolean NOT NULL DEFAULT true;
+ALTER TABLE users ADD COLUMN perm_tag      boolean NOT NULL DEFAULT true;
+ALTER TABLE users ADD COLUMN perm_tagmod   boolean NOT NULL DEFAULT false;
+ALTER TABLE users ADD COLUMN perm_usermod  boolean NOT NULL DEFAULT false;
+
+UPDATE users SET
+    perm_board    = (perm &   1) > 0,
+    perm_boardmod = (perm &   2) > 0,
+    perm_dbmod    = (perm &  32) > 0,
+    perm_edit     = (perm &   4) > 0,
+    perm_imgvote  = (perm &   8) > 0,
+    perm_tag      = (perm &  16) > 0,
+    perm_tagmod   = (perm &  64) > 0,
+    perm_usermod  = (perm & 128) > 0;
+
+ALTER TABLE users DROP COLUMN perm;
+ALTER TABLE users DROP COLUMN hide_list;
+
+DROP FUNCTION user_setperm(integer, integer, bytea, integer);
+
+\i sql/func.sql
+\i sql/perms.sql
diff --git a/util/vndb.pl b/util/vndb.pl
index 3a7dd714..84398ca9 100755
--- a/util/vndb.pl
+++ b/util/vndb.pl
@@ -27,8 +27,6 @@ tuwf->{skins} = { map +($_ => [ $skin->get($_, 'name'), $skin->get($_, 'userid')
 tuwf->{scr_size}     = [ 136, 102 ]; # w*h of screenshot thumbnails
 tuwf->{ch_size}      = [ 256, 300 ]; # max. w*h of char images
 tuwf->{cv_size}      = [ 256, 400 ]; # max. w*h of cover images
-tuwf->{permissions}  = {qw| board 1  boardmod 2  edit 4  tag 16  dbmod 32  tagmod 64  usermod 128 |};
-tuwf->{default_perm} = 1+4+16; # Keep synchronised with the default value of users.perm
 tuwf->{$_} = config->{$_} for keys %{ config() };
 
 TUWF::set %{ config->{tuwf} };