sql-rls-security-definer-bypass

Answer

Root cause: getTeacherDashboard runs an analytics query that JOINs the users table (e.g., students JOIN users ON users.id = students.user_id). Under the eduos_app pool, runWithAuthContext(teacher, teacher) makes the session the teacher, so RLS policy users_select_self_or_admin filters the JOIN to only the teacher's own users row. Every other student's users row vanishes, so COUNT(DISTINCT student) collapses to 0. The fix is to wrap the exact aggregate query in a SECURITY DEFINER function owned by a privileged role, pin search_path, and GRANT EXECUTE only to the app role — never loosening the policy itself.

1. Migration 027 — SECURITY DEFINER function

-- migrations/027_count_active_students_security_definer.sql
BEGIN;

-- Wraps the EXACT COUNT(DISTINCT) query from the analytics dashboard.
-- SECURITY DEFINER: executes with the owner's privileges (owner has
-- BYPASSRLS), so the per-user `users_select_self_or_admin` RLS policy
-- no longer filters out non-self user rows during the JOIN.
CREATE OR REPLACE FUNCTION app.count_active_students(p_teacher_id uuid)
RETURNS bigint
LANGUAGE sql
STABLE
PARALLEL SAFE
SECURITY DEFINER
-- Pinned search_path: no `public`, pg_catalog last, so no shadowing/
-- hijacking of `count`, `public.count(...)`, or relations at runtime.
SET search_path = app, pg_catalog
AS $$
    SELECT COUNT(DISTINCT s.id)
    FROM app.students s
    JOIN app.users u ON u.id = s.user_id
    WHERE s.teacher_id = p_teacher_id
      AND s.status = 'active'   -- keep the EXACT WHERE clause of the original query
$$;

-- Owner must be a role that bypasses RLS on the underlying tables:
-- superuser, BYPASSRLS role, or table owner (owners bypass RLS unless
-- FORCE ROW LEVEL SECURITY is set).
ALTER FUNCTION app.count_active_students(uuid) OWNER TO eduos_owner;

-- Lock it down: no PUBLIC execution; only the app role can call it.
REVOKE ALL ON FUNCTION app.count_active_students(uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION app.count_active_students(uuid) TO eduos_app;

COMMENT ON FUNCTION app.count_active_students(uuid) IS
  'DOGFOOD-005: SECURITY DEFINER wrapper for analytics COUNT(DISTINCT) that joins users. '
  'Caller authz (p_teacher_id must be the caller''s own id) is enforced in the repository layer.';

COMMIT;

2. Repository change — call the function instead of the raw query

// src/repos/analytics.repo.ts
export async function getActiveStudentCount(
  client: PoolClient,
  teacherId: string,
): Promise<number> {
  // RLS-safe: runs inside SECURITY DEFINER app.count_active_students (migration 027)
  // as eduos_owner, bypassing users RLS; search_path pinned; GRANT to eduos_app.
  const { rows } = await client.query(
    `SELECT app.count_active_students($1)::int AS count`,
    [teacherId],
  );
  return Number(rows[0]?.count ?? 0);
}

Security invariant kept intact: SECURITY DEFINER bypasses RLS only; it does not relax authorization. The repository only ever calls count_active_students with the session's own teacherId (already resolved from the auth context by getTeacherDashboard), so a teacher cannot count another teacher's students by passing an arbitrary id. Policy stays untouched.

3. Regression test

// test/regression/dogfood-005.active-students.rls.test.ts
it('DOGFOOD-005: active students != 0 under a teacher RLS session', async () => {
  // Pre-fix: throws "function app.count_active_students(uuid) does not exist"
  // (migration 027 absent) — test fails. Post-fix: returns 2.
  const count = await runWithAuthContext('teacher', 'teacher', async () => {
    const { rows } = await pool.query(
      `SELECT app.count_active_students($1)::int AS count`,
      [teacherId],
    );
    return Number(rows[0].count);
  });
  expect(count).toBe(2);
});