The Fremen religion really was a sham. Paul manipulates them by using their religion to get his vengeance and ultimately the power corrupts him, too.
So though it seems like he is a heroic messianic figure, he actually becomes a fallen hero who leads to a war that kills more than 60 billion people.