Privileges revoked from all storage tables after pull of storage schema
I'm trying to sync all my RLS policies of my buckets by running
supabase db pull --schema storage, but for some reason the generated migration file creates statements that revokes all privileges from all database users from the storage schema. Is this normal? How can I fix it?
Edit: created an issue on GitHub https://github.com/supabase/cli/issues/4107.
Update: https://discord.com/channels/839993398554656828/1412156678459359243/1413922191896215592GitHub
supabase/cli
Supabase CLI. Manage postgres migrations, run Supabase locally, deploy edge functions. Postgres backups. Generating types from your database schema. - supabase/cli
50 Replies
Seems lime something removed those from storage.buckets. Seems like something was trying to stop anon from doing any bucket operation and authenticated to not create or delete them.
The thing is that without privileges I can't even create buckets on my local project. I couldn't even restart the db since it through an Error because of not having privileges:
What I did was:
- supabase db pull --schema storage
- supabase db restart
Gave me that error. Then I checked the migration file and saw the revoke statements. Is this even normal?
There are in total 162 revoke statements.
No, not normal. Seems like you removed the grants for some reason on your storage schema.
But how is that possible if the hosted project works with no problems? I always made changes to the hosted project using all features available through the dashboard and never push a change from my local project since I'm trying to set it up for the first time.
Could this be a bug?
I don't know that the set you showed would hurt running. The dashboard could still create buckets. Authenticated would still be able to do operations.
But that is based on the list you showed with grants on buckets.
I don't know of a bug like this. Seems like you modified your storage schema at some point. Do you use an AI?
I don't
Here is a full list of the revoke statements
Also in order to migrate storage or auth schemas the postgres version needs to be close to the same on the CLI and the hosted instance. If the schema changes then it could cause issues. But grants are not removed in any version I know of.
That list would certainly keep storage from working. Those revokes include storage.objects which you have to have access to.
Those would be fatal to it working...
If your current instance is working then this migration is really messed up.
You'll have to see if someone else who uses this more comes along,
or generate a CLI issue in supabase/cli github,
or contact Support.
I just removed all revoke statements and run db reset again. Now it seems to be working. Before I couldn't even create buckets using supabase studio, now I can. Even though this fixes it I'm still worried because later I want to create an automatic workflow for migrations, but if this kind of statements are generated future workflows could potentially break a production app.
The only schema that it's giving me problems it's storage. I firast pulled public schema, once I saw it was working pull auth, and now I'm with storage. I'm trying to learn as much as I can before building an automated workflow.
Yes, those were not good IF your main instance is working with RLS policies on storage.
You do need to make sure you are on same PG versions or as close as possible, but I don't know of a mismatch that would generate all those revokes.
I installed supabase-cli last week with all it's dependencies for the first time. I really doubt there is a pg version mismatch.
Not major PG version, but the specific Supabase PG version which contains the storage and auth schema changes.
I think in general the idea is to use migrations for RLS on storage (written in SQL) and NOT copy schemas for auth and storage.
There are times the CLI has an older or new version of auth/storage schema than an instance.
Still has nothing to do with the revokes you were seeing.
For instance these are changes to the storage schema over the past few months....
But If you do not pull either auth or storage how are you suppose to set trigger functions or RLS for storage buckets if trying to set a local instance for the first time? Of course you could write those manually but I don't think it's a good idea.
I'll keep testing and see if this happens again. Probably I'll try to setup another local instance of my hosted project to see if this happens again. Either way I will probably generate an issue on github.
Most users I see write the RLS for storage SQL locally and then migrate to their stating then production. They use migration files
If starting a project locally, yes.
But I would ask a new question. I don't use the CLI much and I don't use local dev.
But if started remote and then want to migrate to local, you have to pull both auth and storage.
Also because then you could migrate from local to a new Prod project
Yes tell me
Then you need to be on the same version of Supabase Postgres for your hosted platform and CLI. Then pulling should work. As I said I don't know why you seeing the revokes.
From the list of changes image I showed if your hosted instance was more than 2 months old it would not have the iceburg features the latest CLI version probably has.
My hosted instance it's around 3 years old
You have not upgraded it?
But I assume the supabase pg version of my hosted instance it's being upgraded isn't it?
You have to upgrade it.
By hosted I mean hosted by supabase
Yes. Check your infrastructure tab.
I swaer I never heard of manually upgrading postgres on hosted supabase
I'm on version 15 💀
and cli it's probably using 17
17 is only about 3 to 4 months for Supabase. But 15 covers about 2 or 3 years.
So that could be the cause
There are also minor upgrades all the time which include new auth/storage schema updates, extensions, etc.
So 15 probably had close to 100 releases in its life.
I'll upgrade and create a new local instance.
Are you in production?
Then I'll see if that was generating the revokes
I'm not
But I'm preparing to be
That's why all the laerning
You many have some clean up it will prompt you to resolve going from 15 to 17.
I'll try, it shouldn't be hard
Will upgrade and after trying yo set up the new local instance I will update here so if anyone encounters the same issue can solve it
Thanks for the help @garyaustin
Learned a lot
Well, I checked my local instance pg version and it matches the hosted one
I went to the sql editor and run the select version query and both matched
So the revokes have nothing to do with pg versions mismatch. I will generate an issue on github.
If not too much work, you might create a new instance with no changes and run the same pull, but I would expect to have seen this before as people certainly do pull storage.
I will try with a new instance and see what happens.
Update: I created another local instance and now the revoke statements where created also for the auth schema
The process was quiet simple and even like that the revoke statements where generated:
The generated revoke statements are the following:
Must be a change to how they do migrations. Not really seen this discussed here before that I recall.
I found a similar issue on github from not to long ago. Some revokes where generated but not as much as this.
https://github.com/supabase/cli/issues/4068
GitHub
Missing grants in generated migration generated with version 2.33.0...
Describe the bug When generating database migrations, Supabase CLI 2.33.0 output's is very different from previous version: does not include GRANT on the newly created table revoke existing pri...
You might try the CLI repository or a new post and see if another mod or user has an idea.
Yeah I'll create another post and another issue
Thanks for the help
@patito1009 I'm somewhat facing the same issue (see https://discord.com/channels/839993398554656828/1415801085775446138), did you create another issue?
I just saw your thread, I sent my opened issue there if you wanna check!
Keep me updated if you can find any answers or hints about what could be the problem and how we can fix it
Has anyone seen improvements here? Trying to fix some diffs between prod and code and I keep getting a total revoke of all actions (just like above)
(on 2.50.3)
Still seems to be a problem... https://discord.com/channels/839993398554656828/1415801085775446138/1426328701649289268
After a beta release it started working for me but it seems many people are still experiencing this issue
There's an open issue in github and supabase team is actively working on it
The only thing we can do is to be patient