The main reason some players don't want a powerful espionage system is because it's seen as a threat to the own preferred playstile. Suddenly warfare (or tech rush or economy rush or whatever) has a counterpart or even some options to stop it if you can't adapt. It should be able to prevent wars even before started, maybe because of a collapse of infrastructure or missing resources. Espionage is a cloak and dagger game and you have to plan ahead and should not get caught. Most of the time it is disrupting against the target and that how it should be!
Certainly narratively I think you're right that the idea behind espionage is that it attacks or undermines the other empire. And in theory, I absolutely love your RPS idea above of Warfare > Diplomacy > Espionage > Warfare. (Personally I would reverse it. I would have warfare defeated by alliances, which can be undermined by espionage, which can be defeated by a direct assault. But both are great options.)
I think where I would disagree with you is this quoted part.
I'm sure that a few people don't want mechanics that undermine their playstyle, but by and large I don't see that as a major complaint. I think the reason players don't enjoy powerful espionage systems is that they tend to feel like uncontrollable trolling.
Generally speaking, 4x games deal with espionage through an RNG. You have ways to increase or decrease your odds of winning an operation, then you launch it and see if it works. For the person who launches the operation that's lots of fun. You get narratively clever new ways of disrupting other players' strategies.
However the player on the receiving end doesn't participate in any of this. They can set up some passive defenses, but there's no give-and-take when someone launches an espionage mission at you. You just get a periodic pop up window that says something happened.
This is frustrating enough when it's a periodic building sabotage or tech theft. Being on the receiving end of a powerful espionage system, though, is incredibly frustrating. You can watch your entire strategy get blown up because you lost an invisible die roll. It feels more like trolling than strategy gaming.
That's the balance, and no 4X game that I've ever played has managed to strike it. In theory I think you're right that espionage is about asymmetric warfare and attacking the enemy empire. In practice, however, I've never seen a 4X game build a powerful espionage system that doesn't feel like an infuriating "You Lose" button that pops up at random.
Personally, I've always wanted to see the espionage as essentially a "decision maximization" system. I feel like the right goal for espionage should be that it lets an empire make far better decisions, and so achieve much better results, than an empire without good intelligence. Essentially "punching above their weight." You should be able to design and swiftly retrofit a fleet that's tailor-made to counter theirs. You should be able to piggyback off your opponents' advantages.
And as far as offensive operations, I would give espionage operations designed to capitalize on an opponent's mistakes. For example, I would give players a high-cooldown ability to evade the defenses in a single system ("Blind the Sensors") or turn a single FTL inhibitor on its owner. Players should know better than to stack all of their defenses in a single system or put their fleet in a single doomstack, so an espionage system that capitalizes on those mistakes is one that you can plan ahead for. But even that still might not work. Like I said, I've never seen it done successfully.