[catfood]

So the session keys mapped to usernames, rather than IDs in the database? Otherwise, when the database is restored with the old user IDs, the session would become invalid instead of continuing to work. This is what I'm seeing:

1. Tables truncated. 2. In this window, someone creates an account with a username that existed in the dropped database. 3. They see a blank user page because a new user record was created. 4. Database restored. 5. It's as if you're logged into the original user's account.

[cyrusaf]

This is not what happened. The tokens were mapped to user IDs and when people signed in, the db created new users which may have had the same IDs as old deleted accounts. When they restored the DB, these tokens pointed to other users and granted access to these other users' accounts. Quite an unfortunate situation. May have been mostly avoidable if UUIDs were used instead of incrementing IDs, but hindsight is 20/20.

[catfood]

The part of that that I don't get is how a new user could have the same ID as an old (truncated) user since "our system created new records for them, with primary keys generated from the existing sequence (PostgreSQL does not reset id sequences on truncate)."

Do they mean that the only potentially exposed accounts are those that signed up after the database was restored?

[cyrusaf]

It's possible their truncate also restarted the sequence: "TRUNCATE TABLE users RESTART IDENTITY;"

https://stackoverflow.com/questions/13989243/sequence-does-n...

Although, I am confused about how sign ins created new users. When they say sign ins, do they mean new accounts?

[catfood]

Yeah they must mean new accounts, if not then I'm lost. I guess it could have reset autoincrement but they said it didn't. The only other thing I can think of is that the signed token that's put in localStorage is sent to the server like "someuser|sometoken", the server inspects sometoken, says it checks out, then takes the client at its word that it's someuser.

[inopinatus]

A sign-in can result in an automatic account creation when using federated identity.

[bpicolo]

Yeah, it mentions that it's only accounts created after restore.

[jonny_eh]

"hindsight is 20/20"

Absolutely, but at the same time I now feel vindicated about using UUIDs with my stuff :D