How to access custom schema from supabase-js client?

user_11101156 0
Viewed 180

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;

  1. Exposed schema to the API via this setting: enter image description here

  2. 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
}]);
  1. 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):

Did we missed something? Thanks in advance!

supabase postgresql sql
1 Answers

You've granted usage to the schema, but you also need to grant actions to be able to insert/select data:

e.g

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

Then you can call as in your example:

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;
}

Note: I didn't include the anon role in the grant command above. Depending on your use case, you may want to include this role there.

user_21881860
Related