tables.sql

From the guide Supabase authentication in TypeScript: two safe ways, with code. Placeholder values only: put your own keys in environment variables, never in the code.

tables.sqlSQLRawExternal
-- tables.sql: the users table, locked so only your server can reach it.
-- Run it in the Supabase SQL Editor, or keep it as a migration (supabase/migrations/0001_users.sql).
create table public.users (
  id               uuid primary key default gen_random_uuid(),
  provider         text not null,          -- e.g. 'google', 'github'
  provider_user_id text not null,          -- the person's id at that provider
  email            text,
  created_at       timestamptz not null default now(),
  last_sign_in_at  timestamptz,
  unique (provider, provider_user_id)      -- one row per person, even if two sign-ins race
);

-- Server only: Row Level Security on, with NO policy. A publishable (anon) key now reads and writes nothing here.
-- The secret key's role, service_role, bypasses RLS, so your server still can.
alter table public.users enable row level security;
revoke all on public.users from anon, authenticated;
grant all on public.users to service_role;

-- The same three lines for every table you add. With many tables, a loop:
do $$
declare t text;
begin
  foreach t in array array['users'] loop    -- list every table here
    execute format('alter table public.%I enable row level security', t);
    execute format('revoke all on public.%I from anon, authenticated', t);
    execute format('grant all on public.%I to service_role', t);
  end loop;
end $$;

-- And for every SQL function you call through /rest/v1/rpc: only the server may run it.
-- revoke execute on function public.my_function(uuid) from public, anon, authenticated;
-- grant execute on function public.my_function(uuid) to service_role;

Back to the guide