CLI Permission Error: Cannot Create Tables in Storage Schema During Local Reset
I'm hitting a weird permission issue with the Supabase CLI that seems like it shouldn't be happening.
What I'm trying to do:
The error:
Context:
- Local Supabase instance (Docker).
- This happens when the CLI tries to run my migration file that seems to create a table in the
storage schema.
- I initially got this error when running db pull --local --schema public,auth,storage.
- Even a fresh db reset --local is failing on the same permission error.
- The migration file being ran was created after I updated my RLS policies to use (select auth.uid()) instead of auth.uid(). Apparently, this improves performance. The db pull ... command worked fine here.
- After that, I made all my storage buckets public, and then removed all the RLS policies on them.
- Then tried running db pull --local ... which didn't work. I thought I messed up my DB somehow and decided to reset it. That, too, didn't work.
I thought the Supabase CLI should have full superuser permissions on the local database?
Any help would be appreciated! 🙏8 Replies
You cannot create tables in any schema not created by yourself. This is a change that Supabase has added around a month ago.
auth, storage, realtime, etc... schemas are not created by you but by the relevant Supabase services, you should not add to them.
https://github.com/orgs/supabase/discussions/34270
You only need to do db pull --local, which will only pull the public schema. The auth and storage` schemas are already part of the default setup of the local Supabase instance, you don't need to pull those down.
I thought the Supabase CLI should have full superuser permissions on the local database?It does not, it has to follow the same rules as the hosted platform otherwise you get inconsistent behavior.
The auth and storage schemas are already part of the default setup of the local Supabase instance, you don't need to pull those down.What if I modify policies in the
storage schema? Should I still not pull?
Mind you, the statement that creates a table in the storage schema was added when I did a db pull, I didn't write it myself.Please, help me understand, @silentworks .
This file was generated when I ran
db pull after modifying my RLS policies. You can see there are several create indexes/tables for the storage schema.
Are you saying, adding storage to the --schema flag is what caused this? And if I'm not supposed to pull the storage schema, how do I pull changes to policies and switching buckets from private to public, for example?If you look at the particular query you are having issue with, you will notice its trying to create a table.
db pull doesn't pull the auth or storage schema by default, these are only pulled when you explicitly set the --schema flag.
I'm not going to go through this entire file, but if there are any operations that do CREATE, ALTER, DELETE on any of the reserved schemas from the post I linked to, it will fail.db pull doesn't pull the auth or storage schema by default, these are only pulled when you explicitly set the --schema flag.Yes, I'm aware of this. I understand this too. I want to know if despite this, pulling RLS modification will work.
Creating RLS policies on these tables are not affected by this change. Also you should check the post for more info on what to do.
I'm not a Supabase staff, just a user here helping another user.
understood. thanks for your help
Basically Supabase made a breaking change with no clear upgrade path, but they had to do it due to security reasons. I just wished it wasn't done without a clear upgrade path.