Files
faunosteo/supabase/migrations/20261008110100_private_execute_hardening.sql

29 lines
1.7 KiB
SQL

-- Harden EXECUTE on the private helpers created by the foundation, profiles
-- and platform-roles migrations.
--
-- The foundation ran `alter default privileges in schema private revoke
-- execute on functions from public`. PostgreSQL applies per-schema default
-- privileges on top of the built-in defaults; a per-schema REVOKE cannot
-- remove the built-in EXECUTE for PUBLIC. Every function created since kept
-- it. Nothing was reachable through the API (PostgREST exposes `public`
-- only and anon has no USAGE on `private`), but the project rule is
-- "EXECUTE revoked from public, granted only to the roles that evaluate
-- it". This migration makes it true; pgTAP 001 pins it with a census.
revoke all on function private.set_updated_at() from public;
revoke all on function private.reset_phone_verification() from public;
revoke all on function private.handle_new_auth_user() from public;
revoke all on function audit.reject_mutation() from public;
-- Role predicates keep their explicit grant to authenticated (policies).
revoke all on function private.has_platform_role(uuid, public.platform_role[]) from public;
revoke all on function private.is_platform_owner(uuid) from public;
revoke all on function private.is_platform_admin(uuid) from public;
revoke all on function private.is_platform_moderator(uuid) from public;
revoke all on function private.is_support(uuid) from public;
-- Owner-context helpers: RPCs and privileged SQL sessions only.
revoke all on function private.can_manage_platform_role(uuid, public.platform_role) from public;
revoke all on function private.assign_platform_role(uuid, uuid, public.platform_role) from public;
revoke all on function private.withdraw_platform_role(uuid, uuid, public.platform_role, text) from public;