I consider myself to lean Libertarian (small, limited, well-defined federal government), and I'm neither celebrating nor lamenting the decision.
This decision is not a usurpation of legislative powers. It has always been the case that the federal courts can (and should) strike down unConstitutional state laws... unless you were perfectly happy with Jim Crow laws. (In fact, I don't remember you on that side of the the WBTS threads. I should ping all those usual suspects... they'd have a conniption if they saw you supporting states rights, LOL.)
While I'm all for a limited federal government, one of their appropriate functions is to strike down unConstitutional state laws (although it should be struck down in the lower state courts). Nothing gives the states the power to usurp the Constitution. SCOTUS has just declared that the Constitutional does not allow sodomy laws of this kind. I can see the results being Constitutionally supportable and consistent, but I would have though that an Equal Protection argument would have been far better.
My main worry is that this logic will be extended to marriage ("since two men can consent to it, and it is allowed when a straight couple consents to it, then it should be allowed"). My argument against would be that the institution is a religious one, and that the state can only rule over civil institutions. Then, of course, the states would create civil unions, and give them the same rights and benefits that married couples enjoy. It is coming.
What are the WBTS threads?
Jim Crow laws were what the 14th amendment was about, well part in question anyway. The question here is twofold, is privacy one of the "liberties", or "priveleges and immunities" that states are not allowed to violate (without due process in the case of liberties) which leads to the second question of whether such privacy extends to acts of sodomy, or merely to enforcement actions. My read is that "privacy" isn't about what can or can not be made illegal, but rather about enforcement actions. Even the privacy, such as it is, of being "secure in one's person, house, papers and effects", protected by the 4th amendment is only secured against unreasonable searches and seizures. It does not protect any an all actions taken in private. The only possible place to find a right of privacy is in the 9th amendent, but to find one there, and one that protects private sexual conduct at that, one would have to submit some evidence that such a right was recognized at the time of adoption of the 9th amendment.. and that would be a tough row to hoe, IMHO. It would be a lot easier to find a right to smoke pot or opium in private, or even in public, in the 9th amendment, since such things weren't, for the most part, criminalised until the 20th century in most places.
All this is not to say that the states aren't free to put privacy, even sexual privacy, rights into their state constitutions, or even merely pass laws to protect, or recind laws against, such acts.