Yes, a renaissance artwork that contains naked women, actual hardcore porn, and a Russian soldier being blown to pieces in Ukraine, should not occupy the same "NSFW" tag...
Of course there's going to be grey area stuff, but 3 buckets like this is much much better than a single "everything people might not want to see" bucket.
I'm not sure my concern would still be blurred lines and people either forgetting to use tags or not realizing how or when to use them. For example a news article about police violence getting tagged with the violence tag on some instances but not others.
I'm also opposed to auto or over moderation and I feel like it's one of those things that could potentially push Lemmy in that direction eventually.
You're only seeing celebrity pics because that is the only SFW community on lemmynsfw. By default most lemmy instances hide NSFW posts unless you're logged in with an account set to show NSFW. They are running 0.18.3-nsfwpatch, so if you navigate there directly you will see everything, but viewing lemmynsfw logged out from another instance will only show you celebrity pictures.
So Lemmy is the Linux of smut, watch a 15 min tutorial and you'll probably have it set up. If not, it's comprehensive troubleshooting time! That's always the sexiest part anyways.
You need to enable 'NSFW' content in settings, which probably requires an account. Without that, you just see 'SFW' content (on feeds, at least), which is as you describe
At this point just have a tag system and let people vote on tags. It's a great way to get to reddit-like niche content curation at smaller acale across many smaller instances.