Can a stock be “overvalued” on its own but still be the rational best choice for a portfolio?

Yes, this can happen, as illustrated here. Starting from that answer, think of each strategy as a stock (as though Alice and Bob are each playing "Warren Buffett" and running their own "Berkshire Hathaway"). Then with stock A or stock B alone you are guaranteed to eventually lose all your money, but with a combination of the two you will get rich. Of course, the return distributions assumed there are not very realistic, but it makes the point.

A well-diversified portfolio should include a wide variety of asset classses. Sometimes it may happen that all stocks in a particular class happen to be overvalued. In this case, if you still want that type of stock in your portfolio to balance it out, you'll need to purchase (or keep holding) overvalued stocks. You can minimize the impact by choosing the ones that are least overvalued.

It's also possible that your assessment that the stock is overvalued is simply wrong. While the market isn't always rational, "wisdom of the crowd" suggests that market valuations shouldn't usually be that far off. Of course, contrarians are betting that the market tends to overreact frequently -- you can't beat the market just by doing what everyone else is doing.

Finally, if you already own a stock that you think is overvalued, you have to weigh the profit you may be able to take with the transaction cost of selling it. This is why stock market analysts have "Hold" recommendations that are different from "Buy" and "Sell".

Let's assume a hypothetical (or real based on a possible opinion) world where petroleum companies are undervalued, but everything else is overvalued.

Should you invest all your money into petroleum companies?

No, because recently, petroleum company price moves in different direction than everything else. If for example Donald Trump and Iran agree on a ceasefire and free passage through the Strait of Hormuz, then the value of petroleum companies decreases, but the value of nearly everything else increases, because most companies benefit from low oil prices.

So by investing a suitable amount into petroleum companies, you are hedging against the risk of oil price, but by investing into other (overvalued) sectors as well, you are diversifying your portfolio.

Diversification and low costs are the only two free lunches in investing.