Hosted postgres cannot GRANT USAGE ON SCHEMA auth to a custom NOLOGIN role; need supabase_admin

[sentientia-india]

Official support ticket: SU-497097
Project: sentientia-revenue-os (phzssifqkrpdrftxejpy)

A SECURITY DEFINER RPC owned by custom role ros_enrichment_executor needs to call existing auth.uid(). The role already has EXECUTE on auth.uid(), but not USAGE on schema auth, so the call fails with SQLSTATE 42501 permission denied for schema auth.

Please run exactly this as supabase_admin (current owner of schema auth):

GRANT USAGE ON SCHEMA auth TO ros_enrichment_executor;

Verified on this project:

• Schema auth owner is supabase_admin
• Only supabase_admin has USAGE WITH GRANT OPTION on auth
• Dashboard SQL Editor and CLI both run as postgres
• has_schema_privilege(current_user, 'auth', 'USAGE WITH GRANT OPTION') is false
• GRANT as postgres returns success with no ACL change (silent no-op)
• ros_enrichment_executor remains NOLOGIN / NOINHERIT / NOSUPERUSER / NOBYPASSRLS
• It has no SELECT on auth.users

Do not change schema ownership, grant superuser/BYPASSRLS, add role memberships, or grant table privileges on auth.users. Only schema USAGE for that existing role.

[DigitalA7]

Only Supabase staff can run that GRANT, so this thread cannot get it done for you; the support ticket is the right place for it. Since the April 2025 restriction on the auth, storage and realtime schemas (changelog), postgres has no grant option on auth, which is why your GRANT reports success and changes nothing (Postgres prints WARNING: no privileges were granted for "auth").

You may not need the grant, though. auth.uid() only reads the JWT claims from session settings. Its definition in the auth migrations is:

select coalesce(
  nullif(current_setting('request.jwt.claim.sub', true), ''),
  (nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub')
)::uuid

current_setting lives in pg_catalog, so no privilege on auth is involved. Two ways to use that without touching the schema ACL:

A. Read the claim directly in the function owned by ros_enrichment_executor, with the expression above in place of auth.uid().

B. Wrap auth.uid() in a function owned by postgres, which does have USAGE on auth, and grant only EXECUTE on the wrapper:

create function private.current_uid() returns uuid
language sql stable security definer set search_path = ''
as $$ select auth.uid() $$;

revoke execute on function private.current_uid() from public;
grant usage on schema private to ros_enrichment_executor;
grant execute on function private.current_uid() to ros_enrichment_executor;

Both keep your constraints: no ownership change, no role membership, no access to auth.users. B has the advantage of following auth.uid() if Supabase ever changes it; A has no dependency on auth at all.

What I checked: I reproduced the privilege setup on a plain PostgreSQL 16 (schema owned by another role, executor with EXECUTE on the function but no USAGE on the schema). Creating a SECURITY DEFINER function that calls auth.uid() as the executor fails with permission denied for schema auth, and both A and B return the sub claim. I did not run this on a hosted Supabase project, so please confirm there, in particular that postgres can still call auth.uid() on yours.

[GaryAustin1]

You are very unlikely to get roles added to Auth schema because future migrations might break or reset them.
The example above creating a new function is workable.
You could also grant service_role or authenticated role to your role depending on what it does.

[cekuu35]

Agreed that the ticket is the right channel for the literal GRANT — but depending on what the RPC actually needs, you may be able to skip the auth schema dependency entirely. Three patterns that work under the April 2025 schema restrictions, roughly in order of least-privilege-fit:

1. Read the subject from the transaction GUC instead of calling auth.uid()

auth.uid() is itself a thin reader over the JWT claims that PostgREST stamps into the session for every request. Inside your SECURITY DEFINER RPC you can read the same value without touching the auth schema at all:

coalesce(
  nullif(current_setting('request.jwt.claims', true), '')::json ->> 'sub',
  null
)::uuid

current_setting(..., true) returns NULL when the GUC is absent (manual/psql sessions), so the function can decide its own behavior for "no request context" (raise vs. return null). This is the cleanest fit for your setup: ros_enrichment_executor keeps no auth privileges whatsoever, and the RPC's security posture becomes "whatever the JWT says", which is exactly what auth.uid() would have given you. If you also need the caller's role claim, it's in the same JSON: -> 'user_metadata', ->> 'role', etc.

Worth knowing the one semantic difference: this bypasses auth.uid()'s internals, so if Supabase ever changes how claims are derived, your reader changes with them (their helper functions exist partly to future-proof that). Pin the assumption with a test that asserts your reader equals auth.uid() when called from an authenticated request.

2. Change the definer's OWNER to postgres

The restriction removed postgres's grant option on auth, not plain USAGE — postgres still resolves objects in the schema (RLS policies evaluate with table-owner privileges and call auth.uid() constantly; the platform would not function otherwise). Verify on your project:

select has_schema_privilege('postgres', 'auth', 'USAGE');  -- expected: true

If true, alter function public.<your_rpc>() owner to postgres; makes the definer's runtime privileges include schema USAGE, and the existing EXECUTE grant on auth.uid() becomes usable. This is the standard Supabase pattern for SECURITY DEFINER helpers (owner postgres, pinned search_path, minimal body) — but it does widen the RPC's runtime privileges to whatever postgres can do, so it's only appropriate if the function body is small and auditable. With your NOINHERIT executor role doing everything else, this may be more privilege migration than you want — pattern 1 avoids it entirely.

3. Pass the uid from a calling policy

If the RPC is invoked from RLS policies rather than directly by clients, capture auth.uid() in the policy (policies evaluate with owner privileges, so the call works there) and pass it as a parameter to the RPC. Only safe when the function is reachable solely from trusted contexts — a client-reachable function must not accept a caller-supplied "who am I" argument, so pair this with a revoke of direct EXECUTE from anon/authenticated.

Of the three, pattern 1 is the one I'd actually ship for your description — a SECURITY DEFINER RPC that needs "the caller's sub" shouldn't need schema grants to obtain it, and reading the claims GUC keeps your least-privilege design intact while the support ticket proceeds.