| 1 | -- Session cookies carry a random token, not the row id. |
| 2 | -- |
| 3 | -- The cookie used to be the `sessions.id` UUIDv7: a 48-bit millisecond |
| 4 | -- timestamp plus a counter that is reseeded once per millisecond and then |
| 5 | -- incremented. Sessions minted in the same millisecond therefore shared their |
| 6 | -- leading bits, and other UUIDs from the same generator (comment ids, which are |
| 7 | -- rendered into review pages) disclosed the counter state. A UUIDv7 is a good |
| 8 | -- primary key and was never meant to be a bearer secret. |
| 9 | -- |
| 10 | -- The cookie now carries 32 CSPRNG bytes and this table stores only their |
| 11 | -- SHA-256, so a leaked snapshot of `sessions` no longer contains anything that |
| 12 | -- can be presented to the server. |
| 13 | -- |
| 14 | -- Existing rows cannot be migrated: their tokens never existed, and the id is |
| 15 | -- deliberately no longer accepted. Everyone is signed out exactly once, which |
| 16 | -- is the correct price for the change. |
| 17 | DELETE FROM sessions; |
| 18 | |
| 19 | ALTER TABLE sessions ADD COLUMN token_hash text NOT NULL; |
| 20 | |
| 21 | -- Unique because it is the lookup key, and the index is what makes resolving a |
| 22 | -- session one probe rather than a scan. |
| 23 | CREATE UNIQUE INDEX sessions_token_hash_idx ON sessions (token_hash); |
23 lines · SQL