How to access custom schema from supabase-js client?

Question

I love Supabase but our team needs to use schema functionality that PostgreSQL offers - unfortunately we have been unsuccessfully to make schemas working so far.

Other info:

• PostgreSQL 14
• newest version of the Supabase JS SDK's
• we have supabase hosted with Supabase's pro plan

What we have tried:

1. We created a new schema and added access to all types of authentication (why all? We first thought that maybe there is an error with authentication):

   CREATE SCHEMA Brano;
   GRANT USAGE ON SCHEMA Brano TO postgres, anon, authenticated, service_role, dashboard_user;
   

2. Exposed schema to the API via this setting:

   

3. Tried the following code:

   var options = {
       schema: 'brano'
   }
   
   const supabaseUrl = 'supabaseUrl'
   const supabaseKey = 'supabaseKey'
   const supabaseClient = createClient(supabaseUrl, supabaseKey, options);
   
   const { data, error } = await supabaseClient
       .from('test_brano')
       .insert([{
           data: 123
   }]);
   

4. Got this error:

   {
       "message":"permission denied for table test_brano",
       "code":"42501",
       "details":null,
       "hint":null
   }
   

Links and documentation that we have tried reading (unfortunately we didn't make it work either way):

• https://github.com/supabase/supabase/discussions/7642
• https://github.com/supabase/postgrest-js/issues/280
• https://supabase.com/docs/reference/javascript/initializing#with-additional-parameters
• https://github.com/supabase/supabase/discussions/1222

Did we missed something? Thanks in advance!

Answer 1

In addition to the first two steps you did:

1. Granting usage:

   CREATE SCHEMA Brano;
   
   GRANT USAGE 
   ON SCHEMA Brano 
   TO postgres, anon, authenticated, service_role, dashboard_user;
   
   ALTER DEFAULT PRIVILEGES IN SCHEMA brano
   GRANT ALL ON TABLES TO postgres, anon, authenticated, service_role, dashboard_user;
   

2. Exposing the schema in the Settings:

   

There's a third step that was missing:

3. Granting actions to be able to insert/select data:

   GRANT SELECT, INSERT, UPDATE, DELETE 
   ON ALL TABLES IN SCHEMA brano 
   TO postgres, authenticated, service_role, dashboard_user, anon;
   
   GRANT USAGE, SELECT 
   ON ALL SEQUENCES IN SCHEMA brano 
   TO postgres, authenticated, service_role, dashboard_user, anon;
   

⚠️ Warning ⚠️

You must set these grants again for every new table created in the custom schema.

Then you can call it as in your example:

Supabase JS v2:

    const options = {
      db: { schema: 'brano' }
    };
    const supabase = createClient(supabaseUrl, SUPABASE_KEY, options)
    const d = new Date(2018, 11, 24, 10, 33, 30, 0);
    const { data, error } = await supabase
      .from('test_brano')
      .insert([
        { data: 3, created_at: d }
      ])
    console.log(data)
    if (error) {
        console.log("error getting results");
        throw error;
    }

Supabase JS v1:

    const options = {
      schema: 'brano'
    }
    const supabase = createClient(supabaseUrl, SUPABASE_KEY, options)
    const d = new Date(2018, 11, 24, 10, 33, 30, 0);
    const { data, error } = await supabase
      .from('test_brano')
      .insert([
        { data: 3, created_at: d }
      ])
    console.log(data)
    if (error) {
        console.log("error getting results");
        throw error;
    }

Answer 2

To access schema using the newest Supabase JS SDK'S you need to use different code according to the migration documentation:

New method:

const supabase = createClient(SUPABASE_URL, SUPABASE_ANON_KEY, {
  db: {
    schema: 'custom',
  },
})

The main change is that: All client specific options within the constructor are keyed to the library: PR

And of course you need to run these queries:

CREATE SCHEMA IF NOT EXISTS  custom;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA custom TO ROLES_YOU_WANT_TO_GIVE_ACCESS;
GRANT USAGE ON SCHEMA custom TO ROLES_YOU_WANT_TO_GIVE_ACCESS;

Answer 3

Also, check out this very important github thread: https://github.com/supabase/supabase/discussions/10428

There is instructions to local development.

And don't forget to add -s schema_name when you diffing your local db changes