Your CI caught this: the check passed on SQLite and never fired on the
stack where the database container runs on a different timezone from the
app, so a stale token stayed valid. The comparison was between a token's
`iat`, which is UTC seconds from Node, and `auth.modified_on`, which the
driver hands back interpreted in the app's timezone. With the app on
Australia/Brisbane and the database on UTC, that column comes back ten
hours in the past and the token always looks newer than the change.
Record the moment in `auth.meta.password_changed_at` instead, written by
`setPassword` with the same `Date.now()` clock that mints `iat`. Same
unit on both sides, one clock, and no timestamp parsing: the Date and
local-string branch is gone, and so is the whole-second flooring that
Postgres microseconds made necessary.
Rows written before this have no marker and revoke nothing until their
next password change, which is the safe direction to be wrong in.
Tokens are stateless JWTs, so changing a password left every session that
the old one had opened working until its own expiry, up to a day later.
That is the case the password change is meant to close: an administrator
resetting a compromised account did not evict whoever was already in it.
The auth row already records when the password last changed, so no
migration is needed: `Access.init()` reads it alongside the user it
already loads and refuses a token whose `iat` is older. Both sides are
compared as whole seconds, which is all `iat` carries, so a token minted
in the same second as the change is kept. Postgres stores that column to
the microsecond, which is why the comparison is not done in milliseconds.
It is reported as 401 rather than the usual 403 because that is what the
frontend clears the session on, so the browser holding the dead token
lands on the login page instead of a page full of errors, and `can()`
lets that one error through unwrapped for the same reason.
Only the password does this. A user row changing (a rename, an avatar,
permissions) does not, and a user with no password auth row, which is
what a login through an external provider looks like, is not affected.
José M. Requena Plens