Thanks for your response. Indexing by UUID-alike has to be generally more costly than by an int I believe, and then for retrieving user I either need two trips or a join. Two trips is a no go. Join is OK, surely “less bad” of the two yet still not free. But yeah - I’d need to do some proper benchmarking to stop guessing and see what the difference actually is. Might in fact be that it is negligible even in large DB, high-traffic applications.
silverdr
dimitarvp
Not necessarily. PostgreSQL compiles the UUID to a simple byte array (128 bits / 16 bytes) and searches for that, and it is just 2x bigger than the normal numeric IDs (64 bits).
I doubt this will make a significant difference unless you’re doing 10k+ queries per second. And I’ve seen average tier Amazon RDS databases handle 50k+ reqs/sec easily (never going above 40% CPU).
0
cnck1387
Will it do the same optimization on the @primary_key {:id, :binary_id, autogenerate: true} and field :token, :binary fields used in the generator?
0
dimitarvp
Can’t claim it with 100% certainty, I simply looked at how PostgreSQL is doing things (a while ago) so IMO the abstraction layer is not important. Hope that I am not egregiously wrong.
0
LostKobrakai
You can see here how ecto native types map to postgres specific types:
5
cnck1387
silverdr
There are two things about indexing. One is the lookup performance related to the index structure/column(s), another one is the index size and related resources usage. In small and madium-sized datasets this isn’t going to be of any issue on modern hardware. OTOH I relatively recently worked on a set of applications backed by large databases. When one gets into nine figure ranges of interrelated records things look different. We had to be really careful about how and by what we wanted to index things and what querries we wanted to execute Things that worked nice on a dev machine could easily spell a disaster on prod DB. Now, I agree that such apps may not be common. It’s simply that I am mentally still in that “high alert” mode. For the current Phoenix based project I don’t expect problems but I plan to do some benchmarking anyway 
0
thojanssens1
Are there any simple rules/parameters based on which I should invalidate a session token and force the user to log back in?
For example:
- user agent changed: won’t work for most apps because nowadays users access those from multiple devices;
- ip changed: same problem as above;
- location changed: requires a third-party allowing to resolve an ip to a location.
Correct me if I’m wrong but storing the session token in DB will not be useful until such automatic invalidation system is implemented. I don’t see any other way to invalidate a session tokens in DB.
(Not saying it shouldn’t be stored though, it’s a must as the above system should be implemented some day).
0
drl123
Your first two points are exactly why you would want a session token instead of just an auth or bearer token. If the auth or bearer token were to get compromised, even if you forced logout for any connected sessions with that token (say they user logs out in one tab), the other device with that same auth/bearer token would immediately be re-authenticated because their token is still valid. By storing one token per ‘session’ (connected user), you could individually control logging each tab/browser/device out.
I recently just ran into the same problem with a legacy app where they only used auth tokens and did not have the tokens stored in the db. As a result, if you asked the auth service if the token was valid, it would always be. Logging out was only managed by deleting the token from the session so the browser no longer knew what it was. However this means that if another browser is using the same token, they don’t get logged out when you log out of your tab. Having a unique token per connection gives you finer control of which sessions get terminated and when. And there is no more load on the system because you still have to look up at least one token anyhow.
Additionally, you could now display for the user all of the sessions using their account and allow them to invalidate any they don’t think are legitimate. Then the compromised session user would need to re-authenticate and if all they knew was that session token, they would not be able to unless they knew the username/password.
I’m actually in the process of adding the exact mechanism phx_gen_auth uses in my legacy app right now for these very reasons.
0
thojanssens1
Thank you. I think you replied to the original post from May 2020 which was already answered. The subsequent question was about strategies to invalidate the tokens stored in the database, more concretely.
You answered that partially here:
When do you display the user’s list of sessions? Even then, you still have to wait for the user to take action (on his next login, which might be in a long time), while the compromised session is active.
So to reformulate my question, the theory is clear, we want the ability to invalidate a particular session that might have been potentially compromised; but in practice, are there some simple parameters (i.e. which won’t take me months to code) on which I can base myself to force the user to login again (thus by invalidating the session in the db)?
In other words, I currently do store the sessions in DB, but I do not make use of the advantage to store those, as long as I do not have an invalidation mechanism.
0