Bar the RX7 and early RX8s, they aren't really that unreliable - they just require a little more maintenance.
Oil consumption isn't as bad as some would have you believe, and it isn't nearly expensive as you'd think either, seeing as they don't require synthetic oils. There are many BMW and Honda engines that consume oil as well, yet this is never a consideration when buying such a car.
They aren't massively powerful in NA form, but will make (and take!) silly power under FI as proven by just about any single turbo RX7 you look at. Not that 230BHP (or 250BHP JDM) is particularly bad out of the RX8 - not different to the kind of power that say E46 330s of the time were making, so I'm not sure how people deduce they aren't particularly powerful.
High fuel consumption is a problem, and many of your average punter isn't willing to run a car averaging 16MPG. Lots and lots of RX8 owners do though, its a price worth paying for such an awesome engine IMO
Their pros are an awesomely linear power delivery in NA form, which is great for actual performance, but not so great for your 'butt dyno'. Their size allows them to be mounted further back and lower down than your traditional piston engine - lower centre of mavity and having the engine closer to the middle of the car is only ever going to be a good thing and is partly what the RX7 and 8 owes its poise and such balanced handling to.
Oh, and the high revs are awesome. A standard RX8 revs to something like 9500RPM, and will happily rev to ~14000RPM reliably (although would require a bit of work to make power there!)
They aren't really that light at all though.
I guess what it boils down to for manufacturers is an engine technology that is expensive to develop and has limited demand.