supabase

Supabase

Migration Testing

Use a disposable local stack. db reset --local destroys local data, so first confirm it is task-owned or preserve that data. Never reset a shared stack.

supabase start
supabase db reset --local
supabase status
# Use the reported local connection string to run role-based SQL tests:
psql "$LOCAL_DB_URL" -v ON_ERROR_STOP=1 -f tests/access.sql

Test anonymous access, the authenticated owner, a different authenticated user, and the service role. Include intentional denials and a future table. A query as postgres bypasses RLS and cannot validate client authorization. Local success is necessary but does not guarantee the remote schema, roles, extensions, or data match. supabase db push is a separate remote application requiring local evidence, target inspection, and production authorization when applicable; it is never part of the local test command.

Table Privileges and Row Level Security

SQL grants allow operations on objects; RLS policies constrain which rows an allowed operation can access. Both must express the project's intended access. For owner-only notes (not public content):

CREATE TABLE public.notes (id uuid PRIMARY KEY, owner_id uuid NOT NULL, body text);
ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON public.notes FROM anon, authenticated;
GRANT SELECT ON public.notes TO authenticated;
CREATE POLICY notes_read_own ON public.notes FOR SELECT TO authenticated
  USING ((SELECT auth.uid()) = owner_id);

Here anon has no SELECT grant; an authenticated owner can read their own rows, and another authenticated user sees none of those rows. Add write grants and policies only when required, including WITH CHECK for allowed inserts or updates. Existing projects intentionally serving public data may grant anon SELECT with an explicit public-read policy; preserve that contract and any owner-only overrides. See Supabase RLS.