[Security] Doubt upvote counts can be forged by any authenticated user

[atul-upadhyay-7]

Summary

The doubts table lets any authenticated user (1) raise the upvotes value of any doubt arbitrarily via UPDATE, and (2) INSERT a doubt directly with a fabricated upvotes count, because the consolidated INSERT policy is WITH CHECK (true).

Evidence

supabase/migrations/20260617000000_consolidate_rls_policies.sql:

CREATE POLICY "Users can insert doubts"
  ON public.doubts FOR INSERT WITH CHECK (true);

This replaced the strict policy from 20260608000001_anonymous_doubts.sql which enforced upvotes = 0 and user-attribution rules.

supabase/migrations/20260704000002_allow_doubt_upvotes.sql:

CREATE POLICY "authenticated users can update doubt upvotes"
  ON public.doubts FOR UPDATE TO authenticated
  USING (auth.uid() IS NOT NULL)
  WITH CHECK (auth.uid() IS NOT NULL AND upvotes >= 0);

The UPDATE policy has no per-row restriction — any authenticated user can set upvotes on any doubt.

Exploit

update public.doubts set upvotes = 999999 where id = '<any doubt>';
-- or insert with a fake count
insert into public.doubts (user_id, content, subject, upvotes) values (auth.uid(), 'x', 'x', 999999);

Impact

• Doubt ranking and "Most Voted" sorting can be manipulated by anyone.
• Fake doubts with inflated counts pollute the community board.

Suggested Fix

1. Restore the strict INSERT policy: upvotes = 0 and (anonymous = true requires user_id IS NULL; anonymous = false requires user_id = auth.uid()).
2. Tighten the UPDATE policy to a single-increment invariant: WITH CHECK (upvotes = OLD.upvotes + 1).
3. Move upvoting to a SECURITY DEFINER RPC upvote_doubt(doubt_id) that atomically increments and records the vote in a new doubt_upvotes table (unique per user + doubt) so each user can vote once.