Lock down your Supabase tables, without writing SQL

-- Generated by RLS Studio
-- Review every policy before running against production.
-- Permissive policies on the same command combine with OR.
-- ============================================================
-- Table: public.posts
-- ============================================================
alter table "public"."posts" enable row level security;

-- Anyone (incl. anonymous) can read.
drop policy if exists "posts_select_public" on "public"."posts";
create policy "posts_select_public"
  on "public"."posts"
  for select
  to anon, authenticated
  using ( true );

-- Owner can insert their own rows.
drop policy if exists "posts_insert_owner" on "public"."posts";
create policy "posts_insert_owner"
  on "public"."posts"
  for insert
  to authenticated
  with check ( (select auth.uid()) = user_id );

-- Owner can update their own rows.
drop policy if exists "posts_update_owner" on "public"."posts";
create policy "posts_update_owner"
  on "public"."posts"
  for update
  to authenticated
  using ( (select auth.uid()) = user_id )
  with check ( (select auth.uid()) = user_id );

-- Owner can delete their own rows.
drop policy if exists "posts_delete_owner" on "public"."posts";
create policy "posts_delete_owner"
  on "public"."posts"
  for delete
  to authenticated
  using ( (select auth.uid()) = user_id );

-- ============================================================
-- Table: public.documents
-- ============================================================
alter table "public"."documents" enable row level security;

-- Org members can read rows in their org.
drop policy if exists "documents_select_org" on "public"."documents";
create policy "documents_select_org"
  on "public"."documents"
  for select
  to authenticated
  using ( org_id in (
      select org_id from "public"."org_members"
      where user_id = (select auth.uid())
    ) );

-- Org members can insert rows in their org.
drop policy if exists "documents_insert_org" on "public"."documents";
create policy "documents_insert_org"
  on "public"."documents"
  for insert
  to authenticated
  with check ( org_id in (
      select org_id from "public"."org_members"
      where user_id = (select auth.uid())
    ) );

-- Org members can update rows in their org.
drop policy if exists "documents_update_org" on "public"."documents";
create policy "documents_update_org"
  on "public"."documents"
  for update
  to authenticated
  using ( org_id in (
      select org_id from "public"."org_members"
      where user_id = (select auth.uid())
    ) )
  with check ( org_id in (
      select org_id from "public"."org_members"
      where user_id = (select auth.uid())
    ) );

-- Org members can delete rows in their org.
drop policy if exists "documents_delete_org" on "public"."documents";
create policy "documents_delete_org"
  on "public"."documents"
  for delete
  to authenticated
  using ( org_id in (
      select org_id from "public"."org_members"
      where user_id = (select auth.uid())
    ) );

-- ============================================================
-- Table: public.projects
-- ============================================================
alter table "public"."projects" enable row level security;

-- Roles [admin, member, viewer] can read.
drop policy if exists "projects_select_role" on "public"."projects";
create policy "projects_select_role"
  on "public"."projects"
  for select
  to authenticated
  using ( (select (auth.jwt() ->> 'role')) in ('admin', 'member', 'viewer') );

-- Roles [admin, member] can insert.
drop policy if exists "projects_insert_role" on "public"."projects";
create policy "projects_insert_role"
  on "public"."projects"
  for insert
  to authenticated
  with check ( (select (auth.jwt() ->> 'role')) in ('admin', 'member') );

-- Roles [admin, member] can update.
drop policy if exists "projects_update_role" on "public"."projects";
create policy "projects_update_role"
  on "public"."projects"
  for update
  to authenticated
  using ( (select (auth.jwt() ->> 'role')) in ('admin', 'member') )
  with check ( (select (auth.jwt() ->> 'role')) in ('admin', 'member') );

-- Roles [admin] can delete.
drop policy if exists "projects_delete_role" on "public"."projects";
create policy "projects_delete_role"
  on "public"."projects"
  for delete
  to authenticated
  using ( (select (auth.jwt() ->> 'role')) in ('admin') );