The common opinion is an oversimplification at best.
The problem with buying a house using cash is that it may leave you cash-poor, forcing you to take out a home equity loan at some point... which may be at a higher rate than the mortgage would have been. On the other hand, knowing that you have no obligation to a lender is quite nice, and many folks prefer eliminating that source of stress.
IF you can get a mortgage at a sufficiently low rate, using it to leverage an investment is not a bad strategy. Average historical return on the stock market is around 8%, so any mortgage rate lower than that is a relatively good bet and a rate MUCH lower (as now) is that much better a bet. There is, of course, some risk involved and the obligation to make mortgage payments, and your actual return is reduced by what you're paying on the mortage... but it's still a pretty good deal.
As far as investment vehicles: The same answers apply as always. You want a rate of return higher than what you're paying on the mortgage, preferably market rate of return or better.
CDs won't do it, as you've found. You're going to have to increase the risk to increase the return. That does mean picking and maintaining a diversified balance of investments and investment types. Working with index funds makes diversifying within a type easy, but you're probably going to want both stocks and bonds, rebalancing between them when they drift too far from your desired mix.
My own investments are a specific mix with one each of bond fund, large cap fund, small cap fund, REIT, and international fund. Bonds are the biggest part of that, since they're lowest risk, but the others play a greater part in producing returns on the investments. The exact mix that would be optimal for you depends on your risk tolerance (I'm classified as a moderately aggressive investor), the time horizon you're looking at before you may be forced to pull money back out of the investments, and some matters of personal taste. I've been averaging about 10%, but I had the luxury of being able to ride out the depression and indeed invest during it.
Against that, my mortgage is under 4% interest rate, and is for less than 80% of the purchase price so I didn't need to pay the surcharge for mortgage insurance. In fact, I borrowed only half the cost of the house and paid the rest in cash, specifically because leveraging does involve some risk and this was the level of risk I was comfortable with. I also set the duration of the loan so it will be paid off at about the same time I expect to retire. Again, that's very much a personal judgement.
If you need specific advice, it's worth finding a financial counselor and having them help you run the numbers. Do NOT go with someone associated with an investment house; they're going to be biased toward whatever produces the most income for them. Select someone who is strictly an advisor; they may cost you a bit more but they're more likely to give you useful advice.
Don't take my word for any of this. I know enough to know how little I know. But hopefully I've given you some insight into what the issues are and what questions you need to ask, and answer, before making your decisions.
