I'm not a tattoo person and neither is my wife. I can respect the art of the and have seen some true beauty but also just some that I saw and said "why?" I am glad my wife doesn't have any and has no desire to-if she wanted one that would certainly be her call but that was also something I felt strongly enough about that we had a conversation about it early on when we were dating.
If that's your thing, though, more power to you.
On the original question-I think well done Photoshop work(or other post processing-Photoshop is almost a generic term now) is often subtle enough that you don't notice it.
I rarely use PS, and do most of my editing in Lightroom. There are a few times PS comes out for the "heavy lifting." One is that the spot healing and clone tools are MUCH better than in Lightroom, to the point that film scans basically have to go through Photoshop for me to be happy. Other tools like lens correction and perspective correction are getting much better in Lightroom, but still IMO are inferior to PS.
Another one is dealing with difficult lighting in post. As an example, and nothing crazy, but our dog is black. He's jet black-almost like he's rolled in a coal bin or been slathered in shoe polish. For Christmas this year, my wife wanted a photo of the three of us in front of the Christmas tree for a card. I pulled out every lighting trick I could(short of the studio lights, which are still in storage after moving), but absolutely could not get the result I wanted where you could see us clearly and also have him be anything other than a black blob.
After trying everything I could in Lightroom, I finally pulled the DNG over to Photoshop and went to work. It took me a little while since I don't do this stuff often, but I masked the dog off into a separate layer then adjusted the exposure on him independent of us. We got a useable card...
On darkroom manipulation, stuff like dodging and burning Darkroom 101(or maybe 102). It takes some skill, but is easy to play with especially since paper is relatively cheap and you can see the results. When I'm doing a print as a gift or something like that, I'll go through a dozen cut sheets to dial in what I want in a particular area, at least one or maybe 2-3 full size ones to set exposure(unless it's a huge photo, then I'll spot check exposure with an 8x10). My favorite exposure "meter" is one of the old Kodak pie-chart overlays, but that's a different discussion.
The next level up, and pretty much a lost art, is full hand-retouching of negatives. I have a "retouching station" somewhere in storage I have played with a bit. It's essentially an 8x10 light table that vibrates gently to aid using your retouching tools. To do it, you first generally apply an etching solution to add "tooth" to the base side of the film, or use a film that already comes that way. Kodak TXP320 is the only I know of. I have some in 220 put back in the freezer, but it's now only made in sheet film(which is much easier to retouch anyway thanks to its size). You then use a VERY sharp pencil to add density where needed, of course blending it in to the surroundings. Things like this make quick work of things like skin blemishes in portraits(Mole? It will show up as a clear or at least lighter spot on the negative, and you just color it in). The more advanced technique is working on the emulsion side, where you might use a tiny sharp knife to cut off parts of the emulsion. Back in the day, there were retouching masters who might spend hours on something like a Hollywood glamour portrait. Of course, you can do this stuff on color too using dyes, but that's a totally different level of skill.