Access custom schema through API

[wllnu]

Hi,

I'm trying to build a private table (the table should not be accessible through supabase UI).
To achieve this, I created a new schema and added the table to that schema. Like so:

create schema private;
grant usage on schema private to anon, authenticated;
create table private.todos_private 
...

I also added the private schema to "Extra search path" in settings/api

My problem:
When I try to add a todo, I get the error
relation "public.todos_private" does not exist

Why is the app trying to access the public schema, and how can I access the custom private schema through the API?

(P.S. I'm using the supabase react-todo-list example)

[dshukertjr]

I don't have an answer, but I am getting close 😅

In TypeScript, createClient function has options argument where you can set the schema to use like this:

  const supabase = createClient('supabaseUrl', 'annonKey', { schema: 'private' })
  const { data, error } = await supabase.from('posts').select()

I thought this was the answer, but when I ran this, I got an error saying The schema must be one of the following: public, storage. I have added private schema as my search path as well.

[r100-stack]

Also got the same problem. I added the schema property in the constructor. I also added the new schema name to the extra search path on the Supabase website under Settings > API > Extra Search Path. But I still get the same error as @dshukertjr

Here's the dart code I'm using, using the example provided in the postgrest package readme:

const supabaseUrl = '...';
const supabaseKey = '...';
final client = PostgrestClient(
  '$supabaseUrl/rest/v1',
  headers: {'apikey': supabaseKey},
  schema: 'testing', // Note that the schema = 'testing', not the default 'public'
);

try {
  final response = await client
      .from('table_name_xyz') // This is one of the tables in the schema 'testing'
      .select()
      .execute(count: CountOption.exact);
  if (response.error != null) {
    throw response.error!;
  }
  print(response.data);
  return response.data;
} on PostgrestError catch (e) {
  // handle PostgrestError
  print(e.code);
  print(e.message);
}

Exact error I'm receiving:
The schema must be one of the following: public, storage

[inian]

Hi, everyone this is not supported yet. we need to expose the db-schema parameter in the dashboard to allow configuring the schemas postgrest should connect to https://postgrest.org/en/v7.0.0/configuration.html

Created an issue internally to track this

Edit - you can expose schemas in the API by editing the "Exposed schemas" option here

[GaryAustin1]

This has been working for awhile. Click on the existing schemas in the API settings to get a list of others to add. Then createClient has the schema option discussed.
https://supabase.com/docs/reference/javascript/initializing#api-schemas

[mwarnerdotme]

Thanks @GaryAustin1!

[isaacharrisholt]

Any news on when the relevant setting will be available for local development? Bit annoying that a lot of settings aren't present

[imrane]

@isaacharrisholt did you find any resolution? Having the same issue...

[dshukertjr]

@imrane @isaacharrisholt I believe api.schemas is the environment variable to set what schemas you want to expose for local development.
https://supabase.com/docs/guides/cli/config#api.schemas

[Akash187]

Overall, I don't understand why a private schema would be exposed. I have a private schema myself, but I only use it to create functions that are called by other public functions. My reason for having a private schema is to keep it hidden from the public. Exposing it defeats the purpose of having a private schema in the first place.

[inian]

Private schemas are not exposed by default and you need opt in to expose it over the API

[Akash187]

Yeah that's why it is called Private.

[kimhwanhoon]

I'm not sure if it's the answer you're looking for, but you can go to supabase dashboard settings - API - Exposed schemas and select the custom schema you have made and after that you will be able to fetch data from there!

[tresorama]

In local development this config has to be made in config.toml not in the UI.

# in ./config.toml

project_id = "test-supabase"

[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` is always included.
schemas = ["public", "graphql_public", "MY_CUSTOM_SCHEMA"] # 🤩
# Extra schemas to add to the search_path of every request. `public` is always included.
extra_search_path = ["public", "extensions"]
# The maximum number of rows returns from a view, table, or stored procedure. Limits payload size
# for accidental or malicious requests.
max_rows = 1000