Blinded permissions carryover

[jagerman]

We need to ensure that user permissions carry over when blinding gets enabled, so that if someone is a moderator they remain a moderator post-blinding, and a banned user remains banned.

Here's an approach that would work:

• Add a needs_blinding table with two columns: [user, blinded_pos]. user is a foreign key to users.id, blinded_pos is a varchar.
• At startup (before forking) when blinding is enabled: get the list of unblinded users with user_permission_override rows that don't exist in the needs_blinding table, insert (id, |kA|) rows into the needs_blinding table, where |kA| is the positive branch kA (i.e. has the MSB cleared: kA[31] &= 0x7f).
• During authentication:
  • if blinded user does not exist in the database then see if |kA| (i.e. clear the sign bit) exists in needs_blinding. If it does:
    1. insert user row (using kA, not |kA|).
    2. update any user_permission_override, user_permission_futures, and user_ban_futures rows with the old ID to the newly inserted user id.
    3. delete the needs_blinding row.
• when inserting a moderator by unblinded id, look up kA/-kA and if either exist in the users table then use that instead. Otherwise insert the unblinded id as a user (if not already existing) + add a needs_blinding row for the unblinded id, then set the permission bits for that unblinded user.

---

Some implementation details:

• Getting unmigrated users list:

SELECT users.id FROM users WHERE session_id > '05' AND session_id < '06'
    AND EXISTS (SELECT * FROM user_permission_overrides WHERE "user" = users.id);

• Should also add an index on user_permission_overrides(user) to make that query fast.
• permission updating:

UPDATE user_permission_overrides SET "user" = :new_id WHERE "user" = :old_id;
UPDATE user_permission_futures SET "user" = :new_id WHERE "user" = :old_id;
UPDATE user_ban_futures SET "user" = :new_id WHERE "user" = :old_id;