Supabase•3d ago

"The schema must be one of the following: api"

I am moving from public schema to api schema. So I made the schema api also exposed api in config.toml and set it as extra search path Made this function:

CREATE OR REPLACE FUNCTION api.get_user_profile_with_subjects()
RETURNS TABLE(
   ...
)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
    ...
$$;

GRANT EXECUTE ON FUNCTION api.get_user_profile_with_subjects() TO authenticated;

CREATE OR REPLACE FUNCTION api.get_user_profile_with_subjects()
RETURNS TABLE(
   ...
)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
    ...
$$;

GRANT EXECUTE ON FUNCTION api.get_user_profile_with_subjects() TO authenticated;

But for some reason it doesnt work anymore, worked perfectly when it was in public.

26 Replies

IdrisOP•3d ago

[api]
enabled = true
# Port to use for the API URL.
port = 54321
# Schemas to expose in your API. Tables, views and stored procedures in this schema will get API
# endpoints. `public` and `graphql_public` schemas are included by default.
schemas = ["api"]
# Extra schemas to add to the search_path of every request.
extra_search_path = ["api"]
# The maximum number of rows returns from a view, table, or stored procedure. Limits payload size
# for accidental or malicious requests.
max_rows = 40

[api]
enabled = true
# Port to use for the API URL.
port = 54321
# Schemas to expose in your API. Tables, views and stored procedures in this schema will get API
# endpoints. `public` and `graphql_public` schemas are included by default.
schemas = ["api"]
# Extra schemas to add to the search_path of every request.
extra_search_path = ["api"]
# The maximum number of rows returns from a view, table, or stored procedure. Limits payload size
# for accidental or malicious requests.
max_rows = 40

garyaustin•3d ago

How are you calling it? Did you grant access to the schema? https://supabase.com/docs/guides/api/using-custom-schemas

IdrisOP•3d ago

const { data, error } = await supabase.rpc('get_user_profile_with_subjects').single(); Yes I did, I have the feeling that it still queries public.get...... But I thought modifying the search path in config.toml would change that

garyaustin•3d ago

You need to add .schema('api') to the call.

IdrisOP•3d ago

I see, do you know if there is a way to set api as default?

garyaustin•3d ago

When you createClient you can set the default schema for that client.

IdrisOP•3d ago

.schema doesn't exist btw Will try that thanks

garyaustin•3d ago

It does.

IdrisOP•3d ago

Ah weird my ide didn't recognize it Thanks!

garyaustin•3d ago

The search_path did not work because the Client uses public.xxx for all calls unless you override it. It does not just call the function name.

IdrisOP•3d ago

Ahh makes sense thanks a lot Hmm really weird, after setting createClient to schema api i get Property 'auth' does not exist on type 'PostgrestClient<any, "api", any>'.deno-ts(2339) for const { data: newUser, error: createError } = await supabase.auth.admin.createUser({ worked around it by not setting on schema and insttead on queries that need it. Is this a bug that should be reported or am i missing something?

garyaustin•3d ago

Not sure. One normally does not do auth calls with service_role on a shared client (doing other calls than auth admin) so maybe a something missed. That was the only way to do schemas until about a year ago so surprised it would not have been hit though.

IdrisOP•3d ago

Well same for

supabase.auth.onAuthStateChange((_event, session) => {
    user.data = session?.user ?? null;
}

supabase.auth.onAuthStateChange((_event, session) => {
    user.data = session?.user ?? null;
}

this would surely be discovered?

garyaustin•3d ago

Or is that "just" a typescript error....

IdrisOP•3d ago

Unfortunatly not, fails in runtime Cannot read properties of undefined (reading 'onAuthStateChange')

garyaustin•3d ago

I see deno in your error above. Is this an edge function?

IdrisOP•3d ago

Yes responsible for creating users on a admins request. The last snippet is from the front end Same for Property 'getChannels' does not exist on type 'PostgrestClient<any, "api", any>'.ts(2339)

garyaustin•3d ago

I'm not getting any error but I don't use typescript. onAuthStateChange works fine with db: schema option.

IdrisOP•3d ago

I see, does it fail for you in runtime Ah okay. Really weird export const supabase = createClient(CF_SUPABASE_URL, CF_ANON_KEY).schema('api'); You did it like this right?

garyaustin•3d ago

IdrisOP•3d ago

Ah now I am going to feel really stupid haha, what did i do wrong

garyaustin•3d ago

garyaustin•3d ago

It is one of many createClient options.

IdrisOP•3d ago

import { createClient } from '@supabase/supabase-js' const supabase = createClient('https://xyzcompany.supabase.co', 'publishable-or-anon-key', { // Provide a custom schema. Defaults to "public". db: { schema: 'other_schema' } }) oh just found this haha (method) SupabaseClient<any, "public", any>.schema<"api">(schema: "api"): PostgrestClient<any, "api", any> Select a schema to query or perform an function (rpc) call. The schema needs to be on the list of exposed schemas inside Supabase. @param schema — The schema to query Should have double checked the docs but honestly this jsdoc is confusing af

garyaustin•3d ago

Well glad you showed the .schema added on. I would have never guessed that on createClient. That is used before .rpc and .from.

IdrisOP•3d ago

Yeah in that case it makes sense it's also there for createClient since you use that through a ref. My bad, thanks for thinking along!!

Gaming

Programming

"The schema must be one of the following: api"

Did you find this page helpful?