You're probably right. I wasn't alive nor in the US during that period, so can only infer from what I've seen and read, but I would wager that even the staunchest opponents of the US space program back then couldn't have helped but feel pride of what their country accomplished in such a short time.
And even if the majority opposed it, I still think that overall the amount of supporters then would've been greater than the amount of people who support it today. We're living in a time of ignorance and public disinterest in science that Carl Sagan predicted in the '90s[1], which didn't exist in the '60s. That spirit of optimism was partly what enabled such grand scientific projects, and I think most Americans were deeply moved by the words of JFK in that historic 1962 speech[2].
And even if the majority opposed it, I still think that overall the amount of supporters then would've been greater than the amount of people who support it today. We're living in a time of ignorance and public disinterest in science that Carl Sagan predicted in the '90s[1], which didn't exist in the '60s. That spirit of optimism was partly what enabled such grand scientific projects, and I think most Americans were deeply moved by the words of JFK in that historic 1962 speech[2].
[1]: https://www.goodreads.com/quotes/632474-i-have-a-foreboding-...
[2]: https://www.youtube.com/watch?v=WZyRbnpGyzQ