S
Supabase•2y ago
maja

Permission denied on schema <schema_name> from supabase UI

Hi everyone, I'm trying to view my data via the supabase UI and running to this issue (see attached SS). I'm pretty sure the problem is that this schema was created / is owned by a different user (one I created for an external integration) so postgres, supabase_admin, or whatever user is executing the query to display to the UI does not have access to it by default. (SELECT current_user; -- returns postgres) I guess my question is twofold - 1. how should I resolve this in the short-term (is there a better way other than to make sure I grant all on schema ... to ... after creating each schema 2. is there a set of best-practices for application-specific user management
No description
1 Reply
garyaustin
garyaustin•2y ago
Not sure how you created the schema but postgres user has to have been granted access for the dashboard UI or SQL editor to work. If you want access from the API then there are other roles. 
grant usage on schema public to postgres, anon, authenticated, service_role;

grant all privileges on all tables in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all functions in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all sequences in schema public to postgres, anon, authenticated, service_role;

alter default privileges in schema public grant all on tables to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on functions to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on sequences to postgres, anon, authenticated, service_role;
grant usage on schema public to postgres, anon, authenticated, service_role;

grant all privileges on all tables in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all functions in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all sequences in schema public to postgres, anon, authenticated, service_role;

alter default privileges in schema public grant all on tables to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on functions to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on sequences to postgres, anon, authenticated, service_role;
This is the code Supabase shows to restore the public schema and would be needed to do all things Supabase like on the public schema.

Did you find this page helpful?