Mange storage from rpc/postgresql function

[Vinzent03]

In my current setup, I have a list of objects in a database table. For each object there is a dedicated folder in storage to store files for that object. I want to create a trigger that deletes the folder/all files from that object from storage on row delete in the database table. How can I accomplish this?

The only way to access storage that I know is via the client libraries which use REST apis. Do I have to use them in a postgresql function too?

[w3b6x9]

@Vinzent03

How can I accomplish this?

You can set up a trigger like this in the Supabase dashboard SQL editor:

CREATE TRIGGER your_trigger_name
AFTER DELETE ON your_database_table
FOR EACH ROW
EXECUTE FUNCTION delete_folder_and_files();

You have to make sure that the delete_folder_and_files function RETURNS trigger when you define it and you can access the deleted row with the special variable OLD so you know which folder/files to delete from storage. You'll most likely need to define the function as a SECURITY DEFINER one since the storage schema and its tables limit roles from performing certain operations. See Writing SECURITY DEFINER Functions Safely section in https://www.postgresql.org/docs/13/sql-createfunction.html for more information.

[Vinzent03]

Sorry I should have been more precise. I know how to create a trigger and function, but I don't know how to delete a folder/files from storage. Is the only way to delete the rows in storage.objects, because I think that's only metadata and I don't know if it really deletes the file from storage.

[inian]

Hi @Vinzent03, yup that doesn't delete the underlying object and only deletes the metadata and it will still be counted towards your object storage quota.

[Vinzent03]

Then this is not an option. I guess the only way is to call the REST api from the postgresql function.

[zakaria-chahboun]

@Vinzent03 visit #3124

[Vinzent03]

As @zakaria-chahboun suggested, see #3124