Multiple Schemas in Supabase

I really like Supabase. It’s open source, using PostgreSQL under the hood, provides great utilities like authentication and file blobs, and an autogenerated REST API using PostgREST, that makes it accessible through Edge Functions easily, & they have a very generous free tier. Moreover, as a company level, it seems like they are an innovating the startup release cycles, and lots of companies started to copy from them. “In the year 2022, all landing pages look like Vercel’s, all product releases look like Supabase’s.”

Supabase is running on PostgreSQL and one way to have isolation/multiple tenants is to have multiple database schemas. Fortunately, this is supported out of the box in Supabase—but you’re gonna need to provide extra permissions to allow PostgREST to query it, and it took me a while to find the exact permissions.

So for future reference, here’s a simple SQL migration to add a my_new_schema schema to your Supabase database.

create schema if not exists my_new_schema;

alter default privileges for user supabase_admin
  in schema my_new_schema grant all
  on sequences to postgres, anon, authenticated, service_role;

alter default privileges for user supabase_admin
  in schema my_new_schema grant all
  on tables to postgres, anon, authenticated, service_role;

alter default privileges for user supabase_admin
  in schema my_new_schema grant all
  on functions to postgres, anon, authenticated, service_role;

alter default privileges for user postgres
  in schema my_new_schema grant all
  on sequences to postgres, anon, authenticated, service_role;

alter default privileges for user postgres
  in schema my_new_schema grant all
  on tables to postgres, anon, authenticated, service_role;

alter default privileges for user postgres
  in schema my_new_schema grant all
  on functions to postgres, anon, authenticated, service_role;

grant all privileges on schema my_new_schema to postgres;
grant usage on schema my_new_schema
  to postgres, anon, authenticated, service_role;

• Prisma accepts a query string in the connection string. Use ?schema=my_new_schema to connect to a specific schema
• If you want to test PostgREST using plain HTTP calls, the header to set a schema for the request is Accept-Profile: my_new_schema 😃