Persistent PGRST202 Error - RPC Calls Ignoring search_path / Custom Schemas

[hugoheadquarter]

Hi,

I'm encountering a persistent PGRST202 error when trying to call PostgreSQL functions located in a custom schema (user) via RPC using the supabase-js client (also tested with supabase-py). Despite extensive troubleshooting, the API layer (PostgREST) consistently seems to search only the public schema, ignoring the configured search_path and exposed schema settings.

Goal:
Call a function user.add_song_like(p_user_id uuid, p_song_id uuid) from the client as an authenticated user.

Problem:
When calling supabase.rpc('add_song_like', { p_user_id: userId, p_song_id: songId }), I consistently receive the following error:

{
  "code": "PGRST202",
  "details": "Searched for the function public.add_song_like with parameters p_song_id, p_user_id or with a single unnamed json/jsonb parameter, but no matches were found in the schema cache.",
  "hint": null, // Sometimes hints at unrelated public functions
  "message": "Could not find the function public.add_song_like(p_song_id, p_user_id) in the schema cache"
}

Database Setup:

1. Schema: Custom schema named user exists.
2. Function: The function add_song_like(p_user_id uuid, p_song_id uuid) exists within the user schema. (It was previously named add_liked_song, renaming didn't help). Definition:

   -- Function definition (simplified for example)
   CREATE OR REPLACE FUNCTION "user".add_song_like(p_user_id uuid, p_song_id uuid)
   RETURNS text -- Example return type
   LANGUAGE plpgsql
   SECURITY DEFINER -- Or INVOKER, tested both ways
   AS $$
   BEGIN
     -- Simplified logic for testing - actual logic involves INSERT
     -- INSERT INTO "user".liked_songs (user_id, song_id) VALUES (p_user_id, p_song_id);
     RETURN 'SUCCESS'; -- Example return
   -- EXCEPTION -- (Error handling exists in actual function)
   END;
   $$;

3. Table: Associated table user.liked_songs exists with appropriate columns (user_id uuid, song_id uuid, etc.) and RLS policies (SELECT, INSERT, DELETE using auth.uid() = user_id).

Configuration & Troubleshooting Steps Taken:

1. Exposed Schemas: Confirmed via Project Settings -> API -> Exposed schemas that "user" (and test_schema during later tests) is listed alongside public, extensions.
2. Role search_path: Confirmed via SQL ALTER ROLE authenticated SET search_path = '"user"', public, extensions; (also tried variations with test_schema first). Verified using pg_catalog.pg_roles.
3. SQL Permissions: Confirmed via GRANT USAGE ON SCHEMA "user" TO authenticated; and GRANT EXECUTE ON FUNCTION "user".add_song_like(uuid, uuid) TO authenticated;. Also granted to anon during testing with no change.
4. PostgREST Cache/Restart:
   • Tried NOTIFY pgrst, 'reload schema'; - No effect.
   • Repeatedly used select pg_terminate_backend(pid) from pg_stat_activity where application_name = 'postgrest'; followed by waiting, including after schema/function/role changes - No effect on the error.
   • Tried renaming the function (add_liked_song -> add_song_like) - Error persisted, just mentioning the new name but still searching public.
5. Client Call Variations (via Node.js & Python OTP test scripts):
   • Implicit call supabase.rpc('add_song_like', ...) -> Fails, searches public.add_song_like.
   • Explicit Quoted call supabase.rpc('"user".add_song_like', ...) -> Fails, searches public."user".add_song_like (incorrectly prepends public.).
   • Single JSON param call -> Fails, searches public.add_song_like.
6. Parameter Validation: Confirmed via console.log and Python script logs that valid uuid strings are being passed for p_user_id and p_song_id.
7. New Schema/Function Test:
   • Created a new schema test_schema.
   • Added it to Exposed Schemas and search_path (first).
   • Created a simple function test_schema.echo_hello(message text).
   • Granted USAGE and EXECUTE.
   • Restarted PostgREST backends.
   • Calling supabase.rpc('echo_hello', ...) still fails with PGRST202, indicating it searched public.echo_hello.

Question:

Given that all standard configurations (search_path, Exposed Schemas, Grants) seem correct and even forceful cache clearing (pg_terminate_backend) doesn't resolve the issue, why would PostgREST consistently ignore the search_path and exposed schemas for RPC calls and default to searching only public? Is there any other project-level setting, potential bug, or deeper cache mechanism that could cause this behavior?

Any insights or further diagnostic steps would be greatly appreciated!

[GaryAustin1]

There is a dashboard setting for extra search path

Also you are setting your search path on authenticated... you may need to set it on authenticator which is the role postgREST starts with.

You may also need to restart PostgREST (not just reset schema cache) with NOTIFY pgrst, 'reload config' but not sure for search path settings on a role.

Also I would not use user for naming things in Postgres as it is a reserved keyword, but see you did check another schema name.