SQL: Merge users.(passwd|salt) in one column + document values

It doesn't make a whole lot to separate the hashed password and the salt
from each other, you need both to do anything with them, and from the
database perspective they're both completely opaque strings only usable
for direct comparison with other hashed strings.

This change is mostly as preparation for switching to a proper key
derivation function (sha256 isn't...) and to add support for longer
and/or binary salt.

Because the passwd field now needs to be interpreted in Perl, it's being
passed around as a binary string rather than a hex-encoded value.

API login is broken in this commit. I'll get to that.

1 files changed, 9 insertions, 1 deletions

diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 046eccf6..09b334f9 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -363,13 +363,21 @@ CREATE TABLE users (
   username varchar(20) NOT NULL UNIQUE,
   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.
+  -- * 41 bytes: Hashed/salted password
+  --   First 9 bytes: salt (ASCII)
+  --   Latter 32 bytes: sha256(global_salt + password + salt)
+  -- * Anything else: Invalid, account disabled.
   passwd bytea NOT NULL DEFAULT '',
   registered timestamptz NOT NULL DEFAULT NOW(),
   c_votes integer NOT NULL DEFAULT 0,
   c_changes integer NOT NULL DEFAULT 0,
   ip inet NOT NULL DEFAULT '0.0.0.0',
   c_tags integer NOT NULL DEFAULT 0,
-  salt character(9) NOT NULL DEFAULT '',
   ign_votes boolean NOT NULL DEFAULT FALSE,
   email_confirmed boolean NOT NULL DEFAULT FALSE
 );