In the words of the National Security Adjudicative Guidelines[1]: "The illegal use of controlled substances... can raise questions about an individual's reliability and trustworthiness, both because such behavior may lead to physical or psychological impairment and because it raises questions about a person's ability or willingness to comply with laws, rules, and regulations."
Whether or not you agree with the policy, it's still the policy, and if it's enforced when non-famous people with security clearances use drugs (it is), it should be enforced when famous people with security clearances use drugs.
[0] 21 USC § 844: https://www.law.cornell.edu/uscode/text/21/844
[1] the official policy defining the criteria by which security clearances are granted: https://fas.org/sgp/othergov/intel/sead-4.pdf