Developer: session tokens are stored as replayable plaintext credentials

[DeliciousBuding]

Owner: Developer

Problem

The web auth layer stores bearer session tokens in plaintext in the SQLite sessions.token column. The same random value returned by createSession() is written to the database and later sent to the browser as the diffaudit_session cookie.

Evidence:

• apps/web/src/lib/auth.ts:345-360 generates token, inserts that exact value into schema.sessions.token, and returns it to callers.
• apps/web/src/lib/auth.ts:540-565 validates a request by comparing the browser cookie directly with schema.sessions.token.
• apps/web/src/lib/auth.ts:567-570 deletes sessions by matching the raw cookie value against schema.sessions.token.
• apps/web/src/lib/db/schema.ts:16-22, apps/web/src/lib/db/index.ts:29-35, and apps/web/src/lib/db/migrate.ts:23-29 define the session token column as a unique text field, but there is no hash-at-rest boundary for session credentials.

Impact

A read-only leak of the application database, a copied backup, or a local support artifact containing the sessions table exposes active bearer credentials. Any unexpired value from sessions.token can be replayed as the diffaudit_session cookie until it expires or is deleted. Password hashes and email verification tokens already avoid this direct replay property; session credentials should have the same at-rest protection.

Reproduction / verification

1. Create a local account or sign in through any supported provider.
2. Read the sessions.token value for the created session from the application database.
3. Set diffaudit_session=<that database value> in a browser or HTTP client.
4. Requests that rely on validateSession() resolve as the signed-in user because the cookie is compared directly to sessions.token.

Static verification is also enough: the token generated in createSession() is inserted unchanged at apps/web/src/lib/auth.ts:352-356, then compared unchanged at apps/web/src/lib/auth.ts:553-556.

Suggested fix

• Hash session tokens before storing them, using a deterministic cryptographic hash such as SHA-256.
• Continue returning the raw random token only to the cookie setter.
• In validateSession() and deleteSession(), hash the incoming cookie token and compare/delete by the stored digest.
• Add a regression test that the database never stores the raw cookie token, while the raw cookie token still validates normally.

Acceptance criteria

• Newly created sessions store a token digest, not the raw cookie value.
• A raw token copied from the browser cookie validates through validateSession().
• A raw token value is not present in the sessions table after createSession().
• Logout/session deletion uses the digest lookup and removes the session row.