Suppose I submit a paper to Journal A. Under what circumstances can I then ethically withdraw it?
Upon receipt of the reviews. The review process can be thought of rather like the (English) law on contracts. I offer Journal A a paper in a certain form. They think about it for a while, and then (typically) reject my initial proposal, but offer to publish a related paper ("If you add X and Y we'll take it"). Typically, I then make revisions and submit them, forming a new "offer". Eventually, we reach a stage where the proposal is acceptable to both parties, and the paper is accepted. However, I'm under no obligation to continue to negotiate with the journal if at some point I decide that I don't like the terms they're offering (e.g. the reviewers ask for changes that I don't want to make). Does it make a difference if the reviews are largely positive, instead of largely negative? Intuitively, I'd say it does, but I'm struggling to come up with a sound justification for this. [Edited to add:] I suppose the difference is that withdrawing after good reviews would seem to indicate that I'm acting "in bad faith"; see below.
If I discover a serious flaw in the paper. Clearly, I have a responsibility to withdraw it as soon as the flaw is recognised, to avoid wasting everyone's time. Once the flaw has been fixed, the paper may well look quite different (in content); thus, it seems implausible that I am bound to choose the same journal upon resubmission.
If I realise the paper could be significantly improved. This is a trickier one. You might well argue that it was unethical of me to submit it in the first place, as it wasn't "complete". On the other hand, this could easily arise without me being at fault: perhaps someone else has just published a new statistical test which provides additional insights into my results; perhaps I'm working in a field where data is sparse, and an additional set of data points have just been obtained. It seems weird to suggest that it's wholly unethical to withdraw the paper and improve it. (Indeed, it seems arguable that it's more ethical than allowing the incomplete paper to be published, and then immediately writing a follow-up.) Again, when it comes to resubmission, it makes sense to decide the venue based on a fresh assessment of the merits.
Furthermore, I don't see that these latter two points necessarily cease to apply once the paper is "accepted" (rather than "in review"). Certainly, I'm still responsible for fixing any flaws. On the other hand, in the "legal" analysis, acceptance constitutes the making of a contract, which cannot then be broken without consequences.
So, to go back to the original question: if I (a) originally submitted in good faith, and (b) would need to do some nontrivial amount of work to adapt the paper for the more prestigious journal, it seems that it's not unethical for me to withdraw with the intention of "moving upmarket", at least provided I do it before acceptance. After acceptance, it is probably unethical.
However, there are a lot of interesting grey areas. Do I have a responsibility to my funding providers to get maximum exposure for the research they support? What if my paper will directly help, say, a cure for cancer - so that getting it widely known in the relevant community has positive implications for humanity?
The main argument against permitting withdrawal would appear to be the fact that it wastes people's time. Again, a legal comparison is instructive: if you walk away from contract negotiations, you are not in any way breaching the contract: it hasn't yet been made. However, if the other side think they can argue you weren't acting in good faith, you may well find yourself being sued for their costs. However, I would class that as a business matter, not an ethical one.
Finally, to address a point made in the comments: does an editor have a responsibility to inform an author that they're underselling their work? I'm not sure that they do (just as, provided I have not made any misrepresentations, I'm not obliged to tell a prospective buyer that he's offering me too much money for my house). If editors start second-guessing authors' choices in submitting to this or that journal, the system would quickly descend into chaos. However, reviewers (who act like the legal advisors on the house-sale analogy) may well be obliged to make the significance of the work clear (though, perhaps, not to go as far as recommending the author take one course of action over another).