Not to mention drugs such as Salvia, Ibogaine, and Mescaline (mostly in South and Central Americas), as well as DMT (in the form of ayahuasca; Americas and parts of Africa), though admittedly these were more of a ritualised rather than recreational use. Things like Shamanistic vision quests, communing with dead ancestors, and adulthood trials.
This too. Psychoactive drugs were commonly used for these practices rather than privately, as far as I know.
Actually, many psychoactive drugs, like peyote or the like, that were part of some kind of shamanistic/ritualistic purposes were attended specifically because of the potential dangers of the drugs they were using - when letting a young person go on a vision quest, the shaman would often stay with them to make sure they don't overdose on the stuff, or dehydrate, since generations of working with the stuff let them know exactly how dangerous any substance they were taking actually was.
(Peyote, from what I've heard of the drug, is very nasty to take. It causes you to frequently vomit unless you have developed quite a resistance to it, and you are in serious danger of dehydrating because you simply vomit out all your fluids. Because of this, any use of peyote is done with a large number of others, including those who don't take the peyote, because they need to watch over the partakers, and ensure they don't vomit themselves to death.)
It is believed that psychoactive drugs were used by many "prophets" back around the time of the Greeks or Persians or the like. The Oracle at Delphi, for example, was believed to have gotten the powers of foresight from a hallucination-inducing gas in the cave the oracle stayed in at Delphi.
OPIATES on the other hand, were basically sold like any other product on the market. Although there was no Heroine or Morphine, opium was often taken directly - you just plain drank it, often in alcohol, as a painkiller, or just a way of relaxing. Basically, it was completely unregulated, and would even be supplied to soldiers for free by their generals if they wanted to gain some popularity, as a painkiller that could really get one's mind off of things would be of obvious use to soldiers in a foreign and hostile land. (It even gained a reputation as a "gift of the gods", and a drug of mercy.)
Opium was an absolute bane of China, especially during the time of the British colonization of the nation, where as much as a third of the country was addicted to the drug, (specifically, smoking opium-laced pipes) to the massive profit of their British drug pushers. This basically paralyzed China, as so much of its population was in a permanent drug haze that it was impossible to throw off their British oppressors or rebuild their nation with so much of their population almost total deadweights on society who could hardly even stagger out of their opium dens.
America, before the FDA, was plagued by "Patent Medicines", which were over-the-counter drugs sold in any general store, most of which used alcohol and opium as essentially their only ingredients. As the History Channel documentary I saw on it explained, "It really would seem like a cure-anything wonder drug. No matter what ache or pain you had, if you drank something that was 60% opium, you'd feel better for a while." This was particularly harmful, as children were often spoon-fed opium as a way of just shutting them up by their mothers, who were often downing plenty of the stuff, themselves, to make the bitterness of being stuck in domestic "bliss" all her life a little easier.
Those last two examples are obviously more modern, but private drug use is fairly hard to track in earlier times, since it was seen as totally normal and somebody else's business. Basically, opium was a totally unregulated drug for all of human civilization up until the start of the 20th century, when chemistry gave us drugs that were actually powerful enough to really force governments to restrict them.
Fantastic alchemically refined drugs, however, would rationally force a change to such a thing. It's obvious that, while recreational drugs existed for the consumption of anyone who could buy them,
poisons were obviously regulated substances, and it's probably more likely that an alchemist will accidentally create something generally bad for you rather than something good (or at least, good without devastating side-effects). If you're brewing something toxic, it makes all the sense in the world to restrict access to it.
At any rate, the basic thing I'm trying to get at here is that you can't really think about new features from a modern perspective too much. The appropriate thing to do is attempt to research how these things were done in a similar time in real-world history as a baseline, and then speculate based on how the DF world in particular is different.
While realism is always a good goal to stretch for, as it makes the game more relatable, sometimes, interface just needs to be designed to make a good game first, and then you can work in what would be as realistic a way of producing that as possible.
What we need is a way of telling dwarves when it is OK and when it is not OK for them to consume potions or drugs or otherwise use single-use items, so that it can be implimented into the game in as sane and useful a way as possible. If this involves a "pharmacy", that's just window dressing what it's called or how it is supposed to operate, exactly.
I'd also caution you not to get too hung up on a word.
I've been using "drugs" and "potions" interchangably because all these things are is a consumable item with a syndrome attached to them. "Drugs" are just one way of expressing something that can just as easily be "magic potions", since there's no telling what syndromes we might get down the road. We might get a "drug" that lets dwarves breathe fire. Or run around very fast before exploding, as previously mentioned.
This is, obviously, one reason why I was talking about ways to control when and how they get consumed (or shoved down murder holes to force "consumption" by enemies, as the case may be).
If we are talking about potions with potential negative consequences for a fortress that dwarves may want to just take for funsies, it makes perfect sense to keep those drugs behind lock and key with some sort of trusted "keeper of potions", whether we call them a "pharmacist" or not. If this gets rolled in with the interface for how and when consumables are consumed, then it makes perfect sense. (Of course, I still prefer it to be a part of an expanded Priorities List, so that we can view all our fiddling with dwarven AI from one single screen for the purpose of streamlining the interface.)