Supabase•2mo ago

Error on storage Search

Hi everyone, an error just happened in production 'randomly' because no changes have been done, but I now can't see the storage content in the app but also in the supabase dashboard:

Failed to retrieve folder contents from "documents": select * from storage.search($1,$2,$3,$4,$5,$6,$7,$8) - function storage.search_v1_optimised(text, text, integer, integer, integer, text, text, text) does not exist

Failed to retrieve folder contents from "documents": select * from storage.search($1,$2,$3,$4,$5,$6,$7,$8) - function storage.search_v1_optimised(text, text, integer, integer, integer, text, text, text) does not exist

It seems that a change have been done from storage to app_storage . I have tried to add back the search function, but anything I do tell me this: ERROR: permission denied for schema app_storage (SQLSTATE 42501) But I try to do it as the postgres role... Any idea on what to do or / and what is happening?

41 Replies

DamienOP•2mo ago

Since I don't have supabase_admin role how could I add or change functions ?

garyaustin•2mo ago

You should not be using any storage functions on your own and you should not change the one supabase has. IF you made changes a long time ago that are not not working because of a storage schema change, then only support can clean them up now.
Otherwise not sure what the issue is. What are you doing when you get the error?

garyaustin•2mo ago

Seems like it should have been added to your instance ages ago. Not sure how it would have been deleted, so assume it was not installed on your instance.
They may have just started using it is why the sudden impact. It is on all of my recent instances. I would contact support.

DamienOP•2mo ago

I'm getting this error when from the studio dashboard going in any of the bucket in the storage tab

garyaustin•2mo ago

Understand. For some reason your instance does not have that function added.

garyaustin•2mo ago

Check here:

DamienOP•2mo ago

We have the functions added in the app_storage but not on the storage schema

garyaustin•2mo ago

I don't know what app_storage is.

DamienOP•2mo ago

I didn't know too before having the error

DamienOP•2mo ago

(thanks for replying by the way 🙏)

garyaustin•2mo ago

That is not on any of my instances.

DamienOP•2mo ago

so weird, thanks anyway

garyaustin•2mo ago

I don't find the words "app_storage" in the storage github repository at all. I don't know how you or an AI if you use would could move those as I thought storage is protected. Support is still best bet. This is Supabase hosted right?

DamienOP•2mo ago

Yes I have contacted the support, but I wait the answer for now, and yes it is hosted one, on the migrations on the local development instance there is not the app_storage schema neither. only on the hosted one which is even more confusing I would like to hotfix by adding back the functions but since I don't have the rights it seems I can't..

garyaustin•2mo ago

That is correct. You can't modify the schema. Which is why I am puzzled how it occurred, unless it was many months ago when you could still change the schema and they just started using the function recently. I assume you are using .list() in your app for it to be impacted?

DamienOP•2mo ago

Yes I'm using .list() exactly, but anyway it breaks also the dashboard UI since I cannot go in any of the buckets

garyaustin•2mo ago

I understand that. You theoretically could replace .list with an rpc call to your own function that uses SQL to search and return info from storage.objects. But it might be painful to write and test if you are doing searches and folders to a multiple levels. No other reports of this and I don't find a hit on "app_storage" "supabase" on the web.

DamienOP•2mo ago

Yeah this would work as a hotfix you're right, I've search on reddit, github slack and yeah I didn't find anything neither. I found in the migrations when started local development (with a supabase db pull command) but nothing on the app_storage) . And it looks like randomly today, the storage.search_v1_optimised function have been removed from storage schema, and now I cannot create it again.

message.txt

garyaustin•2mo ago

Yeah, no idea how it happened.
But as they locked all their schemas months back there is no way to add or move functions in them. I don't know if you upgraded your remote instance to the latest postgres if it would fix the storage schema or not as it keeps track of the migrations it has applied.

DamienOP•2mo ago

I didn't yet and I was hesitating to do it, because I had the same idea, but it stops the database for around an hour so I wanted to do it by night, not ideal right now, but I should give it a try tonight anyway. (Thank you very much for all the replies again) It seems I might wait the support answer to see what they can do on their side..

garyaustin•2mo ago

They can put them back as they are supabase_admin role.

Aksmo•2mo ago

Hi, did you find a solution? I'm having the same problem in my database

garyaustin•2mo ago

What is the same problem? Do you have app_storage schema with the functions there?

Aksmo•2mo ago

When trying to access storage via supabase dashboard, i receive Failed to retrieve folder contents... storage.search_v1_optimised does not exist. And yes i have an app_storage schema with that function (storage.search_1_optimised), but storage schema doesn't have it

garyaustin•2mo ago

What AI are both of you using if any? Please file a support ticket and post it here. I'm going to bump this.

bobbie tables•2mo ago

Hey all, Bobbie from the Supabase team here. This is related to internal schemas restrictions which we've rolled out to existing projects recently. Please file a support ticket if you're affected and we'll follow up with you there 🙏 To clarify the confusions here: - It seems you have an older version of storage.search() or it's been tampered somehow. Once you file a support ticket we can investigate further. - app_storage and app_auth are created by us. The permission denied error is unintended and we'll be fixing these. You'll be able to modify/drop these schemas if you need to.

GitHub

Restricting Access on Auth, Storage, and Realtime Schemas on April ...

On April 21, we are restricting certain SQL actions you can perform in your database’s auth, storage, and realtime schemas. Why are we making these restrictions? Supabase Auth, Storage, and Realtim...

garyaustin•2mo ago

Some of the functions moved to the app_storage do seem to be ones that should have been in storage schema and are not there now. Like possibly the transfer including some Supabase functions. The function being called is in Supabase server source. Errors are from .list() and UI storage panel.

bobbie tables•2mo ago

Yep we had an allowlist of internal functions that won't get moved out of the internal schemas. But this is retrieved from the latest migrations - a project with up to date Storage migrations would have no storage.search_v1_optimised() and its storage.search() would not call it. Safe to say our rollout was not foolproof. Sorry Gary, should've sent a heads up to yall. Any similar issues with Auth and Storage please direct them to support tickets 🙏 P.S. we've retrospectively fixed potentially affected projects, but please let us know if you're still seeing these.

DamienOP•2mo ago

Ok, thanks for the reply, we just got even worst issue now... In the logs we can find a lot of schema "app_storage" does not exist For the following query that is executed by supabase_admin

WITH
-- Recursively get the base types of domains
base_types AS (
  WITH RECURSIVE
  recurse AS (
    SELECT
      oid,
      typbasetype,
      typnamespace AS base_namespace,
      COALESCE(NULLIF(typbasetype, 0), oid) AS base_type
    FROM pg_type
    UNION
    SELECT
      t.oid,
      b.typbasetype,
........

WITH
-- Recursively get the base types of domains
base_types AS (
  WITH RECURSIVE
  recurse AS (
    SELECT
      oid,
      typbasetype,
      typnamespace AS base_namespace,
      COALESCE(NULLIF(typbasetype, 0), oid) AS base_type
    FROM pg_type
    UNION
    SELECT
      t.oid,
      b.typbasetype,
........

But now for every query we are doing to supabase we get this error: Could not query the database for the schema cache. Retrying.

garyaustin•2mo ago

Not sure about the query, But for the error restart your instance. Seems like postgREST has an issue. You could also check the PostgREST logs. Not sure how that would be related, but I don't know what was done by SB to "repair" storage. You also probably need to update the ticket as no guarantee the Supabase dev is still looking at Discord and this thread.

DamienOP•2mo ago

thanks, I've just re-resent a ticket and also restarted the database to try. Thanks for replying to the thread again... But all the app is down again 😕

garyaustin•2mo ago

You restarted DB or instance? What call is getting that error? And anything in the PostgREST logs (not Postres)?

DamienOP•2mo ago

Yeah I've just reloaded the instance: and got this in PostgREST logs:

"Failed to load the schema cache using db-schemas=public,graphql_public,storage,app_storage and db-extra-search-path=public,extensions. {\"code\":\"3F000\",\"details\":null,\"hint\":null,\"message\":\"schema \\\"app_storage\\\" does not exist\"}"

"Failed to load the schema cache using db-schemas=public,graphql_public,storage,app_storage and db-extra-search-path=public,extensions. {\"code\":\"3F000\",\"details\":null,\"hint\":null,\"message\":\"schema \\\"app_storage\\\" does not exist\"}"

garyaustin•2mo ago

Check the API settings. I think you or SB added that app_storage schema there and it needs to go away.

garyaustin•2mo ago

DamienOP•2mo ago

Your a crak bro Dammn You saved me Amazing, thank you so much 🙏🙏

murad IS the api•5w ago

hi guys

murad IS the api•5w ago

my CPU usage started to reach the max and because of that my project started to have downtimes time to time... it turns out the issue was because of a storage query select * from storage.search ($1, $2, $3, $4, $5, $6, $7, $8) which seems to be the same as Damien

murad IS the api•5w ago

In the screenshot the problematic function has 94% of the time consumed, i tried increasing the compute size from Small to Large and that didnt solve the issue, Im still reaching 99.98% of CPU usage sometimes did you also had this issue with the CPU usage @Damien ?

murad IS the api•5w ago

in this cases i query the storage

murad IS the api•5w ago

@bobbie tables help, please

Gaming

Programming

Error on storage Search

Did you find this page helpful?