Delete Project Scoped API Tokens When the Project is Deleted

[Julian]

What's the problem this feature will solve?
On the rare case that a project is deleted entirely from PyPI, a user's scoped API token(s) to that project still remain present on their account security page.

Describe the solution you'd like
Once the project is deleted, delete the API tokens automatically as well.

Additional context
I just noticed this when performing said "rare case" -- I'm not sure it's a bug since I can't think of anything that goes particularly bad myself, so this may be quite minor and just unsightly (which is why I marked it a feature request), but passing it along nonetheless.

[ewjoachim]

Just to clarify, in case someone would be wondering: if someone else would be to create a new project under that name, the existing token wouldn't automatically give the old token bearer the capacity to upload packages on this new project. Project scoped tokens still require their owner to have upload right on the uploading project.

While we're at it, I guess we may also want to remove one's project scoped tokens if that person is removed from the project by other owners.

I'm noting though that this would require either of the following:

• Filtering token JSONB caveats field through SQL, which would probably not be ideal in conjunction with Use pypitoken to generate, check, introspect tokens #9264 where we try to decouple the caveat format information from the warehouse code.
• Using Python to analyze all the tokens someone owns when their right on a project disappear (through deletion or role removal) to find the project-scoped tokens (let's hope no one has multiple thousands of tokens, but if they did, I'm guessing this specific check would be minor compared to simply displaying their project page). Still, it would probably be the best solution. It might be interesting to do it in a celery task.

[woodruffw]

• Using Python to analyze all the tokens someone owns when their right on a project disappear (through deletion or role removal) to find the project-scoped tokens (let's hope no one has multiple thousands of tokens, but if they did, I'm guessing this specific check would be minor compared to simply displaying their project page). Still, it would probably be the best solution. It might be interesting to do it in a celery task.

This is the cleanest solution, but #11272 will throw a wrench in it (since it'll make users optional on macaroons). In other words, we won't be able to rely on role changes on users as a reliable trigger for when to purge a macaroon.

Ultimately, we may have to go with a SQL query that pokes into the JSON caveats.