sql-rls-secdefiner-lost-migration

Answer

Root cause. The dashboard joined users inline (e.g., enrollments e JOIN users u ON u.id = e.student_id). Under the teacher's role, the users_select_self_or_admin policy filters users down to the teacher's own row, so the JOIN yields 0 rows and the UI faithfully renders Active Students = 0. The fix is a SECURITY DEFINER RPC that runs as the migration owner (bypasses RLS), wired into the repo as migration 027 and called from the client.

1. Migration 027 — SECURITY DEFINER function (supabase/migrations/027_teacher_dashboard_active_students.sql):

-- Runs as postgres/supabase_admin (migration runner) → SECURITY DEFINER bypasses
-- the users_select_self_or_admin RLS that zeroed the inline join.
create or replace function public.get_teacher_active_students(
  p_teacher_id uuid default auth.uid()
)
returns table (
  student_id    uuid,
  student_name  text,
  enrollment_id uuid,
  enrolled_at   timestamptz
)
language plpgsql
stable
security definer
set search_path = public, pg_temp          -- harden against search_path hijacking
as $$
declare
  v_requester uuid := auth.uid();
  v_is_admin  boolean;
begin
  if v_requester is null then
    raise exception 'not authenticated';
  end if;

  -- Admins may inspect any teacher's roster; teachers only their own.
  select exists (
    select 1 from public.user_roles r
    where r.user_id = v_requester and r.role = 'admin'
  ) into v_is_admin;

  if not v_is_admin and (p_teacher_id is distinct from v_requester) then
    raise exception 'insufficient privilege: cannot query teacher %', p_teacher_id;
  end if;

  return query
    select e.student_id,
           u.name          as student_name,
           e.id            as enrollment_id,
           e.created_at    as enrolled_at
    from public.enrollments e
    join public.users u on u.id = e.student_id
    where e.teacher_id = p_teacher_id
      and e.status = 'active';
end;
$$;

revoke all on function public.get_teacher_active_students(uuid) from public;
grant execute on function public.get_teacher_active_students(uuid) to authenticated;

create index if not exists enrollments_teacher_status_idx
  on public.enrollments (teacher_id, status);

2. App wiring — replace the RLS-filtered inline query with an RPC call:

// dashboard.ts — before (broken: count always 0 for teachers)
const { count } = await supabase
  .from('users')
  .select('id', { count: 'exact', head: true })
  .in('id', rosterStudentIds);

// after — SECURITY DEFINER RPC, scoped server-side to auth.uid()
const { data, error } = await supabase.rpc('get_teacher_active_students', {
  p_teacher_id: session.user.id,
});
if (error) throw error;
const activeStudents = data ?? [];

3. test-db helper — stop hardcoding devsecret; authenticate to the test container:

// test/helpers/db.ts — before: JWT hand-signed with the dev secret, so every
// request to the test container (different secret) landed unauthenticated.
const SERVICE_ROLE_KEY = createJwt({ secret: 'devsecret', claims: { role: 'service_role' } });

// after — resolve from the test project, never hardcode
const SERVICE_ROLE_KEY =
  process.env.TEST_SERVICE_ROLE_KEY ?? (await fetchTestContainerKeys()); // supabase status / container env

4. Seed ordering — parent_links.parent_id FKs to parent_profiles, not users:

-- wrong: FK violation
insert into parent_links (parent_id, student_id) values ('u-parent-1', 'u-stu-1');

-- correct: parent_profiles row first
insert into parent_profiles (id, user_id, name) values ('pp-1', 'u-parent-1', 'Parent One');
insert into parent_links (parent_id, student_id) values ('pp-1', 'u-stu-1');

5. Quizzes — quiz RLS is created_by = auth.uid(), so seeds must set it:

insert into quizzes (id, lesson_id, title, created_by)
values ('quiz-1', 'lesson-1', 'Ch.1 Quiz', 'u-teacher-1');