Repository navigation
Carry the production function revokes in the Supabase migrations #397
Description
Summary
Production has the correct EXECUTE revokes on sensitive SECURITY DEFINER functions. The repo migrations do not. A fresh deploy from packages/supabase/migrations/ (a self-hoster, a new staging project, or a production rebuild) would expose these functions to anon or to any signed-in user.
This issue merges three review items: get_inactive_users, the push queue functions, and admin_get_document_member_counts.
- Severity: Medium. Production is safe today (see below). Any new deploy from the repo is not.
- Area: Supabase migrations
- Source: security review of 2026-10-06, findings C1, H3, M10, L6, L7
Production check (2026-10-06, read-only)
has_function_privilege on production returns false for both anon and authenticated on every function below. So production is not exploitable today.
The gap in the repo
The broad revoke lives only in packages/supabase/scripts/29-lint-hardening.sql §5–§8. That file runs on a local seed only; packages/supabase/CLAUDE.md says "no migration carries it". Hosted Supabase grants EXECUTE on new public functions to anon and authenticated by default. So a migration that revokes only from public, anon leaves authenticated open.
| Function | Where it is defined | What the repo revokes | Risk on a fresh deploy |
|---|---|---|---|
purge_document_footprint |
migrations/20260715082400_fix_purge_document_views_keying.sql:9-55 |
public, anon only |
Any signed-in user deletes any document's chat, chat media and view stats |
consume_push_queue, ack_push_message |
migrations/20260519200000_scripts_functions_triggers_parity.sql:577-624 |
nothing | Anyone reads pending push payloads, or acks them so no push is sent |
get_inactive_users |
scripts/28-ghost-accounts-audit.sql:18-50 only (no migration) |
nothing | Anyone reads the email of every never-active user |
get_user_deletion_impact, get_ghost_summary_public |
scripts/28-ghost-accounts-audit.sql:58,88 only |
nothing | Admin data open to anyone |
admin_get_document_member_counts |
migrations/20260519200000_...sql:4478-4500 |
anon only, not public; body skips its check when auth.uid() is null |
Anon reads member counts |
create_direct_message_channel |
migrations/20260519200000_...sql:5504-5603 |
none for authenticated |
Any user adds any user to a DM (notification spam) |
enqueue_document_view, update_view_duration |
migrations/20260610135024_revoke_view_tracking_rpcs_from_browser.sql |
anon, authenticated but not public |
Anon inflates view counts |
process_document_views_queue |
migrations/20260726181000_*.sql:32 |
nothing | Anon runs a cron job |
get_*_media_storage_* (3) |
migrations/20260623120000_chat_media_attachments.sql:1108,1150,1184 |
public only |
Anon reads per-document storage stats |
Script 28 is not in any migration, but the production admin server calls two of its functions (apps/hocuspocus.server/src/api/services/adminGhostAccounts.service.ts:182,253). So it was applied to production by hand.
Fix plan
- Add one migration that, for each function above, runs
revoke all on function ... from public, anon, authenticated;andgrant execute on function ... to service_role;. - Move the three script-28 functions into that migration with
create or replace, so production and the repo match. - Port the programmatic sweep from
scripts/29-lint-hardening.sql§5–§8 into a migration. The same rule must then apply to every future DEFINER function, not only this list. - Add a CI check after
db reset: query everyprosecdeffunction inpublicand fail whenanonorauthenticatedholds EXECUTE on a function that is not in theuser_facing_namesallowlist. - Mirror the change in
packages/supabase/scripts/. Then runbun run --filter @docs.plus/supabase_back types.
Before you start, check every caller. Each function above is called only with the service role (hocuspocus worker or admin service). Confirm with grep -rn "<function name>" apps/.
Acceptance criteria
- On a fresh
db resetfrom migrations only (skip29-lint-hardening.sql), every function in the table returnsfalseforanonandauthenticatedinhas_function_privilege. - Production still returns
falsefor all of them after the migration. - The push worker, the email worker, the admin ghost-account pages and the document purge still work.
- The new CI check fails when a test DEFINER function is added without a revoke.
Verify
select p.oid::regprocedure,
has_function_privilege('anon', p.oid, 'EXECUTE') as anon,
has_function_privilege('authenticated', p.oid, 'EXECUTE') as authed
from pg_proc p
where p.pronamespace = 'public'::regnamespace and p.prosecdef
and p.prorettype <> 'trigger'::regtype
order by 2 desc, 3 desc, 1;Every row with true must be a browser RPC that checks auth.uid() in its body.
Generated by Claude Code
Activity
fix(supabase): close client write and function grant gaps
HMarzban commentedon Oct 6, 2026
Fixed in
335138donclaude/youthful-lovelace-3nvsc6: explicit revokes frompublic, anon, authenticatedfor every listed function, and the three script-28 functions moved into migration20261006130000_close_client_write_and_grant_gaps.sql. Their return tables were checked against production and match exactly, socreate or replaceis safe there.Deliberately not done:
packages/supabase/CLAUDE.mdrecords that this sweep already brokeget_document_member_previewsandget_document_memberslocally. The explicit list is safer.Also removed
create_direct_message_channelfromuser_facing_namesin29-lint-hardening.sql, so a local reset matches production. Its webapp wrapperapps/webapp/src/api/rpc/createDirectMessageChannel.tsis now unused; that is a separate cleanup.Generated by Claude Code
docs: record the security and push fixes in changelogs and agent docs
[-][Security] Migrations do not carry the function revokes that production has[/-][+]Carry the production function revokes in the Supabase migrations[/+]fix: close follow-ups from the security and push review
HMarzban commentedon Oct 9, 2026
Reopened: the board automation closed this issue before the push. The code is on
mainthrough merge083f37d84(migration20261006130000_close_client_write_and_grant_gaps.sql). It stays open until that migration is applied on prod. Fix-plan steps 3 and 4 were cut by the implementing agent, and that cut still needs a maintainer ruling.HMarzban commentedon Oct 9, 2026
Decision, 2026-10-09 (HoE, delegated by the maintainer for today's delivery)
The scope cut is accepted. Fix-plan steps 3 (port the
29-lint-hardening.sql§5–§8 sweep into a migration) and 4 (a CI grant check) are not done. Reasons:What shipped instead:
20261006130000revokes the explicit list of 14 functions. It was applied to prod on 2026-10-09.packages/supabase/CLAUDE.mdnow has a rule: every new service-roleSECURITY DEFINERfunction revokes its own EXECUTE in its own migration.The remaining gaps are tracked in #469 (the
internalschema helpers) and #470 (anon default table privileges).