Expose non-public schema to supabase-js client

[giannico]

Is it possible to expose a custom database schema directly to the @supabase/supabase-js client? Can I extend the list of exposed schemas beyond public and storage?

I see the client constructor takes a schema option that defines the option as:

options.schema - You can switch in between schemas. The schema needs to be on the list of exposed schemas inside Supabase.

I believe I have my schema grants in order, but, when I try to make a request with the client I'm failing content negotiation with a 406 and I get the response:

{"message":"The schema must be one of the following: public, storage"}

[steve-chavez]

Not right now, but it will possible soon(ish). The supabase UI will add a db-schemas setting on /settings/api, there you'll be able to add more schemas besides public and storage.

[rbrueckner]

@MildTomato: any ETA on this topic? On Sep 15, 2021 you mentioned that you are working on this, but I still don't see this working.

[TomasHubelbauer]

FWIW I get The schema must be one of the following: public, storage, graphql_public even when my Supabase client is authorized (so it is not because it would be using only the public anon key - it has correct auth). So while the JS SDK now has the option to configure the DB schema, it looks like it is locked to those three fixed values only?

[GaryAustin1]

@TomasHubelbauer Did you set the schema in the API settings?

[TomasHubelbauer]

Thanks for the tip! Does this mean a Postgres function in this schema will be callable with the public anon key or is it possible to enforce a function must be called via the rpc method only when authorized? (I apologize for being possible off-topic from the original thread with this.)

[GaryAustin1]

If you have follow up please start a new discussion or try Supabase Discord. Not fair to other users on this thread to get notifications if they have it on.
It does IF you granted that role access to the schema. You can control all of the roles that can access with Postgres grants. By default it should only allow access from Postgres role so none of the API roles can use it. You have to add them. https://supabase.com/docs/guides/api/using-custom-schemas

[glomyst]

Its a very urgently needed feature. This stops us from using Supabase-Js even with a self hosted Supabase instance.
Unnecessary limitation from JS side.

[ARMATAV]

Exposes, but does not add it to the api documentation list as it should.

[johndpope]

note - you should be able to bypass it using vanilla postgres library

import pg from 'pg';

// node-pg returns numerics as strings by default. since we don't expect to
// have large currency values, we'll parse them as floats instead.
pg.types.setTypeParser(1700, (val) => parseFloat(val));

export const db = new pg.Pool({
database: 'postgres',
// host: 'db.1234.supabase.co',
host: '0.0.0.0',
// port: 6543,
port: 5432,
user: 'postgres',
// password: 'm[`(B56\V~SQ$/<)',
password: 'JzSL#g89@f7wzf!',
max: 10,
min: 2,
idleTimeoutMillis: 1000, // close idle clients after 1 second
connectionTimeoutMillis: 1000, // return an error after 1 second if connection could not be established
});

[ItayShmuel]

You can expose the custom schema using the config.toml file API configuration section.

[Z0rdak]

I encountered this issue as well today with the self-hosted version (using docker)

I added my schema to the .env file:
PGRST_DB_SCHEMAS=public,storage,graphql_public,my_custom_schema

Then docker compose down followed by docker compose up -d and it worked.