I am a staunch supporter of the space program, but... the Apollo program also got cut before it had achieved its objectives, and probably never would have made it to the moon at all if Kennedy had lived.
The space program's history is very different from what most people imagine it to have been.
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.