Log User Media Uploads to the Database

[asimons04]

Requirements

• Is this a feature request? For questions or discussions use https://lemmy.ml/c/lemmy_support
• Did you check to see if this issue already exists?
• Is this only a feature request? Do not put multiple feature requests in one issue.
• Is this a UI / front end issue? Use the lemmy-ui repo.

Is your proposal related to a problem?

As of 0.17.4 (have not tested 0.18.0 RC), it is currently possible to upload media to an instance when creating a post, grab the returned image URL, and then link or reference that URL from anywhere and use the Lemmy instance to host their media.

The user does not have to even save/submit the post, so this entire process is invisible to the administrators.

Additionally, purging an item does not seem to delete any images associated with it; if someone still has the URL, it's still accessible.

Describe the solution you'd like.

When a user uploads media, the backend should log the upload into the database into a media table.

The media table should track, at minimum, the local user id of the uploader, the filename and delete token returned by pictrs, and the post/comment id where it is referenced.

The service worker should periodically check, ideally at an admin-defined interval, for any orphaned media (entries in the media table without a post/comment id or with a post/comment ID that has been purged), and would call the pictrs delete endpoint on those using the stored values.

This would also work toward the goal of enhanced moderation tools as you would now have a list of media that can be correlated against a user as well as the necessary information to remove any problematic media; additional tools can utilize and build from this.

Describe alternatives you've considered.

Other than configuring the user-facing reverse proxy to deny POST requests to the /pictrs path to block user uploads, I can't think of any alternative solution as there doesn't appear to be an option to disable user-provided media.

Additional context

The issue is mentioned in #2277 but doesn't seem to have a dedicated issue of its own.

A very similar issue is mentioned in #1331 and was closed with a supposed fix via #1809 but that doesn't cover the full problem and doesn't seem to actually delete the images from pictrs.

[techno156]

Just adding on a possible use case, but it would also be handy for dealing with spam. Thus way, any image upload spam is less likely to clog up a server with orphaned images, which might not be an issue for the time being, but could become a problem as the instances grow, and the spambots become more sophisticated.

[dessalines]

This would add a pictrs_upload table, with the columns local_user_id, pictrs_id . That would be able to help with all purging, deleting, and would also solve #3504

[db0]

Please also add a nullable column "post_id" so that the corresponding comment or post can also be traced

[dessalines]

Fixed by #3927