Stop exposing every user's online status and last-active time #434

Summary

anon and authenticated can read users.status and users.online_at for every row, and public.users is in the Realtime publication. The webapp writes status every 60 seconds while a tab is visible, and on every tab hide or show. Since #417, each of those writes also stamps online_at. So anyone with the public anon key can follow, live, when each signed-in person opens or leaves a tab.

• Severity: Medium (privacy)
• Area: Supabase grants and Realtime publication on public.users, admin dashboard
• Source: security review of 2026-10-06, finding M8

Production check (2026-10-06, read-only)

• public.users is in the supabase_realtime publication.
• anon has SELECT on users.status and users.online_at.
• email and notification_preferences stay hidden.

Where

• Row policies are USING (true): users_select (packages/supabase/scripts/13-RLS.sql:60-62) and users_public_anon_select (:322-324).
• Column SELECT grants that include status and online_at:
  • authenticated: packages/supabase/scripts/13-RLS.sql:71-75.
  • anon: packages/supabase/scripts/29-lint-hardening.sql:92-95.
• Publication: packages/supabase/scripts/17-realtime-replica.sql:14-19. Its comment names the wrong path; the page is apps/admin-dashboard/src/pages/users.tsx.
• The only postgres_changes consumer of users: apps/admin-dashboard/src/pages/users.tsx:108-126. It only triggers a debounced refetch(). The page loads its rows from /api/admin/users and has a refresh button (:311).
• Heartbeat: apps/webapp/src/hooks/useHandleUserStatus.ts:34-41 (every 60 s), :70-78 (tab hide or show) and :99-116 (unload).
• online_at is stamped on every status write by update_user_online_at: packages/supabase/scripts/10-1-func-users.sql:140-157.
• The webapp selects both columns in USER_PROFILE_COLUMNS (apps/webapp/src/api/users/getUserById.ts:14-15). updateUser reuses that list as its RETURNING select (apps/webapp/src/api/users/updateUser.ts:6-13). Only the heartbeat calls updateUser; Settings saves go through the update_profile RPC. No webapp code reads online_at. Presence shows other users' status, not this table.
• Server-side readers run as postgres, so the revoke does not touch them:
  • internal.is_user_online (packages/supabase/scripts/10-1-func-users.sql:174), called from security definer triggers.
  • enqueue_push_notification (packages/supabase/scripts/07-4-push-notifications-pgmq.sql:173).
  • The update-user-status cron job (packages/supabase/scripts/16-cron-jobs.sql:9-14).

Fix plan

1. Webapp first. Remove status and online_at from USER_PROFILE_COLUMNS in getUserById.ts.
   • This is required. A PostgREST update with .select() runs UPDATE … RETURNING, which needs SELECT on each returned column. Without this edit, every heartbeat fails with 42501. The hook swallows the error, so online_at goes stale and push stops skipping online users.
   • applySignedInProfile.ts:27 already sets the local status to 'ONLINE', so the store keeps a value.
2. Revoke the columns. In one new migration: revoke select (status, online_at) on public.users from anon, authenticated;. Keep status in the authenticated UPDATE grant, so the heartbeat still writes.
   • Mirror it by removing both columns from the grant lists in 13-RLS.sql:72-75 and 29-lint-hardening.sql:92-95.
3. Drop the table from Realtime. In the same migration, run alter publication supabase_realtime drop table public.users. Guard it on pg_publication_tables, so a re-run does not fail.
   • Mirror it in 17-realtime-replica.sql: remove public.users and its comment.
4. Admin page. Delete the useRealtimeSubscription({ table: 'users', ... }) call and its debounce timer in apps/admin-dashboard/src/pages/users.tsx:108-126. The refresh button stays. Keep useRealtimeSubscription itself; pages/channels.tsx still uses it.

Ship step 1 before or with the migration. The migration alone breaks the heartbeat for any tab still running the old webapp.

Repo rules that apply:

• Regenerate the seed with bun run --filter @docs.plus/supabase_back seed. Never hand-edit packages/supabase/seed.sql.
• Run bun run --filter @docs.plus/supabase_back types. Grants and the publication do not change the generated types, so expect no diff.

Out of scope

• The public profile directory (id, username, full_name, avatar). Chat sender info and the mention picker read it. Narrowing it is a separate design.
• Presence peers see the status that the client broadcasts (A forged presence payload can change the avatar on live chat messages #428).

Acceptance criteria

• With the anon key, GET /rest/v1/users?select=status,online_at is refused.
• A signed-in client's select of status or online_at on another user is refused.
• A postgres_changes subscription on public.users receives no events.
• The heartbeat still succeeds, and online_at still moves on each heartbeat.
• Signing in and a Settings profile save still work.
• Push and email still skip users who are online.
• The admin Users page loads and refreshes.

Verify

1. Apply locally: docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres < packages/supabase/migrations/<new file>.sql.
2. In psql, inside begin; ... rollback;:
   • set local role anon; select status from public.users limit 1; must fail with 42501.
   • select count(*) from pg_publication_tables where pubname = 'supabase_realtime' and tablename = 'users'; must return 0.
3. Sign in to the local webapp. Watch the network tab for the 60 s PATCH /rest/v1/users and confirm 200. Then check online_at as postgres.
4. Sign out and in again, then save a profile change in Settings. Sign-in (applySignedInProfile.ts) and useProfileSync.ts read the profile through getUserById.
5. Run cd apps/webapp && bun run test.

Related

• Bring back push on Chrome and Android devices the server switched off #417 made each status write stamp online_at.
• A forged presence payload can change the avatar on live chat messages #428 fake presence.

Generated by Claude Code