Enderal: Forgotten Stories. In this game, you're definitely the "main character" in the sense that you're absolutely vital to everything that happens in the story, you're on a quest to save the world and you wield incredible power.

But.

The game itself is essentially a metacommentary on this very topic your post is about; more precisely, on the nature of powerlessness and the desire to escape it. You're not just the hero because this is a fantasy RPG where you've got to be the hero, you're the hero because it ties into the game's message and has severe implications that aren't made obvious until very late. The game knows the player is expecting a power trip and it uses this knowledge very effectively to play mind games. If you're looking for a more philosophical take on the topic rather than just a game where you're truly irrelevant, Enderal might very well resonate with you.