How to safely diagnose PGRST202 for a missing Media Catalog RPC?

[suvarnabhanu123-tech]

Hello Supabase Community,

I am diagnosing a Media Catalog API issue in my Watch & Earn project on the Supabase Free Plan.

PROBLEM

• The application endpoint GET /api/media returns HTTP 503.
• The backend calls the Supabase RPC:
  we_media_catalog(p_media: null)
• The reported PostgREST error is PGRST202.
• An earlier request also reported PGRST205 for the we_media relation.

READ-ONLY OBSERVATIONS

• Queries against information_schema.tables returned four legacy tables:
  we_profiles, we_rewards, we_sessions, and we_videos.
• The expected media tables were not returned by those queries.
• A pg_catalog query did not return we_media_catalog or we_media_eligible.
• The dashboard shows "Last Migration: No migrations" and
  "Last Backup: No backups".

IMPORTANT LIMITATIONS
These query results do not prove that the media objects are physically absent.
The authoritative migration history, installed definitions, permissions,
and PostgREST schema exposure/cache state have not been fully verified.

QUESTION
What is the safest read-only procedure to verify:

1. Migration history and possible partial/manual migration execution.
2. The actual existence and definitions of the required tables and RPCs.
3. Function grants, RLS, and schema exposure.
4. Whether the error is caused by missing objects, permissions,
   schema exposure, or stale PostgREST schema cache.

I want to diagnose the issue without risking existing user profiles,
sessions, points, or reward balances.

I have not applied migrations or refreshed the schema cache.
I do not want to run migrations until the production state and
recovery options are verified.

Thank you.

[hippi345]

PGRST202 means PostgREST couldn't find a function with that name and argument set in its schema cache. PGRST205 is the same error for a table or view. Your pg_catalog result (no we_media_catalog) already points to "object missing" rather than "cache stale". Here are read-only checks to confirm:

-- 1. Migration history (the CLI records applied migrations here)
select version, name from supabase_migrations.schema_migrations order by version;
-- error "relation does not exist" = no CLI migrations were ever applied to this project
-- 2. Does the function exist in ANY schema, and with which args?
select n.nspname, p.proname, pg_get_function_identity_arguments(p.oid) as args
from pg_proc p join pg_namespace n on n.oid = p.pronamespace
where p.proname ilike 'we_media%';
-- 3. Does the table exist in any schema?
select table_schema, table_name from information_schema.tables where table_name ilike 'we_media%';
-- (information_schema only lists objects you have privileges on; to be sure, also check:)
select relnamespace::regnamespace, relname, relkind from pg_class where relname ilike 'we_media%';
-- 4. If it exists: grants and exposure
select has_function_privilege('anon', p.oid, 'execute'), has_function_privilege('authenticated', p.oid, 'execute')
from pg_proc p where p.proname = 'we_media_catalog';
How to read the results:

Nothing in pg_proc/pg_class: the objects were never created. The fix is applying the migration that defines them, not a cache reload.
Exists, but in a schema other than public: that schema isn't exposed. Check Dashboard → Settings → API → Exposed schemas.
Exists in public with a different argument list: PostgREST matches on argument names too, so p_media has to match exactly.
Exists with matching args: the cache is stale. notify pgrst, 'reload schema'; only reloads the cache and doesn't touch data.
Separately, since the dashboard shows "No backups" on the Free plan, export what you have before changing anything (supabase db dump --data-only, or pg_dump against the connection string).

[pierrebrunelle]

You could have a look at https://github.com/pixeltable/pixeltable

[cekuu35]

Building on @hippi345's existence checks — one branch of the PGRST202 tree they don't cover, and it's the one that's about to get much more common: the function exists, but the calling role has no EXECUTE privilege on it. PostgREST returns the same PGRST202 for that case, so existence checks alone can clear the wrong branch.

The grant-level check (read-only, safe):

-- Does the calling role actually have EXECUTE?
select has_function_privilege('anon', 'public.we_media_catalog(text)', 'EXECUTE')  as anon_can,
       has_function_privilege('authenticated', 'public.we_media_catalog(text)', 'EXECUTE') as auth_can;

-- And the raw ACL, including who granted:
select p.proname, p.proacl, pg_get_userbyid(p.proowner) as owner
from pg_proc p join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public' and p.proname ilike 'we_media%';

has_function_privilege returning false on an existing function is the PGRST202-without-missing-objects case: the fix is grant execute on function public.we_media_catalog(...) to anon, authenticated; — not a migration of the object itself.

Schema exposure is the third look-alike: if the function lives outside the schemas PostgREST exposes (public by default, plus whatever's in the exposed-schemas/db-pre-request config), you get the same 202 even with existence + grants intact. Your pg_catalog query already covers existence across schemas, so if it comes back empty, that branch wins; if it comes back populated, run the two checks above before concluding "missing".

On the schema cache: the reload trigger is notify pgrst, 'reload schema'; — it's technically a command, not a read, but it's idempotent and touches no data; safe to run once your existence + grant checks agree that objects should be visible. Worth doing as the last step, not the first, because a reload masks nothing — if 202 persists after reload, the object genuinely isn't reachable by that role.

Why this branch matters right now: Supabase's October 30 change stops auto-granting the platform roles access to newly created objects in existing projects. That means "object exists, role has no grant" becomes the default PGRST202/205 cause for anything created after that date — your four-way diagnosis (missing object / wrong signature / missing grant / stale cache) is exactly the protocol more people will need, and step 2 of it (the has_function_privilege check) is the one that catches the incoming wave. The grant-side fixes for all of this are collected here: https://dev.to/cekuu35/supabase-permission-denied-after-october-30-the-exact-fix-copy-paste-sql-4d45

Your read-only instinct is right, by the way — every query in this thread reads pg_proc, pg_namespace, information_schema, or the ACL helpers. Nothing here can touch profiles, sessions, points, or reward balances.

[auraseb]

One correction to the thread before the procedure, because it changes how you read the results: a missing EXECUTE grant does not produce PGRST202. PostgREST loads every function in the exposed schemas into its schema cache whatever the grants are. The privilege filter only applies to the OpenAPI output. So when the caller lacks EXECUTE, Postgres rejects the call and you get 42501 permission denied for function ... (401 for anon, 403 for authenticated). Tables work the same way: no grant gives 42501, not PGRST205. RLS never causes either code.

That leaves four causes for PGRST202/PGRST205:

1. The object does not exist.
2. It lives in a schema that is not exposed.
3. The argument names don't match. For RPC, PostgREST matches on the function name plus the JSON keys, so p_media has to match exactly.
4. The schema cache is stale.

Everything below is read only. Run it in the SQL Editor, which connects as postgres, so the catalogs are not filtered by privileges.

-- 1. Migration history (written only by the Supabase CLI)
select version, name from supabase_migrations.schema_migrations order by version;
-- "relation does not exist" or 0 rows = the CLI never applied anything.
-- Objects created by hand in the SQL Editor never appear here, so use 2 as the source of truth.

-- 2. Do the objects exist anywhere, and with what signature?
select n.nspname, p.proname, pg_get_function_identity_arguments(p.oid) as args
from pg_proc p join pg_namespace n on n.oid = p.pronamespace
where p.proname ilike 'we_media%';

select c.relnamespace::regnamespace as schema, c.relname, c.relkind, c.relrowsecurity
from pg_class c where c.relname ilike 'we_media%';

-- 3. Only if 2 found something: grants and RLS policies
select p.oid::regprocedure,
       has_function_privilege('anon', p.oid, 'execute') as anon_exec,
       has_function_privilege('authenticated', p.oid, 'execute') as auth_exec
from pg_proc p where p.proname ilike 'we_media%';

select * from pg_policies where tablename ilike 'we_media%';

How to read it:

• Query 2 returns nothing: the objects were never created. This is your case, since your pg_catalog query was already empty. A cache reload or a grant will not fix it. You have to apply the migration or SQL that defines we_media, we_media_catalog and we_media_eligible.
• The objects exist outside public: add that schema under Data API settings > Exposed schemas, or move the objects.
• The function exists in public with different argument names: this is cause 3 above. Fix the call or the signature. The PGRST202 hint field usually suggests the right one.
• Everything matches but you still get 202: the cache is stale. Run notify pgrst, 'reload schema';. It does not touch any data.
• The function exists but anon or auth has no EXECUTE: expect 42501, not 202.

Before you apply anything, take a copy, because the Free plan shows no backups. Run supabase db dump -f schema.sql and supabase db dump --data-only -f data.sql (or pg_dump against the connection string). Then check that the new migration only creates the we_media* objects and doesn't drop or recreate we_profiles, we_sessions or we_rewards.

Sources:

• PostgREST schema cache function query (no EXECUTE filter): https://github.com/PostgREST/postgrest/blob/922f635dc19edc1abdcc614c22535763dc7d4669/src/library/PostgREST/SchemaCache.hs#L461-L463
• The privilege filter, which only applies to OpenAPI: https://github.com/PostgREST/postgrest/blob/922f635dc19edc1abdcc614c22535763dc7d4669/src/library/PostgREST/Catalog/Query.hs#L95-L156
• PGRST202 raised on a name plus argument key mismatch: https://github.com/PostgREST/postgrest/blob/922f635dc19edc1abdcc614c22535763dc7d4669/src/library/PostgREST/Plan.hs#L279
• 42501 mapped to 401/403:

  supabase/apps/docs/content/guides/api/rest/postgrest-error-codes.mdx

  Line 67 in 7425d49

  | 42501 | if authenticated 403, else 401 | insufficient privileges |

• PGRST202/205 definitions:

  supabase/apps/docs/content/guides/api/rest/postgrest-error-codes.mdx

  Lines 135 to 138 in 7425d49

  	| PGRST202 | 404 | Caused by a stale function signature, otherwise the function may not exist in the database. |
  	| PGRST203 | 300 | Caused by requesting overloaded functions with the same argument names but different types, or by using a `POST` verb to request overloaded functions with a `JSON` or `JSONB` type unnamed parameter. The solution is to rename the function or add/modify the names of the arguments. |
  	| PGRST204 | 400 | Caused when the column specified in the columns query parameter is not found. |
  	| PGRST205 | 404 | Caused when the table specified in the URI is not found. |

• Why "No migrations" shows when the CLI table is missing:

  supabase/apps/docs/content/troubleshooting/error-relation-supabase_migrationsschema_migrations-does-not-exist-a787d6.mdx

  Lines 8 to 11 in 7425d49

  	If you are observing the error `relation "supabase_migrations.schema_migrations" does not exist` in your Postgres logs, it is typically caused by the Supabase dashboard polling for migration history.
  	**Why Does This Happen?**
  	The Supabase dashboard performs background queries to retrieve migration data from a table that is managed specifically by the Supabase CLI. If you are using a custom migration tool, a manual setup, or have not yet deployed migrations via the CLI, this table will not exist, causing the logs to report this error.