I don't think this is a fair characterization of the situation, but my knowledge is mostly based on one book, Reaching for the Moon, a Short History of the Space Race[0], that I recently read.
You're right that Kennedy was ambivalent about it, and that working with the USSR was floated, but ultimately he did decide to go ahead with it. And there were people opposed, but "extremely unpopular" doesn't sound right. Like, I think the majority of the population was broadly in favor.
[0] https://www.amazon.com/gp/product/030023046X/