| 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