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!

CodePudding user response：

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([
    { foo: 3, created_at: d }
  ])
console.log(data)
if (error) {
    console.log("error getting results");
    throw error;
}