Moving off Supabase Auth today: keep your users, keep auth.uid()

Moving a Supabase database is a dump and a restore. Auth is the part that makes people hesitate, because Supabase Auth is a running service, not just tables. The good news is that almost everything your app depends on is already in your database: the users, their bcrypt password hashes, and the small SQL function your policies call. What you replace is the service that checks a password and signs a token, and that is a short piece of server code.

1. What Supabase Auth keeps in your database

Supabase Auth is a Go service (GoTrue, published as supabase/auth) that stores its state in the auth schema of your project's Postgres. Its migrations create, among others:

• auth.users: one row per user. The encrypted_password column holds a bcrypt hash. GoTrue's password code calls Go's bcrypt.GenerateFromPassword at the default cost, which writes hashes that start $2a$10$.
• auth.identities: one row per sign-in method, with provider, provider_id (the user's id at Google, GitHub and so on) and identity_data.
• auth.sessions and auth.refresh_tokens: the sign-ins that are live right now.
• auth.mfa_factors: enrolled second factors.
• The functions auth.uid(), auth.role(), auth.email() and auth.jwt().

The functions are what your RLS policies care about. This is auth.uid() as GoTrue's migrations define it today:

create or replace function auth.uid()
returns uuid
language sql stable
as $$
  select
  coalesce(
    nullif(current_setting('request.jwt.claim.sub', true), ''),
    (nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub')
  )::uuid
$$;

It reads the sub claim of the token on the current request. PostgREST puts the verified claims of every request into the transaction setting request.jwt.claims, which its documentation reads with current_setting('request.jwt.claims', true). The first line is a fallback for a per-claim setting that older PostgREST versions used. Nothing in it is specific to Supabase: any PostgREST that receives a token with the user's id in sub makes auth.uid() return that id.

2. Export your users

Take the connection string from Project Settings → Database in Supabase and copy out the columns you need. There is no need to bring GoTrue's whole schema: it carries many tables, types and role grants that only GoTrue uses.

export SUPABASE_URL="postgresql://postgres:[password]@db.[ref].supabase.co:5432/postgres"

# Check which hash formats you have before you rely on bcrypt
psql "$SUPABASE_URL" -c "select left(encrypted_password, 4) as prefix, count(*)
  from auth.users group by 1;"

psql "$SUPABASE_URL" -c "\copy (select id, email, encrypted_password,
  raw_user_meta_data, created_at, email_confirmed_at, banned_until
  from auth.users) to 'users.csv' with csv header"

psql "$SUPABASE_URL" -c "\copy (select user_id, provider, provider_id,
  identity_data from auth.identities) to 'identities.csv' with csv header"

Users created through Supabase's sign-up flow will show $2a$. Users who only ever signed in with OAuth have no password hash. If you imported users into Supabase from another system, you may also see $argon2 or $fbscrypt, which GoTrue accepts and bcrypt does not; those users need a different check or a password reset.

These two files hold password hashes and personal information. Treat them like a database backup: keep them out of synced folders and shared drives, and delete them once the import is done.

3. Recreate auth.uid() before you restore

First turn on the REST API for the database in the console (the REST API tab). That creates the anon and authenticated roles the steps below grant to; until it is on, the grant fails because those roles do not exist.

Order matters here too. A public schema dump contains your policies, and a policy that calls auth.uid() can only be created if the function already exists. We tested it: restoring such a dump into a database with no auth schema fails with schema "auth" does not exist, and the policy is simply missing afterwards. So create the functions first, connected as your normal database user over the direct URL:

create schema if not exists auth;
grant usage on schema auth to anon, authenticated;

create or replace function auth.jwt() returns jsonb
language sql stable as $$
  select nullif(current_setting('request.jwt.claims', true), '')::jsonb
$$;

create or replace function auth.uid() returns uuid
language sql stable as $$
  select (auth.jwt() ->> 'sub')::uuid
$$;

create or replace function auth.role() returns text
language sql stable as $$
  select auth.jwt() ->> 'role'
$$;

The grant lets the anon and authenticated roles, which PostgREST switches into for each request, call the functions inside your policies. Then create a users table you own and load the export into it:

create table auth.users (
  id                 uuid primary key,
  email              text unique,
  encrypted_password text,
  raw_user_meta_data jsonb,
  created_at         timestamptz,
  email_confirmed_at timestamptz,
  banned_until       timestamptz
);
revoke all on auth.users from public, anon, authenticated;

psql "$DIRECT_URL" -c "\copy auth.users from 'users.csv' with csv header"

Now restore your public schema as described in the full migration guide. When we restored after creating the functions, the policy came back as (user_id = auth.uid()), unchanged. If you restore with --clean, pg_restore may finish with two errors, cannot drop schema public (the REST API depends on that schema) and schema "public" already exists; both are harmless, and the rest of the restore completes.

The guide restores with --no-privileges, which drops every grant and revoke you made on Supabase. Two things follow. Your tables arrive with no grants for the API roles, so a signed-in request gets permission denied for table. And any function whose execute you had revoked is callable by everyone again, because Postgres lets anyone execute a new function by default; through the API that is POST /rpc/<name>, with or without a token. We tested it: a security definer function that Supabase refused to anonymous callers deleted every row when called with no token after the restore. So lock functions first, then grant table access back the way Supabase had it, to authenticated with RLS filtering the rows:

-- Functions: nobody but the owner, then grant back each one your app calls through the API
revoke execute on all functions in schema public from public, anon, authenticated;
alter default privileges revoke execute on functions from public;
-- grant execute on function public.my_function(uuid) to authenticated;

grant select, insert, update, delete on all tables in schema public to authenticated;
grant usage, select on all sequences in schema public to authenticated;

-- Anything listed here can be read (and, for tables, changed) in full by every signed-in user
select relname, relkind from pg_class
where relnamespace = 'public'::regnamespace
  and relkind in ('r', 'p', 'v', 'm', 'f') and not relrowsecurity;

Enable RLS and add a policy on every table that query lists, or revoke the grant on it. Views are listed too because a view runs with its owner's rights and skips the caller's RLS unless it is created with (security_invoker = true); recreate such views that way or revoke them. If a policy calls one of your own functions, grant execute on it back to authenticated. Do all of this before you point users at the API.

The users table stays out of reach of the API. WattleDB's REST API serves the public schema only, and with the revoke above, a query on auth.users as the authenticated role returns permission denied for table users.

4. Sign tokens in your own server

WattleDB's REST API is PostgREST. It verifies HS256 tokens signed with your database's JWT secret. To get the secret, open the database in the console, go to the REST API tab and choose Reveal JWT secret under Authentication. The workspace owner can reveal it; other workspace members cannot. WattleDB generates the secret and you cannot set it to a value of your own, which is one reason tokens Supabase issued will not verify on WattleDB. A Rotate button next to it replaces the secret, stops every existing token working, and restarts the REST API for a few seconds.

Here is a complete sign-in function. It looks the user up, checks the password against the hash Supabase stored, and signs a token:

// npm install bcryptjs jsonwebtoken pg
import bcrypt from 'bcryptjs'
import jwt from 'jsonwebtoken'
import pg from 'pg'

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL })

// Compared against when the email is unknown, so both paths take the same time
const DUMMY_HASH = bcrypt.hashSync('not-a-real-password', 10)

export async function signIn(email, password) {
  const { rows } = await pool.query(
    `select id, email, encrypted_password from auth.users
      where lower(email) = lower($1)
        and email_confirmed_at is not null
        and (banned_until is null or banned_until < now())`,
    [email])
  const user = rows[0]
  // Unknown, unconfirmed, banned and OAuth-only users all fail the same way, after the same work
  const ok = await bcrypt.compare(password, user?.encrypted_password || DUMMY_HASH)
  if (!user || !user.encrypted_password || !ok) return null

  return jwt.sign(
    { sub: user.id, role: 'authenticated', email: user.email },
    process.env.WATTLEDB_JWT_SECRET,
    { algorithm: 'HS256', expiresIn: '1h' })
}

We ran this against a hash made by Go's bcrypt package, the one GoTrue uses. bcryptjs accepted the right password, rejected a wrong one, and returned nothing for a user with no hash. The query also refuses users Supabase would have refused: anyone who never confirmed their email, and anyone banned. Leave those conditions out and those accounts can sign in again. The dummy comparison matters too: without it an unknown email answers instantly while a known one takes the time of a bcrypt check, and that difference tells an attacker which addresses have accounts.

Two claims matter. sub is what auth.uid() returns. role names the database role PostgREST switches into, and a token without one is treated as anonymous. A WattleDB database has two roles for the REST API, anon and authenticated, so use authenticated. A token with role: service_role has no role to switch into, and PostgREST answers role "service_role" does not exist. Do admin work from your server over the direct Postgres connection instead. An aud claim, which Supabase tokens carry, is accepted but not required.

Keep the secret on the server, never in a browser or mobile build. Anyone holding it can sign a token for any user.

5. Proving the policy filters

We checked the whole path on PostgreSQL 17, the version WattleDB runs, and PostgREST v12.2.3, the PostgREST release WattleDB runs, set up in Docker with WattleDB's role layout. The table had Alice's two notes and Bob's one, and this Supabase-style policy:

create policy "own notes" on public.notes
  for all to authenticated
  using (user_id = auth.uid())
  with check (user_id = auth.uid());

GET /notes  (Alice's token)        -> alice note 1, alice note 2
GET /notes  (Bob's token)          -> bob note
GET /notes  (no token)             -> 401 permission denied for table notes
POST /notes as Alice, Bob's id     -> 403 new row violates row-level security policy
GET /notes  (token, wrong secret)  -> 401 JWSError JWSInvalidSignature

The no-token result depends on your grants. A WattleDB database that turned on its REST API after 20 July 2026 gives anon no access to your tables unless you grant it, so an anonymous request is refused before RLS is even consulted. The REST API tab marks any older table that is still readable without a token.

6. The client side today

Your app no longer calls supabase.auth.signInWithPassword(). It calls your own sign-in endpoint, gets the token back, and passes it to postgrest-js, the query builder inside supabase-js:

import { PostgrestClient } from '@supabase/postgrest-js'

const db = new PostgrestClient('https://<db-id>.api.wattledb.com.au', {
  headers: { Authorization: `Bearer ${token}` },
})

const { data, error } = await db.from('notes').select('body')

Use postgrest-js on its own. The full supabase-js client sends table queries to a /rest/v1/ path, which a WattleDB REST URL does not serve today, and its .auth calls expect a GoTrue server. WattleDB's own hosted Auth is in development, with no release date yet; this guide is for moving now.

7. What does not move, and what you now own

• Sessions. Access tokens were signed by Supabase, and refresh tokens are redeemed by GoTrue, which you no longer run. Every user signs in again once. Tell them before the cut-over.
• OAuth. The identities export keeps the link between each provider account and your user id. Your Google or GitHub app, though, sends people back to your Supabase project's callback URL, so you need your own OAuth flow and a new redirect URI in each provider's settings. Re-point the existing provider app rather than creating a new one: some providers issue user ids per app, and a new app would not match the stored provider_id.
• MFA. Factors live in auth.mfa_factors, and checking them was GoTrue's job. Plan on users enrolling their second factor again in whatever you build.
• Email templates. Confirmation, magic link and reset emails are set up in Supabase's dashboard, not in your database, so a dump does not include them. Copy the wording across by hand and send them from your own mail provider.
• Everything GoTrue did for you. Rate limits and lockouts on sign-in, checks against breached passwords if you want them, single-use reset and confirmation links that expire, email verification, and token refresh. Keep access tokens short-lived, and decide how a user gets a new one (for example, a session cookie your server checks before signing a fresh token).

That last list is the real cost of the move. It is ordinary, well-understood work, and a mature auth library covers most of it, but it is now yours.

Moving off Supabase Auth