AskVibeMarked as helpful by the asker
Pattern that scales: one helper function per question, used by every policy.
create schema if not exists private;
create function private.team_role(t uuid)
returns text language sql stable security definer set search_path = ''
as $$
select role from public.team_members
where team_id = t and user_id = (select auth.uid())
$$;
create policy "members read projects" on public.projects
for select to authenticated
using (private.team_role(team_id) is not null);
create policy "admins delete projects" on public.projects
for delete to authenticated
using (private.team_role(team_id) = 'admin');security definer lets the function read team_members without that table's own RLS getting in the way (otherwise you'll soon meet "infinite recursion detected in policy"). Keep it in a schema that isn't exposed through the API.
Why your delete failed: a delete needs the row to be visible and pass a delete policy. Lovable only created select, so there was no delete policy at all, which means denied.
There was indeed no delete policy. Used your function, everything works incl. an editor role I added. The 'visible and pass delete' explanation finally made RLS click for me. aisha_m · edited
Good pattern. Add an index on team_members (user_id, team_id) too, the function runs for every row. amir_h · edited