Support wildcards in postgrest exposed schemas

[relferreira]

Feature request

I'm using Supabase in a multi-tenant way, meaning that every client will have a separate schema. As discussed here:
#1222
Supabase only exposes those other schemas through the API only when they are manually added to this list:

Is your feature request related to a problem? Please describe.

Trying to access another schema besides public and storage using supabase.js or postgrest.js gives the following error:

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

Describe the solution you'd like

It would be nice to have access to other schemas without needing to include them on the Dashboard manually. Maybe accept a pattern like tenant_*

Describe alternatives you've considered

I tried using postgrest-js but got the same error.

Many thanks for considering my request.

[kiwicopple]

Can I check that you added the schema to "Exposed schemas" @relferreira ?

From you screenshot it looks like you added it to the Search Path ("dnd-test" - I'd also recommend using underscores rather than hyphens)

[relferreira]

Hello @kiwicopple, I've added it, but what I want is a way to change the "Exposed schemas" dynamically

After some digging, I found that it might be a limitation of PostgREST

PostgREST/postgrest#1521

But I ended with the following solution:

Create schema:

CREATE SCHEMA IF NOT EXISTS ??

Grant permissions to supabase users:

GRANT USAGE ON SCHEMA ?? TO postgres, authenticated, service_role, dashboard_user

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA ?? to postgres, authenticated, service_role, dashboard_user

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

Next I altered the db_schemas of postgREST and reloaded it's config and schema:

ALTER ROLE authenticator SET pgrst.db_schemas = ??

NOTIFY pgrst, 'reload config'

NOTIFY pgrst, 'reload schema'

But I don't know if this is the best approach. Nevertheless, It would be nice if the field "Exposed Schemas" accepted a schema pattern like tenant-* or if existed an admin API to make this operation.

If it's not an important feature for the platform, you can close this issue.

Thanks a lot for your attention.

[maurizioriano]

It would be great if it accepted the pattern tenant_*

[relferreira]

@kiwicopple it seems that my strategy only worked because the postgres user on my stagging project had superuser permissions.

So I tried to alter the role just to create the following function:

create function change_db_schema_and_full_reload(schemas text) returns void as $_$ 
 begin 
   execute format($$ 
     alter role postgrest_test_authenticator set pgrst.db_schemas = %L; 
   $$, schemas); 
   perform pg_notify('pgrst', 'reload config'); 
   perform pg_notify('pgrst', 'reload schema'); 
 end $_$ volatile security definer language plpgsql ; 

But this command does not work on the dashboard

ALTER ROLE postgres SUPERUSER

Is there any way to login using a superuser just to create this function?

Thanks in advance

[relferreira]

It seems to be related to this discussion:

#9314 (comment)

[relferreira]

Sorry to bother @kiwicopple, but is there any news about this? I've commented on the following discussion #9314 (comment), but I haven't heard anything from the team.

I really don't want to abandon Supabase because of this.

Thanks

[kiwicopple]

@steve-chavez - are there plans to add wildcards to PostgREST? Otherwise, I think we will need to expose the PostgREST configuration to the API instead, so that it can be added dynamically

[steve-chavez]

Sorry, I missed this one.

So I tried to alter the role just to create the following function:

alter role authenticator will be possible again when supabase/supautils#40 is merged.

[pidanou]

Bump, I am also building a multi tenant app, and it would be great to be able to dynamically add exposed schema using the management API, CLI, in database config or whatever technique that won't need a manual action.

Thanks!

[kavitang]

+1 for this feature, we are also manually updating this in the dashboard for now, but it would be great if this was a dynamic feature. Thanks!

[ykoitzsch]

+1

[ghost]

+1

[zakandrewking]

I was able to edit the Exposed Schemas, and other settings, like so:

ALTER ROLE authenticator SET pgrst.openapi_mode = 'follow-privileges';
ALTER ROLE authenticator SET pgrst.db_schemas = 'public,storage,graphql_public,data_example';
NOTIFY pgrst, 'reload config';
NOTIFY pgrst, 'reload schema'; -- needed if you updated the schema during testing

The updated Exposed Schemas setting is not reflected in the supabase console, so I would love to have confirmation from the Supabase team that this is supported. But, in the meantime, it seems to work (in dev too)!

[pidanou]

So you run ALTER ROLE authenticator SET pgrst.db_schemas = 'public,storage,graphql_public,data_example'; everytime you get create a new schema?

[odwrotnie]

+1