Skip to content

Instantly share code, notes, and snippets.

@gragland
Created March 31, 2022 00:59
Show Gist options
  • Save gragland/5e09e5a145b62d44ea908ecf59b6f88d to your computer and use it in GitHub Desktop.
Save gragland/5e09e5a145b62d44ea908ecf59b6f88d to your computer and use it in GitHub Desktop.
Divjoy Supabase security update
/*** USERS ***/
create table public.users (
-- UUID from auth.users
id uuid references auth.users not null primary key,
-- User data
email text,
name text,
-- Validate data
constraint email check (char_length(email) >= 3 OR char_length(email) <= 500),
constraint name check (char_length(name) >= 1 OR char_length(name) <= 144)
);
-- Create security policies
alter table public.users enable row level security;
create policy "Can view their user data" on public.users for select using ( auth.uid() = id );
create policy "Can update their user data" on public.users for update using ( auth.uid() = id );
-- Create a trigger that automatically inserts a new user after signup with Supabase Auth
create or replace function public.handle_new_user()
returns trigger as $$
begin
insert into public.users (id, email, name)
values (new.id, new.email, new.raw_user_meta_data->>'full_name');
return new;
end;
$$ language plpgsql security definer;
create trigger on_auth_user_created
after insert on auth.users
for each row execute procedure public.handle_new_user();
-- Create a trigger that automatically updates a user when their email is changed in Supabase Auth
create or replace function public.handle_update_user()
returns trigger as $$
begin
update public.users
set email = new.email
where id = new.id;
return new;
end;
$$ language plpgsql security definer;
create trigger on_auth_user_updated
after update of email on auth.users
for each row execute procedure public.handle_update_user();
/*** CUSTOMERS ***/
create table public.customers (
-- UUID from public.users
id uuid references public.users not null primary key,
-- Stripe data
stripe_customer_id text,
stripe_subscription_id text,
stripe_price_id text,
stripe_subscription_status text
);
-- Create security policies
alter table public.customers enable row level security;
create policy "Can view their own customer data" on customers for select using (auth.uid() = id);
/*** ITEMS ***/
create table public.items (
-- Auto-generated UUID
id uuid primary key default uuid_generate_v4(),
-- UUID from public.users
owner uuid references public.users not null,
-- Item data
name text,
featured boolean,
created_at timestamp with time zone default timezone('utc'::text, now()) not null
-- Validate data
constraint name check (char_length(name) >= 1 OR char_length(name) <= 144)
);
-- Create security policies
alter table public.items enable row level security;
create policy "Can read items they own" on public.items for select using ( auth.uid() = owner );
create policy "Can insert items they own" on public.items for insert with check ( auth.uid() = owner );
create policy "Can update items they own" on public.items for update using ( auth.uid() = owner );
create policy "Can delete items they own" on public.items for delete using ( auth.uid() = owner );
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment