Add trigger to storage.objects for DELETE

[ds797]

Problem

Hi, I've seen some discussions mentioning the lack of a method to delete Storage objects from SQL (deleting a row from storage.objects will leave the actual file orphaned, therefore it's only accessible from the API). This seems like a major flaw/concern when rows in public tables need to be linked to storage objects. For example, a malicious user could create any number of rows and upload files that are referenced by those rows, then comment out the Storage API code to remove the object but leave the code to delete the row. This would result in dead files consuming storage space and potentially causing the developers extra fees.

Solution Attempt

My initial solution was to add a trigger on my public table that would remove a row from storage.objects, but that leads to objects getting orphaned.

Solution

My proposed solution is to add a trigger on the actual storage.objects table, so when a row is deleted it will ALWAYS remove it from S3. This seems like a relatively simple/feasible solution that would remove the need for any warnings on orphaning objects, or even the possibility of orphaned objects, if my thinking is correct. Please let me know if there are other factors at play or if there's a reason why this wouldn't work.

Thanks!