SQLSTATE 42501 when granting narrow privileges on managed auth schema in hosted Supabase

[thn95]

Hi,
I am testing an MFA / owner-authorization flow on a hosted Supabase staging project.
The local Supabase environment passes all tests, but the hosted project fails during a prerequisite migration with:
SQLSTATE 42501: insufficient privilege
The migration needs narrowly scoped access for NOLOGIN application definer roles:

• USAGE ON SCHEMA auth
• EXECUTE ON auth.uid()
• EXECUTE ON auth.jwt()
• column-level SELECT(id, email_confirmed_at, deleted_at) ON auth.users
  It also conditionally creates a restrictive policy on auth.users.
  These permissions are used only by database-side authorization guards for:
• current authenticated user identity
• AAL2 / recent TOTP verification
• current email verification / deleted-user state
• protecting direct RPC calls inside PostgreSQL
  The connected postgres role can read the managed Auth objects, but it does not appear to have the grant option / ownership required to grant these privileges or create the policy.
  The transaction was rolled back cleanly. I am not trying to disable RLS, broaden grants, change ownership, or bypass Supabase Auth security.
  My questions are:

1. Is there a supported way on hosted Supabase to grant these narrow privileges to application definer roles?
2. If direct grants on managed auth objects are intentionally unsupported, what is the recommended pattern for DB-side authorization that needs auth.uid(), auth.jwt(), and current state from auth.users?
3. For an operator-driven MFA recovery flow, what is the supported approach for inspecting/revoking MFA factors, sessions and refresh tokens while still keeping authorization enforced server-side?
   I would prefer to follow a supported hosted-Supabase pattern rather than work around the managed Auth permissions.
   Thanks.

[Sarvagya-24-chaturvedi]

Hi @thn95,

The difference in behavior between local and hosted Supabase comes down to role ownership:

• In local Supabase (CLI/Docker): The postgres role has superuser privileges (SUPERUSER), so it can alter or grant permissions on any schema, including auth.
• In hosted Supabase: The postgres role is intentionally nosuperuser. The entire auth schema, its tables (auth.users), and functions are owned by the internal service role supabase_auth_admin. Because postgres does not own these objects and does not have WITH GRANT OPTION, attempting to run GRANT or CREATE POLICY on auth.* fails with SQLSTATE 42501 (insufficient privilege).

Here are the authoritative, supported patterns to accomplish your requirements in hosted Supabase:

---

Question 1: Is there a supported way on hosted Supabase to grant direct privileges on auth.*?

No. Modifying grants or adding RLS policies directly on the auth schema is intentionally blocked on hosted projects. The auth schema is managed by Supabase's auth service (GoTrue); direct schema mutations risk breaking automated migrations during platform updates.

---

Question 2: Recommended patterns for DB-side authorization needing auth.uid(), auth.jwt(), and user status

Instead of granting access to auth.users directly to your custom definer roles, use one of the two standard patterns:

Approach A: Inspect AAL2 and MFA Claims Directly from the JWT (Recommended)

You do not need to query auth.users or auth.mfa_factors to verify AAL2 or TOTP verification. When a user completes MFA, GoTrue embeds the verification level and authentication methods (amr) into the JWT claims:

-- Helper function in public or your custom schema
CREATE OR REPLACE FUNCTION public.is_aal2_verified()
RETURNS boolean
LANGUAGE sql
STABLE
AS $$
  SELECT (auth.jwt() ->> 'aal') = 'aal2';
$$
;

-- Check if TOTP was used recently (inspecting the AMR claim array)
CREATE OR REPLACE FUNCTION public.has_recent_totp(within_seconds int)
RETURNS boolean
LANGUAGE plpgsql
STABLE
AS
$$
DECLARE
  amr_array jsonb;
  totp_entry jsonb;
BEGIN
  amr_array := auth.jwt() -> 'amr';
  IF amr_array IS NULL THEN
    RETURN false;
  END IF;

  FOR totp_entry IN SELECT * FROM jsonb_array_elements(amr_array)
  LOOP
    IF totp_entry ->> 'method' = 'totp' AND 
       (totp_entry ->> 'timestamp')::bigint >= (extract(epoch from now())::bigint - within_seconds) THEN
      RETURN true;
    END IF;
  END LOOP;

  RETURN false;
END;
$$
;

Approach B: Wrap State Checks in SECURITY DEFINER Functions in public

Since postgres already has read access to auth.users, wrap any needed column lookups (like email_confirmed_at, deleted_at) in a SECURITY DEFINER function owned by postgres:

CREATE OR REPLACE FUNCTION public.get_auth_guard_status(target_user_id uuid)
RETURNS TABLE (is_email_confirmed boolean, is_deleted boolean)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, auth
AS
$$
BEGIN
  RETURN QUERY
  SELECT 
    (email_confirmed_at IS NOT NULL) AS is_email_confirmed,
    (deleted_at IS NOT NULL) AS is_deleted
  FROM auth.users
  WHERE id = target_user_id;
END;
$$
;

-- Grant execute on your public wrapper to your custom role
GRANT EXECUTE ON FUNCTION public.get_auth_guard_status(uuid) TO your_custom_role;

Note: Always set SET search_path = public, auth on SECURITY DEFINER functions to prevent search-path hijacking.

---

Question 3: Operator-Driven MFA Recovery & Revocation

For inspecting and revoking MFA factors, sessions, and refresh tokens:

• Use the Supabase Auth Admin API (supabase.auth.admin) via the service_role key from a secure backend or Supabase Edge Function.

  // Edge Function or Backend Service
  import { createClient } from '@supabase/supabase-js'
  
  const supabase = createClient(SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY)
  
  // 1. Inspect MFA factors for a user
  const { data: factors } = await supabase.auth.admin.mfa.listFactors({ userId })
  
  // 2. Delete / Unenroll a compromised factor
  await supabase.auth.admin.mfa.deleteFactor({ id: factorId, userId })
  
  // 3. Invalidate all active sessions and refresh tokens
  await supabase.auth.admin.signOut(jwt, 'global')

Why not modify auth.mfa_factors or auth.refresh_tokens via raw SQL?

GoTrue maintains internal session caches and token revocation state in memory and database event triggers. Manually modifying or deleting rows in auth.mfa_factors or auth.refresh_tokens via PostgreSQL can leave stale tokens active in GoTrue or produce orphaned session states. The Admin API is the only supported, consistent path for operator recovery flows.