While these certainly don't qualify as "the" greatest rivalries, they are definitely "my" greatest:
Most Royals fans I know deeply hate the Yankees. The KC A's were treated like a farm team by the 50s and 60s Yankees. Then we had the great playoff series between KC and NY in 1976, 77, 78, and 80. Then the "pine-tar" incident in 1983. So there's some great history there, even if things have been one-sided lately.
Royals-Cardinals is more of an artificial rivalry, fueled quite a bit by Don Denkinger's call in Game 6 of the '85 series. And with interleague play, we basically get to relive the series every year. My wife is a Cardinals fan from St. Louis and we went to a Royals-Cardinals game the day after our wedding. About 30 members of our families were there, and the Royals won, of course (they can always get up for the meaningless games)
Now when we go to interleague games, we take our son, who has a STL cap and a KC jersey.
I often remain high in the stadium, looking down on the men moving over the earth, dark as ants, each sodding, cutting, watering, shaping. Occasionally the moon finds a knife blade as it trims the sod or slices away a chunk of artificial turf, and tosses the reflection skyward like a bright ball.
--W.P. Kinsella "The Thrill of the Grass"