When we typically speak of "drug (ab)use", we have in mind drug use that frustrates the exercise of reason or some other faculties and their use in accordance with reason in one way or another. Alcohol, for instance, taken in small amounts is fine because in small amounts, it have a relaxing effect that causes no harm and does not frustrate the exercise of reason. Indeed, if you're experiencing distress and anxiety, a small amount of alcohol can help restore the use of reason by calming the anxiety that may be in fact impeding the exercise of reason. But in large amounts, our use of reason, among other faculties, is very much frustrated, putting to one side the other harms this causes. And because reason is that by which we know the truth and discern good from evil and reason morally and make decisions, it is evil to frustrate that faculty. And in doing so, open yourself up to evil and making bad decisions. So taking such a drug, and in immoderate amounts, is itself an immoral decision. We have a moral obligation to be rational, and intentionally harming or acting against our own rationality is evil.
The same can be said of any drug. The small amounts of cocaine in Vin Mariani were not the amounts taken by drug fiends snorting it into their noses and rubbing it on their gums. I'm sure Leo XIII and Pius X were experiencing a bit more energy in a manner analogous to what coffee drinkers experience.
Some drugs, however, may simply be bad in any effective dose. Some speak of microdosing psychedelics. I have no knowledge of the effects of doing so, but it sounds a bit SV gimmicky and some studies seem to suggest that is the case. In any event, the doses typically taken, those that cause hallucinations and a warped sense of reality, do frustrate the exercise and use of reason, with the added risk of traumatic experiences that can cause lifelong problems. This is gravely immoral for just the reasons I've given, so the intuition that psychedelics are something to be avoided is correct. Intentionally causing delusional thinking is gravely immoral.
That people are feeding a morbid curiosity in psychedelics seems to track closely the spiritually barren and nihilistic age we live in. New Age and drug culture is a piss-poor substitute for the splendor of truth. They're not rooted in reason, but cheap emotion, and what we call the "spiritual" is ultimately a rational matter (though, of course, you may experience emotional states as a consequence). It is better to address the actual problem instead of escaping into a degrading, mediocre ersatz.