I sometimes hear conspiracy theorists site the US nuclear program as evidence that grand projects can exist without information about them leaking out. But I know that there are actual examples of individuals knowing about the program prior to the bombs being dropped: Kodak, and the editor of Astounding Stories (although, that one might be apocryphal).
Definite knowledge that the US had a nuclear program during World War II was kept relatively limited, although by the end of the war the notion that something was going on was supposedly common knowledge among DC reporters and some scientific authorities. And there were many wartime leaks ranking from the small to the quite large, but most of them were suppressed after initial publication. The German and Japanese nuclear physicists appear to have been totally surprised by it, as were even the vast number of people who worked on it. By that standard, it was very successful at keeping its "big secret" despite a million avenues for leaks. The Soviets, of course, had several major spies in the project and so it was not successful at keeping it from them.
In terms of what it tells us about the ability of a government to keep a big secret, it is somewhat instructive, though not in the way that I think most conspiracy theorists suppose. Most of its secrecy was achieved through compartmentalization — most of the hundreds of thousands of people working on it only knew a tiny bit of the overall project, and not its goal. That was something that could be done with this kind of project (where most of its work was indistinguishable from other kinds of construction and operation activities) but would not be true of, say, someone trying to fake a moon landing.
But most importantly, its secrecy was very temporary. The Manhattan Project as a large-scale project only existed for 2.5 years. That is not very much time, especially during a war in which many secret projects existed and there were capabilities in place to actually suppress press publication (although all wartime censorship in the US was voluntary). The Manhattan Project security forces had to work literally every day to keep it from leaking out; they investigated over one leak or rumor per day, in the end. It required constant effort and at any moment they feared it would all come out. It wasn't just journalists they were worried about, it was Congressmen (who at times even threatened to talk about it on the open floor unless they were given more information), scientists from other countries, and even innocent leaks from people working on the project who didn't know better (like a general who leaked information about the size of the Oak Ridge plant and its importance, or the President of the University of California who bragged about how his laboratory was winning the war — neither of them actually knew what they were talking about, which is exactly why they didn't realize it was such a big deal to say anything about it). The fact that the Germans and Japanese didn't learn about it is more a reflection that they weren't looking for it than because the security team was so effective.
I think the Manhattan Project tells us, in brief, that there is probably a maximum size of a project like this if you want to keep it secret, and the information in it would have to be pretty segregable, and you would need a context in which people would be less likely to challenge demands for silence (like a patriotic war), and you'd need a time limit on the whole thing. For much more on this, there is an entire chapter on Manhattan Project secrecy measures — and their failures and successes — in my forthcoming book.
When I think about the a priori likelihood of conspiracy theories, I think about how many people would have to be involved, how many would know "the secret," and how long the secret would need to be kept to be successful. So the "Moon landing hoax" just doesn't work at all — far too many people (in too many countries) would need to be in on it, and you'd expect after many decades that many people would openly confess to having perpetuated the hoax. It's just not tenable on first approach (and certainly the shallowness of the arguments in favor of it don't help).
Whereas if you want to imagine that some shadowy forces in the CIA, FBI, and/or mafia were in on JFK's assassination... I mean, why not? The minimum size of the conspiracy is one person as it is (that's the Warren Commission's thesis), so increasing that to ten people or so isn't that hard, and one could imagine ten people keeping a secret like that, if they were the right people (and CIA, FBI, and mafia seem like the kind of people who can keep a secret, as opposed to career NASA employees or their Soviet equivalents). And certainly one knows that individual members of all three organizations had deep grudges against JFK. That doesn't mean it's true, but it means that by this measure there's no way to just dismiss it as implausible on the face of it. One still needs evidence, etc., to decide whether something is true or not. But I would put it in a different category than something like the moon landings.
After World War II, the US nuclear program became a different sort of secret — people knew it existed, and knew it had secrets, but didn't always know the contents of those secrets. That is a very different state of things than the wartime experience, in which the fact that there was a program at all was the thing be kept secret, which is very hard to maintain. Certainly the US government had many classified programs during the Cold War (and still does), however it has had very few things analogous to the Manhattan Project in terms of size and scope. The closest similarity in my mind is the National Security Agency, though even that is not quite the secret it once was.