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.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;