Properly Sign Out a Supabase JWT Session

While implementing Supabase Auth using GitHub Oauth to an Astro site, I was having trouble signing a user out. Turns out it’s a “feature” of the Supabase client functionality that doesn’t actually touch the db sessions when you run await supabase.auth.signOut(). This is documented in the docs:

Since Supabase Auth uses JWTs for authentication, the access token JWT will be valid until it’s expired. When the user signs out, Supabase revokes the refresh token and deletes the JWT from the client-side. This does not revoke the JWT and it will still be valid until it expires.

Frankly, I don’t see the point of this function if it actually doesn’t log the user out. It’s also possibly a security issue. JWTs are such a nightmare to work with.

In any case, this was easy to work around using a custom database function which the Supabase assistant helped me to write:

CREATE OR REPLACE FUNCTION public.delete_user_sessions(user_id text)
RETURNS void AS $$
BEGIN
    DELETE FROM auth.sessions WHERE user_id = user_id;
END;
$$ LANGUAGE plpgsql
SET search_path = public;  -- Set the search path to the public schema

..which you can then call when signing out like this:

const { data, error } = await supabase.rpc('delete_user_sessions', { user_id: userId })

My full logout.ts api endpoint for the Astro site looks like this:

export const prerender = false

import { supabase } from '@/lib/supabase'
import type { APIRoute } from 'astro'

export const GET: APIRoute = async ({ cookies, redirect }) => {
  const { data: user } = await supabase.auth.getUser()
  if (user?.user) {
    // Deletes all the session rows from the db for this user
    await supabase.rpc('delete_user_sessions', { user_id: user.user.id })
  }
  // Revokes the refresh token and deletes the JWT from the client
  await supabase.auth.signOut()

  // Deletes the associated cookies
  cookies.delete('sb-access-token')
  cookies.delete('sb-refresh-token')
  return redirect('/')
}