fix:logout all sessions on password reset

[rajeshj11]

logout all sessions on password reset

changes:

1. added a new column in PSQL user collection passwordUpdatedAt
2. Added the same key in the session storage at the time of login/reset password
3. on password change. passwordUpdateAt gets changed in DB. but not in all user's sessions.
4. based on this we are validating every request(user.passwordUpdatedAt > session.passwordUpdatedAt)

-> if we want to reduce the load on PSQL. we can go with Redis. it is open source but it is an independent service that we need to configure in the docker file. (we need to do setup and maintenance regularly) -> Another one we can use is Cloud Redis. supabase has below third-party redis(this needs an account which adds cost to the project. But we don't need to do any maintenance) https://supabase.com/docs/guides/functions/examples/upstash-redis

If we want to go with Redis. I recommend normal Redis as it is open source and it does not add cost to the project.

why redis?

1. fast retrival.
2. reduce no of hits on the db.
3. Compared to db response time redis response time is very fast.

[DonKoko]

hey @rphlmr . Can you tell me what you think of this approach?

[rphlmr]

I think we can do that in a simpler way (without updating the user schema).

1. Revoke tokens On password updates, we could call supabase.auth.admin.signOut() with the scope others Note: the admin version requires a jwt. I think it is the user's access_token.

Then, 2 options.

• Lower the session lifetime (from 1h to 5 min) + updating isExpiringSoon (middleware that trigger a refresh xx min before expiration).

or

• On every request (refresh access token middleware), check if the current refreshToken is revoked with a DB select on the auth.refresh_tokens table (Supabase reserved table in the auth schema) revoked column. Ex: select revoked from auth.refresh_tokens where auth.refresh_tokens.token = 'fEZ1ECs1jy7a7TvSTM5uUg' and auth.refresh_tokens.user_id = 'ab14586f-6ef1-4e85-8bda-9a003424775c'

[DonKoko]

Thanks Rapha and Rajesh for the PR and comments. I am not convinced with this approach, and I think the other one we had was still a bit better. Going to close this PR for now and revive it in the future if needed.

[rajeshj11]

@DonKoko Hey, could you please check this PR? I've addressed the feedback.

1118