I'm not sure "up the totem pole" is really the best way to think about it — it's not a vertical chain so much as a "need to know chain." Some people who knew about it were not high "up the totem pole." Some of the people "up the totem pole" were not told about it at all (Harry Truman was not told of it while he was Vice President, for example).

In the political world, it was essentially reserved to a small group of people: the Secretary of War, the President, sometimes the Vice President (Wallace knew, Truman didn't), and eventually the Secretary of State (I am not sure when Byrnes was told — definitely by Potsdam, but I don't know about earlier). So a pretty small circle. A few Congressmen were told the basic project information late into it, to help expedite funding authorization. Many people were deliberately excluded.

In the military world, the Army Chief of Staff (George Marshall) knew for a long time. Several other top generals (like Hap Arnold) were told about it. The head of the Navy was kept out of initially (by Roosevelt) but eventually Leahy was brought in (again, I don't know exactly when, but at least 1944). Many generals were unaware, however, and complained bitterly about the fact that the secret "Manhattan" project was siphoning off so many resources (in 1943 the bomb project was responsible for 50% of all military construction in the entire country, a fact that did not go unnoticed).

So that's something of an answer: either very high up, or you had to be in a position where you knowing was considered useful. But it could be idiosyncratic, especially under Roosevelt (who played secrecy games). By the time of the Potsdam Conference (July 1945) it was far better known, because it had become part of war planning; I suspect most of the top officials who were not directly involved in its production gave it little attention until this point, especially after it was tested (17 July) and found successful.

Source: I am writing a book on the history of nuclear secrecy. Someday when it comes out you can have a better reference! But there is no published source on this question to my knowledge, just a lot of archival stuff. :-)

Are there any records that indicate what were Roosevelt's opinions or thought about the use of this weapon? I'm interested as to whether the use of these bombs would have been any different had he lived the rest of the year. For example did he have a different opinion that favored the bombing of a purely military target?