Instant logout is an authorization problem

There were seven live sessions on my Royal Subz admin account.

Not seven recent logins. Seven browser or device sessions that still had access, with no page showing where they came from and no way to end one without changing the account password.

That number was not a display bug. The Supabase project had no inactivity timeout and no maximum session lifetime. Both controls require a higher plan. Access tokens lasted one hour, but refresh-token rotation kept sessions alive indefinitely. A laptop signed in last year could still renew itself today.

With one operator, that was easy to ignore. With staff accounts, it becomes a security system built on memory: remember every computer, every phone, every borrowed device, and every café network anybody has ever used.

I wanted a page showing every live admin session—device, IP, location, creation time, last activity—with a Revoke button that worked immediately.

The word “immediately” turned out to be the entire project.

Deleting a session does not kill its token

Supabase access tokens are self-contained JWTs. The server can verify one without asking the database whether its session row still exists. Deleting a row from auth.sessions stops future refreshes, but the current token remains cryptographically valid until it expires.

That leaves up to an hour of privileged access after pressing Revoke.

Shortening token lifetime was not available on the project plan, and doing it would only reduce the window, not close it. So I moved the check from authentication to authorization.

Two database functions already gate almost every admin operation: has_role and has_admin_permission. Across the migration history, they appear roughly 358 times. Adding a revoked-session check inside those functions meant the next authorized query from that token would fail, even while the token itself remained valid.

The guard had to be precise. has_role(user_id, role) is sometimes asked about another person while rendering an admin screen. A revoked caller should lose their own authority, not make the database lie about somebody else’s role. The check applies only when the subject is the caller.

has_admin_permission needed its own patch. Its admin and owner branches call has_role, but moderators can receive granular permissions through a direct query. Relying only on inheritance would have revoked admins while leaving moderators operational.

Authentication answers whether a token is genuine. Revocation answers whether that genuine token is still allowed to do anything.

Visibility and revocation needed different tables

I separated session description from session denial.

admin_session_meta stores enrichment: session ID, user, trusted client IP, country, city, browser, operating system, device type, creation time, and last seen time.

revoked_sessions is deliberately smaller: session ID as the primary key, user, timestamp, who revoked it, and whether the reason was manual, self, or stale.

The revocation lookup sits inside authorization on potentially every admin query. It should be one indexed existence check, not a read through a wide metadata row. More importantly, enrichment is allowed to fail. A location API can be unavailable or a device string can be unfamiliar; neither should make a session impossible to revoke.

The session list is therefore derived from Supabase’s own auth.sessions, left-joined to my metadata. If registration never ran, the row still appears as “Unknown device” with a working Revoke button.

Building the list from metadata would have made an unregistered session invisible. A security page that confidently omits a live session is worse than no page at all.

Reading auth.sessions does create a dependency on Supabase’s internal schema. I contained that risk to two functions. If the schema changes, the list can degrade to metadata; the actual denial mechanism still depends only on my revocation table.

The dangerous migration shipped without a UI

Patching has_role touched the widest security boundary in the application. One bad expression could lock every administrator out at once.

I made that the first phase and shipped it alone: tables, narrow RLS, the revocation lookup, patched authorization functions, session listing, and the revocation RPC. No page and no client subscription yet.

Before applying the forward migration, I wrote and apply-tested the rollback script. At that moment it simply restored the functions to the definitions they already had. That was the point. An emergency procedure should be proven while there is no emergency.

I took a database backup, ran the rollback, applied the schema and core migrations inside explicit transactions, then immediately loaded the heaviest admin pages. Only after the existing panel behaved normally did I simulate JWT claims in SQL, insert a temporary revocation, and verify the chain:

current_session_revoked = true
has_role               = false
has_admin_permission   = false

The test revocation ran inside a block that always rolled back, so I could prove denial against a real session without stranding it.

The phase also exposed an older authorization gap: the project referenced an owner role 132 times, but nobody had ever been assigned it. My account held only admin, making every owner-only behavior unreachable. I added the owner role without removing admin because three older policies checked for admin specifically. Holding both roles was the compatible shape, not redundant cleanup.

Registration must not trust the thing it labels

The second phase enriched sessions through register-admin-session.

The function reads session_id from the caller’s verified JWT, never from the request body. Allowing a caller to name a session would let them overwrite somebody else’s metadata and label a suspicious login as my own laptop in Dhaka—inside the interface meant to identify suspicious logins.

Registration happens from AdminLayout, not only the admin login form. That covers a user who signs in through the storefront and later navigates into the admin area. Every admin route passes through the layout, so one call site captures both paths.

IP comes from cf-connecting-ip, which the trusted proxy overwrites, rather than the caller-controlled left edge of x-forwarded-for. Location is resolved once at registration through DB-IP and stored. Reloading an admin page only advances last_seen_at; it does not spend location quota to rediscover a city that cannot change for that session.

Device parsing also had to prefer honesty over detail. Chrome and Edge can provide the Windows version through client hints. Firefox and Safari cannot, so they show “Windows” rather than a guessed Windows 10 or 11.

Tests against real user-agent strings caught the usual traps: Edge contains Chrome, Chrome contains Safari, Android contains Linux, and iOS says like Mac OS X. Safari on iPhone even places Mobile/... between its version and Safari tokens. The parser works most-specific-first, with fourteen tests protecting the order.

If any enrichment step fails, registration returns safely and the authoritative session remains visible as unknown. Description is optional. Visibility is not.

The page protects access to itself

The new Utilities → Active Sessions page shows device, IP, location, created time, last activity, and revocation controls.

Every staff member can see and end their own other sessions. The owner can see all admin and moderator sessions, grouped by person. I deliberately did not put the route behind a configurable moderator permission. A person should not lose access to their own security page because someone forgot to enable a checkbox.

The current browser displays a “This device” badge and no Revoke button. The database function refuses that session too. UI guards can be bypassed, and accidentally locking yourself out while tidying a list is predictable enough to prevent server-side.

Revocation inserts the denial row first, then tries to delete the Supabase session. The order matters. If deletion fails because the internal schema refuses it, authorization has already ended. A surviving refresh token can only mint another token carrying the same revoked session ID.

Old sessions with no metadata are not hidden or pushed to a secondary list. They are exactly the sessions this page exists to expose.

Realtime explains the decision; it does not enforce it

Once a revocation row is inserted, the database denies the next privileged query. A browser websocket is not part of that guarantee.

Realtime improves the experience. Every admin page subscribes to revocations for its own session. When one arrives, the browser clears its local tokens, redirects to login, and says, “Session expired. Please log in again.” It does not announce that an administrator revoked the session; if the account is compromised, the message is being read by the person holding it.

The same layer shows a new-session toast. The owner sees any new staff session; everyone else sees only new sessions on their own account.

The first RLS design would have silently broken both features. I had enabled RLS with no direct client policies because all business operations used definer functions. But Supabase Realtime evaluates SELECT policies for each subscriber. The channel would connect successfully and deliver nothing.

I added narrow read policies: users can see their own revocations; owners see all session metadata while other staff see their own. There are still no client write policies. RLS decides which events the browser receives, so a moderator is never sent somebody else’s session and asked to hide it in JavaScript.

If Realtime disconnects, safety does not change. The person sees failing queries until reload instead of a clean message. That is a clarity failure, not an access failure.

Sessions that never expire need a sweep

The fifth phase added automatic revocation for staff sessions idle longer than 30 days.

A daily VPS cron calls an edge function protected by the existing cron secret. The database sweep considers only admin, owner, and moderator sessions; customer sessions are never punished for not visiting the storefront for a month.

The function can also be called manually by the owner. Its authorization has a subtle shape: cron arrives as service_role without a user ID, while an anonymous Supabase token also has no user ID. The null branch is safe only because execute permission is explicitly removed from PUBLIC and granted to authenticated users and service role. The grant is part of the security condition, not migration boilerplate.

Before scheduling the first sweep, I added a dry-run count. Sessions had never expired on this project, so the first cleanup could remove several at once. Automation should not make its first consequential decision invisibly.

Revocation rows remain indefinitely for now. Pruning one while an access token still carries its ID would restore authority for the token’s remaining life. At staff scale, a small table is cheaper than a clever retention bug.

The end-to-end audit broke my confidence in the boundary

After all five phases, I audited every authorization surface instead of rereading only the code I already believed was correct.

That found the serious gap.

Fifteen edge functions queried user_roles through the service role. Service role bypasses RLS, so those checks never reached has_role or current_session_revoked. A revoked session could still call functions that reset any user’s password, create a new moderator, create users, issue invoices, verify payments, send email, and access provider diagnostics.

The admin panel was contained. The account was not.

I added a shared session guard to all fifteen privileged paths. It checks the caller’s session against revoked_sessions before any role-authorized work. It deliberately fails closed: if the lookup errors, a legitimate admin retries; letting a revoked attacker reset the owner’s password is not reversible.

The audit then mechanically verified that every function querying roles carried the guard. Parsing all edge functions with the TypeScript compiler caught a broken import introduced by the bulk edit. Another function initially invoked the guard before its service client existed, hitting JavaScript’s temporal dead zone. Security changes need compilation checks as much as threat models.

This was the most useful lesson in the project. “358 enforcement points” sounded comprehensive because it was a large, measured number. It was comprehensive only inside the database. The paths bypassing that layer were where the most powerful actions lived.

Instant revocation is a chain

The completed system makes live sessions visible, records them without trusting client-supplied identity, ends admin authority on the next operation, removes refresh capability where possible, explains forced logout through Realtime, and clears abandoned staff sessions automatically.

It also keeps explicit boundaries. Revocation covers admin authority immediately but leaves ordinary customer-side access until the current token expires, at most an hour. Customer session management and login history remain separate work. Device detection returns unknown rather than inventing precision.

What looked like a Sessions page became a chain of guarantees:

authoritative session list
  -> independent revocation record
  -> database authorization guard
  -> privileged edge-function guard
  -> best-effort refresh-token deletion
  -> Realtime explanation

Any one piece alone would have looked convincing in a demo. The page without database enforcement would be theatre. Database enforcement without the edge-function audit would leave the most dangerous doors open. Realtime without RLS would connect and never speak.

That is why instant logout was never really a logout feature. It was an authorization system with a Revoke button at the front.